List of usage examples for java.sql PreparedStatement getMoreResults
boolean getMoreResults() throws SQLException;
Statement object's next result, returns true if it is a ResultSet object, and implicitly closes any current ResultSet object(s) obtained with the method getResultSet. From source file:com.chaosinmotion.securechat.server.commands.CreateAccount.java
/** * Process the create account request. This should receive the following * objects: the username, the password, the device ID and the public key * for the device. This adds a new entry in the account database, and * creates a new device.//www . ja v a 2s. c o m * * If the user account cannot be created, this returns nil. * @param requestParams * @return */ public static UserInfo processRequest(JSONObject requestParams) throws ClassNotFoundException, SQLException, IOException { String username = requestParams.optString("username"); String password = requestParams.optString("password"); String deviceid = requestParams.optString("deviceid"); String pubkey = requestParams.optString("pubkey"); /* * Attempt to insert a new user into the database */ Connection c = null; PreparedStatement ps = null; ResultSet rs = null; try { c = Database.get(); ps = c.prepareStatement("INSERT INTO Users " + " ( username, password ) " + "VALUES " + " ( ?, ? ); SELECT currval('Users_userid_seq')"); ps.setString(1, username); ps.setString(2, password); try { ps.execute(); } catch (SQLException ex) { return null; // Can't insert; duplicate username? } int utc = ps.getUpdateCount(); int userid = 0; if ((utc == 1) && ps.getMoreResults()) { rs = ps.getResultSet(); if (rs.next()) { userid = rs.getInt(1); } rs.close(); rs = null; } ps.close(); ps = null; /* * We now have the user index. Insert the device. Note that it is * highly unlikely we will have a UUID collision, but we verify * we don't by deleting any rows in the device table with the * specified UUID. The worse case scenario is a collision which * knocks someone else off the air. (The alternative would be * to accidentally send the wrong person duplicate messages.) * * Note that we don't actually use a device-identifying identifer, * choosing instead to pick a UUID, so we need to deal with * the possibility (however remote) of duplicate UUIDs. * * In the off chance we did have a collision, we also delete all * old messages to the device; that prevents messages from being * accidentally delivered. */ ps = c.prepareStatement("DELETE FROM Messages " + "WHERE messageid IN " + " (SELECT Messages.messageid " + " FROM Messages, Devices " + " WHERE Messages.deviceid = Devices.deviceid " + " AND Devices.deviceuuid = ?)"); ps.setString(1, deviceid); ps.execute(); ps.close(); ps = null; ps = c.prepareStatement("DELETE FROM Devices WHERE deviceuuid = ?"); ps.setString(1, deviceid); ps.execute(); ps.close(); ps = null; ps = c.prepareStatement("INSERT INTO Devices " + " ( userid, deviceuuid, publickey ) " + "VALUES " + " ( ?, ?, ?)"); ps.setInt(1, userid); ps.setString(2, deviceid); ps.setString(3, pubkey); ps.execute(); /* * Complete; return the user info record */ return new Login.UserInfo(userid); } finally { if (rs != null) rs.close(); if (ps != null) ps.close(); if (c != null) c.close(); } }
From source file:org.apache.cayenne.access.jdbc.SQLTemplateAction.java
protected void execute(Connection connection, OperationObserver callback, SQLStatement compiled, Collection<Number> updateCounts) throws SQLException, Exception { long t1 = System.currentTimeMillis(); boolean iteratedResult = callback.isIteratedResult(); PreparedStatement statement = connection.prepareStatement(compiled.getSql()); try {/*from w w w . jav a2s . c o m*/ bind(statement, compiled.getBindings()); // process a mix of results boolean isResultSet = statement.execute(); boolean firstIteration = true; while (true) { if (firstIteration) { firstIteration = false; } else { isResultSet = statement.getMoreResults(); } if (isResultSet) { ResultSet resultSet = statement.getResultSet(); if (resultSet != null) { try { processSelectResult(compiled, connection, statement, resultSet, callback, t1); } finally { if (!iteratedResult) { resultSet.close(); } } // ignore possible following update counts and bail early on iterated results if (iteratedResult) { break; } } } else { int updateCount = statement.getUpdateCount(); if (updateCount == -1) { break; } updateCounts.add(updateCount); dataNode.getJdbcEventLogger().logUpdateCount(updateCount); } } } finally { if (!iteratedResult) { statement.close(); } } }
From source file:net.solarnetwork.node.dao.jdbc.AbstractJdbcDao.java
/** * Persist a domain object, without using auto-generated keys. * /*from w w w . j av a 2 s . c o m*/ * @param obj * the domain object to persist * @param sqlInsert * the SQL insert statement to use * @return the primary key created for the domain object */ protected Long storeDomainObjectWithoutAutogeneratedKeys(final T obj, final String sqlInsert) { Object result = getJdbcTemplate().execute(new PreparedStatementCreator() { @Override public PreparedStatement createPreparedStatement(Connection con) throws SQLException { PreparedStatement ps = con.prepareStatement(sqlInsert); setStoreStatementValues(obj, ps); return ps; } }, new PreparedStatementCallback<Object>() { @Override public Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException { ps.execute(); int count = ps.getUpdateCount(); if (count == 1 && ps.getMoreResults()) { ResultSet rs = ps.getResultSet(); if (rs.next()) { return rs.getObject(1); } } return null; } }); if (result instanceof Long) { return (Long) result; } else if (result instanceof Number) { return Long.valueOf(((Number) result).longValue()); } if (log.isWarnEnabled()) { log.warn("Unexpected (non-number) primary key returned: " + result); } return null; }
From source file:com.alibaba.wasp.jdbc.TestPreparedStatement.java
public void testGetMoreResults() throws SQLException { Statement stat = conn.createStatement(); PreparedStatement prep; ResultSet rs;/*from w ww . j a v a 2 s .c o m*/ stat.execute("CREATE TABLE TEST(ID INT)"); stat.execute("INSERT INTO TEST VALUES(1)"); prep = conn.prepareStatement("SELECT * FROM TEST"); // just to check if it doesn't throw an exception - it may be null prep.getMetaData(); assertTrue(prep.execute()); rs = prep.getResultSet(); assertFalse(prep.getMoreResults()); assertEquals(-1, prep.getUpdateCount()); // supposed to be closed now assertThrows(SQLErrorCode.OBJECT_CLOSED, rs).next(); assertEquals(-1, prep.getUpdateCount()); prep = conn.prepareStatement("UPDATE TEST SET ID = 2"); assertFalse(prep.execute()); assertEquals(1, prep.getUpdateCount()); assertFalse(prep.getMoreResults(Statement.CLOSE_CURRENT_RESULT)); assertEquals(-1, prep.getUpdateCount()); // supposed to be closed now assertThrows(SQLErrorCode.OBJECT_CLOSED, rs).next(); assertEquals(-1, prep.getUpdateCount()); prep = conn.prepareStatement("DELETE FROM TEST"); prep.executeUpdate(); assertFalse(prep.getMoreResults()); assertEquals(-1, prep.getUpdateCount()); }
From source file:org.apache.jmeter.protocol.jdbc.AbstractJDBCwoTimeOutTestElement.java
private String resultSetsToString(final PreparedStatement pstmt, boolean result, final int[] out) throws SQLException, UnsupportedEncodingException { final StringBuilder sb = new StringBuilder(); int updateCount = 0; if (!result) { updateCount = pstmt.getUpdateCount(); }// w w w. ja v a 2 s .c om do { if (result) { ResultSet rs = null; try { rs = pstmt.getResultSet(); sb.append(getStringFromResultSet(rs)).append("\n"); // $NON-NLS-1$ } finally { close(rs); } } else { sb.append(updateCount).append(" updates.\n"); } result = pstmt.getMoreResults(); if (!result) { updateCount = pstmt.getUpdateCount(); } } while (result || (updateCount != -1)); if (out != null && pstmt instanceof CallableStatement) { final ArrayList<Object> outputValues = new ArrayList<Object>(); final CallableStatement cs = (CallableStatement) pstmt; sb.append("Output variables by position:\n"); for (int i = 0; i < out.length; i++) { if (out[i] != java.sql.Types.NULL) { final Object o = cs.getObject(i + 1); outputValues.add(o); sb.append("["); sb.append(i + 1); sb.append("] "); sb.append(o); sb.append("\n"); } } final String varnames[] = getVariableNames().split(COMMA); if (varnames.length > 0) { final JMeterVariables jmvars = getThreadContext().getVariables(); for (int i = 0; i < varnames.length && i < outputValues.size(); i++) { final String name = varnames[i].trim(); if (name.length() > 0) { // Save the value in the variable if present final Object o = outputValues.get(i); jmvars.put(name, o == null ? null : o.toString()); } } } } return sb.toString(); }
From source file:org.apache.jmeter.protocol.jdbc.sampler.JDBCSampler.java
private String resultSetsToString(PreparedStatement pstmt, boolean result, int[] out) throws SQLException, UnsupportedEncodingException { StrBuilder sb = new StrBuilder(); int updateCount = 0; if (!result) { updateCount = pstmt.getUpdateCount(); }/* ww w . j av a 2 s .c o m*/ do { if (result) { ResultSet rs = null; try { rs = pstmt.getResultSet(); sb.append(getStringFromResultSet(rs)).append("\n"); // $NON-NLS-1$ } finally { close(rs); } } else { sb.append(updateCount).append(" updates.\n"); } result = pstmt.getMoreResults(); if (!result) { updateCount = pstmt.getUpdateCount(); } } while (result || (updateCount != -1)); if (out != null && pstmt instanceof CallableStatement) { CallableStatement cs = (CallableStatement) pstmt; sb.append("Output variables by position:\n"); for (int i = 0; i < out.length; i++) { if (out[i] != java.sql.Types.NULL) { sb.append("["); sb.append(i + 1); sb.append("] "); sb.append(cs.getObject(i + 1)); sb.append("\n"); } } } return sb.toString(); }
From source file:org.apache.jmeter.protocol.jdbc.AbstractJDBCTestElement.java
private String resultSetsToString(PreparedStatement pstmt, boolean result, int[] out) throws SQLException, UnsupportedEncodingException { StringBuilder sb = new StringBuilder(); int updateCount = 0; if (!result) { updateCount = pstmt.getUpdateCount(); }/* w w w. j a v a2 s .c o m*/ do { if (result) { ResultSet rs = null; try { rs = pstmt.getResultSet(); sb.append(getStringFromResultSet(rs)).append("\n"); // $NON-NLS-1$ } finally { close(rs); } } else { sb.append(updateCount).append(" updates.\n"); } result = pstmt.getMoreResults(); if (!result) { updateCount = pstmt.getUpdateCount(); } } while (result || (updateCount != -1)); if (out != null && pstmt instanceof CallableStatement) { List<Object> outputValues = new ArrayList<>(); CallableStatement cs = (CallableStatement) pstmt; sb.append("Output variables by position:\n"); for (int i = 0; i < out.length; i++) { if (out[i] != java.sql.Types.NULL) { Object o = cs.getObject(i + 1); outputValues.add(o); sb.append("["); sb.append(i + 1); sb.append("] "); sb.append(o); if (o instanceof java.sql.ResultSet && RS_COUNT_RECORDS.equals(resultSetHandler)) { sb.append(" ").append(countRows((ResultSet) o)).append(" rows"); } sb.append("\n"); } } String[] varnames = getVariableNames().split(COMMA); if (varnames.length > 0) { JMeterVariables jmvars = getThreadContext().getVariables(); for (int i = 0; i < varnames.length && i < outputValues.size(); i++) { String name = varnames[i].trim(); if (name.length() > 0) { // Save the value in the variable if present Object o = outputValues.get(i); if (o instanceof java.sql.ResultSet) { ResultSet resultSet = (ResultSet) o; if (RS_STORE_AS_OBJECT.equals(resultSetHandler)) { jmvars.putObject(name, o); } else if (RS_COUNT_RECORDS.equals(resultSetHandler)) { jmvars.put(name, o.toString() + " " + countRows(resultSet) + " rows"); } else { jmvars.put(name, o.toString()); } } else { jmvars.put(name, o == null ? null : o.toString()); } } } } } return sb.toString(); }
From source file:com.cloudera.sqoop.tool.EvalSqlTool.java
@Override /** {@inheritDoc} */ public int run(SqoopOptions options) { if (!init(options)) { return 1; }/*from w ww .j a v a 2s .co m*/ PreparedStatement stmt = null; ResultSet rs = null; PrintWriter pw = null; try { Connection c = manager.getConnection(); String query = options.getSqlQuery(); LOG.debug("SQL query: " + query); stmt = c.prepareStatement(query); boolean resultType = stmt.execute(); // Iterate over all the results from this statement. while (true) { LOG.debug("resultType=" + resultType); if (!resultType) { // This result was an update count. int updateCount = stmt.getUpdateCount(); LOG.debug("updateCount=" + updateCount); if (updateCount == -1) { // We are done iterating over results from this statement. c.commit(); break; } else { LOG.info(updateCount + " row(s) updated."); } } else { // This yields a ResultSet. rs = stmt.getResultSet(); pw = new PrintWriter(System.out, true); new ResultSetPrinter().printResultSet(pw, rs); pw.close(); pw = null; } resultType = stmt.getMoreResults(); } } catch (IOException ioe) { LOG.warn("IOException formatting results: " + StringUtils.stringifyException(ioe)); return 1; } catch (SQLException sqlE) { LOG.warn("SQL exception executing statement: " + StringUtils.stringifyException(sqlE)); return 1; } finally { if (null != pw) { pw.close(); } if (null != rs) { try { rs.close(); } catch (SQLException sqlE) { LOG.warn("SQL exception closing ResultSet: " + StringUtils.stringifyException(sqlE)); } } if (null != stmt) { try { stmt.close(); } catch (SQLException sqlE) { LOG.warn("SQL exception closing statement: " + StringUtils.stringifyException(sqlE)); } } destroy(options); } return 0; }
From source file:csiro.pidsvc.mappingstore.ManagerJson.java
@SuppressWarnings("unchecked") public JSONObject getLookupConfig(String ns) throws SQLException { PreparedStatement pst = null; ResultSet rs = null;// w w w .java 2 s . c o m JSONObject ret = new JSONObject(); JSONArray jsonArr; String lookupType; try { pst = _connection.prepareStatement( "SELECT ns, type, behaviour_type, behaviour_value FROM lookup_ns WHERE ns = ?;SELECT key, value FROM lookup WHERE ns = ?;"); pst.setString(1, ns); pst.setString(2, ns); if (pst.execute()) { rs = pst.getResultSet(); if (rs.next()) { lookupType = rs.getString("type"); ret.put("ns", rs.getString("ns")); ret.put("type", lookupType); ret.put("default", JSONObjectHelper.create("type", rs.getString("behaviour_type"), "value", rs.getString("behaviour_value"))); pst.getMoreResults(); rs = pst.getResultSet(); if (lookupType.equalsIgnoreCase("Static")) { jsonArr = new JSONArray(); while (rs.next()) { jsonArr.add(JSONObjectHelper.create("key", rs.getString(1), "value", rs.getString(2))); } ret.put("lookup", jsonArr); } else if (lookupType.equalsIgnoreCase("HttpResolver")) { if (rs.next()) { final Pattern reType = Pattern.compile("^T:(.+)$", Pattern.CASE_INSENSITIVE | Pattern.MULTILINE); final Pattern reExtract = Pattern.compile("^E:(.+)$", Pattern.CASE_INSENSITIVE | Pattern.MULTILINE); final Pattern reNamespace = Pattern.compile("^NS:(.+?):(.+)$", Pattern.CASE_INSENSITIVE | Pattern.MULTILINE); Matcher m; String buf = rs.getString(2); try { JSONObject jsonPart = new JSONObject(); jsonPart.put("endpoint", rs.getString(1)); // Type. m = reType.matcher(buf); m.find(); jsonPart.put("type", m.group(1)); // Extractor. m = reExtract.matcher(buf); m.find(); jsonPart.put("extractor", m.group(1)); // Namespaces. m = reNamespace.matcher(buf); jsonArr = new JSONArray(); while (m.find()) { jsonArr.add(JSONObjectHelper.create("prefix", m.group(1), "uri", m.group(2))); } jsonPart.put("namespaces", jsonArr); ret.put("lookup", jsonPart); } catch (Exception e) { _logger.debug(e); return null; } } } } } } finally { if (rs != null) rs.close(); if (pst != null) pst.close(); } return ret; }
From source file:org.opoo.oqs.core.AbstractQuery.java
/** * ResultSetID/* w w w . ja va2s.c om*/ * * @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(); }