List of usage examples for java.sql PreparedStatement setMaxRows
void setMaxRows(int max) throws SQLException;
ResultSet object generated by this Statement object can contain to the given number. From source file:org.LexGrid.util.sql.lgTables.SQLTableUtilities.java
/** * Add the root or tail relationship node for an association name in a * coding scheme.//w w w . ja va2s. co m * * @param codingScheme * The coding scheme to add the root node to. * @param associationNames * The association name(s) to calculate the root node for. If you * provide more than one association, the root node will be * calculated using all of the association names (at the same * time). If you don't provide any association names, all * associations names will be used (at the same time). * @param synNames * The association name(s) that define synonymous relationships * between concepts. If provided, nodes that do not directly * participate in an association above but are synonymous with a * node that does participate are not included in the * calculation. If empty or null, synonymy is not considered as * part of the calculation. * @param relationName * The relation name that contains the association. If null, the * native relation for the coding scheme is used. * @param root * - true for root, false for tail. * @throws SQLException */ public void addRootRelationNode(String codingScheme, String[] associationNames, String[] synNames, String relationName, boolean root) throws SQLException { if (!doTablesExist()) { log.debug("Tables don't exist - returning."); return; } String type = (root ? "root" : "tail"); boolean useAll = false; if (associationNames == null || associationNames.length == 0) { useAll = true; } Connection conn = getConnection(); try { if (relationName == null || relationName.length() < 1) { relationName = getNativeRelation(codingScheme); if (relationName == null || relationName.length() < 1) { log.debug("The relation could not be found."); return; } } StringBuffer query = new StringBuffer("SELECT " + stc_.targetEntityCodeOrId + " FROM " + stc_.getTableName(SQLTableConstants.ENTITY_ASSOCIATION_TO_ENTITY) + " WHERE " + stc_.codingSchemeNameOrId + " = ?" + " AND " + stc_.containerNameOrContainerDC + " = ?"); if (!useAll) { query.append(" AND ("); for (int i = 0; i < associationNames.length; i++) { query.append(stc_.entityCodeOrAssociationId + " = ? OR "); } // trim the last 'OR ' query.setLength(query.length() - 3); query.append(")"); } query.append( " AND " + stc_.sourceCSIdOrEntityCodeNS + " = ? AND " + stc_.targetCSIdOrEntityCodeNS + " = ?"); PreparedStatement checkForAssociation = conn.prepareStatement(gsm_.modifySQL(query.toString())); checkForAssociation.setMaxRows(1); int i = 1; checkForAssociation.setString(i++, codingScheme); checkForAssociation.setString(i++, relationName); if (!useAll) { for (int j = 0; j < associationNames.length; j++) { checkForAssociation.setString(i++, associationNames[j]); } } checkForAssociation.setString(i++, codingScheme); checkForAssociation.setString(i++, codingScheme); ResultSet results = checkForAssociation.executeQuery(); boolean hasResults = results.next(); results.close(); checkForAssociation.close(); if (!hasResults) { log.debug("None of the provided associations are present in the table. " + "No reason to calculate " + type + " nodes - returning "); return; } query.setLength(0); query.append("SELECT " + stc_.entityCodeOrId + " FROM " + stc_.getTableName(SQLTableConstants.ENTITY) + " WHERE " + stc_.codingSchemeNameOrId + " = ?" + " AND " + SQLTableConstants.TBLCOL_ISACTIVE + " = ?" + " AND " + stc_.entityCodeOrId + " NOT IN (" + " SELECT " + (root ? stc_.targetEntityCodeOrId : stc_.sourceEntityCodeOrId) + " FROM " + stc_.getTableName(SQLTableConstants.ENTITY_ASSOCIATION_TO_ENTITY) + " WHERE " + stc_.codingSchemeNameOrId + " = ?" + " AND " + stc_.containerNameOrContainerDC + " = ?"); if (!useAll) { query.append(" AND ("); for (int j = 0; j < associationNames.length; j++) { query.append(stc_.entityCodeOrAssociationId + " = ? OR "); } // trim the last 'OR ' query.setLength(query.length() - 3); query.append(")"); } query.append(" AND " + stc_.sourceCSIdOrEntityCodeNS + " = ? AND " + stc_.targetCSIdOrEntityCodeNS + " = ?)"); PreparedStatement getNodes = conn.prepareStatement(gsm_.modifySQL(query.toString())); PreparedStatement insertIntoConcepts = conn .prepareStatement(stc_.getInsertStatementSQL(SQLTableConstants.ENTITY)); PreparedStatement insertIntoConceptAssociationsToConcept = conn .prepareStatement(stc_.getInsertStatementSQL(SQLTableConstants.ENTITY_ASSOCIATION_TO_ENTITY)); PreparedStatement insertIntoAssociations = conn .prepareStatement(stc_.getInsertStatementSQL(SQLTableConstants.ASSOCIATION)); // add the node to the concepts table insertIntoConcepts.setString(1, codingScheme); insertIntoConcepts.setString(2, codingScheme); insertIntoConcepts.setString(3, (root ? "@" : "@@")); DBUtility.setBooleanOnPreparedStatment(insertIntoConcepts, 4, null); DBUtility.setBooleanOnPreparedStatment(insertIntoConcepts, 5, null); DBUtility.setBooleanOnPreparedStatment(insertIntoConcepts, 6, new Boolean(true)); insertIntoConcepts.setInt(7, 0); // entryStateId here insertIntoConcepts.setString(8, type + " relation node for relations"); try { insertIntoConcepts.executeUpdate(); } catch (SQLException e) { // assume this means that the association is already in the // table. } insertIntoConcepts.close(); // if they ask me to calculate root nodes based on multiple // associations // then I want to use a special association name to mark this one. // need // to add it to the associations table, so I don't have foreign key // violations. // if they only provide one association name, then I will just use // that association // name. if (useAll || associationNames.length > 1) { int k = 1; insertIntoAssociations.setString(k++, codingScheme); insertIntoAssociations.setString(k++, relationName); insertIntoAssociations.setString(k++, codingScheme); insertIntoAssociations.setString(k++, "-multi-assn-@-root-"); insertIntoAssociations.setString(k++, "-multi-assn-@-root-"); insertIntoAssociations.setString(k++, "Not Applicable"); insertIntoAssociations.setString(k++, "Not Applicable"); insertIntoAssociations.setString(k++, null); DBUtility.setBooleanOnPreparedStatment(insertIntoAssociations, k++, null); DBUtility.setBooleanOnPreparedStatment(insertIntoAssociations, k++, null); DBUtility.setBooleanOnPreparedStatment(insertIntoAssociations, k++, null); DBUtility.setBooleanOnPreparedStatment(insertIntoAssociations, k++, null); DBUtility.setBooleanOnPreparedStatment(insertIntoAssociations, k++, null); DBUtility.setBooleanOnPreparedStatment(insertIntoAssociations, k++, null); DBUtility.setBooleanOnPreparedStatment(insertIntoAssociations, k++, null); DBUtility.setBooleanOnPreparedStatment(insertIntoAssociations, k++, null); DBUtility.setBooleanOnPreparedStatment(insertIntoAssociations, k++, null); if (stc_.supports2009Model()) insertIntoAssociations.setInt(k++, -1);// entryStateId insertIntoAssociations.setString(k++, null); try { insertIntoAssociations.executeUpdate(); } catch (SQLException e) { // assume this means it already exists } insertIntoAssociations.close(); } // find all the nodes that need to be referenced and insert rows for // them i = 1; getNodes.setString(i++, codingScheme); DBUtility.setBooleanOnPreparedStatment(getNodes, i++, new Boolean(true)); getNodes.setString(i++, codingScheme); getNodes.setString(i++, relationName); if (!useAll) { for (int j = 0; j < associationNames.length; j++) { getNodes.setString(i++, associationNames[j]); } } getNodes.setString(i++, codingScheme); getNodes.setString(i++, codingScheme); results = getNodes.executeQuery(); Set candidateCodes = new HashSet(); try { while (results.next()) { String target = results.getString(stc_.entityCodeOrId); if (target.equals((root ? "@" : "@@"))) { // Already linked to root; don't add this one continue; } // Add code as candidate to be linked to root candidateCodes.add(target); } } finally { results.close(); getNodes.close(); } // If synonymous relations are indicated, filter candidates having // synonymous concepts not in the candidate list. if (synNames != null && synNames.length > 0) { StringBuffer sb = new StringBuffer("SELECT " + stc_.targetEntityCodeOrId + " FROM ") .append(stc_.getTableName(SQLTableConstants.ENTITY_ASSOCIATION_TO_ENTITY)) .append(" WHERE " + stc_.codingSchemeNameOrId + " = '").append(codingScheme).append('\'') .append(" AND " + stc_.containerNameOrContainerDC + " = '").append(relationName) .append('\'').append(" AND " + stc_.sourceEntityCodeOrId + " = ?") .append(" AND " + stc_.entityCodeOrAssociationId + " IN ("); for (int s = 0; s < synNames.length; s++) { if (s > 0) sb.append(','); sb.append('\'').append(synNames[s]).append('\''); } sb.append(")"); PreparedStatement getSynonyms = conn.prepareStatement(gsm_.modifySQL(sb.toString())); try { // Check each concept tagged as a synonym. Collection codesToRemove = new ArrayList(); for (Iterator sourceCodes = candidateCodes.iterator(); sourceCodes.hasNext();) { String sourceCode = (String) sourceCodes.next(); getSynonyms.setString(1, sourceCode); ResultSet rs = getSynonyms.executeQuery(); try { // Is the synonym's code participating as an // intermediate node // in the hierarchy? while (rs.next()) { String synCode = rs.getString(1); if (!candidateCodes.contains(synCode)) { codesToRemove.add(sourceCode); break; } } } finally { rs.close(); } } // Remove those detected to have a synonym that is not a // root node. candidateCodes.removeAll(codesToRemove); } finally { getSynonyms.close(); } } // Insert root relations for remaining candidates for (Iterator candidates = candidateCodes.iterator(); candidates.hasNext();) { String target = (String) candidates.next(); int col = 1; insertIntoConceptAssociationsToConcept.setString(col++, codingScheme); insertIntoConceptAssociationsToConcept.setString(col++, relationName); insertIntoConceptAssociationsToConcept.setString(col++, codingScheme); // use a special association name if there is more than one // association provided. insertIntoConceptAssociationsToConcept.setString(col++, ((useAll || associationNames.length > 1) ? "-multi-assn-@-root-" : associationNames[0])); insertIntoConceptAssociationsToConcept.setString(col++, codingScheme); if (root) { insertIntoConceptAssociationsToConcept.setString(col++, "@"); } else { insertIntoConceptAssociationsToConcept.setString(col++, target); } insertIntoConceptAssociationsToConcept.setString(col++, codingScheme); if (root) { insertIntoConceptAssociationsToConcept.setString(col++, target); } else { insertIntoConceptAssociationsToConcept.setString(col++, "@@"); } //always populate the multiattributeskey -- in this case a random UUID insertIntoConceptAssociationsToConcept.setString(col++, UUID.randomUUID().toString()); insertIntoConceptAssociationsToConcept.setString(col++, null); DBUtility.setBooleanOnPreparedStatment(insertIntoConceptAssociationsToConcept, col++, new Boolean(null)); DBUtility.setBooleanOnPreparedStatment(insertIntoConceptAssociationsToConcept, col++, new Boolean(null)); DBUtility.setBooleanOnPreparedStatment(insertIntoConceptAssociationsToConcept, col++, new Boolean(null)); insertIntoConceptAssociationsToConcept.setInt(col++, 0); // entryStateId // here insertIntoConceptAssociationsToConcept.executeUpdate(); } insertIntoConceptAssociationsToConcept.close(); } finally { returnConnection(conn); } }
From source file:org.lockss.daemon.OpenUrlResolver.java
/** * Resolve query if a single URL matches. * /*from w w w. ja v a 2 s .co m*/ * @param conn the connection * @param query the query * @param args the args * @param results the results * @return the number of results returned * @throws DbException */ private int resolveFromQuery(Connection conn, String query, List<String> args, String[][] results) throws DbException { final String DEBUG_HEADER = "resolveFromQuery(): "; log.debug3(DEBUG_HEADER + "query: " + query); PreparedStatement stmt = daemon.getDbManager().prepareStatement(conn, query); int count = 0; try { for (int i = 0; i < args.size(); i++) { log.debug3(DEBUG_HEADER + " query arg: " + args.get(i)); stmt.setString(i + 1, args.get(i)); } stmt.setMaxRows(results.length); // only need 2 to to determine if unique ResultSet resultSet = daemon.getDbManager().executeQuery(stmt); for (; count < results.length && resultSet.next(); count++) { for (int i = 0; i < results[count].length; i++) { results[count][i] = resultSet.getString(i + 1); } } } catch (SQLException sqle) { throw new DbException("Cannot resolve from query", sqle); } return count; }
From source file:org.lockss.subscription.SubscriptionManager.java
/** * Provides an indication of whether there are subscription ranges. * //from w ww .j a v a 2 s.c om * @param conn * A Connection with the database connection to be used. * @return a boolean with <code>true</code> if there are subscribed * publications, <code>false</code> otherwise. * @throws DbException * if any problem occurred accessing the database. */ boolean hasSubscriptionRanges(Connection conn) throws DbException { final String DEBUG_HEADER = "hasSubscriptionRanges(): "; if (log.isDebug2()) log.debug2(DEBUG_HEADER + "Starting..."); boolean result = false; String query = FIND_ALL_SUBSCRIPTION_RANGES_QUERY; if (log.isDebug3()) log.debug3(DEBUG_HEADER + "SQL = " + query); PreparedStatement findAnySubscription = null; ResultSet resultSet = null; try { findAnySubscription = dbManager.prepareStatement(conn, query); findAnySubscription.setMaxRows(1); resultSet = dbManager.executeQuery(findAnySubscription); result = resultSet.next(); } catch (SQLException sqle) { String message = "Cannot find any subscribed publications"; log.error(message, sqle); log.error("SQL = '" + query + "'."); throw new DbException(message, sqle); } finally { DbManager.safeCloseResultSet(resultSet); DbManager.safeCloseStatement(findAnySubscription); } if (log.isDebug2()) log.debug2(DEBUG_HEADER + "result = " + result); return result; }
From source file:org.openbravo.test.accounting.RecordID2Test.java
private Date getBalancedDate(String strRecordID2, String strAccountId) { String sql = "select max(datebalanced) " // + "from fact_Acct where record_ID2 = ?" + " and account_id = ?"; PreparedStatement sqlQuery = null; ResultSet rs = null;//from www. j a v a2 s. c o m try { sqlQuery = new DalConnectionProvider(false).getPreparedStatement(sql); sqlQuery.setString(1, strRecordID2); sqlQuery.setString(2, strAccountId); sqlQuery.execute(); sqlQuery.setMaxRows(1); rs = sqlQuery.getResultSet(); while (rs.next()) { return rs.getDate(1); } } catch (Exception e) { assertFalse(true); log.error("Error when executing query", e); } finally { try { if (sqlQuery != null) { sqlQuery.close(); } if (rs != null) { rs.close(); } } catch (Exception e) { log.error("Error when closing statement", e); } } return null; }
From source file:org.openbravo.test.accounting.RecordID2Test.java
private String getReceivablesAccount(BusinessPartner businessPartner, String strCAcctSchemaId) { String sql = "select account_id " // + "from c_bp_customer_acct, c_validcombination where c_receivable_acct = c_validcombination_id and c_bp_customer_acct.c_bpartner_id = ? and c_bp_customer_acct.c_acctschema_id = ?"; PreparedStatement sqlQuery = null; ResultSet rs = null;/*w w w. j ava 2 s .c o m*/ try { sqlQuery = new DalConnectionProvider(false).getPreparedStatement(sql); sqlQuery.setString(1, businessPartner.getId()); sqlQuery.setString(2, strCAcctSchemaId); sqlQuery.execute(); sqlQuery.setMaxRows(1); rs = sqlQuery.getResultSet(); while (rs.next()) { return rs.getString(1); } } catch (Exception e) { assertFalse(true); log.error("Error when executing query", e); } finally { try { if (sqlQuery != null) { sqlQuery.close(); } if (rs != null) { rs.close(); } } catch (Exception e) { log.error("Error when closing statement", e); } } return null; }
From source file:org.openbravo.test.accounting.RecordID2Test.java
private String getInTransitAccount(FIN_FinancialAccount account, String strCAcctSchemaId) { String sql = "select account_id " // + "from fin_financial_account_acct, c_validcombination where fin_in_intransit_acct = c_validcombination_id and fin_financial_account_acct.fin_financial_account_id = ? and fin_financial_account_acct.c_acctschema_id = ?"; PreparedStatement sqlQuery = null; ResultSet rs = null;//from w ww .j av a 2 s. c o m try { sqlQuery = new DalConnectionProvider(false).getPreparedStatement(sql); sqlQuery.setString(1, account.getId()); sqlQuery.setString(2, strCAcctSchemaId); sqlQuery.execute(); sqlQuery.setMaxRows(1); rs = sqlQuery.getResultSet(); while (rs.next()) { return rs.getString(1); } } catch (Exception e) { assertFalse(true); log.error("Error when executing query", e); } finally { try { if (sqlQuery != null) { sqlQuery.close(); } if (rs != null) { rs.close(); } } catch (Exception e) { log.error("Error when closing statement", e); } } return null; }
From source file:org.openbravo.test.accounting.RecordID2Test.java
private String getDepositAccount(FIN_FinancialAccount account, String strCAcctSchemaId) { String sql = "select account_id " // + "from fin_financial_account_acct, c_validcombination where fin_deposit_acct = c_validcombination_id and fin_financial_account_acct.fin_financial_account_id = ? and fin_financial_account_acct.c_acctschema_id = ?"; PreparedStatement sqlQuery = null; ResultSet rs = null;/* w w w. j a v a 2 s . c om*/ try { sqlQuery = new DalConnectionProvider(false).getPreparedStatement(sql); sqlQuery.setString(1, account.getId()); sqlQuery.setString(2, strCAcctSchemaId); sqlQuery.execute(); sqlQuery.setMaxRows(1); rs = sqlQuery.getResultSet(); while (rs.next()) { return rs.getString(1); } } catch (Exception e) { assertFalse(true); log.error("Error when executing query", e); } finally { try { if (sqlQuery != null) { sqlQuery.close(); } if (rs != null) { rs.close(); } } catch (Exception e) { log.error("Error when closing statement", e); } } return null; }
From source file:org.openbravo.test.accounting.RecordID2Test.java
private BigDecimal getBalance(String strRecordID2, String strTableId, String strCAcctSchemaId, String strAccountId) {/*from w ww . ja v a 2 s . c om*/ String sql = "select coalesce(sum(amtacctdr-amtacctcr),0) " // + "from fact_Acct where record_ID2 = ?" + " and ad_table_id = ?" + " and c_acctschema_id = ? and account_id = ?"; PreparedStatement sqlQuery = null; ResultSet rs = null; try { sqlQuery = new DalConnectionProvider(false).getPreparedStatement(sql); sqlQuery.setString(1, strRecordID2); sqlQuery.setString(2, strTableId); sqlQuery.setString(3, strCAcctSchemaId); sqlQuery.setString(4, strAccountId); sqlQuery.execute(); sqlQuery.setMaxRows(1); rs = sqlQuery.getResultSet(); while (rs.next()) { return rs.getBigDecimal(1); } } catch (Exception e) { assertFalse(true); log.error("Error when executing query", e); } finally { try { if (sqlQuery != null) { sqlQuery.close(); } if (rs != null) { rs.close(); } } catch (Exception e) { log.error("Error when closing statement", e); } } return BigDecimal.ZERO; }
From source file:org.pentaho.di.core.database.Database.java
public ResultSet openQuery(PreparedStatement ps, RowMetaInterface params, Object[] data) throws KettleDatabaseException { ResultSet res;/*w ww .j a v a2s . c o m*/ // Create a Statement try { log.snap(Metrics.METRIC_DATABASE_OPEN_QUERY_START, databaseMeta.getName()); log.snap(Metrics.METRIC_DATABASE_SQL_VALUES_START, databaseMeta.getName()); setValues(params, data, ps); // set the parameters! log.snap(Metrics.METRIC_DATABASE_SQL_VALUES_STOP, databaseMeta.getName()); if (canWeSetFetchSize(ps)) { int maxRows = ps.getMaxRows(); int fs = Const.FETCH_SIZE <= maxRows ? maxRows : Const.FETCH_SIZE; // mysql have some restriction on fetch size assignment if (databaseMeta.isMySQLVariant()) { setMysqlFetchSize(ps, fs, maxRows); } else { // other databases seems not. ps.setFetchSize(fs); } ps.setFetchDirection(ResultSet.FETCH_FORWARD); } if (rowlimit > 0 && databaseMeta.supportsSetMaxRows()) { ps.setMaxRows(rowlimit); } log.snap(Metrics.METRIC_DATABASE_EXECUTE_SQL_START, databaseMeta.getName()); res = ps.executeQuery(); log.snap(Metrics.METRIC_DATABASE_EXECUTE_SQL_STOP, databaseMeta.getName()); // MySQL Hack only. It seems too much for the cursor type of operation on // MySQL, to have another cursor opened // to get the length of a String field. So, on MySQL, we ignore the length // of Strings in result rows. // log.snap(Metrics.METRIC_DATABASE_GET_ROW_META_START, databaseMeta.getName()); rowMeta = getRowInfo(res.getMetaData(), databaseMeta.isMySQLVariant(), false); log.snap(Metrics.METRIC_DATABASE_GET_ROW_META_STOP, databaseMeta.getName()); } catch (SQLException ex) { throw new KettleDatabaseException("ERROR executing query", ex); } catch (Exception e) { throw new KettleDatabaseException("ERROR executing query", e); } finally { log.snap(Metrics.METRIC_DATABASE_OPEN_QUERY_STOP, databaseMeta.getName()); } return res; }
From source file:org.pentaho.di.core.database.Database.java
public RowMetaInterface getQueryFields(String sql, boolean param, RowMetaInterface inform, Object[] data) throws KettleDatabaseException { RowMetaInterface fields;/*from ww w . ja va 2 s . c o m*/ DBCache dbcache = DBCache.getInstance(); DBCacheEntry entry = null; // Check the cache first! // if (dbcache != null) { entry = new DBCacheEntry(databaseMeta.getName(), sql); fields = dbcache.get(entry); if (fields != null) { return fields; } } if (connection == null) { return null; // Cache test without connect. } // No cache entry found // The new method of retrieving the query fields fails on Oracle because // they failed to implement the getMetaData method on a prepared statement. // (!!!) // Even recent drivers like 10.2 fail because of it. // // There might be other databases that don't support it (we have no // knowledge of this at the time of writing). // If we discover other RDBMSs, we will create an interface for it. // For now, we just try to get the field layout on the re-bound in the // exception block below. // if (databaseMeta.supportsPreparedStatementMetadataRetrieval()) { // On with the regular program. // PreparedStatement preparedStatement = null; try { preparedStatement = connection.prepareStatement(databaseMeta.stripCR(sql), ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); preparedStatement.setMaxRows(1); ResultSetMetaData rsmd = preparedStatement.getMetaData(); fields = getRowInfo(rsmd, false, false); } catch (Exception e) { fields = getQueryFieldsFallback(sql, param, inform, data); } finally { if (preparedStatement != null) { try { preparedStatement.close(); } catch (SQLException e) { throw new KettleDatabaseException( "Unable to close prepared statement after determining SQL layout", e); } } } } else { /* * databaseMeta.getDatabaseType()==DatabaseMeta.TYPE_DATABASE_SYBASEIQ ) { */ fields = getQueryFieldsFallback(sql, param, inform, data); } // Store in cache!! if (dbcache != null && entry != null) { if (fields != null) { dbcache.put(entry, fields); } } return fields; }