List of usage examples for java.sql ResultSet getObject
Object getObject(String columnLabel) throws SQLException;
Gets the value of the designated column in the current row of this ResultSet object as an Object in the Java programming language.
From source file:au.aurin.org.svc.GeodataFinder.java
public List<String> getPolygonIDS(final String uazTbl, final String polygonStr) { final String query = "select a.propid, ST_Asgeojson(geom) from " + postgisDataStoreConfig.getDataStoreParams().get(SCHEMA.key) + "." + uazTbl + " as a INNER JOIN ST_GeomFromText('" + polygonStr + "', 4326) as b ON ST_Intersects(a.geom, b.geometry)" + " where a.propid is not null "; // this line added later for avoiding // null./*from w w w . jav a2 s . c o m*/ final JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource); LOGGER.info("getPolygonIDS: query is {} ", query); final List<String> entries = jdbcTemplate.query(query, new RowMapper<String>() { @Override public String mapRow(final ResultSet rs, final int arg1) throws SQLException { return rs.getObject(1).toString(); } }); LOGGER.info(" returning {} distinct entries ", entries.size()); return entries; }
From source file:com.jbrisbin.vpc.jobsched.sql.SqlMessageHandler.java
public SqlMessage handleMessage(final SqlMessage msg) throws Exception { log.debug("handling message: " + msg.toString()); DataSource ds = appCtx.getBean(msg.getDatasource(), DataSource.class); JdbcTemplate tmpl = new JdbcTemplate(ds); String sql = msg.getSql();/*from w ww . j a va2 s . c om*/ CallableStatementCreator stmtCreator = null; CallableStatementCallback<SqlMessage> callback = null; if (sql.startsWith("plugin:")) { // Use a plugin to get the sql String pluginName = (sql.contains("?") ? sql.substring(7, sql.indexOf('?')) : sql.substring(7)); final Plugin plugin = groovyPluginManager.getPlugin(pluginName); Map<String, Object> vars = new LinkedHashMap<String, Object>(); vars.put("message", msg); vars.put("datasource", ds); vars.put("listen", groovyClosureFactory.createListenClosure(msg)); vars.put("mapreduce", groovyClosureFactory.createMapReduceClosure(msg)); plugin.setContext(vars); // Execute this plugin plugin.run(); Object o = plugin.get("sql"); if (null != o && o instanceof Closure) { sql = ((Closure) o).call(msg).toString(); } else if (o instanceof String || o instanceof GString) { sql = o.toString(); } else { throw new IllegalStateException("Can't convert " + String.valueOf(o) + " to SQL statement."); } msg.setSql(sql); o = plugin.get("statementCreator"); if (null != o && o instanceof Closure) { stmtCreator = new CallableStatementCreator() { public CallableStatement createCallableStatement(Connection con) throws SQLException { Object obj = ((Closure) plugin.get("statementCreator")).call(new Object[] { con, msg }); log.debug("from plugin statementCreator: " + String.valueOf(obj)); return (CallableStatement) obj; } }; } else { throw new IllegalStateException("Can't convert " + String.valueOf(o) + " to CallableStatementCreator. Define a closure named 'statementCreator' in your plugin."); } o = plugin.get("callback"); if (null != o && o instanceof Closure) { callback = new CallableStatementCallback<SqlMessage>() { public SqlMessage doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException { Object obj = ((Closure) plugin.get("callback")).call(new Object[] { cs, msg }); log.debug("from plugin callback: " + String.valueOf(obj)); return (SqlMessage) obj; } }; } else { throw new IllegalStateException("Can't convert " + String.valueOf(o) + " to CallableStatementCallback. Define a closure named 'callback' in your plugin."); } } else { stmtCreator = new CallableStatementCreator() { public CallableStatement createCallableStatement(Connection connection) throws SQLException { CallableStatement stmt = connection.prepareCall(msg.getSql()); List<Object> params = msg.getParams(); if (null != params) { int index = 1; for (Object obj : params) { stmt.setObject(index++, obj); } } return stmt; } }; callback = new CallableStatementCallback<SqlMessage>() { public SqlMessage doInCallableStatement(CallableStatement callableStatement) throws SQLException, DataAccessException { if (null == msg.getResults().getData()) { msg.getResults().setData(new ArrayList<List<Object>>()); } if (callableStatement.execute()) { ResultSet results = callableStatement.getResultSet(); // Pull out column names ResultSetMetaData meta = results.getMetaData(); String[] columns = new String[meta.getColumnCount()]; for (int i = 1; i <= meta.getColumnCount(); i++) { columns[i - 1] = meta.getColumnName(i); } msg.getResults().getColumnNames().addAll(Arrays.asList(columns)); int total = 0; while (results.next()) { List<Object> row = new ArrayList<Object>(columns.length); for (int i = 1; i <= columns.length; i++) { row.add(results.getObject(i)); } msg.getResults().getData().add(row); total++; } msg.getResults().setTotalRows(total); } else { msg.getResults().getColumnNames().add("updateCount"); msg.getResults().setTotalRows(1); List<Object> updCnt = new ArrayList<Object>(1); updCnt.add(callableStatement.getUpdateCount()); msg.getResults().getData().add(updCnt); } return msg; } }; } try { tmpl.setExceptionTranslator(appCtx.getBean(SQLExceptionTranslator.class)); } catch (NoSuchBeanDefinitionException notfound) { // IGNORED } if (null != stmtCreator && null != callback) { try { tmpl.execute(stmtCreator, callback); } catch (Throwable t) { log.error(t.getMessage(), t); List<String> errors = new ArrayList<String>(); errors.add(t.getMessage()); Throwable cause = t.getCause(); if (null != cause) { do { errors.add(cause.getMessage()); } while (null != (cause = cause.getCause())); } msg.getResults().setErrors(errors); } } else { log.warn("CallableStatementCreator and/or CallableStatementCallback where empty. " + "Make sure your plugin provides these under 'statementCreator' and 'callback' respectively."); } return msg; }
From source file:edu.ku.brc.specify.toycode.mexconabio.AnalysisBase.java
/** * @param rs//from www .j av a 2 s .co m * @param colInx * @return * @throws SQLException */ protected String getStr(final ResultSet rs, final int colInx) throws SQLException { Object obj = rs.getObject(colInx); return obj != null ? ((String) obj).trim() : null; }
From source file:com.thinkbiganalytics.ingest.TableMergeSyncSupport.java
protected List<PartitionBatch> toPartitionBatches(PartitionSpec spec, ResultSet rs) throws SQLException { Vector<PartitionBatch> v = new Vector<>(); int count = rs.getMetaData().getColumnCount(); while (rs.next()) { String[] values = new String[count]; for (int i = 1; i <= count; i++) { Object oVal = rs.getObject(i); String sVal = (oVal == null ? "" : oVal.toString()); values[i - 1] = StringUtils.defaultString(sVal, ""); }//from ww w . j a va 2s .c o m Long numRecords = rs.getLong(count); v.add(new PartitionBatch(numRecords, spec, values)); } logger.info("Number of partitions [" + v.size() + "]"); return v; }
From source file:hw.java
public static DefaultTableModel buildTableModel(ResultSet rs) throws SQLException { ResultSetMetaData metaData = rs.getMetaData(); Vector<String> columnNames = new Vector<String>(); int columnCount = metaData.getColumnCount(); for (int column = 1; column <= columnCount; column++) { columnNames.add(metaData.getColumnName(column)); }//from w w w . j a v a2s . com Vector<Vector<Object>> data = new Vector<Vector<Object>>(); while (rs.next()) { Vector<Object> vector = new Vector<Object>(); for (int columnIndex = 1; columnIndex <= columnCount; columnIndex++) { vector.add(rs.getObject(columnIndex)); } data.add(vector); } return new DefaultTableModel(data, columnNames); }
From source file:edu.ku.brc.specify.toycode.mexconabio.CopyPlantsFromGBIF.java
/** * /*from w ww . java 2 s.c o m*/ */ public void processNonNullNonPlantKingdom() { PrintWriter pw = null; try { pw = new PrintWriter("gbif_plants_from_nonnull.log"); } catch (FileNotFoundException e) { e.printStackTrace(); } System.out.println("----------------------- Search non-Plantae ----------------------- "); String gbifWhereStr = "FROM raw WHERE kingdom = '%s'"; Vector<String> nonPlantKingdoms = new Vector<String>(); String sqlStr = "SELECT * FROM (select kingdom, count(kingdom) as cnt from plants.raw WHERE kingdom is not null AND NOT (lower(kingdom) like '%plant%') group by kingdom) T1 ORDER BY cnt desc;"; for (Object[] obj : BasicSQLUtils.query(sqlStr)) { String kingdom = (String) obj[0]; Integer count = (Integer) obj[1]; System.out.println(kingdom + " " + count); pw.println(kingdom + " " + count); if (!StringUtils.contains(kingdom.toLowerCase(), "plant")) { nonPlantKingdoms.add(kingdom); } } long startTime = System.currentTimeMillis(); for (String kingdom : nonPlantKingdoms) { String where = String.format(gbifWhereStr, kingdom); String cntGBIFSQL = "SELECT COUNT(*) " + where; String gbifSQL = gbifSQLBase + where; System.out.println(cntGBIFSQL); long totalRecs = BasicSQLUtils.getCount(srcConn, cntGBIFSQL); long procRecs = 0; int secsThreshold = 0; String msg = String.format("Query: %8.2f secs", (double) (System.currentTimeMillis() - startTime) / 1000.0); System.out.println(msg); pw.println(msg); pw.flush(); startTime = System.currentTimeMillis(); Statement gStmt = null; PreparedStatement pStmt = null; try { pStmt = dstConn.prepareStatement(pSQL); System.out.println("Total Records: " + totalRecs); pw.println("Total Records: " + totalRecs); pw.flush(); gStmt = srcConn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); gStmt.setFetchSize(Integer.MIN_VALUE); ResultSet rs = gStmt.executeQuery(gbifSQL); ResultSetMetaData rsmd = rs.getMetaData(); while (rs.next()) { String genus = rs.getString(16); if (genus == null) continue; String species = rs.getString(17); if (isPlant(colStmtGN, colStmtGNSP, genus, species) || isPlant(colDstStmtGN, colDstStmtGNSP, genus, species)) { for (int i = 1; i <= rsmd.getColumnCount(); i++) { Object obj = rs.getObject(i); pStmt.setObject(i, obj); } try { pStmt.executeUpdate(); } catch (Exception ex) { System.err.println("For Old ID[" + rs.getObject(1) + "]"); ex.printStackTrace(); pw.print("For Old ID[" + rs.getObject(1) + "] " + ex.getMessage()); pw.flush(); } procRecs++; if (procRecs % 10000 == 0) { long endTime = System.currentTimeMillis(); long elapsedTime = endTime - startTime; double avergeTime = (double) elapsedTime / (double) procRecs; double hrsLeft = (((double) elapsedTime / (double) procRecs) * (double) totalRecs - procRecs) / HRS; int seconds = (int) (elapsedTime / 60000.0); if (secsThreshold != seconds) { secsThreshold = seconds; msg = String.format( "Elapsed %8.2f hr.mn Ave Time: %5.2f Percent: %6.3f Hours Left: %8.2f ", ((double) (elapsedTime)) / HRS, avergeTime, 100.0 * ((double) procRecs / (double) totalRecs), hrsLeft); System.out.println(msg); pw.println(msg); pw.flush(); } } } } } catch (Exception ex) { ex.printStackTrace(); } finally { try { if (gStmt != null) { gStmt.close(); } if (pStmt != null) { pStmt.close(); } pw.close(); } catch (Exception ex) { } } } System.out.println("Done transferring."); pw.println("Done transferring."); }
From source file:org.tradex.jdbc.JDBCHelper.java
/** * Executes the passed SQL and returns the resulting rows maps of values keyed by column name within a map keyed by rownumber (starting with zero) * @param sql The SQL to execute/*from www.j av a2s. c o m*/ * @return the results */ public Map<Integer, Map<String, Object>> result(CharSequence sql) { Map<Integer, Map<String, Object>> results = new TreeMap<Integer, Map<String, Object>>(); Map<Integer, String> colNumToName; Connection conn = null; PreparedStatement ps = null; ResultSet rset = null; try { conn = ds.getConnection(); ps = conn.prepareStatement(sql.toString()); rset = ps.executeQuery(); int colCount = rset.getMetaData().getColumnCount(); colNumToName = new HashMap<Integer, String>(colCount); ResultSetMetaData rsmd = rset.getMetaData(); for (int i = 1; i <= colCount; i++) { colNumToName.put(i, rsmd.getColumnLabel(i)); } int rowNum = 0; while (rset.next()) { Map<String, Object> row = new HashMap<String, Object>(colCount); results.put(rowNum, row); for (int i = 1; i <= colCount; i++) { row.put(colNumToName.get(i), rset.getObject(i)); } rowNum++; } return results; } catch (Exception e) { throw new RuntimeException("Query for [" + sql + "] failed", e); } finally { try { rset.close(); } catch (Exception e) { } try { ps.close(); } catch (Exception e) { } try { conn.close(); } catch (Exception e) { } } }
From source file:edu.ku.brc.specify.toycode.mexconabio.CopyFromGBIF.java
public void testSearch() { Statement stmt = null;/*from ww w . ja v a2 s . co m*/ String querystr = "Andrew AND Bentley AND Apogon AND angustatus"; String term = "contents"; try { stmt = srcDBConn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); if (analyzer == null) { analyzer = new StandardAnalyzer(Version.LUCENE_36); } reader = IndexReader.open(FSDirectory.open(INDEX_DIR), true); long startTime = System.currentTimeMillis(); Query q = new QueryParser(Version.LUCENE_36, term, analyzer).parse(querystr); int hitsPerPage = 10; searcher = new IndexSearcher(reader); TopScoreDocCollector collector = TopScoreDocCollector.create(hitsPerPage, true); searcher.search(q, collector); ScoreDoc[] hits = collector.topDocs().scoreDocs; System.out.println("Found " + hits.length + " hits."); for (int i = 0; i < hits.length; ++i) { int docId = hits[i].doc; Document d = searcher.doc(docId); System.out.println((i + 1) + ". " + d.get("id")); String id = d.get("id"); ResultSet rs = stmt.executeQuery( "SELECT id, catalogue_number, genus, species, collector_num, collector_name, year, month, day FROM raw WHERE id = " + id); ResultSetMetaData rsmd = rs.getMetaData(); while (rs.next()) { for (int j = 1; j <= rsmd.getColumnCount(); j++) { System.out.print(rs.getObject(j) + "\t"); } System.out.println(); } rs.close(); } System.out.println(String.format("Time: %8.2f", (System.currentTimeMillis() - startTime) / 1000.0)); searcher.close(); reader.close(); analyzer.close(); } catch (SQLException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } catch (ParseException e) { e.printStackTrace(); } finally { if (stmt != null) { try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); } } } }
From source file:com.quinsoft.zeidon.dbhandler.JdbcHandler.java
private Object getSqlObject(ResultSet rs, Integer idx, DataField dataField, Map<Integer, Object> loadedObjects) { try {/*from ww w . j a v a 2 s .co m*/ Object o = loadedObjects.get(idx); if (o == null) { o = rs.getObject(idx); loadedObjects.put(idx, o); } return o; } catch (SQLException e) { throw ZeidonException.wrapException(e).appendMessage("DataField: %s, column idx: %d", dataField, idx); } }
From source file:edu.ku.brc.specify.datamodel.Preparation.java
/** * @return/*from w w w.j a va 2 s .com*/ */ @Transient public Boolean getIsOnLoan() { if (isOnLoan == null) { Connection conn = null; Statement stmt = null; try { conn = DBConnection.getInstance().createConnection(); if (conn != null) { stmt = conn.createStatement(); String sql = "SELECT p.CountAmt, lp.Quantity, lp.QuantityResolved, lp.QuantityReturned, lp.IsResolved FROM preparation p " + "INNER JOIN loanpreparation lp ON p.PreparationID = lp.PreparationID WHERE p.PreparationID = " + getId(); ResultSet rs = stmt.executeQuery(sql); int totalOnLoan = 0; Integer prepQty = null; while (rs.next()) { prepQty = rs.getObject(1) != null ? rs.getInt(1) : 0; //System.err.println("\nprepQty "+prepQty); boolean isResolved = rs.getObject(5) != null ? rs.getBoolean(5) : false; int loanQty = rs.getObject(2) != null ? rs.getInt(2) : 0; int qtyRes = rs.getObject(3) != null ? rs.getInt(3) : 0; //int qtyRtn = rs.getObject(4) != null ? rs.getInt(4) : 0; //System.err.println("loanQty "+loanQty); //System.err.println("qtyRes "+qtyRes); //System.err.println("qtyRtn "+qtyRtn); if (isResolved && qtyRes != loanQty) // this shouldn't happen { qtyRes = loanQty; } totalOnLoan += loanQty - qtyRes; } rs.close(); if (prepQty == null) { return false; } isOnLoan = totalOnLoan > 0; //System.err.println("totalOnLoan "+totalOnLoan); //System.err.println("isOnLoan "+isOnLoan); } else { UsageTracker.incrNetworkUsageCount(); } } catch (SQLException ex) { edu.ku.brc.exceptions.ExceptionTracker.getInstance().capture(Preparation.class, ex); UsageTracker.incrSQLUsageCount(); ex.printStackTrace(); } finally { if (stmt != null) { try { stmt.close(); } catch (SQLException ex) { } } if (conn != null) { try { conn.close(); } catch (SQLException ex) { } } } } return isOnLoan == null ? false : isOnLoan; }