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:com.itemanalysis.jmetrik.stats.itemanalysis.ItemAnalysis.java
private void computeDindex(double lowerCut, double upperCut) throws SQLException { //TODO if requested Dindex, begin another loop over database //Compute 27th and 73rd percentile //loop over items and people and compute d indexes. //TODO do this in separate method. Statement stmt = null;//from w ww . j a va 2 s . c o m ResultSet rs = null; int missingCount = 0; try { //connect to db Table sqlTable = new Table(tableName.getNameForDatabase()); SelectQuery select = new SelectQuery(); for (VariableAttributes v : variables) { select.addColumn(sqlTable, v.getName().nameForDatabase()); } stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); rs = stmt.executeQuery(select.toString()); Object response = null; RawScore rawScore = null; Double responseScore = null; int[] responseVectorIndex = null; Object[] responseVector = null; ClassicalItem tempItem = null; //loop over examinees while (rs.next()) { //loop over items to compute RawScore rawScore = new RawScore(numberOfItems); missingCount = 0; for (VariableAttributes v : variables) { tempItem = item.get(v.positionInDb()); response = rs.getObject(v.getName().nameForDatabase()); //count missing responses per examinee if (response == null || response.equals("")) {//FIXME need to allow a space " " or other special codes to be viewed as missing data missingCount++; } responseScore = v.getItemScoring().computeItemScore(response); rawScore.increment(responseScore); rawScore.incrementResponseVector(v.positionInDb(), response, responseScore); } //only use complete cases if listwise deletion is specified //otherwise a missing item response is scored as 0 if ((listwiseDeletion && missingCount == 0) || !listwiseDeletion) { //loop over items to compute item analysis responseVector = rawScore.getResponseVector(); responseVectorIndex = rawScore.getResponseVectorIndex(); for (int i = 0; i < responseVector.length; i++) { item.get(responseVectorIndex[i]).incrementDindex(responseVector[i], rawScore.value(), lowerCut, upperCut); } } updateProgress(); } //end loop over examinees } catch (SQLException ex) { throw ex; } finally { if (rs != null) rs.close(); if (stmt != null) stmt.close(); conn.setAutoCommit(true); } }
From source file:hoot.services.writers.review.ReviewPrepareDbWriter.java
protected Map<Long, Object> getReviewableElementRecords(final long mapId, final ElementType elementType, final int limit, final int offset) throws InstantiationException, IllegalAccessException, ClassNotFoundException, NoSuchMethodException, InvocationTargetException { Map<Long, Object> retMap = new LinkedHashMap<Long, Object>(); final Element prototype = ElementFactory.getInstance().create(mapId, elementType, conn); String tableName = prototype.getElementTable().getTableName(); String idFieldName = ColumnMetadata.getColumnMetadata(prototype.getElementIdField()).getName(); String POSTGRESQL_DRIVER = "org.postgresql.Driver"; Statement stmt = null;//from ww w. java2 s. com try { Class.forName(POSTGRESQL_DRIVER); stmt = conn.createStatement(); String sql = "select * from " + tableName + "_" + mapId + " where " + " tags->'hoot:review:needs' = 'yes' " + " order by " + idFieldName + " limit " + limit + " offset " + offset; stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(sql); while (rs.next()) { if (elementType == ElementType.Node) { CurrentNodes nodes = new CurrentNodes(); nodes.setId(rs.getLong("id")); nodes.setLatitude(rs.getInt("latitude")); nodes.setLongitude(rs.getInt("longitude")); nodes.setChangesetId(rs.getLong("changeset_id")); nodes.setVisible(rs.getBoolean("visible")); nodes.setTimestamp(rs.getTimestamp("timestamp")); nodes.setTile(rs.getLong("tile")); nodes.setVersion(rs.getLong("version")); nodes.setTags(rs.getObject("tags")); retMap.put(nodes.getId(), nodes); } else if (elementType == ElementType.Way) { CurrentWays ways = new CurrentWays(); ways.setId(rs.getLong("id")); ways.setChangesetId(rs.getLong("changeset_id")); ways.setVisible(rs.getBoolean("visible")); ways.setTimestamp(rs.getTimestamp("timestamp")); ways.setVersion(rs.getLong("version")); ways.setTags(rs.getObject("tags")); retMap.put(ways.getId(), ways); } else if (elementType == ElementType.Relation) { CurrentRelations rel = new CurrentRelations(); rel.setId(rs.getLong("id")); rel.setChangesetId(rs.getLong("changeset_id")); rel.setVisible(rs.getBoolean("visible")); rel.setTimestamp(rs.getTimestamp("timestamp")); rel.setVersion(rs.getLong("version")); rel.setTags(rs.getObject("tags")); retMap.put(rel.getId(), rel); } } rs.close(); } catch (Exception e) { //throw new Exception("Error inserting node."); } finally { //finally block used to close resources try { if (stmt != null) stmt.close(); } catch (SQLException se2) { } // nothing we can do } //end try return retMap; }
From source file:com.tonbeller.jpivot.mondrian.ScriptableMondrianDrillThroughTableModel.java
/** * execute sql query/*from ww w . j av a 2 s. c o m*/ * @throws Exception */ private void executeQuery() { Connection con = null; try { InputStream catExtIs = ScriptableMondrianDrillThroughTableModel.class.getClassLoader() .getResourceAsStream("/" + catalogExtension); Digester catExtDigester = new Digester(); catExtDigester.push(this); catExtDigester.addSetProperties("extension"); catExtDigester.addObjectCreate("extension/script", "com.tonbeller.jpivot.mondrian.script.ScriptColumn"); catExtDigester.addSetProperties("extension/script"); catExtDigester.addSetNext("extension/script", "addScript"); catExtDigester.parse(catExtIs); URL scriptsBaseURL = Thread.currentThread().getContextClassLoader().getResource(scriptRootUrl); scriptEngine = new GroovyScriptEngine(new URL[] { scriptsBaseURL }); con = getConnection(); Statement s = con.createStatement(); s.setMaxRows(maxResults); ResultSet rs = s.executeQuery(sql); ResultSetMetaData md = rs.getMetaData(); int numCols = md.getColumnCount(); List columnTitlesList = new ArrayList(); // set column headings for (int i = 0; i < numCols; i++) { // columns are 1 based columnTitlesList.add(i, md.getColumnName(i + 1)); } // loop on script columns for (ListIterator sIt = scripts.listIterator(); sIt.hasNext();) { final ScriptColumn sc = (ScriptColumn) sIt.next(); columnTitlesList.add(sc.getPosition() - 1, sc.getTitle()); } columnTitles = (String[]) columnTitlesList.toArray(new String[0]); // loop through rows List tempRows = new ArrayList(); Map scriptInput = new HashMap(); Binding binding = new Binding(); while (rs.next()) { List rowList = new ArrayList(); scriptInput.clear(); // loop on columns, 1 based for (int i = 0; i < numCols; i++) { rowList.add(i, rs.getObject(i + 1)); scriptInput.put(columnTitles[i], rs.getObject(i + 1)); } binding.setVariable("input", scriptInput); // loop on script columns for (ListIterator sIt = scripts.listIterator(); sIt.hasNext();) { final ScriptColumn sc = (ScriptColumn) sIt.next(); scriptEngine.run(sc.getFile(), binding); final Object output = binding.getVariable("output"); if (output instanceof Map) { Map outMap = (Map) output; rowList.add(sc.getPosition() - 1, new DefaultCell((String) outMap.get("URL"), (String) outMap.get("Value"))); } else if (output instanceof String) { rowList.add(sc.getPosition() - 1, (String) output); } else { throw new Exception("Unknown groovy script return type (not a Map nor String)."); } } tempRows.add(new DefaultTableRow(rowList.toArray())); } rs.close(); rows = (TableRow[]) tempRows.toArray(new TableRow[0]); } catch (Exception e) { e.printStackTrace(); logger.error("?", e); // problem occured, set table model to zero size rows = new TableRow[1]; columnTitles = new String[1]; columnTitles[0] = "An error occured"; Object[] row = new Object[1]; row[0] = e.toString(); rows[0] = new DefaultTableRow(row); ready = false; return; } finally { try { con.close(); } catch (Exception e1) { // ignore } } ready = true; }
From source file:com.simplymeasured.prognosticator.ThreadedQueryRunnable.java
@Override public void run() { NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(dataSource); try {//from ww w. j ava 2 s . c om template.query(query, parameters, new RowCallbackHandler() { @Override public void processRow(ResultSet resultSet) throws SQLException { try { Map<String, Object> result = Maps.newHashMap(); final ResultSetMetaData metadata = resultSet.getMetaData(); for (int i = 1; i <= metadata.getColumnCount(); i++) { String columnTypeName = metadata.getColumnTypeName(i); final Object value; if ("array".equalsIgnoreCase(columnTypeName)) { String stringValue = resultSet.getString(i); if (stringValue != null) { value = objectMapper.readValue(stringValue, List.class); } else { value = null; } } else if ("map".equalsIgnoreCase(columnTypeName) || "struct".equalsIgnoreCase(columnTypeName)) { String stringValue = resultSet.getString(i); if (stringValue != null) { value = objectMapper.readValue(stringValue, Map.class); } else { value = null; } } else { value = resultSet.getObject(i); } result.put(metadata.getColumnName(i), value); } resultQueue.put(result); } catch (SQLException se) { LOG.warn("Database error!", se); throw new RuntimeException("Database error!", se); } catch (InterruptedException ie) { LOG.warn("Query killed!", ie); throw new RuntimeException("Query killed!", ie); } catch (Exception ex) { LOG.warn("Unable to parse row!", ex); throw new RuntimeException("Unable to parse row!", ex); } } }); resultQueue.put(Collections.<String, Object>emptyMap()); } catch (DataAccessException dae) { try { resultQueue.put(Collections.<String, Object>emptyMap()); } catch (InterruptedException ie) { LOG.warn("Queue is dead!", ie); } LOG.warn("Unable to execute query - attempting to clean up", dae); } catch (InterruptedException ie) { LOG.warn("Queue is dead!", ie); } }
From source file:com.tonbeller.jpivot.mondrian.script.ScriptableMondrianDrillThroughTableModel.java
/** * execute sql query/*from w w w.j ava 2 s.co m*/ * @throws Exception */ private void executeQuery() { Connection con = null; try { InputStream catExtIs = ScriptableMondrianDrillThroughTableModel.class.getClassLoader() .getResourceAsStream("/" + catalogExtension); if (catExtIs != null) { Digester catExtDigester = new Digester(); catExtDigester.push(this); catExtDigester.addSetProperties("extension"); catExtDigester.addObjectCreate("extension/script", "com.tonbeller.jpivot.mondrian.script.ScriptColumn"); catExtDigester.addSetProperties("extension/script"); catExtDigester.addSetNext("extension/script", "addScript"); catExtDigester.parse(catExtIs); URL scriptsBaseURL = Thread.currentThread().getContextClassLoader().getResource(scriptRootUrl); scriptEngine = new GroovyScriptEngine(new URL[] { scriptsBaseURL }); } con = getConnection(); Statement s = con.createStatement(); s.setMaxRows(maxResults); ResultSet rs = s.executeQuery(sql); ResultSetMetaData md = rs.getMetaData(); int numCols = md.getColumnCount(); List columnTitlesList = new ArrayList(); // set column headings for (int i = 0; i < numCols; i++) { // columns are 1 based columnTitlesList.add(i, md.getColumnName(i + 1)); } // loop on script columns for (ListIterator sIt = scripts.listIterator(); sIt.hasNext();) { final ScriptColumn sc = (ScriptColumn) sIt.next(); columnTitlesList.add(sc.getPosition() - 1, sc.getTitle()); } columnTitles = (String[]) columnTitlesList.toArray(new String[0]); // loop through rows List tempRows = new ArrayList(); Map scriptInput = new HashMap(); Binding binding = new Binding(); while (rs.next()) { List rowList = new ArrayList(); scriptInput.clear(); // loop on columns, 1 based for (int i = 0; i < numCols; i++) { rowList.add(i, rs.getObject(i + 1)); scriptInput.put(columnTitles[i], rs.getObject(i + 1)); } binding.setVariable("input", scriptInput); // loop on script columns for (ListIterator sIt = scripts.listIterator(); sIt.hasNext();) { final ScriptColumn sc = (ScriptColumn) sIt.next(); scriptEngine.run(sc.getFile(), binding); final Object output = binding.getVariable("output"); if (output instanceof Map) { Map outMap = (Map) output; rowList.add(sc.getPosition() - 1, new DefaultCell((String) outMap.get("URL"), (String) outMap.get("Value"))); } else if (output instanceof String) { rowList.add(sc.getPosition() - 1, (String) output); } else { throw new Exception("Unknown groovy script return type (not a Map nor String)."); } } tempRows.add(new DefaultTableRow(rowList.toArray())); } rs.close(); rows = (TableRow[]) tempRows.toArray(new TableRow[0]); } catch (Exception e) { e.printStackTrace(); logger.error("?", e); // problem occured, set table model to zero size rows = new TableRow[1]; columnTitles = new String[1]; columnTitles[0] = "An error occured"; Object[] row = new Object[1]; row[0] = e.toString(); rows[0] = new DefaultTableRow(row); ready = false; return; } finally { try { con.close(); } catch (Exception e1) { // ignore } } ready = true; }
From source file:com.cws.esolutions.security.dao.reference.impl.SecurityReferenceDAOImpl.java
/** * @see com.cws.esolutions.security.dao.reference.interfaces.ISecurityReferenceDAO#obtainSecurityQuestionList() *//* w ww .jav a 2 s. c om*/ public synchronized List<String> obtainSecurityQuestionList() throws SQLException { final String methodName = ISecurityReferenceDAO.CNAME + "#obtainSecurityQuestionList() throws SQLException"; if (DEBUG) { DEBUGGER.debug(methodName); } Connection sqlConn = null; ResultSet resultSet = null; CallableStatement stmt = null; List<String> questionList = null; try { sqlConn = dataSource.getConnection(); if (sqlConn.isClosed()) { throw new SQLException("Unable to obtain application datasource connection"); } sqlConn.setAutoCommit(true); stmt = sqlConn.prepareCall("{CALL retrieve_user_questions()}"); if (DEBUG) { DEBUGGER.debug("CallableStatement: {}", stmt); } if (stmt.execute()) { resultSet = stmt.getResultSet(); resultSet.last(); int iRowCount = resultSet.getRow(); if (iRowCount == 0) { throw new SQLException("No security questions are currently configured."); } resultSet.first(); ResultSetMetaData resultData = resultSet.getMetaData(); int iColumns = resultData.getColumnCount(); questionList = new ArrayList<String>(); for (int x = 1; x < iColumns + 1; x++) { if (DEBUG) { DEBUGGER.debug("resultSet.getObject: {}", resultSet.getObject(resultData.getColumnName(x))); } // check if column is null resultSet.getObject(resultData.getColumnName(x)); // if the column was null, insert n/a, otherwise, insert the column's contents questionList.add((String) (resultSet.wasNull() ? "N/A" : resultSet.getObject(resultData.getColumnName(x)))); } } } catch (SQLException sqx) { throw new SQLException(sqx.getMessage(), sqx); } finally { if (resultSet != null) { resultSet.close(); } if (stmt != null) { stmt.close(); } if ((sqlConn != null) && (!(sqlConn.isClosed()))) { sqlConn.close(); } } return questionList; }
From source file:com.bitnine.tadpole.graph.core.editor.extension.browserHandler.CypherFunctionService.java
/** * Sigma.js ?? ? ./*from w w w .ja v a 2s . c o m*/ * * @param nodeJsonString * @return */ protected String loadDataWithSigma(final String nodeJsonString) { String result = ""; JsonObject jsonNode = JsonObject.readFrom(nodeJsonString); ResultSet rs = null; java.sql.Connection javaConn = null; Statement statement = null; try { SqlMapClient client = TadpoleSQLManager.getInstance(userDB); String reqQuery = "match (n)-[r]->(p) where id(n) = '" + jsonNode.get("id").asString() + "' return n, r, p UNION match (n)-[r]->(p) where id(p) = '" + jsonNode.get("id").asString() + "' return n, r, p"; if (logger.isDebugEnabled()) logger.debug("Execute Math CQL : " + reqQuery); javaConn = client.getDataSource().getConnection(); statement = javaConn.createStatement(); rs = statement.executeQuery(reqQuery); ResultSetMetaData rsmt = rs.getMetaData(); SigmaGraph graph = new SigmaGraph(); Node node = null; GEdge edge = null; long row = 0; double parentAngle = 0; double angleDeg = 0; double nx = 0; double ny = 0; while (rs.next()) { row++; for (int columnIndex = 1; columnIndex <= rsmt.getColumnCount(); columnIndex++) { Object obj = rs.getObject(columnIndex); if (obj instanceof Vertex) { node = new Node(); Vertex vertex = (Vertex) obj; node.setLabel(vertex.getLabel()); angleDeg = (((360 / (rsmt.getColumnCount() * 10)) * (columnIndex * row))); nx = jsonNode.get("x").asDouble() + (100 * Math.cos((angleDeg * (Math.PI / 180)) - parentAngle)); ny = jsonNode.get("y").asDouble() + (100 * Math.sin((angleDeg * (Math.PI / 180)) - parentAngle)); node.setX(nx); node.setY(ny); node.setColor(AgensUtils.getRandomRGB()); node.setSize(500); graph.addNode(node); } else if (obj instanceof Edge) { edge = new GEdge(); Edge relation = (Edge) obj; edge.setId(relation.getEdgeId().getOid() + "." + relation.getEdgeId().getId()); edge.setLabel(relation.getLabel()); edge.setSource( relation.getStartVertexId().getOid() + "." + relation.getStartVertexId().getId()); edge.setTarget( relation.getEndVertexid().getOid() + "." + relation.getEndVertexid().getId()); edge.setType("arrow");//'line', 'curve', 'arrow', 'curvedArrow' edge.setColor(AgensUtils.getRandomRGB("100")); edge.setSize(0.5); if (logger.isDebugEnabled()) logger.debug("Relation is " + relation.toString() + ", edge is " + edge.toString()); graph.addEdge(edge); } else { if (logger.isDebugEnabled()) logger.debug("Unknow Class " + obj.getClass().toString()); } } } Gson gson = new Gson(); result = gson.toJson(graph); if (logger.isDebugEnabled()) logger.debug("##### Graph ####====>" + result); } catch (Exception e) { logger.error(e); } finally { try { if (statement != null) statement.close(); } catch (Exception e) { } try { if (rs != null) rs.close(); } catch (Exception e) { } try { if (javaConn != null) javaConn.close(); } catch (Exception e) { } } return result; }
From source file:com.healthmarketscience.jackcess.ImportUtil.java
/** * Copy an existing JDBC ResultSet into a new (or optionally existing) table * in this database./* www . j ava 2 s . co m*/ * * @param name Name of the new table to create * @param source ResultSet to copy from * @param filter valid import filter * @param useExistingTable if {@code true} use current table if it already * exists, otherwise, create new table with unique * name * * @return the name of the imported table * * @see Builder */ public static String importResultSet(ResultSet source, Database db, String name, ImportFilter filter, boolean useExistingTable) throws SQLException, IOException { ResultSetMetaData md = source.getMetaData(); name = Database.escapeIdentifier(name); Table table = null; if (!useExistingTable || ((table = db.getTable(name)) == null)) { List<Column> columns = new LinkedList<Column>(); for (int i = 1; i <= md.getColumnCount(); i++) { Column column = new Column(); column.setName(Database.escapeIdentifier(md.getColumnName(i))); int lengthInUnits = md.getColumnDisplaySize(i); column.setSQLType(md.getColumnType(i), lengthInUnits); DataType type = column.getType(); // we check for isTrueVariableLength here to avoid setting the length // for a NUMERIC column, which pretends to be var-len, even though it // isn't if (type.isTrueVariableLength() && !type.isLongValue()) { column.setLengthInUnits((short) lengthInUnits); } if (type.getHasScalePrecision()) { int scale = md.getScale(i); int precision = md.getPrecision(i); if (type.isValidScale(scale)) { column.setScale((byte) scale); } if (type.isValidPrecision(precision)) { column.setPrecision((byte) precision); } } columns.add(column); } table = createUniqueTable(db, name, columns, md, filter); } List<Object[]> rows = new ArrayList<Object[]>(COPY_TABLE_BATCH_SIZE); int numColumns = md.getColumnCount(); while (source.next()) { Object[] row = new Object[numColumns]; for (int i = 0; i < row.length; i++) { row[i] = source.getObject(i + 1); } row = filter.filterRow(row); if (row == null) { continue; } rows.add(row); if (rows.size() == COPY_TABLE_BATCH_SIZE) { table.addRows(rows); rows.clear(); } } if (rows.size() > 0) { table.addRows(rows); } return table.getName(); }
From source file:com.glaf.core.jdbc.QueryHelper.java
@SuppressWarnings("unchecked") public int getTotal(Connection conn, SqlExecutor sqlExecutor) { int total = 0; PreparedStatement psmt = null; ResultSet rs = null; try {/* www .ja v a 2s . c o m*/ psmt = conn.prepareStatement(sqlExecutor.getSql()); if (sqlExecutor.getParameter() != null) { List<Object> values = (List<Object>) sqlExecutor.getParameter(); JdbcUtils.fillStatement(psmt, values); } rs = psmt.executeQuery(); if (rs.next()) { Object object = rs.getObject(1); if (object instanceof Integer) { Integer iCount = (Integer) object; total = iCount.intValue(); } else if (object instanceof Long) { Long iCount = (Long) object; total = iCount.intValue(); } else if (object instanceof BigDecimal) { BigDecimal bg = (BigDecimal) object; total = bg.intValue(); } else if (object instanceof BigInteger) { BigInteger bi = (BigInteger) object; total = bi.intValue(); } else { String x = object.toString(); if (StringUtils.isNotEmpty(x)) { total = Integer.parseInt(x); } } } } catch (Exception ex) { logger.error(ex); ex.printStackTrace(); throw new RuntimeException(ex); } finally { JdbcUtils.close(psmt); JdbcUtils.close(rs); } return total; }
From source file:mil.army.usace.data.dataquery.rdbms.RdbmsDataQuery.java
public <T> T getSingleSqlValue(String sql, Class returnType, Object[] params, boolean returnEmptyRecordsetAsNull) { PreparedStatement st = null;// w ww.java 2 s . c o m ResultSet rs = null; try { st = conn.prepareStatement(sql); setParams(st, params); rs = st.executeQuery(); Object val = null; //if there are no records, check returnEmptyRecordsetIsNull boolean //and allow error to be thrown if returnEmptyRecordsetIsNull==false //fyi...returnEmptyRecordsetIsNull==false is the default behavior if (rs.next() || !returnEmptyRecordsetAsNull) { val = rs.getObject(1); } if (val == null) { return null; } else { if (returnType != null) return (T) ConversionUtility.convertType(rs.getObject(1), returnType); else return (T) rs.getObject(1); } } catch (Exception ex) { throw new DataQueryException(sql, null, ex); } finally { closeOnFinally(rs, st); } }