Java/Database SQL JDBC/Predicate

Predicate implementation

   <source lang="java">

/* JDBC Recipes: A Problem-Solution Approach (Problem-Solution Approach) (Hardcover) by Mahmoud Parsian

1. Publisher: Apress (September 15, 2005)
2. Language: English
3. ISBN: 1590595203

• /

import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; import javax.sql.RowSet; import javax.sql.rowset.FilteredRowSet; import javax.sql.rowset.Predicate; import com.sun.rowset.FilteredRowSetImpl; public class Main {

 public static void main(String[] args) throws Exception {
   Connection conn = getHSQLConnection();
   System.out.println("Got Connection.");
   Statement st = conn.createStatement();
   st.executeUpdate("create table survey (id int,name varchar, age int);");
   st.executeUpdate("insert into survey (id,name,age ) values (1,"nameValue", 10)");
   st.executeUpdate("insert into survey (id,name,age ) values (2,"anotherValue", 100)");
   FilteredRowSet frs = new FilteredRowSetImpl();
   frs.setUsername("sa");
   frs.setPassword("");
   frs.setUrl("jdbc:hsqldb:data/tutorial");
   frs.setCommand("SELECT id, name, age FROM survey");
   frs.execute();
   System.out.println("--- Unfiltered RowSet: ---");
   while (frs.next()) {
     System.out.println(frs.getRow() + " - " + frs.getString("id") + ":" + frs.getString("name")
         + ":" + frs.getInt("age"));
   }
   // create a filter that restricts entries in
   // the age column to be between 7 and 10
   AgeFilter filter = new AgeFilter(7, 10, 3);
   // set the filter.
   frs.beforeFirst();
   frs.setFilter(filter);
   // go to the beginning of the Rowset
   System.out.println("--- Filtered RowSet: ---");
   // show filtered data
   while (frs.next()) {
     System.out.println(frs.getRow() + " - " + frs.getString("id") + ":" + frs.getString("name")
         + ":" + frs.getInt("age"));
   }
   System.out.println("--- Try to insert new records ---");
   // Try to add an employee with age = 90 (allowed by filter)
   frs.moveToInsertRow();
   frs.updateString(1, "999");
   frs.updateString(2, "Andre");
   frs.updateInt(3, 90);
   frs.insertRow();
   frs.moveToCurrentRow();
   frs.acceptChanges();
   // try to add an survey with age = 65 (not allowed by filter)
   frs.moveToInsertRow();
   frs.updateString(1, "123");
   frs.updateString(2, "Jeff");
   frs.updateInt(3, 65);
   frs.insertRow();
   frs.moveToCurrentRow();
   frs.acceptChanges();
   // scroll to first row of rowset
   frs.beforeFirst();
   // display rows in FilteredRowset
   System.out.println("FilteredRowSet after trying to insert Jeff (age 65) and Andre (age 90):");
   while (frs.next()) {
     System.out.println(frs.getRow() + " - " + frs.getString("id") + ":" + frs.getString("name")
         + ":" + frs.getInt("age"));
   }
   frs.close();
   st.close();
   frs.close();
 }
 private static Connection getHSQLConnection() throws Exception {
   Class.forName("org.hsqldb.jdbcDriver");
   System.out.println("Driver Loaded.");
   String url = "jdbc:hsqldb:data/tutorial";
   return DriverManager.getConnection(url, "sa", "");
 }
 public static Connection getMySqlConnection() throws Exception {
   String driver = "org.gjt.mm.mysql.Driver";
   String url = "jdbc:mysql://localhost/demo2s";
   String username = "oost";
   String password = "oost";
   Class.forName(driver);
   Connection conn = DriverManager.getConnection(url, username, password);
   return conn;
 }
 public static Connection getOracleConnection() throws Exception {
   String driver = "oracle.jdbc.driver.OracleDriver";
   String url = "jdbc:oracle:thin:@localhost:1521:databaseName";
   String username = "userName";
   String password = "password";
   Class.forName(driver); // load Oracle driver
   Connection conn = DriverManager.getConnection(url, username, password);
   return conn;
 }

} class AgeFilter implements Predicate {

 private int lowAge;
 private int highAge;
 private int columnIndex;
 private String columnName;
 public AgeFilter(int lowAge, int highAge, int columnIndex, String columnName) {
   this.lowAge = lowAge;
   this.highAge = highAge;
   this.columnName = columnName;
   this.columnIndex = columnIndex;
 }
 public AgeFilter(int lowAge, int highAge, int columnIndex) {
   this(lowAge, highAge, 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.lowAge) && (columnValue <= this.highAge)) {
       evaluation = true;
     } else {
       evaluation = false;
     }
   }
   return evaluation;
 }
 public boolean evaluate(Object value, int columnNumber) {
   boolean evaluation = true;
   if (columnIndex == columnNumber) {
     int columnValue = ((Integer) value).intValue();
     if ((columnValue >= this.lowAge) && (columnValue <= this.highAge)) {
       evaluation = true;
     } else {
       evaluation = false;
     }
   }
   return evaluation;
 }
 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.lowAge) && (columnValue <= this.highAge)) {
       evaluation = true;
     }
   } catch (SQLException e) {
     return false;
   }
   return evaluation;
 }

}

      </source>