package javatemplate1;

import com.sun.rowset.FilteredRowSetImpl;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;
import java.util.Properties;
import java.util.function.Function;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.sql.RowSet;
import javax.sql.rowset.FilteredRowSet;
import javax.sql.rowset.Predicate;
import jdk.nashorn.internal.codegen.types.Range;

/**
 *
 * @author Manas14
 */
public class JavaTemplate1 {
   // private static String dbURL;
    public static void main(String[] args) {
        // TODO code application logic here
       System.out.println("main block executing :MySQL");
       Connection c = null;  Statement st = null;
        ResultSet rs = null;// active connection database result
        //MySQL
    String dbURL = "jdbc:mysql://localhost/test?"; 
    String user = "Manas9"; String pwd = "Manas9237";
    String sql1= "Select *  from mysqlvisitor1b ";
        //postgresql 
     // String dbURL = "jdbc:postgresql://localhost:5432/pgsdemo1";
    //  String user = "postgres"; String pwd = "postgre_manas9";
     //String sql1= "Select *  from pgsvisitor1b ";
        // connecting to db
      try {
         c = DriverManager.getConnection(dbURL,user,pwd);
       st = c.createStatement(); 
       rs = st.executeQuery(sql1);
       ResultSetMetaData metaData = rs.getMetaData();
       int numberOfColumns = metaData.getColumnCount();  
       //int rownum = metaData.
// get name of the columuns 
         for ( int i = 1; i <= numberOfColumns; i++ )
         System.out.printf( "%-4s\t", 
                 metaData.getColumnName( i ) );
         System.out.println();
        //
         //Process.loaddata(rs, numberOfColumns);
          //Filetered Row Set
         System.out.println( "Filtered Rowset");
         FilteredRowSet frs = new FilteredRowSetImpl();
         frs.populate(rs);
         Process.loadfrwset(frs, numberOfColumns);
         //
         AgeFilter filter = new AgeFilter(30, 41, 4);

        frs.beforeFirst();
        frs.setFilter(filter);
        System.out.println("\nSetting state filter:");
         //
         Process.loadfrwset(frs, 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"); 
     // st.close(); con.commit();con.close();
    }
    
}

///
class Process {
   //private int empno;  private String empname; 
   // private String empjob; private float empsal; 
    //
public static void loaddata(ResultSet x1 , int n2)
        throws SQLException
{ int size = 0; 
    while ( x1.next() ) 
         {
             size++; 
            for ( int i = 1; i <= n2; i++ )
                try {
          System.out.printf( "%-4s\t", x1.getObject( i ));
          } catch (SQLException ex) {
            Logger.getLogger(Process.class.getName()).
             log(Level.SEVERE, null, ex);
                }
            System.out.println();
         }
    //displayMap(list1); 
    System.out.println("processed rows :: "+ size);
}
//
public static void loadfrwset(FilteredRowSet x1 , int n2)
        throws SQLException
{ int size = 0; 
    while ( x1.next() ) 
         {
             size++; 
            for ( int i = 1; i <= n2; i++ )
                try {
          System.out.printf( "%-4s\t", x1.getObject( i ));
          } catch (SQLException ex) {
            Logger.getLogger(Process.class.getName()).
             log(Level.SEVERE, null, ex);
                }
            System.out.println();
         }
    //displayMap(list1); 
    System.out.println("processed rows :: "+ size);
}
}

///-------
class AgeFilter implements Predicate {
  private int lowValue;  private int highValue;
  private int columnIndex;  private String columnName;

  public AgeFilter(int lowValue, int highValue, int columnIndex, String columnName) {
    this.lowValue = lowValue;    this.highValue = highValue;
    this.columnName = columnName;    this.columnIndex = columnIndex;
  }
  public AgeFilter(int lowValue, int highValue, int columnIndex) {
    this(lowValue, highValue, columnIndex, "age");
  }
  public boolean evaluate(Object value, String columnName) {
    boolean evaluation = true;
    if (columnName.equalsIgnoreCase(this.columnName)) {
      int columnValue = ((Integer) value).intValue();
      if ((columnValue >= this.lowValue) && (columnValue <= this.highValue)) {
        evaluation = true;
      } else {
        evaluation = false;
      }
    }
    return evaluation;
  }

  @Override
  public boolean evaluate(Object value, int columnNumber) {
    boolean evaluation = true;
    if (columnIndex == columnNumber) {
      int columnValue = ((Integer) value);
      evaluation = (columnValue >= this.lowValue) && (columnValue <= this.highValue);
    }
    return evaluation;
  }

  @Override
  public boolean evaluate(RowSet rs) {
    if (rs == null) {
      return false;
    }
    FilteredRowSet frs = (FilteredRowSet) rs;
    boolean evaluation = false;
    try {
      int columnValue = frs.getInt(this.columnIndex);
      if ((columnValue >= this.lowValue) && (columnValue <= this.highValue)) {
        evaluation = true;
      }
    } catch (SQLException e) {
      return false;
    }
    return evaluation;
  }  
}