List of usage examples for java.sql PreparedStatement getResultSet
ResultSet getResultSet() throws SQLException;
ResultSet object. From source file:org.cloudgraph.rdb.filter.RDBStatementExecutor.java
@Override public List<List<PropertyPair>> fetch(PlasmaType type, StringBuilder sql, Set<Property> props, Object[] params) {/* w w w . ja va2 s . c om*/ List<List<PropertyPair>> result = new ArrayList<List<PropertyPair>>(); PreparedStatement statement = null; ResultSet rs = null; try { if (log.isDebugEnabled()) { if (params == null || params.length == 0) { log.debug("fetch: " + sql.toString()); } else { StringBuilder paramBuf = new StringBuilder(); paramBuf.append(" ["); for (int p = 0; p < params.length; p++) { if (p > 0) paramBuf.append(", "); paramBuf.append(String.valueOf(params[p])); } paramBuf.append("]"); log.debug("fetch: " + sql.toString() + " " + paramBuf.toString()); } } statement = con.prepareStatement(sql.toString(), ResultSet.TYPE_FORWARD_ONLY, /* * ResultSet * . * TYPE_SCROLL_INSENSITIVE * , */ ResultSet.CONCUR_READ_ONLY); for (int i = 0; i < params.length; i++) statement.setString(i + 1, // FIXME String.valueOf(params[i])); statement.execute(); rs = statement.getResultSet(); ResultSetMetaData rsMeta = rs.getMetaData(); int numcols = rsMeta.getColumnCount(); int count = 0; while (rs.next()) { List<PropertyPair> row = new ArrayList<PropertyPair>(numcols); result.add(row); for (int i = 1; i <= numcols; i++) { String columnName = rsMeta.getColumnName(i); int columnType = rsMeta.getColumnType(i); PlasmaProperty prop = (PlasmaProperty) type.getProperty(columnName); PlasmaProperty valueProp = prop; while (!valueProp.getType().isDataType()) { valueProp = this.statementUtil.getOppositePriKeyProperty(valueProp); } Object value = converter.fromJDBCDataType(rs, i, columnType, valueProp); if (value != null) { PropertyPair pair = new PropertyPair((PlasmaProperty) prop, value); if (!valueProp.equals(prop)) pair.setValueProp(valueProp); if (!props.contains(prop)) pair.setQueryProperty(false); row.add(pair); } } count++; } if (log.isDebugEnabled()) log.debug("returned " + count + " results"); } catch (Throwable t) { throw new DataAccessException(t); } finally { try { if (rs != null) rs.close(); if (statement != null) statement.close(); } catch (SQLException e) { log.error(e.getMessage(), e); } } return result; }
From source file:ru.runa.wf.logic.bot.DatabaseTaskHandler.java
@SuppressWarnings("unchecked") private void executeDatabaseTasks(User user, VariableProvider variableProvider, WfTask task, Map<String, Object> outputVariables, DatabaseTask[] databaseTasks) throws Exception { Context context = new InitialContext(); for (DatabaseTask databaseTask : databaseTasks) { Connection conn = null;// www . ja v a 2 s .c o m try { String dsName = databaseTask.getDatasourceName(); int colonIndex = dsName.indexOf(':'); if (dsName.startsWith(DataSourceStuff.PATH_PREFIX_DATA_SOURCE) || dsName.startsWith(DataSourceStuff.PATH_PREFIX_DATA_SOURCE_VARIABLE)) { if (dsName.startsWith(DataSourceStuff.PATH_PREFIX_DATA_SOURCE)) { dsName = dsName.substring(colonIndex + 1); } else { dsName = (String) variableProvider.getValue(dsName.substring(colonIndex + 1)); } JdbcDataSource jds = (JdbcDataSource) DataSourceStorage.getDataSource(dsName); conn = DriverManager.getConnection(DataSourceStuff.adjustUrl(jds), jds.getUserName(), jds.getPassword()); } else { // jndi if (colonIndex > 0) { if (dsName.startsWith(DataSourceStuff.PATH_PREFIX_JNDI_NAME)) { dsName = dsName.substring(colonIndex + 1); } else { dsName = (String) variableProvider.getValue(dsName.substring(colonIndex + 1)); } } conn = ((DataSource) context.lookup(dsName)).getConnection(); } for (int j = 0; j < databaseTask.getQueriesCount(); j++) { AbstractQuery query = databaseTask.getQuery(j); PreparedStatement ps = null; try { if (query instanceof Query) { ps = conn.prepareStatement(query.getSql()); } else if (query instanceof StoredProcedureQuery) { final CallableStatement cps = conn.prepareCall(query.getSql()); ps = cps; fillQueryParameters(user, ps, variableProvider, query, task); cps.executeUpdate(); Map<String, Object> result = extractResultsToProcessVariables(user, variableProvider, new Function<Integer, Object>() { @Override public Object apply(Integer input) { try { return cps.getObject(input); } catch (SQLException e) { throw new InternalApplicationException(e); } } }, query); outputVariables.putAll(result); return; } else { String unknownQueryClassName = query == null ? "null" : query.getClass().getName(); throw new Exception("Unknown query type:" + unknownQueryClassName); } fillQueryParameters(user, ps, variableProvider, query, task); if (ps.execute()) { final ResultSet resultSet = ps.getResultSet(); boolean first = true; while (resultSet.next()) { Map<String, Object> result = extractResultsToProcessVariables(user, variableProvider, new Function<Integer, Object>() { @Override public Object apply(Integer input) { try { return resultSet.getObject(input); } catch (SQLException e) { throw new InternalApplicationException(e); } } }, query); if (first) { for (Map.Entry<String, Object> entry : result.entrySet()) { WfVariable variable = variableProvider.getVariableNotNull(entry.getKey()); Object variableValue; if (variable.getDefinition().getFormatNotNull() instanceof ListFormat) { ArrayList<Object> list = new ArrayList<Object>(); list.add(entry.getValue()); variableValue = list; } else { variableValue = entry.getValue(); } outputVariables.put(entry.getKey(), variableValue); } first = false; } else { for (Map.Entry<String, Object> entry : result.entrySet()) { Object object = outputVariables.get(entry.getKey()); if (!(object instanceof List)) { throw new Exception("Variable " + entry.getKey() + " expected to have List<X> format"); } ((List<Object>) object).add(entry.getValue()); } } } } } finally { SqlCommons.releaseResources(ps); } } } finally { SqlCommons.releaseResources(conn); } } }
From source file:csiro.pidsvc.mappingstore.Manager.java
private Vector<csiro.pidsvc.mappingstore.condition.Descriptor> getConditionsImpl(String searchField, int id) throws SQLException { PreparedStatement pst = null; ResultSet rs = null;//from ww w. java 2 s. c om try { pst = _connection.prepareStatement( "SELECT * FROM condition WHERE " + searchField + " = ? ORDER BY condition_id"); pst.setInt(1, id); if (!pst.execute()) return null; // Get list of conditions. Vector<csiro.pidsvc.mappingstore.condition.Descriptor> conditions = new Vector<csiro.pidsvc.mappingstore.condition.Descriptor>(); for (rs = pst.getResultSet(); rs.next(); conditions .add(new csiro.pidsvc.mappingstore.condition.Descriptor(rs.getInt("condition_id"), rs.getString("type"), rs.getString("match"), rs.getString("description")))) ; return conditions; } catch (Exception e) { _logger.error(e); } finally { if (rs != null) rs.close(); if (pst != null) pst.close(); } return null; }
From source file:org.openmrs.module.sync.api.db.hibernate.HibernateSyncDAO.java
/** * (non-Javadoc)//from w ww . ja v a 2s . c o m * * @see org.openmrs.module.sync.api.db.SyncDAO#isConceptIdValidForUuid(int, java.lang.String) */ public boolean isConceptIdValidForUuid(Integer conceptId, String uuid) { if (uuid == null || conceptId == null) { return true; } boolean ret = true; //assume all is well until proven otherwise PreparedStatement ps = null; Connection connection = sessionFactory.getCurrentSession().connection(); int foundId = 0; String foundUuid = null; try { ps = connection .prepareStatement("SELECT concept_id, uuid FROM concept WHERE (uuid = ? AND concept_id <> ?)" + "OR (uuid <> ? AND concept_id = ?)"); ps.setString(1, uuid); ps.setInt(2, conceptId); ps.setString(3, uuid); ps.setInt(4, conceptId); ps.execute(); ResultSet rs = ps.getResultSet(); if (rs.next()) { ret = false; foundId = rs.getInt("concept_id"); foundUuid = rs.getString("uuid"); String msg = "Found inconsistent data during concept ingest." + "\nto be added conceptid/uuid are:" + conceptId + "/" + uuid + "\nfound in DB conceptid/uuid:" + foundId + "/" + foundUuid; log.error(msg); } ; ps.close(); ps = null; } catch (SQLException e) { log.error("Error while doing isConceptIdValidForUuid.", e); ret = false; } if (ps != null) { try { ps.close(); } catch (SQLException e) { log.error("Error generated while closing statement", e); } } return ret; }
From source file:org.openmrs.module.sync.api.db.hibernate.HibernateSyncDAO.java
public void processSyncSubclassStub(SyncSubclassStub stub) { Connection connection = sessionFactory.getCurrentSession().connection(); boolean stubInsertNeeded = false; PreparedStatement ps = null;//from w w w . j a v a 2 s. co m int internalDatabaseId = 0; // check if there is a row with a matching person record and no patient record try { // becomes something like "select person_id from person where uuid = x" or "select concept_id from concept where uuid = x" ps = connection.prepareStatement( "SELECT " + stub.getParentTableId() + " FROM " + stub.getParentTable() + " WHERE uuid = ?"); ps.setString(1, stub.getUuid()); ps.execute(); ResultSet rs = ps.getResultSet(); if (rs.next()) { stubInsertNeeded = true; internalDatabaseId = rs.getInt(stub.getParentTableId()); } else { stubInsertNeeded = false; } //this should get no rows ps = connection.prepareStatement("SELECT " + stub.getSubclassTableId() + " FROM " + stub.getSubclassTable() + " WHERE " + stub.getSubclassTableId() + " = ?"); ps.setInt(1, internalDatabaseId); ps.execute(); if (ps.getResultSet().next()) { stubInsertNeeded = false; } ps.close(); ps = null; } catch (SQLException e) { log.error( "Error while trying to see if this person is a patient already (or concept is a concept numeric already)", e); } if (ps != null) { try { ps.close(); } catch (SQLException e) { log.error("Error generated while closing statement", e); } } if (stubInsertNeeded) { try { //insert the stub String sql = "INSERT INTO " + stub.getSubclassTable() + " (" + stub.getSubclassTableId() + "COLUMNNAMEGOESHERE) VALUES (?COLUMNVALUEGOESHERE)"; if (CollectionUtils.isNotEmpty(stub.getRequiredColumnNames()) && CollectionUtils.isNotEmpty(stub.getRequiredColumnValues()) && CollectionUtils.isNotEmpty(stub.getRequiredColumnClasses())) { for (int x = 0; x < stub.getRequiredColumnNames().size(); x++) { String column = stub.getRequiredColumnNames().get(x); sql = sql.replace("COLUMNNAMEGOESHERE", ", " + column + "COLUMNNAMEGOESHERE"); sql = sql.replace("COLUMNVALUEGOESHERE", ", ?COLUMNVALUEGOESHERE"); } } sql = sql.replace("COLUMNNAMEGOESHERE", ""); sql = sql.replace("COLUMNVALUEGOESHERE", ""); ps = connection.prepareStatement(sql); ps.setInt(1, internalDatabaseId); if (CollectionUtils.isNotEmpty(stub.getRequiredColumnNames()) && CollectionUtils.isNotEmpty(stub.getRequiredColumnValues()) && CollectionUtils.isNotEmpty(stub.getRequiredColumnClasses())) { for (int x = 0; x < stub.getRequiredColumnValues().size(); x++) { String value = stub.getRequiredColumnValues().get(x); String className = stub.getRequiredColumnClasses().get(x); Class c; try { c = Context.loadClass(className); ps.setObject(x + 2, SyncUtil.getNormalizer(c).fromString(c, value)); } catch (ClassNotFoundException e) { log.error("Unable to convert classname into a Class object " + className); } } } ps.executeUpdate(); //*and* create sync item for this HibernateSyncInterceptor.addSyncItemForSubclassStub(stub); log.debug("Sync Inserted " + stub.getParentTable() + " Stub for " + stub.getUuid()); } catch (SQLException e) { log.warn("SQL Exception while trying to create a " + stub.getParentTable() + " stub", e); } finally { if (ps != null) { try { ps.close(); } catch (SQLException e) { log.error("Error generated while closing statement", e); } } } } return; }
From source file:csiro.pidsvc.mappingstore.Manager.java
protected String exportConditionSetImpl(String name) throws SQLException { PreparedStatement pst = null; ResultSet rs = null, rsMap = null; String ret = ""; try {/*from w ww . j a v a2 s.c o m*/ if (name == null) { // Export all condition sets. pst = _connection.prepareStatement("SELECT * FROM condition_set;"); } else { // Export a particular condition set. pst = _connection.prepareStatement("SELECT * FROM condition_set WHERE name = ?;"); pst.setString(1, name); } if (pst.execute()) { rs = pst.getResultSet(); boolean dataAvailable = rs.next(); // Backups may be empty. Otherwise throw an exception. if (name != null && !dataAvailable) throw new SQLException("Condition set cannot be exported. Data may be corrupted."); if (dataAvailable) { do { String setName = rs.getString("name"); String setDesc = rs.getString("description"); ret += "<conditionSet xmlns=\"urn:csiro:xmlns:pidsvc:backup:1.0\">"; ret += "<name>" + StringEscapeUtils.escapeXml(setName) + "</name>"; if (setDesc != null && !setDesc.isEmpty()) ret += "<description>" + StringEscapeUtils.escapeXml(setDesc) + "</description>"; ret += exportConditionsBySetId(rs.getInt("condition_set_id")); ret += "</conditionSet>"; } while (rs.next()); } } } finally { if (rsMap != null) rsMap.close(); if (rs != null) rs.close(); if (pst != null) pst.close(); } return ret; }
From source file:org.opoo.oqs.core.AbstractQuery.java
/** * ResultSetID//from www.j a v a 2 s . co m * * @param sql String * @return Serializable */ private Serializable getInsertSelectIdentity(String sql) { try { ConnectionManager manager = queryFactory.getConnectionManager(); Connection conn = queryFactory.getConnectionManager().getConnection(); PreparedStatement ps = conn.prepareStatement(sql); try { Object values[] = valueArray(); for (int i = 0; i < values.length; i++) { Object value = values[i]; if (value == null) { Type.SERIALIZABLE.safeSet(ps, value, i + 1); } else { TypeFactory.guessType(value).safeSet(ps, value, i + 1); } } if (dialect.supportsInsertSelectIdentity()) { if (!ps.execute()) { while (!ps.getMoreResults() && ps.getUpdateCount() != -1) { continue; // Do nothing (but stop checkstyle from complaining). } } //note early exit! ResultSet rs = ps.getResultSet(); try { return getGeneratedIdentity(rs); } finally { //JdbcUtils.closeResultSet(rs); close(rs, null); } } //else if (Settings.isGetGeneratedKeysEnabled()) //{ // ps.executeUpdate(); // //note early exit! // return getGeneratedIdentity(ps.getGeneratedKeys()); //} //else //{ // ps.executeUpdate(); //need post insert then select identity //postInsert.append("NEED"); //} //else if else else { ps.executeUpdate(); ResultSet rs = ps.getGeneratedKeys(); if (rs != null) { //getGeneratedKeys()key try { log.debug("Using getGeneratedKeys() to get keys."); return getGeneratedIdentity(rs); } finally { close(rs, null); } } //elsegetPostInsertGeneratedIndentity } } finally { //JdbcUtils.closeStatement(ps); close(null, ps); //JdbcUtils.closeConnection(conn); //PostInsertGeneratedIndentiry manager.releaseConnection(conn); } } catch (SQLException ex) { //throw new QueryException("could not insert: ", ex); log.debug("could not get id for insert, using getPostInsertGeneratedIndentity(): " + ex.getMessage()); } return getPostInsertGeneratedIndentity(); }
From source file:com.xqdev.sql.MLSQL.java
protected void doPost(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException { res.setContentType("text/xml"); Namespace sql = Namespace.getNamespace("sql", "http://xqdev.com/sql"); Document responseDoc = new Document(); Element root = new Element("result", sql); Element meta = new Element("meta", sql); responseDoc.setRootElement(root);/*from www .java 2 s . com*/ root.addContent(meta); Document requestDoc = null; try { // Normally the request comes via the post body, // but we let you bookmark w/ a query string String postbody = req.getParameter("postbody"); if (postbody != null) { SAXBuilder builder = new SAXBuilder(); requestDoc = builder.build(new StringReader(postbody)); } else { InputStream in = req.getInputStream(); SAXBuilder builder = new SAXBuilder(); requestDoc = builder.build(in); } } catch (Exception e) { addExceptions(meta, e); // Now write the error and return OutputStream out = res.getOutputStream(); new XMLOutputter().output(responseDoc, out); out.flush(); return; } Connection con = null; try { Namespace[] namespaces = new Namespace[] { sql }; XPathHelper xpath = new XPathHelper(requestDoc, namespaces); String type = xpath.getString("/sql:request/sql:type"); String query = xpath.getString("/sql:request/sql:query"); int maxRows = xpath.getInt("/sql:request/sql:execute-options/sql:max-rows", -1); int queryTimeout = xpath.getInt("/sql:request/sql:execute-options/sql:query-timeout", -1); int maxFieldSize = xpath.getInt("/sql:request/sql:execute-options/sql:max-field-size", -1); List<Element> params = xpath .getElements("/sql:request/sql:execute-options/sql:parameters/sql:parameter"); con = pool.getConnection(); PreparedStatement stmt = null; if (type.equalsIgnoreCase("procedure")) { stmt = con.prepareCall(query); } else { // Note this call depends on JDBC 3.0 (accompanying Java 1.4). // The call without the 2nd argument would work on earlier JVMs, // you just won't catch any generated keys. stmt = con.prepareStatement(query, Statement.RETURN_GENERATED_KEYS); } configureStatement(stmt, maxRows, queryTimeout, maxFieldSize); parameterizeStatement(stmt, params); if (type.equalsIgnoreCase("select")) { try { ResultSet rs = stmt.executeQuery(); addWarnings(meta, stmt.getWarnings()); addResultSet(root, rs); } catch (SQLException e) { addExceptions(meta, e); Log.log(e); } } else if (type.equalsIgnoreCase("update")) { try { int count = stmt.executeUpdate(); addWarnings(meta, stmt.getWarnings()); addUpdateCount(meta, count); try { addGeneratedKeys(meta, stmt.getGeneratedKeys()); } catch (SQLException e) { // Generated keys are available on INSERT calls but not UPDATE calls // So catch and eat the exception that Oracle (and maybe others) will throw } } catch (SQLException e) { addExceptions(meta, e); } } else if (type.equalsIgnoreCase("procedure")) { boolean isResultSet = stmt.execute(); if (isResultSet) { addResultSet(root, stmt.getResultSet()); addOutParam(root, stmt, params); } else { addOutParam(root, stmt, params); } } else { try { boolean isResultSet = stmt.execute(); addWarnings(meta, stmt.getWarnings()); if (isResultSet) { addResultSet(root, stmt.getResultSet()); } else { addUpdateCount(meta, stmt.getUpdateCount()); addGeneratedKeys(meta, stmt.getGeneratedKeys()); } } catch (SQLException e) { addExceptions(meta, e); } } // Close the statement holding the connection to the JDBC Server stmt.close(); } catch (Exception e) { addExceptions(meta, e); } finally { if (con != null) pool.returnConnection(con); } OutputStream out = res.getOutputStream(); new XMLOutputter().output(responseDoc, out); out.flush(); }
From source file:org.wso2.carbon.appfactory.core.dao.JDBCApplicationDAO.java
/** * This method is used to get the all version ids of the given application * (This method is private since it is only been used when deleting an application. * We do not use getAllApplicationVersions(ApplicationID) because of the following reason. * The version object does not have a field to keep the database index. * If we add such a field then there is the risk of exposing the database index publicly. * Since that is a bad practice we use this method to get only the version IDs) * * @param applicationID The application id of the current application * @return a list of version ids//from ww w .j a va2s.co m * @throws AppFactoryException if SQL operation fails */ private List<Integer> getAllVersionIdsOfApplication(String applicationID) throws AppFactoryException { Connection databaseConnection = null; PreparedStatement preparedStatement = null; ResultSet resultSet = null; List<Integer> versionList = new ArrayList<Integer>(); try { databaseConnection = AppFactoryDBUtil.getConnection(); preparedStatement = databaseConnection.prepareStatement(SQLConstants.GET_ALL_APPLICATION_VERSION_ID); preparedStatement.setInt(1, getAutoIncrementAppID(applicationID, databaseConnection)); preparedStatement.execute(); resultSet = preparedStatement.getResultSet(); while (resultSet.next()) { versionList.add(resultSet.getInt(SQLParameterConstants.COLUMN_NAME_ID)); } } catch (SQLException e) { handleException("Error while getting all the version of application : " + applicationID, e); } finally { AppFactoryDBUtil.closeResultSet(resultSet); AppFactoryDBUtil.closePreparedStatement(preparedStatement); AppFactoryDBUtil.closeConnection(databaseConnection); } handleDebugLog("List of Version IDs of application : " + applicationID + " are : " + versionList); return versionList; }
From source file:csiro.pidsvc.mappingstore.Manager.java
public MappingParentDescriptorList getParents(int mappingId, URI uri) throws SQLException { MappingParentDescriptorList ret = new MappingParentDescriptorList(1); PreparedStatement pst = null; ResultSet rs = null;/*from w w w . java2 s . c o m*/ int parentId; int defaultActionId = MappingMatchResults.NULL; String mappingPath, parentPath = null, mappingType = null; Object aux = null; try { pst = _connection.prepareStatement( "SELECT mapping_path, parent, type, default_action_id FROM vw_active_mapping WHERE mapping_id = ? AND mapping_path IS NOT NULL"); pst.setInt(1, mappingId); // Get initial mapping. if (pst.execute()) { rs = pst.getResultSet(); if (rs.next()) { mappingPath = rs.getString(1); parentPath = rs.getString(2); mappingType = rs.getString(3); defaultActionId = rs.getInt(4); if (rs.wasNull()) defaultActionId = MappingMatchResults.NULL; aux = mappingType.equalsIgnoreCase("1:1") ? true : Pattern.compile(mappingPath, getCaseSensitivity().RegularExpressionFlags); ret.add(new MappingParentDescriptor(mappingId, mappingPath, defaultActionId, aux)); } } rs.close(); pst.close(); rs = null; pst = null; // Get parents. while (parentPath != null) { pst = _connection.prepareStatement( "SELECT mapping_id, mapping_path, parent, default_action_id FROM vw_active_mapping WHERE mapping_path = ? AND type = 'Regex'"); pst.setString(1, parentPath); parentPath = null; if (pst.execute()) { rs = pst.getResultSet(); if (rs.next()) { parentId = rs.getInt(1); mappingPath = rs.getString(2); parentPath = rs.getString(3); defaultActionId = rs.getInt(4); if (rs.wasNull()) defaultActionId = MappingMatchResults.NULL; // Prevent cyclic inheritance syndrome. if (ret.contains(parentId)) break; // Check that parent pattern matches URI. Pattern re = Pattern.compile(mappingPath, getCaseSensitivity().RegularExpressionFlags); Matcher m = re.matcher(uri.getPathNoExtension()); if (!m.find()) break; // Add new parent to the list. ret.add(new MappingParentDescriptor(parentId, rs.getString(2), defaultActionId, re)); } } } // Get catch-all mapping descriptor. MappingParentDescriptor catchAll = getCatchAllDescriptor(); if (catchAll != null) ret.add(catchAll); } finally { if (rs != null) rs.close(); if (pst != null) pst.close(); } return ret; }