Java/Database SQL JDBC/RowSet
Register RowSet Listener
<source lang="java">
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());
}
}
</source>
RowSetSync Provider
<source lang="java">
/* 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;
}
}
</source>
Use Jdbc RowSet Event
<source lang="java">
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());
}
}
</source>
