List of usage examples for javax.sql.rowset CachedRowSet close
void close() throws SQLException;
ResultSet object's database and JDBC resources immediately instead of waiting for this to happen when it is automatically closed. From source file:Main.java
public static void main(String[] args) throws Exception { CachedRowSet rs; String ROWSET_IMPL_CLASS = "com.sun.rowset.CachedRowSetImpl"; Class c = Class.forName(ROWSET_IMPL_CLASS); rs = (CachedRowSet) c.newInstance(); rs.setUrl("jdbc:postgresql:dbname"); rs.setUsername("username"); rs.setPassword("password"); rs.setCommand("select * from members where name like ?"); rs.setString(1, "I%"); rs.execute();/*from ww w .j a v a2 s. com*/ while (rs.next()) { if (rs.getInt("id") == 42) { rs.setString(1, "newString"); rs.updateRow(); // Normal JDBC rs.acceptChanges(); } } rs.close(); }
From source file:CachedRS.java
public static void main(String[] args) throws Exception { FileInputStream fis = new FileInputStream(CRS_FILE_LOC); ObjectInputStream in = new ObjectInputStream(fis); CachedRowSet crs = (CachedRowSet) in.readObject(); fis.close();// w w w .j a va2 s . c o m in.close(); Class.forName("oracle.jdbc.driver.OracleDriver"); crs.setUrl("jdbc:oracle:thin:@localhost:1521:ORCL"); crs.setUsername("yourName"); crs.setPassword("mypwd"); String sql = "SELECT SSN, Name, Salary, Hiredate FROM Employees WHERE SSN=?"; crs.setCommand(sql); crs.setInt(1, 111111111); crs.execute(); FileOutputStream fos = new FileOutputStream(CRS_FILE_LOC); ObjectOutputStream out = new ObjectOutputStream(fos); out.writeObject(crs); out.close(); crs.close(); fis = new FileInputStream(CRS_FILE_LOC); in = new ObjectInputStream(fis); crs = (CachedRowSet) in.readObject(); fis.close(); in.close(); while (crs.next()) { System.out.print("SSN: " + crs.getInt("ssn")); System.out.print(", Name: " + crs.getString("name")); System.out.print(", Salary: $" + crs.getDouble("salary")); System.out.print(", HireDate: " + crs.getDate("hiredate")); } crs.close(); }
From source file:Main.java
public static void main(String[] args) throws Exception { CachedRowSet rs; // Create the class with class.forName to avoid importing // from the unsupported com.sun packages. Class c = Class.forName(ROWSET_IMPL_CLASS); rs = (CachedRowSet) c.newInstance(); rs.setUrl("jdbc:postgresql:tmclub"); rs.setUsername("ian"); rs.setPassword("secret"); rs.setCommand("select * from members where name like ?"); rs.setString(1, "I%"); // This will cause the RowSet to connect, fetch its data, and // disconnect rs.execute();// w ww.ja v a 2 s . c o m // Some time later, the client tries to do something. // Suppose we want to update data: while (rs.next()) { if (rs.getInt("id") == 42) { rs.setString(1, "Marvin"); rs.updateRow(); // Normal JDBC // This additional call tells the CachedRowSet to connect // to its database and send the updated data back. rs.acceptChanges(); } } // If we're all done... rs.close(); }
From source file:com.telefonica.iot.cygnus.backends.mysql.MySQLBackendImpl.java
@Override public void expirateRecordsCache(long expirationTime) throws CygnusRuntimeError, CygnusPersistenceError { // Iterate on the cached resource IDs cache.startDbIterator();//w w w. j a v a 2 s . com while (cache.hasNextDb()) { String dbName = cache.nextDb(); cache.startTableIterator(dbName); while (cache.hasNextTable(dbName)) { String tableName = cache.nextTable(dbName); // Get the records within the table CachedRowSet records = select(dbName, tableName, "*"); // Get the number of records int numRecords = 0; try { if (records.last()) { numRecords = records.getRow(); records.beforeFirst(); } // if } catch (SQLException e) { try { records.close(); } catch (SQLException e1) { LOGGER.debug("Can't close CachedRowSet."); } throw new CygnusRuntimeError("Data expiration error", "SQLException", e.getMessage()); } // try catch // Get the reception times (they work as IDs) for future // deletion // to-do: refactor after implementing // https://github.com/telefonicaid/fiware-cygnus/issues/1371 String filters = ""; try { for (int i = 0; i < numRecords; i++) { records.next(); String recvTime = records.getString("recvTime"); long recordTime = CommonUtils.getMilliseconds(recvTime); long currentTime = new Date().getTime(); if (recordTime < (currentTime - (expirationTime * 1000))) { if (filters.isEmpty()) { filters += "recvTime='" + recvTime + "'"; } else { filters += " or recvTime='" + recvTime + "'"; } // if else } else { break; } // if else } // for } catch (SQLException e) { throw new CygnusRuntimeError("Data expiration error", "SQLException", e.getMessage()); } catch (ParseException e) { throw new CygnusRuntimeError("Data expiration error", "ParseException", e.getMessage()); } // try catch if (filters.isEmpty()) { LOGGER.debug("No records to be deleted"); } else { LOGGER.debug("Records must be deleted (dbName=" + dbName + ",tableName=" + tableName + ", filters=" + filters + ")"); delete(dbName, tableName, filters); } // if else } // while } // while }
From source file:com.oracle.tutorial.jdbc.JoinSample.java
public void testJoinRowSet(String supplierName) throws SQLException { CachedRowSet coffees = null;/* ww w . j a v a 2 s . c o m*/ CachedRowSet suppliers = null; JoinRowSet jrs = null; try { coffees = new CachedRowSetImpl(); coffees.setCommand("SELECT * FROM COFFEES"); coffees.setUsername(settings.userName); coffees.setPassword(settings.password); coffees.setUrl(settings.urlString); coffees.execute(); suppliers = new CachedRowSetImpl(); suppliers.setCommand("SELECT * FROM SUPPLIERS"); suppliers.setUsername(settings.userName); suppliers.setPassword(settings.password); suppliers.setUrl(settings.urlString); suppliers.execute(); jrs = new JoinRowSetImpl(); jrs.addRowSet(coffees, "SUP_ID"); jrs.addRowSet(suppliers, "SUP_ID"); System.out.println("Coffees bought from " + supplierName + ": "); while (jrs.next()) { if (jrs.getString("SUP_NAME").equals(supplierName)) { String coffeeName = jrs.getString(1); System.out.println(" " + coffeeName); } } } catch (SQLException e) { JDBCTutorialUtilities.printSQLException(e); } finally { if (jrs != null) { jrs.close(); } if (suppliers != null) { suppliers.close(); } if (coffees != null) { coffees.close(); } } }
From source file:com.telefonica.iot.cygnus.backends.mysql.MySQLBackendImpl.java
@Override public void capRecords(String dbName, String tableName, long maxRecords) throws CygnusRuntimeError, CygnusPersistenceError { // Get the records within the table CachedRowSet records = select(dbName, tableName, "*"); // Get the number of records int numRecords = 0; try {/* w w w. jav a 2 s . co m*/ if (records.last()) { numRecords = records.getRow(); records.beforeFirst(); } // if } catch (SQLException e) { throw new CygnusRuntimeError("Data capping error", "SQLException", e.getMessage()); } // try catch // Get the reception times (they work as IDs) for future deletion // to-do: refactor after implementing // https://github.com/telefonicaid/fiware-cygnus/issues/1371 String filters = ""; try { if (numRecords > maxRecords) { for (int i = 0; i < (numRecords - maxRecords); i++) { records.next(); String recvTime = records.getString("recvTime"); if (filters.isEmpty()) { filters += "recvTime='" + recvTime + "'"; } else { filters += " or recvTime='" + recvTime + "'"; } // if else } // for } // if records.close(); } catch (SQLException e) { throw new CygnusRuntimeError("Data capping error", "SQLException", e.getMessage()); } // try catch if (filters.isEmpty()) { LOGGER.debug("No records to be deleted"); } else { LOGGER.debug("Records must be deleted (dbName=" + dbName + ",tableName=" + tableName + ", filters=" + filters + ")"); delete(dbName, tableName, filters); } // if else }
From source file:com.jaspersoft.jasperserver.remote.dbservices.impl.MetaDataServiceImpl.java
/** * This method invokes a method ( a total of around 170 odd ) on the DatabaseMetaData object based on * method name and parameters. If the result is a Resultset a CachedRowSet object is populated with * its results and returned. Else all other types are returned as is. * @param request/* w ww . j a v a 2s .com*/ * @return */ public byte[] getDBMetaData(Resource resource, CachedRowSetWrapper crw) { long startTime = System.currentTimeMillis(); byte[] ret = new byte[0]; Connection conn = null; Method method = null; CachedRowSet crs = null; Object result = null; try { if (logger.isDebugEnabled()) { logger.debug("Enter getDBMetaData .. Start Time" + System.currentTimeMillis()); } if (crw.getParameters() != null) { for (int i = 0; i < crw.getParameters().length; i++) { Object param = crw.getParameters()[i]; //if(param instanceof String && ((String) param).length() == 0){ if (param instanceof String && StringUtil.isEmpty((String) param)) { crw.getParameters()[i] = null; // make it null } } } conn = QueryUtil.getConnection(resource); DatabaseMetaData dm = conn.getMetaData(); method = QueryUtil.findMethod(dm, crw.getRequestId(), crw.getParameters()); if (null != method) { result = method.invoke(dm, crw.getParameters()); if (null != result) { if (result instanceof java.sql.ResultSet) { // got a resultset crs = RowSetProvider.newFactory().createCachedRowSet(); crs.populate((ResultSet) result); ((java.sql.ResultSet) result).close(); // close the resultset result = crs; } if (result instanceof Serializable) { ret = JasperSerializationUtil.serialize((Serializable) result); } else { logger.warn("Cannot serialize object" + result.getClass().getName()); } } // if } else { throw new RemoteException(crw.getRequestId() + " method name is not supported."); } } catch (Exception ex) { logger.error(ex.getMessage(), ex); throw new RemoteException("Meta Data fail." + ex.getMessage()); } finally { try { if (conn != null) conn.close(); if (crs != null) crs.close(); } catch (Exception ex) { logger.error(ex.getMessage(), ex); throw new RemoteException("Meta Data fail." + ex.getMessage()); } if (logger.isDebugEnabled()) { long elapsedTime = System.currentTimeMillis() - startTime; logger.debug("Exit getDBMetaData .. Total Time Spent: " + elapsedTime); } } return ret; }
From source file:com.oracle.tutorial.jdbc.CachedRowSetSample.java
public void testPaging() throws SQLException, MalformedURLException { CachedRowSet crs = null; this.con.setAutoCommit(false); try {//from w ww.j av a 2 s .c o m crs = new CachedRowSetImpl(); crs.setUsername(settings.userName); crs.setPassword(settings.password); if (this.dbms.equals("mysql")) { crs.setUrl(settings.urlString + "?relaxAutoCommit=true"); } else { crs.setUrl(settings.urlString); } crs.setCommand("select * from MERCH_INVENTORY"); // Setting the page size to 4, such that we // get the data in chunks of 4 rows @ a time. crs.setPageSize(100); // Now get the first set of data crs.execute(); crs.addRowSetListener(new ExampleRowSetListener()); // Keep on getting data in chunks until done. int i = 1; do { System.out.println("Page number: " + i); while (crs.next()) { System.out.println("Found item " + crs.getInt("ITEM_ID") + ": " + crs.getString("ITEM_NAME")); if (crs.getInt("ITEM_ID") == 1235) { int currentQuantity = crs.getInt("QUAN") + 1; System.out.println("Updating quantity to " + currentQuantity); crs.updateInt("QUAN", currentQuantity + 1); crs.updateRow(); // Syncing the row back to the DB crs.acceptChanges(con); } } // End of inner while i++; } while (crs.nextPage()); // End of outer while // Inserting a new row // Doing a previous page to come back to the last page // as we ll be after the last page. int newItemId = 123456; if (this.doesItemIdExist(newItemId)) { System.out.println("Item ID " + newItemId + " already exists"); } else { crs.previousPage(); crs.moveToInsertRow(); crs.updateInt("ITEM_ID", newItemId); crs.updateString("ITEM_NAME", "TableCloth"); crs.updateInt("SUP_ID", 927); crs.updateInt("QUAN", 14); Calendar timeStamp; timeStamp = new GregorianCalendar(); timeStamp.set(2006, 4, 1); crs.updateTimestamp("DATE_VAL", new Timestamp(timeStamp.getTimeInMillis())); crs.insertRow(); crs.moveToCurrentRow(); // Syncing the new row back to the database. System.out.println("About to add a new row..."); crs.acceptChanges(con); System.out.println("Added a row..."); this.viewTable(con); } } catch (SyncProviderException spe) { SyncResolver resolver = spe.getSyncResolver(); Object crsValue; // value in the RowSet object Object resolverValue; // value in the SyncResolver object Object resolvedValue; // value to be persisted while (resolver.nextConflict()) { if (resolver.getStatus() == SyncResolver.INSERT_ROW_CONFLICT) { int row = resolver.getRow(); crs.absolute(row); int colCount = crs.getMetaData().getColumnCount(); for (int j = 1; j <= colCount; j++) { if (resolver.getConflictValue(j) != null) { crsValue = crs.getObject(j); resolverValue = resolver.getConflictValue(j); // Compare crsValue and resolverValue to determine // which should be the resolved value (the value to persist) // // This example choses the value in the RowSet object, // crsValue, to persist., resolvedValue = crsValue; resolver.setResolvedValue(j, resolvedValue); } } } } } catch (SQLException sqle) { JDBCTutorialUtilities.printSQLException(sqle); } finally { if (crs != null) crs.close(); this.con.setAutoCommit(true); } }
From source file:uk.ac.ox.it.ords.api.database.structure.services.impl.hibernate.StructureServiceImpl.java
private int runCountSql(String sql, List<Object> parameters, String dbName, String databaseServer) throws Exception { CachedRowSet result = this.runJDBCQuery(sql, parameters, databaseServer, dbName); try {/*from w ww . j a v a 2s . co m*/ // If count is 1, then a table with the given name was found while (result.next()) { return result.getInt("count"); } } finally { if (result != null) result.close(); } return 0; }
From source file:uk.ac.ox.it.ords.api.database.structure.services.impl.hibernate.StructureServiceImpl.java
protected String columnComment(String databaseName, String databaseServer, String tableName, String columnName) throws Exception { log.debug("columnComment"); // col_description gives the comment stored for the given column. // As with obj_description, we need the oid of the table which we // acheieve// www. ja v a 2s . c o m // by casting to regclass then to oid. We also need the column number // within the table, which we get from the name by using a subquery to // look it up in pg_attribute (Again requiring the table oid). String query = "SELECT col_description(quote_ident(?)::regclass::oid, (SELECT attnum FROM pg_attribute WHERE attrelid = quote_ident(?)::regclass::oid AND attname = ?)) as comment"; ArrayList<Object> parameters = new ArrayList<Object>(); parameters.add(tableName); parameters.add(tableName); parameters.add(columnName); String comment = ""; CachedRowSet result = this.runJDBCQuery(query, parameters, databaseServer, databaseName); if (result == null) { return comment; } try { while (result.next()) { comment = result.getString("comment"); } } finally { result.close(); } return comment; }