List of usage examples for java.sql PreparedStatement setFetchSize
void setFetchSize(int rows) throws SQLException;
ResultSet objects generated by this Statement. From source file:com.commander4j.db.JDBDespatch.java
public boolean getDespatchProperties() { boolean result = false; PreparedStatement stmt; ResultSet rs;//from ww w .ja v a 2 s. c om setErrorMessage(""); clear(); try { stmt = Common.hostList.getHost(getHostID()).getConnection(getSessionID()) .prepareStatement(Common.hostList.getHost(getHostID()).getSqlstatements() .getSQL("JDBDespatch.getDespatchProperties")); stmt.setFetchSize(1); stmt.setString(1, getDespatchNo()); rs = stmt.executeQuery(); if (rs.next()) { getPropertiesfromResultSet(rs); result = true; } else { setErrorMessage("Invalid Despatch No"); } rs.close(); stmt.close(); } catch (SQLException e) { setErrorMessage(e.getMessage()); } return result; }
From source file:gobblin.source.extractor.extract.jdbc.JdbcExtractor.java
/** * Execute query using JDBC PreparedStatement to pass query parameters Set * fetch size/* ww w . j ava 2 s. c o m*/ * * @param cmds commands - query, fetch size, query parameters * @return JDBC ResultSet * @throws Exception */ private CommandOutput<?, ?> executePreparedSql(List<Command> cmds) { String query = null; List<String> queryParameters = null; int fetchSize = 0; for (Command cmd : cmds) { if (cmd instanceof JdbcCommand) { JdbcCommandType type = (JdbcCommandType) cmd.getCommandType(); switch (type) { case QUERY: query = cmd.getParams().get(0); break; case QUERYPARAMS: queryParameters = cmd.getParams(); break; case FETCHSIZE: fetchSize = Integer.parseInt(cmd.getParams().get(0)); break; default: this.log.error("Command " + type.toString() + " not recognized"); break; } } } this.log.info("Executing query:" + query); ResultSet resultSet = null; try { this.jdbcSource = createJdbcSource(); this.dataConnection = this.jdbcSource.getConnection(); PreparedStatement statement = this.dataConnection.prepareStatement(query, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); int parameterPosition = 1; if (queryParameters != null && queryParameters.size() > 0) { for (String parameter : queryParameters) { statement.setString(parameterPosition, parameter); parameterPosition++; } } if (fetchSize != 0) { statement.setFetchSize(fetchSize); } final boolean status = statement.execute(); if (status == false) { this.log.error("Failed to execute sql:" + query); } resultSet = statement.getResultSet(); } catch (Exception e) { this.log.error("Failed to execute sql:" + query + " ;error-" + e.getMessage(), e); } CommandOutput<JdbcCommand, ResultSet> output = new JdbcCommandOutput(); output.put((JdbcCommand) cmds.get(0), resultSet); return output; }
From source file:com.commander4j.db.JDBDespatch.java
public LinkedList<String> getAssignedSSCCs(String despatchNo) { PreparedStatement stmt = null; LinkedList<String> result = new LinkedList<String>(); ResultSet rs;//from w w w. ja v a 2s. c o m result.clear(); try { stmt = Common.hostList.getHost(getHostID()).getConnection(getSessionID()).prepareStatement( Common.hostList.getHost(getHostID()).getSqlstatements().getSQL("JDBDespatch.getAssignedSSCCs")); stmt.setFetchSize(50); stmt.setString(1, despatchNo); rs = stmt.executeQuery(); while (rs.next()) { result.addLast(rs.getString("SSCC")); } rs.close(); stmt.close(); } catch (SQLException e) { setErrorMessage(e.getMessage()); } return result; }
From source file:com.commander4j.db.JDBDespatch.java
public boolean getDespatchPropertiesFromTransactionRef() { boolean result = false; PreparedStatement stmt; ResultSet rs;//from w ww .ja v a2s . c o m setErrorMessage(""); clear(); try { stmt = Common.hostList.getHost(getHostID()).getConnection(getSessionID()) .prepareStatement(Common.hostList.getHost(getHostID()).getSqlstatements() .getSQL("JDBDespatch.getDespatchPropertiesFromTransactionRef")); stmt.setFetchSize(1); stmt.setLong(1, getTransactionRef()); rs = stmt.executeQuery(); if (rs.next()) { getPropertiesfromResultSet(rs); result = true; } else { setErrorMessage("Invalid Despatch No"); } rs.close(); stmt.close(); } catch (SQLException e) { setErrorMessage(e.getMessage()); } return result; }
From source file:com.commander4j.db.JDBDespatch.java
public int getDespatchPalletWrongLocationCount() { int result = 0; setTotalPallets(0);// w ww .jav a2 s.co m PreparedStatement stmt; ResultSet rs; setErrorMessage(""); logger.debug("getDespatchPalletWrongLocationCount"); try { stmt = Common.hostList.getHost(getHostID()).getConnection(getSessionID()) .prepareStatement(Common.hostList.getHost(getHostID()).getSqlstatements() .getSQL("JDBDespatch.getDespatchPalletWrongLocationCount")); stmt.setFetchSize(50); stmt.setString(1, getDespatchNo()); stmt.setString(2, getLocationIDFrom()); rs = stmt.executeQuery(); if (rs.next()) { result = rs.getInt("pallet_count"); if (result > 0) { setErrorMessage(String.valueOf(result) + " SSCC's not in " + getLocationIDFrom()); } } else { result = -1; setErrorMessage("Invalid Despatch No"); } rs.close(); stmt.close(); } catch (SQLException e) { setErrorMessage(e.getMessage()); } return result; }
From source file:com.commander4j.db.JDBDespatch.java
public LinkedList<JDBDespatch> browseDespatchData(String status, int limit) { String temp = ""; Boolean top = false;/*from ww w . j av a 2 s . c om*/ PreparedStatement stmt = null; LinkedList<JDBDespatch> result = new LinkedList<JDBDespatch>(); ResultSet rs; result.clear(); try { temp = Common.hostList.getHost(getHostID()).getSqlstatements().getSQL("JDBDespatch.browse"); if (temp.indexOf("[top]") >= 0) { top = true; temp = temp.replace("[top]", "top " + String.valueOf(limit)); } stmt = Common.hostList.getHost(getHostID()).getConnection(getSessionID()).prepareStatement(temp); stmt.setFetchSize(100); stmt.setString(1, status); if (top == false) { stmt.setInt(2, limit); } rs = stmt.executeQuery(); while (rs.next()) { result.addLast(new JDBDespatch(getHostID(), getSessionID(), rs.getString("despatch_no"), rs.getTimestamp("despatch_date"), rs.getString("location_id_from"), rs.getString("location_id_to"), rs.getString("status"), rs.getInt("total_pallets"), rs.getString("trailer"), rs.getString("haulier"), rs.getString("load_no"), rs.getString("user_id"), rs.getString("journey_ref"))); } rs.close(); stmt.close(); } catch (SQLException e) { setErrorMessage(e.getMessage()); } return result; }
From source file:edu.pitt.apollo.db.ApolloDbUtils.java
public PreparedStatement getDataContentForBatchSimulations(BigInteger batchRunId, List<String> fileNamesToMatch, Connection conn) throws ApolloDatabaseException { String query = "SELECT" + " rddav.value AS name," + " rdc.text_content," + " r2.id" + " FROM" + " run_data_description_axis_value rddav," + " run_data_description_axis rdda," + " run_data_content rdc," + " run_data rd," + " simulation_group_definition sgd," + " run r1," + " run r2" + " WHERE" + " rd.content_id = rdc.id AND" + " r2.id = sgd.run_id AND" + " rd.run_id = r2.id AND"; if (!fileNamesToMatch.isEmpty()) { query += " (rddav.value = '" + fileNamesToMatch.get(0) + "'"; for (int i = 1; i < fileNamesToMatch.size(); i++) { query += " OR rddav.value = '" + fileNamesToMatch.get(i) + "'"; }/* w w w . j a v a2 s .c om*/ query += ") AND"; } query += " rddav.run_data_description_axis_id = rdda.id AND" + " rddav.run_data_description_id = rd.description_id AND" + " sgd.simulation_group_id = r1.simulation_group_id AND" + " r1.id = ? AND" + " rdda.label = 'label'"; System.out.println(query); PreparedStatement pstmt = null; try { pstmt = conn.prepareStatement(query, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); pstmt.setFetchSize(Integer.MIN_VALUE); pstmt.setInt(1, batchRunId.intValue()); return pstmt; } catch (SQLException ex) { throw new ApolloDatabaseException("SQLException attempting to get data content for batch run ID " + batchRunId + ": " + ex.getMessage()); } }
From source file:edu.mayo.informatics.lexgrid.convert.directConversions.UMLSToSQL.java
private int loadRelationsHelper(Association assoc, String UMLSCodingSchemeName, String codingSchemeName, boolean hasPossibleRUIQualifiers) throws SQLException { PreparedStatement getRRFRelationsCount = umlsConnection2_ .prepareStatement("SELECT COUNT(*) AS cnt FROM MRREL WHERE " + assoc.rrfField + " = ? AND SAB = ?"); // Note: ordered to enable a cache to not load duplicates. PreparedStatement getRRFRelations = umlsConnection2_ .prepareStatement(umlsSqlModifier_.modifySQL("SELECT CUI1, AUI1, CUI2, AUI2, RUI FROM MRREL WHERE " + assoc.rrfField + " = ? AND SAB = ? ORDER BY CUI1 {LIMIT}")); int count = 0; try {/*from www .j a v a 2s . c o m*/ int start = 0; int total = 0; int dupeCount = 0; getRRFRelationsCount.setString(1, assoc.rrfName); getRRFRelationsCount.setString(2, UMLSCodingSchemeName); messages_.info("Counting relations for " + assoc.name); ResultSet results = getRRFRelationsCount.executeQuery(); results.next(); total = results.getInt("cnt"); results.close(); while (start < total) { getRRFRelations.setString(1, assoc.rrfName); getRRFRelations.setString(2, UMLSCodingSchemeName); // mysql doesn't stream results - the {LIMIT above and this is // for getting limits on mysql code} if (umlsSqlModifier_.getDatabaseType().equals("MySQL")) { getRRFRelations.setInt(3, start); getRRFRelations.setInt(4, batchSize * 3); start += batchSize * 3; } // postgres properly streams results, we can just set the fetch // size, and only loop once else if (umlsSqlModifier_.getDatabaseType().equals("PostgreSQL")) { getRRFRelations.setFetchSize(batchSize * 3); umlsConnection2_.setAutoCommit(false); start = total; } else { start = total; } messages_.info("Getting a batch of relations for " + assoc.rrfName); results = getRRFRelations.executeQuery(); while (results.next()) { // If the source association definition is reversed, we need // to flip // the source and target concepts accordingly. boolean isReversed = assoc.rrfSourceDirectionalityReversed; String cui = results.getString(isReversed ? "CUI2" : "CUI1"); String aui = results.getString(isReversed ? "AUI2" : "AUI1"); // Getting RUI so we can check against the list of distinct // qualifier METAUI's String rui = results.getString("RUI"); CodeHolder[] sourceCode = mapCUIToCode(cui, aui, UMLSCodingSchemeName); if (sourceCode == null || sourceCode.length == 0) { log.error("Could not find sourceCode for " + cui + " : " + aui); continue; } cui = results.getString((isReversed ? "CUI1" : "CUI2")); aui = results.getString((isReversed ? "AUI1" : "AUI2")); CodeHolder[] targetCode = mapCUIToCode(cui, aui, UMLSCodingSchemeName); if (targetCode == null || targetCode.length == 0) { log.error("Could not find targetCode for " + cui + " : " + aui); continue; } /* * Most terminologies have a one to one mapping from cui - * aui pairs to unique codes. So there will only be one * source and one target concept code. RXNorm and MTH, * however, don't always provide AUI's. When no AUI's are * provided, it is possible to have the CUI's map to more * than one source or target code. */ for (int i = 0; i < sourceCode.length; i++) { for (int j = 0; j < targetCode.length; j++) { int tempval = addConceptAssociationToConceptsHelper(codingSchemeName, sourceCode[i], assoc, targetCode[j], rui, hasPossibleRUIQualifiers); if (tempval > 0) { count++; if (count % 100 == 0) { messages_.busy(); } if (count % 10000 == 0) { messages_.info("Loaded " + count + " out of a possible total of " + total); } } else if (tempval < 0) { dupeCount++; } } } } results.close(); } messages_.info("Loaded " + count + " out of a possible total of " + total); if (dupeCount > 0) { messages_.info("Encountered " + dupeCount + " duplicates"); } } finally { getRRFRelations.close(); getRRFRelationsCount.close(); } return count; }
From source file:edu.mayo.informatics.lexgrid.convert.directConversions.UMLSToSQL.java
private void loadConcepts(String UMLSCodingSchemeName, String codingSchemeName) throws SQLException { messages_.info("loading concepts - getting a total count"); PreparedStatement getCodingSchemeInfo = umlsConnection2_ .prepareStatement("SELECT COUNT(*) as cnt FROM MRCONSO WHERE SAB = ?"); getCodingSchemeInfo.setString(1, UMLSCodingSchemeName); ResultSet results = getCodingSchemeInfo.executeQuery(); results.next();//from w ww . j a v a 2 s . c o m int total = results.getInt("cnt"); results.close(); getCodingSchemeInfo.close(); int start = 0; String lastCode = null; ArrayList conceptPresentations = new ArrayList(); int codeCount = 0; int rowCount = 0; // collect them all into batches of concept codes. when the code // changes, load that code while (start < total) { messages_.info("Fetching a batch of results"); getCodingSchemeInfo = umlsConnection2_.prepareStatement( umlsSqlModifier_.modifySQL("SELECT LAT, CODE, CUI, LUI, SUI, AUI, TS, STT, STR, TTY " + " FROM MRCONSO WHERE SAB = ? ORDER BY {BINARY} CODE {LIMIT}")); getCodingSchemeInfo.setString(1, UMLSCodingSchemeName); // mysql doesn't stream results - the {LIMIT above and this is for // getting limits on mysql code} if (umlsSqlModifier_.getDatabaseType().equals("MySQL")) { getCodingSchemeInfo.setInt(2, start); getCodingSchemeInfo.setInt(3, batchSize); start += batchSize; } else if (umlsSqlModifier_.getDatabaseType().equals("PostgreSQL")) { // postgres properly streams results, we can just set the fetch // size, and only loop once getCodingSchemeInfo.setFetchSize(batchSize); umlsConnection2_.setAutoCommit(false); start = total; } else { start = total; } results = getCodingSchemeInfo.executeQuery(); log.debug("query finished, processing results"); while (results.next()) { rowCount++; // store all the data from this row. ConceptPresentation temp = new ConceptPresentation(); temp.conceptCode = results.getString("CODE"); temp.cui = results.getString("CUI"); // there are some concepts that just have "NOCODE" as the // code... need to make them unique. if (temp.conceptCode.equals("NOCODE")) { temp.conceptCode = temp.conceptCode + "-" + temp.cui; } if (lastCode != null && !temp.conceptCode.equals(lastCode)) { loadConcept(UMLSCodingSchemeName, codingSchemeName, (ConceptPresentation[]) conceptPresentations .toArray(new ConceptPresentation[conceptPresentations.size()])); conceptPresentations.clear(); commentCounter_ = 1; presentationCounter_ = 1; instructionCounter_ = 1; definitionCounter_ = 1; propertyCounter_ = 1; cuiCounter_ = 1; semTypeCounter_ = 1; if (codeCount % 10 == 0) { messages_.busy(); } codeCount++; if (codeCount % 1000 == 0) { messages_.info("On row " + rowCount + " out of " + total + " rows - found " + codeCount + " concepts"); } } temp.language = results.getString("LAT"); if (UMLSCodingSchemeName.equals("SNOMEDCT")) { // snomed has a different way of recording the language of // the presentation... String snomedLanguage = getSnomedLanguageForPresentation(results.getString("CUI"), results.getString("LUI"), results.getString("SUI")); if (snomedLanguage != null && snomedLanguage.length() > 0) { temp.language = snomedLanguage; } } // add for populating the supportedLanguages later supportedLanguages_.add(temp.language); temp.presentationFormat = SQLTableConstants.TBLCOLVAL_FORMAT_TXT_PLAIN; temp.TTY = results.getString("TTY"); temp.representationForm = temp.TTY; temp.value = results.getString("STR"); temp.TS = results.getString("TS"); temp.STT = results.getString("STT"); temp.AUI = results.getString("AUI"); temp.isPreferred = new Boolean(false); temp.source = UMLSCodingSchemeName; // See if there is a "better" value for the representationalForm // mapping String repFormMap = (String) mrconsoRepresentationalMap_.get(temp.representationForm); if (repFormMap != null) { temp.representationForm = repFormMap; } lastCode = temp.conceptCode; conceptPresentations.add(temp); } results.close(); } // load the last one if (lastCode != null) { // need to add this, just once - assuming there were codes, so this // is a good place for it. supportedPropertyTypes_.add(SQLTableConstants.TBLCOLVAL_TEXTUALPRESENTATION); loadConcept(UMLSCodingSchemeName, codingSchemeName, (ConceptPresentation[]) conceptPresentations .toArray(new ConceptPresentation[conceptPresentations.size()])); codeCount++; } messages_.info("Loaded " + codeCount + " concepts from " + rowCount + " rows"); // we are done with inserting entities, close the preparedStatement. insertIntoEntities.close(); insertIntoEntityType.close(); updateApproxNumberOfConcepts(codeCount, codingSchemeName); loadSupportedProperties(codingSchemeName); loadSupportedLanguages(codingSchemeName); getCodingSchemeInfo.close(); }
From source file:edu.mayo.informatics.lexgrid.convert.directConversions.MetaThesaurusToSQL.java
private void loadConcepts(String codingSchemeName) throws SQLException { messages_.info("loading concepts - getting a total count"); PreparedStatement getCodingSchemeInfo = umlsConnection2_ .prepareStatement("SELECT COUNT(*) as cnt FROM MRCONSO"); ResultSet results = getCodingSchemeInfo.executeQuery(); results.next();//from w w w . jav a 2 s. c o m int total = results.getInt("cnt"); results.close(); getCodingSchemeInfo.close(); int start = 0; String lastCui = null; ArrayList conceptPresentations = new ArrayList(); int codeCount = 0; int rowCount = 0; // collect them all into batches of concept codes. when the code // changes, load that code while (start < total) { // mysql still can't seem to stream results properly, so I'm going // to batch them. messages_.info("Fetching a batch of results"); getCodingSchemeInfo = umlsConnection2_.prepareStatement(umlsSqlModifier_.modifySQL( "SELECT LAT, CODE, AUI, CUI, TS, STT, ISPREF, STR, TTY, SAB FROM MRCONSO ORDER BY {BINARY} CUI {LIMIT}")); // mysql doesn't stream results - the {LIMIT above and this is for // getting limits on mysql code} if (umlsSqlModifier_.getDatabaseType().equals("MySQL")) { getCodingSchemeInfo.setInt(1, start); getCodingSchemeInfo.setInt(2, batchSize); start += batchSize; } else if (umlsSqlModifier_.getDatabaseType().equals("PostgreSQL")) { // postgres properly streams results, we can just set the fetch // size, and only loop once getCodingSchemeInfo.setFetchSize(batchSize); umlsConnection2_.setAutoCommit(false); start = total; } else { start = total; } results = getCodingSchemeInfo.executeQuery(); log.debug("query finished, processing results"); while (results.next()) { rowCount++; // store all the data from this row. ConceptPresentation temp = new ConceptPresentation(); temp.conceptCode = results.getString("CODE"); temp.cui = results.getString("CUI"); if (lastCui != null && !temp.cui.equals(lastCui)) { loadConcept(codingSchemeName, (ConceptPresentation[]) conceptPresentations .toArray(new ConceptPresentation[conceptPresentations.size()])); conceptPresentations.clear(); presentationCounter_ = 1; codeCounter_ = 1; definitionCounter_ = 1; cuiCounter_ = 1; semTypeCounter_ = 1; if (codeCount % 100 == 0) { messages_.busy(); } codeCount++; if (codeCount % 10000 == 0) { messages_.info("On row " + rowCount + " out of " + total + " rows - found " + codeCount + " concepts"); } } temp.language = results.getString("LAT"); // add for populating the supportedLanguages later supportedLanguages_.add(temp.language); temp.presentationFormat = null; temp.TTY = results.getString("TTY"); temp.representationForm = temp.TTY; temp.value = results.getString("STR"); temp.TS = results.getString("TS"); temp.STT = results.getString("STT"); temp.isPreferred = new Boolean(false); temp.source = results.getString("SAB"); temp.ISPREF = results.getString("ISPREF"); temp.AUI = results.getString("AUI"); // See if there is a "better" value for the representationalForm // mapping String repFormMap = (String) mrconsoRepresentationalMap_.get(temp.representationForm); if (repFormMap != null) { temp.representationForm = repFormMap; } lastCui = temp.cui; conceptPresentations.add(temp); } results.close(); getCodingSchemeInfo.close(); } // load the last one if (lastCui != null) { // need to add this, just once - assuming there were codes, so this // is a good place for it. supportedPropertyTypes_.add(SQLTableConstants.TBLCOLVAL_TEXTUALPRESENTATION); loadConcept(codingSchemeName, (ConceptPresentation[]) conceptPresentations .toArray(new ConceptPresentation[conceptPresentations.size()])); codeCount++; } // we are done with inserting entities, close the preparedStatement. insertIntoEntities.close(); insertIntoEntityType.close(); messages_.info("Loaded " + codeCount + " concepts from " + rowCount + " rows"); supportedPropertyQualifiers_.add("source-code"); supportedPropertyQualifiers_.add("AUI"); supportedPropertyQualifiers_.add("HCD"); updateApproxNumberOfConcepts(codeCount, codingSchemeName); loadSupportedProperties(codingSchemeName); loadSupportedPropertyQualifiers(codingSchemeName); loadSupportedLanguages(codingSchemeName); loadSupportedSources(codingSchemeName); }