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:csiro.pidsvc.mappingstore.ManagerJson.java
@SuppressWarnings("unchecked") public JSONObject getLookups(int page, String namespace) throws SQLException { PreparedStatement pst = null; ResultSet rs = null;//from w w w. ja va 2 s.c o m JSONObject ret = new JSONObject(); final int pageSize = 20; try { String query = ""; if (namespace != null && !namespace.isEmpty()) query += " AND ns ILIKE ?"; query = "SELECT COUNT(*) FROM lookup_ns" + (query.isEmpty() ? "" : " WHERE " + query.substring(5)) + ";\n" + "SELECT * FROM lookup_ns" + (query.isEmpty() ? "" : " WHERE " + query.substring(5)) + " ORDER BY ns LIMIT " + pageSize + " OFFSET " + ((page - 1) * pageSize) + ";"; pst = _connection.prepareStatement(query); for (int i = 1, j = 0; j < 2; ++j) { // Bind parameters twice to two almost identical queries. if (namespace != null && !namespace.isEmpty()) pst.setString(i++, "%" + namespace.replace("\\", "\\\\") + "%"); } if (pst.execute()) { rs = pst.getResultSet(); rs.next(); ret.put("count", rs.getInt(1)); ret.put("page", page); ret.put("pages", (int) Math.ceil(rs.getFloat(1) / pageSize)); JSONArray jsonArr = new JSONArray(); for (pst.getMoreResults(), rs = pst.getResultSet(); rs.next();) { jsonArr.add(JSONObjectHelper.create("ns", rs.getString("ns"), "type", rs.getString("type"))); } ret.put("results", jsonArr); } } finally { if (rs != null) rs.close(); if (pst != null) pst.close(); } return ret; }
From source file:csiro.pidsvc.mappingstore.ManagerJson.java
@SuppressWarnings("unchecked") public JSONObject getConditionSets(int page, String searchQuery) throws SQLException { PreparedStatement pst = null; ResultSet rs = null;/*from ww w . j ava 2 s .c om*/ JSONObject ret = new JSONObject(); final int pageSize = 20; boolean isQueryNotEmpty = searchQuery != null && !searchQuery.isEmpty(); try { String query = ""; if (isQueryNotEmpty) query += " AND name ILIKE ?"; query = "SELECT COUNT(*) FROM condition_set" + (query.isEmpty() ? "" : " WHERE " + query.substring(5)) + ";\n" + "SELECT * FROM condition_set" + (query.isEmpty() ? "" : " WHERE " + query.substring(5)) + " ORDER BY name LIMIT " + pageSize + " OFFSET " + ((page - 1) * pageSize) + ";"; pst = _connection.prepareStatement(query); for (int i = 1, j = 0; j < 2; ++j) { // Bind parameters twice to two almost identical queries. if (isQueryNotEmpty) pst.setString(i++, "%" + searchQuery.replace("\\", "\\\\") + "%"); } if (pst.execute()) { rs = pst.getResultSet(); rs.next(); ret.put("count", rs.getInt(1)); ret.put("page", page); ret.put("pages", (int) Math.ceil(rs.getFloat(1) / pageSize)); JSONArray jsonArr = new JSONArray(); for (pst.getMoreResults(), rs = pst.getResultSet(); rs.next();) { jsonArr.add(JSONObjectHelper.create("name", rs.getString("name"), "description", rs.getString("description"))); } ret.put("results", jsonArr); } } finally { if (rs != null) rs.close(); if (pst != null) pst.close(); } return ret; }
From source file:org.apache.beehive.controls.system.jdbc.JdbcControlImpl.java
/** * Create and exec a {@link PreparedStatement} * * @param method the method to invoke// w w w . j a v a2s . co m * @param args the method's arguments * @return the return value from the {@link PreparedStatement} * @throws Throwable any exception that occurs; the caller should handle these appropriately */ protected Object execPreparedStatement(Method method, Object[] args) throws Throwable { final SQL methodSQL = (SQL) _context.getMethodPropertySet(method, SQL.class); if (methodSQL == null || methodSQL.statement() == null) { throw new ControlException("Method " + method.getName() + " is missing @SQL annotation"); } setTypeMappers(methodSQL.typeMappersOverride()); // // build the statement and execute it // PreparedStatement ps = null; try { Class returnType = method.getReturnType(); SqlStatement sqlStatement = _sqlParser.parse(methodSQL.statement()); ps = sqlStatement.createPreparedStatement(_context, _connection, _cal, method, args); if (LOGGER.isInfoEnabled()) { LOGGER.info("PreparedStatement: " + sqlStatement.createPreparedStatementString(_context, _connection, method, args)); } // // special processing for batch updates // if (sqlStatement.isBatchUpdate()) { return ps.executeBatch(); } // // execute the statement // boolean hasResults = ps.execute(); // // callable statement processing // if (sqlStatement.isCallableStatement()) { SQLParameter[] params = (SQLParameter[]) args[0]; for (int i = 0; i < params.length; i++) { if (params[i].dir != SQLParameter.IN) { params[i].value = ((CallableStatement) ps).getObject(i + 1); } } return null; } // // process returned data // ResultSet rs = null; int updateCount = ps.getUpdateCount(); if (hasResults) { rs = ps.getResultSet(); } if (sqlStatement.getsGeneratedKeys()) { rs = ps.getGeneratedKeys(); hasResults = true; } if (!hasResults && updateCount > -1) { boolean moreResults = ps.getMoreResults(); int tempUpdateCount = ps.getUpdateCount(); while ((moreResults && rs == null) || tempUpdateCount > -1) { if (moreResults) { rs = ps.getResultSet(); hasResults = true; moreResults = false; tempUpdateCount = -1; } else { moreResults = ps.getMoreResults(); tempUpdateCount = ps.getUpdateCount(); } } } Object returnObject = null; if (hasResults) { // // if a result set mapper was specified in the methods annotation, use it // otherwise find the mapper for the return type in the hashmap // final Class resultSetMapperClass = methodSQL.resultSetMapper(); final ResultSetMapper rsm; if (!UndefinedResultSetMapper.class.isAssignableFrom(resultSetMapperClass)) { if (ResultSetMapper.class.isAssignableFrom(resultSetMapperClass)) { rsm = (ResultSetMapper) resultSetMapperClass.newInstance(); } else { throw new ControlException( "Result set mappers must be subclasses of ResultSetMapper.class!"); } } else { if (_resultMappers.containsKey(returnType)) { rsm = _resultMappers.get(returnType); } else { if (_xmlObjectClass != null && _xmlObjectClass.isAssignableFrom(returnType)) { rsm = _resultMappers.get(_xmlObjectClass); } else { rsm = DEFAULT_MAPPER; } } } returnObject = rsm.mapToResultType(_context, method, rs, _cal); if (rsm.canCloseResultSet() == false) { getResources().add(ps); } // // empty ResultSet // } else { if (returnType.equals(Void.TYPE)) { returnObject = null; } else if (returnType.equals(Integer.TYPE)) { returnObject = new Integer(updateCount); } else if (!sqlStatement.isCallableStatement()) { throw new ControlException( "Method " + method.getName() + "is DML but does not return void or int"); } } return returnObject; } finally { // Keep statements open that have in-use result sets if (ps != null && !getResources().contains(ps)) { ps.close(); } } }
From source file:csiro.pidsvc.mappingstore.ManagerJson.java
@SuppressWarnings("unchecked") public JSONObject getMappings(int page, String mappingPath, String type, String creator, int includeDeprecated) throws SQLException { PreparedStatement pst = null; ResultSet rs = null;//from www . j a v a 2 s. com JSONObject ret = new JSONObject(); final int pageSize = 10; final String sourceView = (includeDeprecated == 2 ? "vw_deprecated_mapping" : (includeDeprecated == 1 ? "vw_latest_mapping" : "vw_active_mapping")); try { String query = "mapping_path IS NOT NULL"; if (mappingPath != null && !mappingPath.isEmpty()) query += " AND (title ILIKE ? OR mapping_path ILIKE ?)"; if (type != null && !type.isEmpty()) query += " AND type = ?"; if (creator != null && !creator.isEmpty()) query += " AND creator = ?"; query = "SELECT COUNT(*) FROM " + sourceView + (query.isEmpty() ? "" : " WHERE " + query) + ";\n" + "SELECT mapping_id, mapping_path, title, description, creator, type, to_char(date_start, 'DD/MM/YYYY HH24:MI') AS date_start, to_char(date_end, 'DD/MM/YYYY HH24:MI') AS date_end FROM " + sourceView + (query.isEmpty() ? "" : " WHERE " + query) + " ORDER BY COALESCE(title, mapping_path) LIMIT " + pageSize + " OFFSET " + ((page - 1) * pageSize) + ";"; pst = _connection.prepareStatement(query); // Bind parameters twice to two almost identical queries. for (int i = 1, j = 0; j < 2; ++j) { if (!mappingPath.isEmpty()) { pst.setString(i++, "%" + mappingPath.replace("\\", "\\\\") + "%"); pst.setString(i++, "%" + mappingPath.replace("\\", "\\\\") + "%"); } if (!type.isEmpty()) pst.setString(i++, type); if (!creator.isEmpty()) pst.setString(i++, creator); } if (pst.execute()) { rs = pst.getResultSet(); rs.next(); ret.put("count", rs.getInt(1)); ret.put("page", page); ret.put("pages", (int) Math.ceil(rs.getFloat(1) / pageSize)); JSONArray jsonArr = new JSONArray(); for (pst.getMoreResults(), rs = pst.getResultSet(); rs.next();) { // String dateStart = sdf.format(sdfdb.parse(rs.getString("date_start"))); // String dateEnd = rs.getString("date_end"); // if (dateEnd != null) // dateEnd = sdf.format(sdfdb.parse(dateEnd)); jsonArr.add(JSONObjectHelper.create("mapping_id", rs.getString("mapping_id"), "mapping_path", rs.getString("mapping_path"), "title", rs.getString("title"), "description", rs.getString("description"), "creator", rs.getString("creator"), "type", rs.getString("type"), "date_start", rs.getString("date_start"), "date_end", rs.getString("date_end"), "date", "")); } ret.put("results", jsonArr); } } finally { if (rs != null) rs.close(); if (pst != null) pst.close(); } return ret; }