RowSetDemo.zip java 5新特性

package demo.rowset; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import javax.sql.RowSet; import javax.sql.RowSetEvent; import javax.sql.RowSetListener; import javax.sql.rowset.CachedRowSet; import javax.sql.rowset.spi.SyncProviderException; import javax.sql.rowset.spi.SyncResolver; import com.sun.rowset.CachedRowSetImpl; import db.rowset.DBCreator; public class CachedRowSetDemo { public static Connection conn; public static Statement stmt; public static void main(String[] args) throws SQLException, ClassNotFoundException { Class.forName(DBCreator.DRIVER); conn = DriverManager.getConnection(DBCreator.DERBY_URL); stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); CachedRowSet cachedRS = new CachedRowSetImpl(); /* * 填充CachedRowSet的两种方式： 1. 调用execute() 2. 调用populate(ResultSet) */ fillRowSetWithExecute(cachedRS); // Update updateOnRowSet(cachedRS); // Insert insertOnRowSet(cachedRS); // Delete deleteOnRowSet(cachedRS); // Register listener registerOnRowSet(cachedRS); confict(); paging(); transaction(); stmt.close(); conn.close(); } public static void fillRowSetWithExecute(CachedRowSet cachedRS) throws SQLException { cachedRS.setUrl(DBCreator.DERBY_URL); cachedRS.setCommand(DBCreator.SQL_SELECT_CUSTOMERS); // derby默认用户名和密码都是"APP",也可以不设置. cachedRS.setUsername("APP"); //$NON-NLS-1$ cachedRS.setPassword("APP"); //$NON-NLS-1$ cachedRS.execute(); } public static void fillRowSetWithPopulate(CachedRowSet cachedRS) throws SQLException { ResultSet rs = stmt.executeQuery(DBCreator.SQL_SELECT_CUSTOMERS); cachedRS.populate(rs); rs.close(); } /** * 将第一行第二列值由"Tom"更新为"Hello",之后再更新回"Tom". * * @param cachedRS * @throws SQLException */ public static void updateOnRowSet(CachedRowSet cachedRS) throws SQLException { System.out.println("\n/*************updateOnRowSet************/"); //$NON-NLS-1$ cachedRS.first(); System.out.println("before update: " + cachedRS.getString(2)); //$NON-NLS-1$ cachedRS.updateString(2, "Hello"); //$NON-NLS-1$ cachedRS.updateRow(); cachedRS.acceptChanges(); // check db ResultSet rs = stmt .executeQuery("SELECT * FROM CUSTOMERS WHERE ID = 1"); //$NON-NLS-1$ if (rs.next()) { System.out.println("after update: " + rs.getString(2)); //$NON-NLS-1$ } rs.close(); cachedRS.absolute(1); cachedRS.updateString(2, "Tom"); //$NON-NLS-1$ cachedRS.updateRow(); cachedRS.acceptChanges(); rs = stmt.executeQuery("SELECT * FROM CUSTOMERS WHERE ID = 1"); //$NON-NLS-1$ if (rs.next()) { System.out.println("restore to original: " + rs.getString(2)); //$NON-NLS-1$ } rs.close(); } /** * 新插入一行到表CUSTOMERS. 该行位于CachedRowSet的最后. * * @param cachedRS * @throws SQLException */ public static void insertOnRowSet(CachedRowSet cachedRS) throws SQLException { System.out.println("\n/*************insertOnRowSet************/"); //$NON-NLS-1$ cachedRS.last(); System.out.println("current cursor position before insert: " //$NON-NLS-1$ + cachedRS.getInt(1)); cachedRS.moveToInsertRow(); cachedRS.updateInt(1, 3); cachedRS.updateString(2, "Bob"); //$NON-NLS-1$ cachedRS.updateString(3, "A new user"); //$NON-NLS-1$ cachedRS.insertRow(); cachedRS.moveToCurrentRow(); cachedRS.acceptChanges(); // 当前光标位置 System.out.println("current cursor position after insert: " //$NON-NLS-1$ + cachedRS.getInt(1)); printTable(DBCreator.TABLE_CUSTOMERS); printRowSet(cachedRS); } /** * 删除在insertOnRowSet(crset)中新增的那一行. * * @param cachedRS * @throws SQLException */ public static void deleteOnRowSet(CachedRowSet cachedRS) throws SQLException { System.out.println("\n/*************deleteOnRowSet************/"); //$NON-NLS-1$ cachedRS.last(); System.out.println("The ID of row which is to be deleted: " //$NON-NLS-1$ + cachedRS.getInt(1)); cachedRS.deleteRow(); cachedRS.acceptChanges(); printTable(DBCreator.TABLE_CUSTOMERS); printRowSet(cachedRS); } /** * 注册事件监听器 * * @param cachedRS * @throws SQLException */ public static void registerOnRowSet(CachedRowSet cachedRS) throws SQLException { System.out.println("\n/*************registerOnRowSet************/"); //$NON-NLS-1$ Listener listener = new Listener(); cachedRS.addRowSetListener(listener); updateOnRowSet(cachedRS); cachedRS.removeRowSetListener(listener); System.out.println("call first() after removing listener: "); cachedRS.first(); } public static void confict() throws SQLException { stmt.executeUpdate("DELETE FROM CUSTOMERS"); stmt .executeUpdate("INSERT INTO CUSTOMERS VALUES(1,'Tom', 'Tom is VIP.')"); //$NON-NLS-1$ stmt.executeUpdate("INSERT INTO CUSTOMERS VALUES(2,'Jim', null)"); //$NON-NLS-1$ ResultSet rs = stmt.executeQuery(DBCreator.SQL_SELECT_CUSTOMERS); CachedRowSet cachedRS = new CachedRowSetImpl(); cachedRS.populate(rs); cachedRS.setUrl(DBCreator.DERBY_URL); // 修改数据库中的数据 stmt.executeUpdate("UPDATE CUSTOMERS SET NAME = 'Terry' WHERE ID = 1"); // 在CachedRowSet中更新同一行 cachedRS.absolute(1); cachedRS.updateString(3, "Tom is not VIP"); cachedRS.updateRow(); SyncResolver resolver = null; try { cachedRS.acceptChanges(); } catch (SyncProviderException e) { resolver = e.getSyncResolver(); } while (resolver.nextConflict()) { System.out.println(resolver.getStatus()); } rs = stmt.executeQuery(DBCreator.SQL_SELECT_CUSTOMERS); cachedRS = new CachedRowSetImpl(); cachedRS.populate(rs); printRowSet(cachedRS); } public static void paging() throws SQLException { stmt.executeUpdate("DELETE FROM ORDERS"); //$NON-NLS-1$ stmt.executeUpdate("INSERT INTO ORDERS VALUES(1, 1, 'Book')"); //$NON-NLS-1$ stmt.executeUpdate("INSERT INTO ORDERS VALUES(2, 1, 'Compute')"); //$NON-NLS-1$ stmt.executeUpdate("INSERT INTO ORDERS VALUES(3, 2, 'Phone')"); //$NON-NLS-1$ stmt.executeUpdate("INSERT INTO ORDERS VALUES(4, 2, 'Java')"); //$NON-NLS-1$ stmt.executeUpdate("INSERT INTO ORDERS VALUES(5, 2, 'Test')"); //$NON-NLS-1$ stmt.executeUpdate("INSERT INTO ORDERS VALUES(6, 1, 'C++')"); //$NON-NLS-1$ stmt.executeUpdate("INSERT INTO ORDERS VALUES(7, 2, 'Perl')"); //$NON-NLS-1$ stmt.executeUpdate("INSERT INTO ORDERS VALUES(8, 1, 'Ruby')"); //$NON-NLS-1$ stmt.executeUpdate("INSERT INTO ORDERS VALUES(9, 1, 'Erlang')"); //$NON-NLS-1$ stmt.executeUpdate("INSERT INTO ORDERS VALUES(10, 2, 'Python')"); //$NON-NLS-1$ ResultSet rs = stmt.executeQuery(DBCreator.SQL_SELECT_ORDERS); CachedRowSet cachedRS = new CachedRowSetImpl(); cachedRS.setPageSize(4); cachedRS.populate(rs, 1); cachedRS.setPageSize(3); while (cachedRS.nextPage()) { printRowSet(cachedRS); } rs.close(); } public static void transaction() throws SQLException { stmt.executeUpdate("DELETE FROM CUSTOMERS"); //$NON-NLS-1$ stmt .executeUpdate("INSERT INTO CUSTOMERS VALUES(1,'Tom', 'Tom is VIP.')"); //$NON-NLS-1$ stmt.executeUpdate("INSERT INTO CUSTOMERS VALUES(2,'Jim', null)"); //$NON-NLS-1$ ResultSet rs = stmt.executeQuery(DBCreator.SQL_SELECT_CUSTOMERS); CachedRowSet cachedRS = new CachedRowSetImpl(); cachedRS.populate(rs); cachedRS.setUrl(DBCreator.DERBY_URL); cachedRS.ab