package javatemplate1;
// MYSQL table mysqlVisitor1C
import com.sun.rowset.CachedRowSetImpl;
import com.sun.rowset.JoinRowSetImpl;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.sql.rowset.CachedRowSet;
import javax.sql.rowset.JdbcRowSet;
import javax.sql.rowset.JoinRowSet;
import javax.sql.rowset.RowSetFactory;
import javax.sql.rowset.RowSetProvider;

public class JavaTemplate1 {
    // example of 
       public static void main(String[] args)  {
        // TODO code application logic here
        System.out.println("main block executing ::mysqlVisitor1C");
       Connection c = null; //manage copnnection
       Statement st = null;// query statement support
       ResultSet rs = null;// active connection database result
       String sql1 = "Select empno, ename ,job, deptno  from emp where deptno = 30 ";
       String sql2 = "Select * from dept ";
      String dbURL = "jdbc:postgresql://localhost:5432/pgsdemo1";
      String user = "postgres"; String pwd = "postgre_manas9";
       String ID = "deptno";
      // connecting to db
      try {
          // connection>>statement >> Resultset 
      c = DriverManager.getConnection(dbURL,user,pwd);
       st = c.createStatement(); 
       //
       CachedRowSet cdept = new CachedRowSetImpl();
        cdept.setCommand(sql2);
        cdept.setUsername(user);
        cdept.setPassword(pwd);
        cdept.setUrl(dbURL);
        cdept.execute();
       //
         CachedRowSet cemp = new CachedRowSetImpl();
        cemp.setCommand(sql1);
        cemp.setUsername(user);
        cemp.setPassword(pwd);
        cemp.setUrl(dbURL);
        cemp.execute();
       
       //
        System.out.println("- Create join rowset");
       RowSetFactory rsFactory = RowSetProvider.newFactory();
        JoinRowSet jrs = rsFactory.createJoinRowSet();
        System.out.println("- Add two cached rowsets, joined on: " + ID);
        cemp.setMatchColumn(ID);
        jrs.addRowSet(cemp);
        jrs.addRowSet(cdept, ID);
        jrs.last();
         
         int numberOfColumns = jrs.getRow();
        System.out.println("Total rows: " + jrs.getRow());
         jrs.beforeFirst();
        Process.loadrset(jrs, numberOfColumns);
          

      } catch (SQLException ex) { ex.getErrorCode();
            String message = ex.getMessage();  
            System.out.println(message); 
      } finally {
          System.out.println("going through final block"); 
            try {
                 if (st== null || st.isClosed()) {
                } else {
                  st.close();
              }
                if (c != null && !c.isClosed()) {
                    c.close(); }
            } catch (SQLException ex) { ex.getErrorCode();
                  ex.getMessage();              
            }
        }
      
      System.out.println("dis-Connected to  database successfully"); 
    } 
      
}
//
class Process {
  
public static void loadrset( JoinRowSet   x1 , int n2)
        throws SQLException
{ int size = 0; int i = 0;
    while ( x1.next() ) 
         {
            int n1 = x1.getInt("empno"); 
            int deptno = x1.getInt("deptno"); 
            String ename = x1.getString("ename");
            String job = x1.getString("job"); 
            String place = x1.getString("loc"); 
            System.out.printf( "%-6s\t",n1);   
            System.out.printf( "%-4s\t",ename); 
            System.out.printf( "%-4s\t",deptno);  
            System.out.printf( "%-10s\t",job); 
             System.out.printf( "%-8s\t",place); 
            System.out.println();
            
         }
    //displayMap(list1); 
    System.out.println("processed rows :: "+ size);
}
}