List of usage examples for java.sql PreparedStatement getResultSet
ResultSet getResultSet() throws SQLException;
ResultSet object. From source file:Tim.MarkovChains.java
public int getSeedWord(String message, String type, int lastSeed) { String[] words = message.split(" "); HashSet<Integer> wordIds = new HashSet<>(); Connection con = null;/*from w w w . j a v a 2 s . co m*/ for (String word : words) { wordIds.add(getMarkovWordId(word)); } wordIds.remove(lastSeed); if (wordIds.isEmpty()) { return 0; } String ids = StringUtils.join(wordIds, ","); try { con = Tim.db.pool.getConnection(timeout); PreparedStatement s; if (type.equals("say")) { s = con.prepareStatement( "SELECT * FROM (SELECT second_id FROM markov3_say_data msd WHERE msd.first_id = 1 AND msd.third_id != 1 AND msd.second_id IN (" + ids + ") " + "GROUP BY msd.second_id ORDER BY sum(msd.count) ASC LIMIT ?) derived ORDER BY RAND() LIMIT 1"); } else { s = con.prepareStatement( "SELECT * FROM (SELECT second_id FROM markov3_emote_data msd WHERE msd.first_id = 1 AND msd.third_id != 1 AND msd.second_id IN (" + ids + ") " + "GROUP BY msd.second_id ORDER BY sum(msd.count) ASC LIMIT ?) derived ORDER BY RAND() LIMIT 1"); } int innerLimit = 2; if ((words.length / 4) > 2) { innerLimit = words.length / 4; } s.setInt(1, innerLimit); s.executeQuery(); ResultSet rs = s.getResultSet(); if (rs.next()) { return rs.getInt(1); } rs.close(); s.close(); } catch (SQLException ex) { Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex); } finally { try { if (con != null) { con.close(); } } catch (SQLException ex) { Logger.getLogger(Tim.class.getName()).log(Level.SEVERE, null, ex); } } return 0; }
From source file:org.apache.ambari.log4j.hadoop.mapreduce.jobhistory.MapReduceJobHistoryUpdater.java
private void processJobSubmittedEvent(PreparedStatement jobPS, PreparedStatement workflowSelectPS, PreparedStatement workflowPS, PreparedStatement workflowUpdateTimePS, LoggingEvent logEvent, JobSubmittedEvent historyEvent) { try {//from w ww .j a v a 2s. co m String jobId = historyEvent.getJobId().toString(); jobPS.setString(1, jobId); jobPS.setString(2, historyEvent.getJobName()); jobPS.setString(3, historyEvent.getUserName()); jobPS.setString(4, historyEvent.getJobConfPath()); jobPS.setString(5, historyEvent.getJobQueueName()); jobPS.setLong(6, historyEvent.getSubmitTime()); WorkflowContext workflowContext = buildWorkflowContext(historyEvent); // Get workflow information boolean insertWorkflow = false; String existingContextString = null; ResultSet rs = null; try { workflowSelectPS.setString(1, workflowContext.getWorkflowId()); workflowSelectPS.execute(); rs = workflowSelectPS.getResultSet(); if (rs.next()) { existingContextString = rs.getString(1); } else { insertWorkflow = true; } } catch (SQLException sqle) { LOG.warn("workflow select failed with: ", sqle); insertWorkflow = false; } finally { try { if (rs != null) rs.close(); } catch (SQLException e) { LOG.error("Exception while closing ResultSet", e); } } // Insert workflow if (insertWorkflow) { workflowPS.setString(1, workflowContext.getWorkflowId()); workflowPS.setString(2, workflowContext.getWorkflowName()); workflowPS.setString(3, getWorkflowString(getSanitizedWorkflow(workflowContext, null))); workflowPS.setString(4, historyEvent.getUserName()); workflowPS.setLong(5, historyEvent.getSubmitTime()); workflowPS.setLong(6, historyEvent.getSubmitTime()); workflowPS.setLong(7, workflowContext.getWorkflowDag().size()); workflowPS.executeUpdate(); LOG.debug("Successfully inserted workflowId = " + workflowContext.getWorkflowId()); } else { ObjectMapper om = new ObjectMapper(); WorkflowContext existingWorkflowContext = null; try { if (existingContextString != null) existingWorkflowContext = om.readValue(existingContextString.getBytes(), WorkflowContext.class); } catch (IOException e) { LOG.warn("Couldn't read existing workflow context for " + workflowContext.getWorkflowId(), e); } WorkflowContext sanitizedWC = getSanitizedWorkflow(workflowContext, existingWorkflowContext); workflowUpdateTimePS.setString(1, getWorkflowString(sanitizedWC)); workflowUpdateTimePS.setLong(2, sanitizedWC.getWorkflowDag().size()); workflowUpdateTimePS.setLong(3, historyEvent.getSubmitTime()); workflowUpdateTimePS.setLong(4, historyEvent.getSubmitTime()); workflowUpdateTimePS.setString(5, workflowContext.getWorkflowId()); workflowUpdateTimePS.executeUpdate(); LOG.debug("Successfully updated workflowId = " + workflowContext.getWorkflowId()); } // Insert job jobPS.setString(7, workflowContext.getWorkflowId()); jobPS.setString(8, workflowContext.getWorkflowEntityName()); jobPS.executeUpdate(); LOG.debug("Successfully inserted job = " + jobId + " and workflowId = " + workflowContext.getWorkflowId()); } catch (SQLException sqle) { LOG.info("Failed to store " + historyEvent.getEventType() + " for job " + historyEvent.getJobId() + " into " + JOB_TABLE, sqle); } catch (Exception e) { LOG.info("Failed to store " + historyEvent.getEventType() + " for job " + historyEvent.getJobId() + " into " + JOB_TABLE, e); } }
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;/* w ww.j a v a2 s. co m*/ 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.plasma.sdo.jdbc.service.JDBCSupport.java
protected List<List<PropertyPair>> fetch(PlasmaType type, StringBuilder sql, Object[] params, Connection con) { List<List<PropertyPair>> result = new ArrayList<List<PropertyPair>>(); PreparedStatement statement = null; ResultSet rs = null;/*from w ww .ja v a 2 s . c om*/ 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, String.valueOf(params[i])); statement.execute(); rs = statement.getResultSet(); ResultSetMetaData rsMeta = rs.getMetaData(); int numcols = rsMeta.getColumnCount(); 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); Object value = converter.fromJDBCDataType(rs, i, columnType, prop); if (value != null) { PropertyPair pair = new PropertyPair((PlasmaProperty) prop, value); row.add(pair); } } } } 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 getMappingDependencies(Object thisMapping, String parentPath) throws SQLException { Vector<String> parentsList = new Vector<String>(); PreparedStatement pst = null; ResultSet rs = null;//from w ww . ja v a2s.co m JSONObject ret = null, jsonThis = null, jsonParent = null, jsonParent2 = null; JSONArray jsonArr = null; String mappingPath, title; int mappingId, inheritors; try { // Get current mapping descriptor. if (thisMapping instanceof JSONObject) { jsonThis = (JSONObject) thisMapping; parentsList.add("__this"); } else { mappingId = (Integer) thisMapping; pst = _connection.prepareStatement("SELECT a.mapping_path, a.title, a.description, a.creator, " + " (SELECT COUNT(1) FROM vw_active_mapping aa WHERE aa.parent = a.mapping_path) AS inheritors " + "FROM mapping a " + "WHERE a.mapping_id = ?"); pst.setInt(1, mappingId); if (pst.execute()) { jsonThis = new JSONObject(); rs = pst.getResultSet(); if (rs.next()) { mappingPath = rs.getString("mapping_path"); title = rs.getString("title"); inheritors = rs.getInt("inheritors"); // Initial mapping ID is required for detection of cyclic inheritance. parentsList.add(mappingPath); jsonThis.put("id", "__this"); jsonThis.put("name", title == null ? mappingPath : title); jsonThis.put("data", JSONObjectHelper.create("mapping_path", mappingPath, "title", title, "description", rs.getString("description"), "author", rs.getString("creator"), "css", "chart_label_current", "inheritors", inheritors)); if (inheritors > 0) { jsonArr = new JSONArray(); jsonArr.add(JSONObjectHelper.create("id", -2, "name", inheritors + " inheritor" + (inheritors == 1 ? "" : "s") + "...", "data", JSONObjectHelper.create("css", "chart_label_hidden", "inheritors", inheritors))); jsonThis.put("children", jsonArr); } } } } // Get parents. while (parentPath != null) { pst = _connection.prepareStatement("SELECT mapping_path, title, description, creator, parent " + "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()) { mappingPath = rs.getString("mapping_path"); title = rs.getString("title"); parentPath = rs.getString("parent"); // Prevent cyclic inheritance syndrome. if (parentsList.contains(mappingPath)) { jsonArr = new JSONArray(); jsonArr.add(jsonThis); jsonParent = JSONObjectHelper.create("id", -3, "name", "ERROR", "data", JSONObjectHelper.create("css", "chart_label_error", "description", "Cyclic inheritance encountered!<br/><br/>Please inspect the inheritance chain and rectify the problem. Mappings with detected cyclic inheritance will fall back to Catch-all mapping automatically."), "children", jsonArr); return jsonParent; } // Construct JSON for the first parent. if (jsonParent2 == null) { JSONObject tmp = new JSONObject(); tmp.put("id", mappingPath); tmp.put("name", title == null ? mappingPath : title); tmp.put("data", JSONObjectHelper.create("mapping_path", mappingPath, "title", title, "description", rs.getString("description"), "author", rs.getString("creator"))); if (jsonParent == null) { jsonArr = new JSONArray(); jsonArr.add(jsonThis); jsonParent = tmp; jsonParent.put("children", jsonArr); } else if (jsonParent2 == null) { jsonArr = new JSONArray(); jsonArr.add(jsonParent); jsonParent2 = tmp; jsonParent2.put("children", jsonArr); } } // Add new parent to the list. parentsList.add(mappingPath); } } } if (jsonParent == null) jsonParent = jsonThis; // Get catch-all mapping descriptor. String author = null, description = null; int hiddenParents = parentsList.size() - 2; pst = _connection.prepareStatement("SELECT description, creator " + "FROM vw_active_mapping " + "WHERE mapping_path IS NULL AND type = 'Regex'"); if (pst.execute()) { if ((rs = pst.getResultSet()).next()) { author = rs.getString("creator"); description = rs.getString("description"); } } if (hiddenParents == 1) jsonParent = jsonParent2; else if (hiddenParents > 1) { jsonArr = new JSONArray(); jsonArr.add(jsonParent); jsonParent = JSONObjectHelper.create("id", -1, "name", hiddenParents + " more parents...", "data", JSONObjectHelper.create("css", "chart_label_hidden"), "children", jsonArr); } // Create return object. jsonArr = new JSONArray(); jsonArr.add(jsonParent); ret = JSONObjectHelper.create("id", 0, "name", "<Catch-all>", "data", JSONObjectHelper.create( "author", author, "description", description, "css", "chart_label_root"), "children", jsonArr ); } finally { if (rs != null) rs.close(); if (pst != null) pst.close(); } return ret; }
From source file:gobblin.source.extractor.extract.jdbc.JdbcExtractor.java
/** * Execute query using JDBC PreparedStatement to pass query parameters Set * fetch size// ww w . ja v a2 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:csiro.pidsvc.mappingstore.Manager.java
public int getTotalQrCodeHits(String mappingPath) throws SQLException { PreparedStatement pst = null; try {/*from w w w . ja v a 2 s . co m*/ pst = _connection.prepareStatement("SELECT SUM(qr_hits) FROM mapping WHERE mapping_path = ?;"); pst.setString(1, mappingPath); if (pst.execute()) { ResultSet rs = pst.getResultSet(); if (rs.next()) return rs.getInt(1); } } finally { if (pst != null) pst.close(); } return 0; }
From source file:csiro.pidsvc.mappingstore.Manager.java
public String getSetting(String name) { PreparedStatement pst = null; ResultSet rs = null;//w w w . j ava 2s .c om try { pst = _connection.prepareStatement("SELECT value FROM configuration WHERE name = ?"); pst.setString(1, name); if (pst.execute()) { rs = pst.getResultSet(); if (rs.next()) return rs.getString(1); } } catch (Exception e) { _logger.error(e); } finally { try { if (rs != null) rs.close(); if (pst != null) pst.close(); } catch (SQLException e) { _logger.error(e); } } return null; }
From source file:csiro.pidsvc.mappingstore.Manager.java
protected String[] getLookupKeyValue(String ns) { PreparedStatement pst = null; ResultSet rs = null;/* ww w . jav a 2s.c o m*/ try { pst = _connection.prepareStatement("SELECT key, value FROM lookup WHERE ns = ? LIMIT 1"); pst.setString(1, ns); if (pst.execute()) { rs = pst.getResultSet(); if (rs.next()) return new String[] { rs.getString(1), rs.getString(2) }; } } catch (Exception e) { _logger.error(e); } finally { try { if (rs != null) rs.close(); if (pst != null) pst.close(); } catch (SQLException e) { _logger.error(e); } } return null; }
From source file:csiro.pidsvc.mappingstore.Manager.java
public int getConditionSetId(String name) throws SQLException { PreparedStatement pst = null; ResultSet rs = null;/* ww w . j av a 2s . c o m*/ try { pst = _connection.prepareStatement("SELECT condition_set_id FROM condition_set WHERE name = ?;"); pst.setString(1, name); if (pst.execute()) { rs = pst.getResultSet(); if (rs.next()) return rs.getInt("condition_set_id"); } return -1; } finally { if (rs != null) rs.close(); if (pst != null) pst.close(); } }