List of usage examples for javax.sql.rowset CachedRowSet getString
String getString(int columnIndex) throws SQLException;
ResultSet object as a String in the Java programming language. From source file:CSVTools.CsvToolsApi.java
public void writeResultSetIntoCSVFile(CachedRowSet resultSet, String path) { PrintWriter csvWriter = null; try {//w w w . jav a 2 s. c om csvWriter = new PrintWriter(new File(path)); ResultSetMetaData meta = resultSet.getMetaData(); int numberOfColumns = meta.getColumnCount(); String dataHeaders = "\"" + meta.getColumnName(1) + "\""; for (int i = 2; i < numberOfColumns + 1; i++) { dataHeaders += ",\"" + meta.getColumnName(i) + "\""; } csvWriter.println(dataHeaders); resultSet.beforeFirst(); while (resultSet.next()) { String row = "\"" + resultSet.getString(1) + "\""; for (int i = 2; i < numberOfColumns + 1; i++) { row += ",\"" + resultSet.getString(i) + "\""; } csvWriter.println(row); } csvWriter.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } }
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 w w .j a v a2s . 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: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 {//from w w w . jav a2 s .com 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:org.openecomp.sdnc.sli.resource.sql.SqlResource.java
public void saveCachedRowSetToCtx(CachedRowSet results, SvcLogicContext ctx, String prefix, DbLibService dblibSvc) throws SQLException { if (ctx != null) { if ((prefix != null) && prefix.endsWith("[]")) { // Return an array. String pfx = prefix.substring(0, prefix.length() - 2); int idx = 0; do {/*w w w .jav a2 s . c o m*/ ResultSetMetaData rsMeta = results.getMetaData(); int numCols = rsMeta.getColumnCount(); for (int i = 0; i < numCols; i++) { String colValue = null; String tableName = rsMeta.getTableName(i + 1); if (rsMeta.getColumnType(i + 1) == java.sql.Types.VARBINARY) { colValue = decryptColumn(tableName, rsMeta.getColumnName(i + 1), results.getBytes(i + 1), dblibSvc); } else { colValue = results.getString(i + 1); } LOG.debug("Setting " + pfx + "[" + idx + "]." + rsMeta.getColumnLabel(i + 1).replaceAll("_", "-") + " = " + colValue); ctx.setAttribute(pfx + "[" + idx + "]." + rsMeta.getColumnLabel(i + 1).replaceAll("_", "-"), colValue); } idx++; } while (results.next()); LOG.debug("Setting " + pfx + "_length = " + idx); ctx.setAttribute(pfx + "_length", "" + idx); } else { ResultSetMetaData rsMeta = results.getMetaData(); int numCols = rsMeta.getColumnCount(); for (int i = 0; i < numCols; i++) { String colValue = null; String tableName = rsMeta.getTableName(i + 1); if ("VARBINARY".equalsIgnoreCase(rsMeta.getColumnTypeName(i + 1))) { colValue = decryptColumn(tableName, rsMeta.getColumnName(i + 1), results.getBytes(i + 1), dblibSvc); } else { colValue = results.getString(i + 1); } if (prefix != null) { LOG.debug("Setting " + prefix + "." + rsMeta.getColumnLabel(i + 1).replaceAll("_", "-") + " = " + colValue); ctx.setAttribute(prefix + "." + rsMeta.getColumnLabel(i + 1).replaceAll("_", "-"), colValue); } else { LOG.debug( "Setting " + rsMeta.getColumnLabel(i + 1).replaceAll("_", "-") + " = " + colValue); ctx.setAttribute(rsMeta.getColumnLabel(i + 1).replaceAll("_", "-"), colValue); } } } } }
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();/*from w w w . j a v a 2 s . co m*/ 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 }