Java/Database SQL JDBC/RowSet

Register RowSet Listener

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import javax.sql.RowSetEvent;
import javax.sql.RowSetListener;
import javax.sql.rowset.JdbcRowSet;
import com.sun.rowset.JdbcRowSetImpl;
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);");
    st.executeUpdate("insert into survey (id,name ) values (1,"nameValue")");
    st.executeUpdate("insert into survey (id,name ) values (2,"anotherValue")");    
    
    JdbcRowSet jdbcRS;
    jdbcRS = new JdbcRowSetImpl(conn);
    jdbcRS.setType(ResultSet.TYPE_SCROLL_INSENSITIVE);
    String sql = "SELECT * FROM survey";
    jdbcRS.setCommand(sql);
    jdbcRS.execute();
    jdbcRS.addRowSetListener(new ExampleListener());
    while (jdbcRS.next()) {
      // each call to next, generates a cursorMoved event
      System.out.println("id=" + jdbcRS.getString(1));
      System.out.println("name=" + jdbcRS.getString(2));
    }
    conn.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 ExampleListener implements RowSetListener {
  public void cursorMoved(RowSetEvent event) {
    System.out.println("ExampleListener notified of cursorMoved event");
    System.out.println(event.toString());
  }
  public void rowChanged(RowSetEvent event) {
    System.out.println("ExampleListener notified of rowChanged event");
    System.out.println(event.toString());
  }
  public void rowSetChanged(RowSetEvent event) {
    System.out.println("ExampleListener notified of rowSetChanged event");
    System.out.println(event.toString());
  }
}

RowSetSync Provider

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

# Publisher: Apress (September 15, 2005)
# Language: English
# ISBN: 1590595203
*/

