List of usage examples for java.sql PreparedStatement getResultSet
ResultSet getResultSet() throws SQLException;
ResultSet object. From source file:csiro.pidsvc.mappingstore.ManagerJson.java
@SuppressWarnings("unchecked") public JSONArray searchParentMapping(int mappingId, String searchTerm) throws SQLException { PreparedStatement pst = null; ResultSet rs = null;/*from w ww . j a v a 2s. c o m*/ JSONArray ret = new JSONArray(); try { String query = "SELECT mapping_path, title " + "FROM vw_active_mapping " + "WHERE type = 'Regex' AND (mapping_path ILIKE ? OR title ILIKE ?) " + (mappingId > 0 ? "AND mapping_id != " + mappingId + " " : "") + "ORDER BY title, mapping_path " + "LIMIT 10;"; searchTerm = "%" + searchTerm + "%"; pst = _connection.prepareStatement(query); pst.setString(1, searchTerm); pst.setString(2, searchTerm); if (pst.execute()) { for (rs = pst.getResultSet(); rs.next();) ret.add(JSONObjectHelper.create("mapping_path", rs.getString("mapping_path"), "title", rs.getString("title"))); } } finally { if (rs != null) rs.close(); if (pst != null) pst.close(); } return ret; }
From source file:org.zaproxy.zap.extension.websocket.db.TableWebSocket.java
public synchronized List<WebSocketMessageDTO> getMessages(WebSocketMessageDTO criteria, List<Integer> opcodes, List<Integer> inScopeChannelIds, WebSocketMessagesPayloadFilter payloadFilter, int offset, int limit, int payloadPreviewLength) throws DatabaseException { try {/*from w w w .j a v a 2s . c o m*/ String query = "SELECT m.message_id, m.channel_id, m.timestamp, m.opcode, m.payload_length, m.is_outgoing, " + "m.payload_utf8, m.payload_bytes, " + "f.fuzz_id, f.state, f.fuzz " + "FROM websocket_message AS m " + "LEFT OUTER JOIN websocket_message_fuzz f " + "ON m.message_id = f.message_id AND m.channel_id = f.channel_id " + "<where> " + "ORDER BY m.timestamp, m.channel_id, m.message_id " + "LIMIT ? " + "OFFSET ?"; PreparedStatement stmt; try { stmt = buildMessageCriteriaStatement(query, criteria, opcodes, inScopeChannelIds); } catch (SQLException e) { if (getConnection().isClosed()) { return new ArrayList<>(0); } throw e; } try { int paramsCount = stmt.getParameterMetaData().getParameterCount(); stmt.setInt(paramsCount - 1, limit); stmt.setInt(paramsCount, offset); stmt.execute(); return checkPayloadFilter(payloadFilter, buildMessageDTOs(stmt.getResultSet(), true, payloadPreviewLength)); } finally { stmt.close(); } } catch (SQLException e) { throw new DatabaseException(e); } }
From source file:org.apache.openjpa.jdbc.sql.PostgresDictionary.java
private boolean queryOwnership(Connection conn, String[] namePair, DBIdentifier schema) throws Throwable { PreparedStatement ps = null; ResultSet rs = null;/*w w w .j a v a2s . c om*/ try { ps = prepareStatement(conn, isOwnedSequenceSQL); String tblName = ""; if (!DBIdentifier.isEmpty(schema)) { tblName = schema.getName() + getIdentifierDelimiter(); } tblName += namePair[0]; ps.setString(1, tblName); String colName = toDBName(DBIdentifier.newColumn(namePair[1])); ps.setString(2, colName); ps.execute(); rs = ps.getResultSet(); if (rs == null || !rs.next()) { return false; } String val = getString(rs, 1); if (val == null || val.length() == 0) { return false; } return true; } catch (Throwable t) { if (t instanceof ReportingSQLException) { // Handle known/acceptable exceptions // 42P01 - table does not exist // 42703 - column does not exist within table ReportingSQLException rse = (ReportingSQLException) t; if ("42P01".equals(rse.getSQLState()) || "42703".equals(rse.getSQLState())) { return false; } } throw t; } finally { if (rs != null) { try { rs.close(); } catch (Throwable t) { } } if (ps != null) { try { ps.close(); } catch (Throwable t) { } } } }
From source file:at.alladin.rmbt.statisticServer.OpenTestSearchResource.java
/** * Gets a JSON-String containing all open-data-values of all rows * that matched the given criteria/*from w w w . ja va 2 s. c om*/ * @param whereClause the where-clause to use * @param searchValues the values for the columns which the user wants to filter * @param offset a offset-value for paging (given as "next-cursor" in the response), -1 if none is set * @return */ private String getSearchResult(String whereClause, Queue<Map.Entry<String, FieldType>> searchValues, String orderClause, long offset, long maxrows) { long startTime = System.currentTimeMillis(); String offsetString = (offset > 0) ? " AND t.uid<" + offset : ""; //if no sorting is used String offsetString2 = (offset > 0) ? " OFFSET " + offset : ""; //if sorting is used => may have concurrency issues in the results boolean defaultOrder = true; if (orderClause == null || orderClause.isEmpty()) { orderClause = " ORDER BY t.uid DESC "; offsetString2 = ""; } else { defaultOrder = false; offsetString = ""; } if (maxrows > MAXROWS) maxrows = MAXROWS; if (maxrows <= 0) maxrows = DEFAULTROWS; //There are many LEFT JOINs in the sql statement that are usual not needed. //This has no significant impact on the performance since our DBMS (postgres) //is intelligent enough to ignore these during query optimization if they are //not needed final String sql = "SELECT" + " t.uid as cursor, " + //only for pagination " ('P' || t.open_uuid) open_uuid," + " ('O' || t.open_test_uuid) open_test_uuid," + " to_char(t.time AT TIME ZONE 'UTC', 'YYYY-MM-DD HH24:MI:SS') \"time\"," + //" nt.group_name cat_technology," + //" nt.name network_type," + " t.geo_lat lat," + " t.geo_long long," + //" t.geo_provider loc_src," + //" t.zip_code," + " t.speed_download download_kbit," + " t.speed_upload upload_kbit," + " (t.ping_median::float / 1000000) ping_ms," + " t.signal_strength," + " t.lte_rsrp," + //" ts.name server_name," + //" duration test_duration," + //" num_threads," + " (CASE WHEN publish_public_data THEN CONCAT(plattform,' ',network_group_name) ELSE network_group_name END) as platform, " + " (CASE WHEN publish_public_data THEN COALESCE(adm.fullname, t.model) ELSE '' END) model," + " COALESCE(prov.shortname, mprov.shortname, msim.shortname,msim.name," + " prov.name, mprov.name, network_operator_name, t.public_ip_as_name, network_sim_operator) provider_name " + //" client_software_version client_version," + //" network_operator network_mcc_mnc," + //" network_operator_name network_name," + //" network_sim_operator sim_mcc_mnc," + //" nat_type \"connection\"," + //" public_ip_asn asn," + //" client_public_ip_anonymized ip_anonym," + //" (ndt.s2cspd*1000)::int ndt_download_kbit," + //" (ndt.c2sspd*1000)::int ndt_upload_kbit" + " FROM test t" + " LEFT JOIN network_type nt ON nt.uid=t.network_type" + " LEFT JOIN device_map adm ON adm.codename=t.model" + " LEFT JOIN test_server ts ON ts.uid=t.server_id" + " LEFT JOIN provider prov ON provider_id = prov.uid " + " LEFT JOIN provider mprov ON mobile_provider_id = mprov.uid" + " LEFT JOIN mccmnc2name msim ON mobile_sim_id = msim.uid" + //TODO: finalize migration to msim/mnwk " WHERE " + " (t.deleted = false)" + ((this.excludeImplausible) ? " AND implausible = false" : "") + " AND status = 'FINISHED' " + whereClause + offsetString + orderClause + " LIMIT " + maxrows + offsetString2; final String[] columns; PreparedStatement ps = null; ResultSet rs = null; final JSONObject response = new JSONObject(); final JSONArray resultList = new JSONArray(); try { ps = conn.prepareStatement(sql); //fill in values for WHERE ps = fillInWhereClause(ps, searchValues, 1); //Logger.getLogger(OpenTestResource.class.getName()).log(Level.INFO, "prepstmt" + ps); if (!ps.execute()) return null; rs = ps.getResultSet(); long lastUID = 0; //remember last uid for pagination since rs can only be traversed in one direction while (rs.next()) { final JSONObject jsonItem = new JSONObject(); for (int i = 0; i < openDataFieldsSummary.length; i++) { final Object obj = rs.getObject(openDataFieldsSummary[i]); if (obj == null) { jsonItem.put(openDataFieldsSummary[i], JSONObject.NULL); } else if (openDataNumberFields.contains(openDataFieldsSummary[i])) { final String tmp = obj.toString().trim(); if (tmp.isEmpty()) jsonItem.put(openDataFieldsSummary[i], JSONObject.NULL); else jsonItem.put(openDataFieldsSummary[i], JSONObject.stringToValue(tmp)); } else { jsonItem.put(openDataFieldsSummary[i], obj.toString()); } } lastUID = rs.getLong("cursor"); resultList.put(jsonItem); } //if there are more results than we send, use pagination if (resultList.length() == maxrows) { //if it is the standard sort order if (defaultOrder) { response.put("next_cursor", lastUID); } else { offset = (offset < 0) ? 0 : offset; response.put("next_cursor", offset + maxrows); } } else { response.put("next_cursor", JSONObject.NULL); } response.put("results", resultList); //also put in the result, how long the query took to execute long elapsedTime = System.currentTimeMillis() - startTime; response.put("duration_ms", elapsedTime); } catch (final JSONException e) { Logger.getLogger(OpenTestResource.class.getName()).log(Level.SEVERE, null, e); } catch (SQLException ex) { try { setStatus(Status.CLIENT_ERROR_NOT_FOUND); response.put("error", "invalid parameters"); } catch (JSONException ex1) { Logger.getLogger(OpenTestResource.class.getName()).log(Level.SEVERE, null, ex1); } Logger.getLogger(OpenTestResource.class.getName()).log(Level.SEVERE, null, ex); } finally { try { if (rs != null) rs.close(); if (ps != null) ps.close(); } catch (final SQLException e) { Logger.getLogger(OpenTestResource.class.getName()).log(Level.SEVERE, null, e); } } return response.toString(); }
From source file:csiro.pidsvc.mappingstore.ManagerJson.java
@SuppressWarnings("unchecked") protected JSONObject getPidConfigImpl(String query, Object value) throws SQLException { // InputStream inputStream = Thread.currentThread().getContextClassLoader().getResourceAsStream("../pid.json"); // byte[] bytes = new byte[inputStream.available()]; // inputStream.read(bytes); // String s = new String(bytes); // return s; PreparedStatement pst = null; ResultSet rsMapping = null, rsAction = null, rsHistory = null; JSONObject ret = null;//from w w w . jav a 2 s .c om JSONArray jsonArr = null; int mappingId; String mappingPath, parentPath; boolean isParentActive; try { pst = _connection.prepareStatement(query); if (value != null) { if (value instanceof Integer) pst.setInt(1, (Integer) value); else pst.setString(1, (String) value); } if (pst.execute()) { ret = new JSONObject(); for (rsMapping = pst.getResultSet(); rsMapping.next();) { String actionType = rsMapping.getString("action_type"); if (rsMapping.wasNull()) actionType = null; mappingId = rsMapping.getInt("mapping_id"); mappingPath = rsMapping.getString("mapping_path"); parentPath = rsMapping.getString("parent"); isParentActive = rsMapping.getBoolean("parent_is_active"); ret.put("mapping_id", mappingId); ret.put("mapping_path", mappingPath); ret.put("original_path", rsMapping.getString("original_path")); ret.put("type", mappingPath == null ? "Regex" : rsMapping.getString("type")); ret.put("title", mappingPath == null ? "Catch-all" : rsMapping.getString("title")); ret.put("description", rsMapping.getString("description")); ret.put("creator", rsMapping.getString("creator")); ret.put("commit_note", rsMapping.getString("commit_note")); ret.put("ended", rsMapping.getBoolean("ended")); ret.put("qr_hits", this.getTotalQrCodeHits(mappingPath)); // Parent mapping. JSONObject jsonParent = new JSONObject(); jsonParent.put("mapping_path", parentPath); jsonParent.put("title", rsMapping.getString("parent_title")); jsonParent.put("active", isParentActive); if (isParentActive) jsonParent.put("cyclic", !this.checkNonCyclicInheritance(mappingId)); jsonParent.put("graph", getMappingDependencies(mappingId, parentPath)); ret.put("parent", jsonParent); // Default action. if (actionType == null) ret.put("action", null); else { ret.put("action", JSONObjectHelper.create("type", actionType, "name", rsMapping.getString("action_name"), "value", rsMapping.getString("action_value"), "description", rsMapping.getString("default_action_description"))); } // Serialise change history. pst = _connection.prepareStatement("SELECT mapping_id, creator, commit_note, " + " 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 mapping " + "WHERE mapping_path " + (mappingPath == null ? "IS NULL " : "= ? ") + "ORDER BY mapping.date_start DESC"); if (mappingPath != null) pst.setString(1, mappingPath); // History. jsonArr = new JSONArray(); if (pst.execute()) { for (rsHistory = pst.getResultSet(); rsHistory.next();) { jsonArr.add(JSONObjectHelper.create("mapping_id", rsHistory.getInt("mapping_id"), "creator", rsHistory.getString("creator"), "commit_note", rsHistory.getString("commit_note"), "date_start", rsHistory.getString("date_start"), "date_end", rsHistory.getString("date_end"))); } } ret.put("history", jsonArr); // Conditions. ret.put("conditions", getConditionsByMappingId(rsMapping.getInt("mapping_id"))); } if (value == null && (ret == null || ret.isEmpty())) { // Catch-all mapping is not defined yet. Return default. ret = JSONObjectHelper.create( "mapping_id", 0, "mapping_path", null, "original_path", null, "type", "Regex", "title", "Catch-all", "description", null, "creator", null, "commit_note", null, "ended", false, "qr_hits", 0, "parent", null, "action", JSONObjectHelper.create("type", "404", "name", null, "value", null, "description", null), "history", null, "conditions", new JSONArray()); } } } finally { if (rsMapping != null) rsMapping.close(); if (rsAction != null) rsAction.close(); if (rsHistory != null) rsHistory.close(); if (pst != null) pst.close(); } return ret; }
From source file:kenh.xscript.database.elements.Execute.java
/** * Execute sql./*w w w. j av a2 s. co m*/ * @param sql * @param parameter * @param var variable name of result * @param conn */ private int executeSQL(SQLBean sqlBean, String var, java.sql.Connection conn) throws UnsupportedScriptException { if (sqlBean == null || StringUtils.isBlank(sqlBean.getSql())) { UnsupportedScriptException ex = new UnsupportedScriptException(this, "Can't find the sql to execute."); throw ex; } if (conn == null) { throw new UnsupportedScriptException(this, "Connection is empty."); } var = StringUtils.trimToNull(var); PreparedStatement pstmt = null; ResultSet rs = null; try { if (conn.isClosed()) { throw new UnsupportedScriptException(this, "Connection is closed."); } StringBuffer traceInfo = new StringBuffer(); traceInfo.append("Execute SQL: \n" + StringUtils.trim(sqlBean.getSql())); pstmt = conn.prepareStatement(sqlBean.getSql()); Map<String, String> parameters = getParameters(sqlBean); Iterator<String> elements = parameters.values().iterator(); // set the Paramter for PreparedStatement int i = 1; while (elements.hasNext()) { String str = elements.next(); Object obj = this.getEnvironment().parse(str); traceInfo.append("\nParam " + i + ": " + obj.toString()); pstmt.setObject(i, obj); i++; } logger.trace(traceInfo.toString()); boolean result = false; result = pstmt.execute(); if (result) { rs = pstmt.getResultSet(); if (StringUtils.isNotBlank(var)) { ResultSetBean bean = new ResultSetBean(rs); this.saveVariable(var, bean, null); } } else { int count = pstmt.getUpdateCount(); if (StringUtils.isNotBlank(var)) this.saveVariable(var, count, null); } } catch (java.sql.SQLException | IllegalAccessException | InstantiationException e) { this.getEnvironment().setVariable(Constant.VARIABLE_EXCEPTION, e); return EXCEPTION; } catch (UnsupportedExpressionException e) { throw new UnsupportedScriptException(this, e); } finally { if (rs != null) { try { rs.close(); } catch (Exception e) { } rs = null; } if (pstmt != null) { try { pstmt.close(); } catch (Exception e) { } pstmt = null; } } return NONE; }
From source file:Tim.Amusement.java
public void boxodoom(MessageEvent event, String[] args) { int goal;/*from w ww.ja v a 2 s. co m*/ long duration, base_wpm; double modifier; String difficulty = "", original_difficulty = ""; Connection con; if (args.length != 2) { event.respond("!boxodoom requires two parameters."); return; } try { if (Pattern.matches( "(?i)((extra|super)?easy)|average|medium|normal|hard|extreme|insane|impossible|tadiera", args[0])) { original_difficulty = difficulty = args[0].toLowerCase(); duration = (long) Double.parseDouble(args[1]); } else if (Pattern.matches( "(?i)((extra|super)?easy)|average|medium|normal|hard|extreme|insane|impossible|tadiera", args[1])) { original_difficulty = difficulty = args[1].toLowerCase(); duration = (long) Double.parseDouble(args[0]); } else { event.respond( "Difficulty must be one of: extraeasy, easy, average, hard, extreme, insane, impossible, tadiera"); return; } } catch (NumberFormatException ex) { duration = 0; } if (duration < 1) { event.respond("Duration must be greater than or equal to 1."); return; } switch (difficulty) { case "extraeasy": case "supereasy": difficulty = "easy"; break; case "medium": case "normal": difficulty = "average"; break; case "extreme": case "insane": case "impossible": case "tadiera": difficulty = "hard"; break; } String value = ""; try { con = Tim.db.pool.getConnection(timeout); PreparedStatement s = con.prepareStatement( "SELECT `challenge` FROM `box_of_doom` WHERE `difficulty` = ? ORDER BY rand() LIMIT 1"); s.setString(1, difficulty); s.executeQuery(); ResultSet rs = s.getResultSet(); while (rs.next()) { value = rs.getString("challenge"); } con.close(); } catch (SQLException ex) { Logger.getLogger(Tim.class.getName()).log(Level.SEVERE, null, ex); } base_wpm = (long) Double.parseDouble(value); switch (original_difficulty) { case "extraeasy": case "supereasy": base_wpm *= 0.65; break; case "extreme": base_wpm *= 1.4; break; case "insane": base_wpm *= 1.8; break; case "impossible": base_wpm *= 2.2; break; case "tadiera": base_wpm *= 3; break; } modifier = 1.0 / Math.log(duration + 1.0) / 1.5 + 0.68; goal = (int) (duration * base_wpm * modifier / 10) * 10; event.respond("Your goal is " + String.valueOf(goal)); }
From source file:org.cloudgraph.rdb.service.JDBCSupport.java
protected List<PropertyPair> fetchRow(PlasmaType type, StringBuilder sql, Connection con) { List<PropertyPair> result = new ArrayList<PropertyPair>(); PreparedStatement statement = null; ResultSet rs = null;/*from w w w.j av a 2s .c o m*/ try { if (log.isDebugEnabled()) { log.debug("fetch: " + sql.toString()); } statement = con.prepareStatement(sql.toString(), ResultSet.TYPE_FORWARD_ONLY, /* * ResultSet * . * TYPE_SCROLL_INSENSITIVE * , */ ResultSet.CONCUR_READ_ONLY); statement.execute(); rs = statement.getResultSet(); ResultSetMetaData rsMeta = rs.getMetaData(); int numcols = rsMeta.getColumnCount(); int count = 0; while (rs.next()) { 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 = 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); result.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:org.cloudgraph.rdb.service.JDBCSupport.java
protected Map<String, PropertyPair> fetchRowMap(PlasmaType type, StringBuilder sql, Connection con) { Map<String, PropertyPair> result = new HashMap<String, PropertyPair>(); PreparedStatement statement = null; ResultSet rs = null;/*from w w w.ja va2s .com*/ try { if (log.isDebugEnabled()) { log.debug("fetch: " + sql.toString()); } statement = con.prepareStatement(sql.toString(), ResultSet.TYPE_FORWARD_ONLY, /* * ResultSet * . * TYPE_SCROLL_INSENSITIVE * , */ ResultSet.CONCUR_READ_ONLY); statement.execute(); rs = statement.getResultSet(); ResultSetMetaData rsMeta = rs.getMetaData(); int numcols = rsMeta.getColumnCount(); int count = 0; while (rs.next()) { 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 = 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); result.put(prop.getName(), 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:csiro.pidsvc.mappingstore.ManagerJson.java
@SuppressWarnings("unchecked") public JSONObject getLookups(int page, String namespace) throws SQLException { PreparedStatement pst = null; ResultSet rs = null;/*from ww w. j a v a 2s . 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; }