List of usage examples for java.sql ResultSet TYPE_SCROLL_INSENSITIVE
int TYPE_SCROLL_INSENSITIVE
To view the source code for java.sql ResultSet TYPE_SCROLL_INSENSITIVE.
Click Source Link
ResultSet object that is scrollable but generally not sensitive to changes to the data that underlies the ResultSet. From source file:edu.ku.brc.specify.conversion.ConvertTaxonHelper.java
/** ============================================================================= * Convert Taxon// w ww. j av a 2 s .c o m * ============================================================================= */ private void convertTaxonRecords() { txMapper = IdMapperMgr.getInstance().get("taxonname", "TaxonNameID"); txTypMapper = IdMapperMgr.getInstance().get("TaxonomyType", "TaxonomyTypeID"); txUnitTypMapper = IdMapperMgr.getInstance().get("TaxonomicUnitType", "TaxonomicUnitTypeID"); mappers = new IdMapperIFace[] { txMapper, txMapper, txTypMapper, txMapper, txUnitTypMapper }; IdHashMapper.setTblWriter(tblWriter); newToOldColMap.put("TaxonID", "TaxonNameID"); newToOldColMap.put("ParentID", "ParentTaxonNameID"); newToOldColMap.put("TaxonTreeDefID", "TaxonomyTypeID"); newToOldColMap.put("TaxonTreeDefItemID", "TaxonomicUnitTypeID"); newToOldColMap.put("Name", "TaxonName"); newToOldColMap.put("FullName", "FullTaxonName"); newToOldColMap.put("IsAccepted", "Accepted"); oldToNewColMap.put("TaxonNameID", "TaxonID"); oldToNewColMap.put("ParentTaxonNameID", "ParentID"); oldToNewColMap.put("TaxonomyTypeID", "TaxonTreeDefID"); oldToNewColMap.put("TaxonomicUnitTypeID", "TaxonTreeDefItemID"); oldToNewColMap.put("TaxonName", "Name"); oldToNewColMap.put("FullTaxonName", "FullName"); oldToNewColMap.put("Accepted", "IsAccepted"); // Ignore new fields // These were added for supporting the new security model and hybrids /*String[] ignoredFields = { "GUID", "Visibility", "VisibilitySetBy", "IsHybrid", "HybridParent1ID", "HybridParent2ID", "EsaStatus", "CitesStatus", "UsfwsCode", "IsisNumber", "Text1", "Text2", "NcbiTaxonNumber", "Number1", "Number2", "CreatedByAgentID", "ModifiedByAgentID", "Version", "CultivarName", "LabelFormat", "COLStatus", "VisibilitySetByID"}; */ StringBuilder newSB = new StringBuilder(); StringBuilder vl = new StringBuilder(); for (int i = 0; i < cols.length; i++) { fieldToColHash.put(cols[i], i + 1); colToFieldHash.put(i + 1, cols[i]); if (newSB.length() > 0) newSB.append(", "); newSB.append(cols[i]); if (vl.length() > 0) vl.append(','); vl.append('?'); } StringBuilder oldSB = new StringBuilder(); for (int i = 0; i < oldCols.length; i++) { oldFieldToColHash.put(oldCols[i], i + 1); if (oldSB.length() > 0) oldSB.append(", "); oldSB.append("tx."); oldSB.append(oldCols[i]); } rankIdOldDBInx = oldFieldToColHash.get("RankID"); String sqlStr = String.format("SELECT %s FROM taxon", newSB.toString()); log.debug(sqlStr); String sql = String.format("SELECT %s %s", oldSB.toString(), taxonFromClause); log.debug(sql); String cntSQL = String.format("SELECT COUNT(*) %s", taxonFromClause); log.debug(cntSQL); int txCnt = BasicSQLUtils.getCountAsInt(oldDBConn, cntSQL); if (frame != null) { frame.setProcess(0, txCnt); } String pStr = String.format("INSERT INTO taxon (%s) VALUES (%s)", newSB.toString(), vl.toString()); log.debug(pStr); try { stmtTx = newDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); ResultSet rs1 = stmtTx.executeQuery(sqlStr); ResultSetMetaData rsmd1 = rs1.getMetaData(); colTypes = new int[rsmd1.getColumnCount()]; colSizes = new int[rsmd1.getColumnCount()]; for (int i = 0; i < colTypes.length; i++) { colTypes[i] = rsmd1.getColumnType(i + 1); colSizes[i] = rsmd1.getPrecision(i + 1); } rs1.close(); stmtTx.close(); missingParentList.clear(); strandedFixedHash.clear(); lastEditedByInx = oldFieldToColHash.get("LastEditedBy"); modifiedByAgentInx = fieldToColHash.get("ModifiedByAgentID"); stmtTx = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); pStmtTx = newDBConn.prepareStatement(pStr); mappers[1].setShowLogErrors(false); int cnt = 0; ResultSet rs = stmtTx.executeQuery(sql); ResultSetMetaData rsmd = rs.getMetaData(); while (rs.next()) { processRow(rs, rsmd, null); cnt++; if (cnt % 1000 == 0) { log.debug(cnt); if (frame != null) { frame.setProcess(cnt); } } } rs.close(); if (frame != null) { frame.setProcess(txCnt, txCnt); } if (missingParentList.size() > 0) { fixStrandedTaxon(oldSB); tblWriter.setHasLines(); tblWriter.startTable("Stranded Taxon (no parent): " + missingParentList.size()); tblWriter.logHdr("Full Name", "RankID", "Sp5 RecordID", "Was Re-parented", "Description"); for (Pair<Integer, String> p : missingParentList) { tblWriter.append("<TR>"); Object[] row = BasicSQLUtils.queryForRow(oldDBConn, "SELECT FullTaxonName, RankID, TaxonNameID FROM taxonname WHERE TaxonNameID = " + p.first); for (Object obj : row) { tblWriter.append("<TD>"); tblWriter.append(obj != null ? obj.toString() : "null"); tblWriter.append("</TD>"); } tblWriter.append("<TD>"); tblWriter.append(strandedFixedHash.contains(p.first) ? "Yes" : "No"); tblWriter.append("</TD><TD>"); tblWriter.append(p.second); tblWriter.append("</TD></TR>"); } tblWriter.endTable(); tblWriter.append("<BR>"); frame.setDesc("Renumbering the tree nodes, this may take a while..."); HashSet<Integer> ttdHash = new HashSet<Integer>(); for (CollectionInfo colInfo : CollectionInfo.getFilteredCollectionInfoList()) { if (!ttdHash.contains(colInfo.getTaxonTreeDef().getId())) { DataProviderSessionIFace session = null; try { session = DataProviderFactory.getInstance().createSession(); TaxonTreeDef taxonTreeDef = colInfo.getTaxonTreeDef(); taxonTreeDef = (TaxonTreeDef) session .getData("FROM TaxonTreeDef WHERE id = " + taxonTreeDef.getId()); sql = "SELECT TaxonID FROM taxon WHERE RankID = 0 AND TaxonTreeDefID = " + taxonTreeDef.getId(); log.debug(sql); Integer txRootId = BasicSQLUtils.getCount(sql); Taxon txRoot = (Taxon) session.getData("FROM Taxon WHERE id = " + txRootId); NodeNumberer<Taxon, TaxonTreeDef, TaxonTreeDefItem> nodeNumberer = new NodeNumberer<Taxon, TaxonTreeDef, TaxonTreeDefItem>( txRoot.getDefinition()); nodeNumberer.doInBackground(); } catch (Exception ex) { //session.rollback(); ex.printStackTrace(); } finally { if (session != null) { session.close(); } } ttdHash.add(colInfo.getTaxonTreeDef().getId()); } } frame.setDesc("Renumbering done."); } missingParentList.clear(); strandedFixedHash.clear(); } catch (SQLException ex) { ex.printStackTrace(); } finally { try { stmtTx.close(); pStmtTx.close(); } catch (Exception ex) { } } IdHashMapper.setTblWriter(null); }
From source file:edu.ncsa.sstde.indexing.postgis.PostgisIndexer.java
private PreparedStatement createSqlQuery(SqlQueryBuilder builder) throws SQLException { String sql = builder.getSQL(); boolean success = false; if (builder.limit > 0) { sql += " limit ?"; }// w ww . java 2 s .c o m PreparedStatement ps = getConnection().prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); try { int bindingIdx = 1; for (Binding binding : builder.inputBindings) ps.setObject(bindingIdx++, binding.value, binding.type); success = true; // if (builder.limit > 0) { // ps.setFetchSize((int)builder.limit + 1); // } return ps; } finally { if (!success) IndexedStatement.closeQuietly(ps); } }
From source file:rapture.repo.jdbc.JDBCStructuredStore.java
/** * Get a forwards and backwards scrollable ResultSet that is read-only * * @param sql//from w ww . ja v a2 s. c o m * @return */ private PreparedStatement getPreparedStatementForCursor(String sql) throws SQLException { return jdbc.getDataSource().getConnection().prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); }
From source file:com.alibaba.wasp.jdbc.JdbcConnection.java
private void checkTypeConcurrency(int resultSetType, int resultSetConcurrency) { switch (resultSetType) { case ResultSet.TYPE_FORWARD_ONLY: case ResultSet.TYPE_SCROLL_INSENSITIVE: case ResultSet.TYPE_SCROLL_SENSITIVE: break;//from w w w.ja v a 2 s .c o m default: throw JdbcException.getInvalidValueException("resultSetType", resultSetType); } switch (resultSetConcurrency) { case ResultSet.CONCUR_READ_ONLY: case ResultSet.CONCUR_UPDATABLE: break; default: throw JdbcException.getInvalidValueException("resultSetConcurrency", resultSetConcurrency); } }
From source file:jp.mathes.databaseWiki.db.postgres.PostgresBackend.java
@Override public void executeUpdate(final String user, final String password, final String db, final String statement) throws BackendException { Statement st = null;//w w w. j a v a 2s .c om Connection conn = null; try { conn = this.connectToDB(user, password, db); st = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); this.logString(statement.trim(), user); st.executeUpdate(statement.trim()); } catch (SQLException e) { throw new BackendException(e); } catch (ClassNotFoundException e) { throw new BackendException(e); } finally { DbUtils.closeQuietly(st); DbUtils.closeQuietly(conn); } }
From source file:it.cnr.icar.eric.server.persistence.rdb.AbstractDAO.java
/** * Executes a Select statment that has an IN clause while * taking care to execute it in chunks to avoid scalability limits * in some databases (Oracle 10g limits terms in IN clause to 1000) * * Note: Caller is responsible for closing statement associated with each resultSet * in resultSets. /*from ww w. j a v a2 s .c om*/ * * @param selectStmtTemplate a string representing the SELECT statment in a parameterized format consistent withebRR parameterized queries. * @return a List of Objects */ public List<ResultSet> executeBufferedSelectWithINClause(String selectStmtTemplate, List<?> terms, int termLimit) throws RegistryException { ArrayList<ResultSet> resultSets = new ArrayList<ResultSet>(); if (terms.size() == 0) { return resultSets; } Iterator<?> iter = terms.iterator(); try { //We need to count the number of terms in "IN" list. //We need to split the SQL Strings into chunks if there are too many terms. //Reason is that some database such as Oracle, do not allow the IN list is too long int termCounter = 0; StringBuffer inTerms = new StringBuffer(); while (iter.hasNext()) { String term = (String) iter.next(); if (iter.hasNext() && (termCounter < termLimit)) { inTerms.append("'" + term + "',"); } else { inTerms.append("'" + term + "' "); String sql = selectStmtTemplate.replaceAll("\\$InClauseTerms", inTerms.toString()); // xxx 120216 pa allow scrollable resultset for jump to end with rs.last() Statement stmt = context.getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); ResultSet rs = stmt.executeQuery(sql); resultSets.add(rs); termCounter = 0; inTerms = new StringBuffer(); } termCounter++; } } catch (SQLException e) { throw new RegistryException(e); } return resultSets; }
From source file:org.wso2.carbon.registry.core.jdbc.dao.JDBCTagsVersionDAO.java
/** * Method to get tags added to the given resource, along with the count. * * @param resourceImpl the resource./*from w ww . j a va 2 s . c o m*/ * * @return an array of tags (with counts). * @throws RegistryException if an error occurred while getting tags. */ public Tag[] getTagsWithCount(ResourceImpl resourceImpl) throws RegistryException { JDBCDatabaseTransaction.ManagedRegistryConnection conn = JDBCDatabaseTransaction.getConnection(); try { String dbName = conn.getMetaData().getDatabaseProductName(); if (dbName.contains("Microsoft") || dbName.equals("Oracle")) { enableApiPagination = "false"; } } catch (SQLException e) { throw new RegistryException("Failed to get Database product name ", e); } List<Tag> tagList = new ArrayList<Tag>(); ResultSet result = null; PreparedStatement ps = null; boolean paginated = false; int start = 0; int count = 0; String sortOrder = ""; String sortBy = ""; MessageContext messageContext = null; // enableApiPagination is the value of system property - enable.registry.api.paginating if (enableApiPagination == null || enableApiPagination.equals("true")) { messageContext = MessageContext.getCurrentMessageContext(); if (messageContext != null && PaginationUtils.isPaginationHeadersExist(messageContext)) { PaginationContext paginationContext = PaginationUtils.initPaginationContext(messageContext); start = paginationContext.getStart(); if (start == 0) { start = 1; } count = paginationContext.getCount(); sortBy = paginationContext.getSortBy(); sortOrder = paginationContext.getSortOrder(); paginated = true; } } try { String sql = "SELECT T.REG_TAG_NAME, COUNT(T.REG_ID) FROM REG_TAG T, REG_RESOURCE_TAG RT " + "WHERE RT.REG_VERSION=? AND T.REG_ID=RT.REG_TAG_ID AND " + "T.REG_TENANT_ID=? AND RT.REG_TENANT_ID=? " + "GROUP BY T.REG_TAG_NAME"; if (paginated) { if (!"".equals(sortBy) && !"".equals(sortOrder)) { sql = sql + " ORDER BY " + sortBy + " " + sortOrder; } } if (enableApiPagination == null || enableApiPagination.equals("true")) { // TYPE_SCROLL_INSENSITIVE and CONCUR_UPDATABLE should be set to move the cursor through the resultSet ps = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); } else { ps = conn.prepareStatement(sql); } ps.setLong(1, resourceImpl.getVersionNumber()); ps.setInt(2, CurrentSession.getTenantId()); ps.setInt(3, CurrentSession.getTenantId()); result = ps.executeQuery(); if (paginated) { //Check start index is a valid one if (result.relative(start)) { //This is to get cursor to correct position to execute results.next(). result.previous(); int i = 0; while (result.next() && i < count) { i++; tagList.add(getTag(result)); } } else { log.debug("start index doesn't exist in the result set"); } //move the cursor to the last index if (result.last()) { log.debug("cursor move to the last index of result set"); } else { log.debug("cursor doesn't move to the last index of result set"); } //set row count to the message context. PaginationUtils.setRowCount(messageContext, Integer.toString(result.getRow())); } else { while (result.next()) { tagList.add(getTag(result)); } } } catch (SQLException e) { String msg = "Failed to get tags and tag counts of the resource " + resourceImpl.getPath() + ". " + e.getMessage(); log.error(msg, e); throw new RegistryException(msg, e); } finally { try { try { if (result != null) { result.close(); } } finally { if (ps != null) { ps.close(); } } } catch (SQLException ex) { String msg = RegistryConstants.RESULT_SET_PREPARED_STATEMENT_CLOSE_ERROR; log.error(msg, ex); } } return tagList.toArray(new Tag[tagList.size()]); }
From source file:jp.mathes.databaseWiki.db.postgres.PostgresBackend.java
@Override public List<String> getTables(final String user, final String password, final String db) throws BackendException { Statement st = null;/* ww w . j a va 2s . com*/ Connection conn = null; ResultSet rs = null; String query = String.format("select table_name from information_schema.tables where table_schema='%s'", this.getSchemaName("", db)); List<String> result = new LinkedList<String>(); try { conn = this.connectToDB(user, password, db); st = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); this.logString(query.trim(), user); rs = st.executeQuery(query.trim()); while (rs.next()) { result.add(rs.getString(1)); } } catch (SQLException e) { throw new BackendException(e); } catch (ClassNotFoundException e) { throw new BackendException(e); } finally { DbUtils.closeQuietly(rs); DbUtils.closeQuietly(st); DbUtils.closeQuietly(conn); } return result; }
From source file:org.eclipse.ecr.core.storage.sql.jdbc.JDBCMapper.java
@Override public PartialList<Serializable> query(String query, QueryFilter queryFilter, boolean countTotal) throws StorageException { QueryMaker queryMaker = findQueryMaker(query); if (queryMaker == null) { throw new StorageException("No QueryMaker accepts query: " + query); }//from w ww .j a v a 2 s. c o m QueryMaker.Query q = queryMaker.buildQuery(sqlInfo, model, pathResolver, query, queryFilter); if (q == null) { logger.log("Query cannot return anything due to conflicting clauses"); return new PartialList<Serializable>(Collections.<Serializable>emptyList(), 0); } long limit = queryFilter.getLimit(); long offset = queryFilter.getOffset(); if (logger.isLogEnabled()) { String sql = q.selectInfo.sql; if (limit != 0) { sql += " -- LIMIT " + limit + " OFFSET " + offset; } if (countTotal) { sql += " -- COUNT TOTAL"; } logger.logSQL(sql, q.selectParams); } String sql = q.selectInfo.sql; if (!countTotal && limit > 0 && sqlInfo.dialect.supportsPaging()) { // full result set not needed for counting sql += " " + sqlInfo.dialect.getPagingClause(limit, offset); limit = 0; offset = 0; } PreparedStatement ps = null; try { ps = connection.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); int i = 1; for (Object object : q.selectParams) { if (object instanceof Calendar) { Calendar cal = (Calendar) object; Timestamp ts = new Timestamp(cal.getTimeInMillis()); ps.setTimestamp(i++, ts, cal); // cal passed for timezone } else if (object instanceof String[]) { Array array = sqlInfo.dialect.createArrayOf(Types.VARCHAR, (Object[]) object, connection); ps.setArray(i++, array); } else { ps.setObject(i++, object); } } ResultSet rs = ps.executeQuery(); // limit/offset long totalSize = -1; boolean available; if ((limit == 0) || (offset == 0)) { available = rs.first(); if (!available) { totalSize = 0; } if (limit == 0) { limit = -1; // infinite } } else { available = rs.absolute((int) offset + 1); } Column column = q.selectInfo.whatColumns.get(0); List<Serializable> ids = new LinkedList<Serializable>(); int rowNum = 0; while (available && (limit != 0)) { Serializable id = column.getFromResultSet(rs, 1); ids.add(id); rowNum = rs.getRow(); available = rs.next(); limit--; } // total size if (countTotal && (totalSize == -1)) { if (!available && (rowNum != 0)) { // last row read was the actual last totalSize = rowNum; } else { // available if limit reached with some left // rowNum == 0 if skipped too far rs.last(); totalSize = rs.getRow(); } } if (logger.isLogEnabled()) { logger.logIds(ids, countTotal, totalSize); } return new PartialList<Serializable>(ids, totalSize); } catch (Exception e) { checkConnectionReset(e); throw new StorageException("Invalid query: " + query, e); } finally { if (ps != null) { try { closeStatement(ps); } catch (SQLException e) { log.error("Cannot close connection", e); } } } }
From source file:jp.mathes.databaseWiki.db.postgres.PostgresBackend.java
@Override public List<String> getNames(final String user, final String password, final String db, final String table) throws BackendException { Statement st = null;/*from w w w. j a va2 s. c o m*/ Connection conn = null; ResultSet rs = null; List<String> result = new LinkedList<String>(); try { conn = this.connectToDB(user, password, db); st = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); String query = String.format("select %s from \"%s\".\"%s\"", this.getNameField(conn, table, db), this.getSchemaName(table, db), this.getPlainTableName(table)); this.logString(query.trim(), user); rs = st.executeQuery(query.trim()); while (rs.next()) { result.add(rs.getString(1)); } } catch (SQLException e) { throw new BackendException(e); } catch (ClassNotFoundException e) { throw new BackendException(e); } finally { DbUtils.closeQuietly(rs); DbUtils.closeQuietly(st); DbUtils.closeQuietly(conn); } return result; }