import java.sql.SQLException;
import java.sql.Types;
import java.util.Hashtable;
import javax.sql.RowSet;
import javax.sql.RowSetInternal;
import javax.sql.RowSetMetaData;
import javax.sql.RowSetReader;
import javax.sql.RowSetWriter;
import javax.sql.rowset.CachedRowSet;
import javax.sql.rowset.RowSetMetaDataImpl;
import javax.sql.rowset.spi.SyncFactory;
import javax.sql.rowset.spi.SyncProvider;
import com.sun.rowset.CachedRowSetImpl;
public class Main {
  CachedRowSet crs;
  String stringColumn1;
  String stringColumn3;
  int intColumn2;
  public Main() {
    try {
      SyncFactory.registerProvider("MySyncProvider");
      Hashtable env = new Hashtable();
      env.put(SyncFactory.ROWSET_SYNC_PROVIDER, "MySyncProvider");
      crs = new CachedRowSetImpl(env);
      crs.execute(); // load data from custom RowSetReader
      System.out.println("Fetching from RowSet...");
      while (crs.next()) {
        displayData();
      }
      if (crs.isAfterLast() == true) {
        System.out.println("We have reached the end");
        System.out.println("crs row: " + crs.getRow());
      }
      System.out.println("And now backwards...");
      while (crs.previous()) {
        displayData();
      } // end while previous
      if (crs.isBeforeFirst()) {
        System.out.println("We have reached the start");
      }
      crs.first();
      if (crs.isFirst()) {
        System.out.println("We have moved to first");
      }
      System.out.println("crs row: " + crs.getRow());
      if (!crs.isBeforeFirst()) {
        System.out.println("We aren"t before the first row.");
      }
      crs.last();
      if (crs.isLast()) {
        System.out.println("...and now we have moved to the last");
      }
      System.out.println("crs row: " + crs.getRow());
      if (!crs.isAfterLast()) {
        System.out.println("we aren"t after the last.");
      }
    } catch (SQLException e) {
      e.printStackTrace();
      System.err.println("SQLException: " + e.getMessage());
    }
  }
  public void displayData() throws SQLException {
    stringColumn1 = crs.getString(1);
    if (crs.wasNull()) {
      System.out.println("stringColumn1 is null");
    } else {
      System.out.println("stringColumn1: " + stringColumn1);
    }
    intColumn2 = crs.getInt(2);
    if (crs.wasNull()) {
      System.out.println("intColumn2 is null");
    } else {
      System.out.println("intColumn2: " + intColumn2);
    }
    stringColumn3 = crs.getString(3);
    if (crs.wasNull()) {
      System.out.println("stringColumn3 is null");
    } else {
      System.out.println("stringColumn3: " + stringColumn3);
    }
  }
  public static void main(String[] a){
    new Main();
  }
}

---------------------------------------------------------------------

import java.sql.SQLException;
import java.sql.Types;
import java.util.Hashtable;
import javax.sql.RowSet;
import javax.sql.RowSetInternal;
import javax.sql.RowSetMetaData;
import javax.sql.RowSetReader;
import javax.sql.RowSetWriter;
import javax.sql.rowset.CachedRowSet;
import javax.sql.rowset.RowSetMetaDataImpl;
import javax.sql.rowset.spi.SyncFactory;
import javax.sql.rowset.spi.SyncProvider;
import com.sun.rowset.CachedRowSetImpl;
public class CustomRowSetReader implements RowSetReader {
  public CustomRowSetReader() {
  }
  public void readData(RowSetInternal caller) throws SQLException {
    System.out.println("--- CustomRowSetReader: begin. ---");
    if (caller == null) {
      System.out.println("CustomRowSetReader: caller is null.");
      return;
    }
    CachedRowSet crs = (CachedRowSet) caller;
    // CachedRowSet crs = (CachedRowSet) caller.getOriginal();
    RowSetMetaData rsmd = new RowSetMetaDataImpl();
    rsmd.setColumnCount(3);
    rsmd.setColumnType(1, Types.VARCHAR);
    rsmd.setColumnType(2, Types.INTEGER);
    rsmd.setColumnType(3, Types.VARCHAR);
    rsmd.setColumnName(1, "col1");
    rsmd.setColumnName(2, "col2");
    rsmd.setColumnName(3, "col3");
    crs.setMetaData(rsmd);
    System.out.println("CustomRowSetReader: crs.setMetaData( rsmd );");
    crs.moveToInsertRow();
    crs.updateString(1, "StringCol11");
    crs.updateInt(2, 1);
    crs.updateString(3, "StringCol31");
    crs.insertRow();
    System.out.println("CustomRowSetReader: crs.insertRow() 1");
    crs.updateString(1, "StringCol12");
    crs.updateInt(2, 2);
    crs.updateString(3, "StringCol32");
    crs.insertRow();
    System.out.println("CustomRowSetReader: crs.insertRow() 2");
    crs.moveToCurrentRow();
    crs.beforeFirst();
    displayRowSet(crs);
    crs.beforeFirst();
    // crs.acceptChanges();
    System.out.println("CustomRowSetReader: end.");
  } // end readData
  static void displayRowSet(RowSet rs) throws SQLException {
    while (rs.next()) {
      System.out.println(rs.getRow() + " - " + rs.getString("col1") + ":" + rs.getInt("col2") + ":"
          + rs.getString("col3"));
    }
  }
}

---------------------------------------------------------------------------

import java.sql.SQLException;
import java.sql.Types;
import java.util.Hashtable;
import javax.sql.RowSet;
import javax.sql.RowSetInternal;
import javax.sql.RowSetMetaData;
import javax.sql.RowSetReader;
import javax.sql.RowSetWriter;
import javax.sql.rowset.CachedRowSet;
import javax.sql.rowset.RowSetMetaDataImpl;
import javax.sql.rowset.spi.SyncFactory;
import javax.sql.rowset.spi.SyncProvider;
import com.sun.rowset.CachedRowSetImpl;

class CustomRowSetWriter implements RowSetWriter {
  public CustomRowSetWriter() {
    System.out.println("CustomRowSetWriter: constructor.");
  }
  public boolean writeData(RowSetInternal caller) throws SQLException {
    System.out.println("--- CustomRowSetWriter: begin. ---");
    if (caller == null) {
      System.out.println("CustomRowSetWriter: caller is null.");
      return false;
    }
    CachedRowSet crs = (CachedRowSet) caller;
    // for now do not write any data
    return true;
  }
}

---------------------------------------------------------------------------------

import javax.sql.RowSet;
import javax.sql.RowSetInternal;
import javax.sql.RowSetMetaData;
import javax.sql.RowSetReader;
import javax.sql.RowSetWriter;
import javax.sql.rowset.CachedRowSet;
import javax.sql.rowset.RowSetMetaDataImpl;
import javax.sql.rowset.spi.SyncFactory;
import javax.sql.rowset.spi.SyncProvider;
import com.sun.rowset.CachedRowSetImpl;
import javax.sql.rowset.spi.SyncProvider;
public class MySyncProvider extends SyncProvider {
  private int dataSourceLock;
  /**
   * creates a default SyncProvider object.
   */
  public MySyncProvider() {
    System.out.println("MySyncProvider: constructor.");
    this.dataSourceLock = SyncProvider.DATASOURCE_NO_LOCK;
  }
  /**
   * Returns the current data source lock severity level active in this
   * SyncProvider implementation.
   */
  public int getDataSourceLock() {
    return this.dataSourceLock;
  }
  /**
   * Returns a constant indicating the grade of synchronization a RowSet object
   * can expect from this SyncProvider object.
   */
  public int getProviderGrade() {
    return SyncProvider.GRADE_NONE;
  }
  /**
   * Returns the unique identifier for this SyncProvider object.
   */
  public String getProviderID() {
    String id = getClass().getName();
    System.out.println("--- MySyncProvider: getProviderID() =" + id);
    return id; // "MySyncProvider";
  }
  /**
   * Returns a javax.sql.RowSetReader object, which can be used to populate a
   * RowSet object with data.
   */
  public RowSetReader getRowSetReader() {
    System.out.println("--- MySyncProvider: getRowSetReader() ---");
    return new CustomRowSetReader();
  }
  /**
   * Returns a javax.sql.RowSetWriter object, which can be used to write a
   * RowSet object"s data back to the underlying data source.
   */
  public RowSetWriter getRowSetWriter() {
    System.out.println("--- MySyncProvider: getRowSetWriter() ---");
    return new CustomRowSetWriter();
  }
  /**
   * Returns the vendor name of this SyncProvider instance
   */
  public String getVendor() {
    return "custom-made";
  }
  /**
   * Returns the release version of this SyncProvider instance.
   */
  public String getVersion() {
    return "1.0";
  }
  /**
   * Sets a lock on the underlying data source at the level indicated by
   * datasourceLock.
   */
  public void setDataSourceLock(int dataSourceLock) {
    this.dataSourceLock = dataSourceLock;
  }
  /**
   * Returns whether this SyncProvider implementation can perform
   * synchronization between a RowSet object and the SQL VIEW in the data source
   * from which the RowSet object got its data.
   */
  public int supportsUpdatableView() {
    return SyncProvider.NONUPDATABLE_VIEW_SYNC;
  }
}

Use Jdbc RowSet Event

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import javax.sql.RowSetEvent;
import javax.sql.RowSetListener;
import javax.sql.rowset.JdbcRowSet;
import com.sun.rowset.JdbcRowSetImpl;
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);");
    st.executeUpdate("insert into survey (id,name ) values (1,"nameValue")");
    st.executeUpdate("insert into survey (id,name ) values (2,"anotherValue")");    
    
    JdbcRowSet jdbcRS;
    jdbcRS = new JdbcRowSetImpl(conn);
    jdbcRS.setType(ResultSet.TYPE_SCROLL_INSENSITIVE);
    String sql = "SELECT * FROM survey";
    jdbcRS.setCommand(sql);
    jdbcRS.execute();
    jdbcRS.addRowSetListener(new ExampleListener());
    while (jdbcRS.next()) {
      // each call to next, generates a cursorMoved event
      System.out.println("id=" + jdbcRS.getString(1));
      System.out.println("name=" + jdbcRS.getString(2));
    }
    conn.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 ExampleListener implements RowSetListener {
  public void cursorMoved(RowSetEvent event) {
    System.out.println("ExampleListener notified of cursorMoved event");
    System.out.println(event.toString());
  }
  public void rowChanged(RowSetEvent event) {
    System.out.println("ExampleListener notified of rowChanged event");
    System.out.println(event.toString());
  }
  public void rowSetChanged(RowSetEvent event) {
    System.out.println("ExampleListener notified of rowSetChanged event");
    System.out.println(event.toString());
  }
}