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.useekm.indexing.postgis.IndexedStatement.java
private static IndexedStatement convert(ResultSet results) throws SQLException { IndexedStatement result = new IndexedStatement(); int idx = 1;//from www . j av a 2 s . co m result.objectDate = results.getDate(idx++); result.objectLanguage = results.getString(idx++); result.objectSpatial = (PGgeometry) results.getObject(idx++); result.objectString = results.getString(idx++); result.objectTsVectorConfig = results.getString(idx++); result.objectType = results.getString(idx++); result.objectUri = results.getBoolean(idx++); result.predicate = results.getString(idx++); result.subject = results.getString(idx++); return result; }
From source file:com.adaptris.core.util.JdbcUtil.java
public static Connection testConnection(Connection sqlConnection, String testStatement, boolean debugMode) throws SQLException { Statement stmt = sqlConnection.createStatement(); ResultSet rs = null; try {//from ww w.java 2 s .c o m if (isEmpty(testStatement)) { return sqlConnection; } if (debugMode) { rs = stmt.executeQuery(testStatement); if (rs.next()) { StringBuffer sb = new StringBuffer("TestStatement Results - "); ResultSetMetaData rsm = rs.getMetaData(); for (int i = 1; i <= rsm.getColumnCount(); i++) { sb.append("["); sb.append(rsm.getColumnName(i)); sb.append("="); sb.append(rs.getObject(i)); sb.append("] "); } log.trace(sb.toString()); } } else { stmt.execute(testStatement); } } finally { JdbcUtil.closeQuietly(rs); JdbcUtil.closeQuietly(stmt); } return sqlConnection; }
From source file:com.hangum.tadpole.engine.sql.util.QueryUtils.java
/** * query to xml// ww w. j a va 2 s . c o m * * @param userDB * @param strQuery * @param listParam */ @SuppressWarnings("deprecation") public static String selectToXML(final UserDBDAO userDB, final String strQuery, final List<Object> listParam) throws Exception { final StringWriter stWriter = new StringWriter(); DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance(); DocumentBuilder builder = factory.newDocumentBuilder(); final Document doc = builder.newDocument(); final Element results = doc.createElement("Results"); doc.appendChild(results); SqlMapClient client = TadpoleSQLManager.getInstance(userDB); QueryRunner qr = new QueryRunner(client.getDataSource()); qr.query(strQuery, listParam.toArray(), new ResultSetHandler<Object>() { @Override public Object handle(ResultSet rs) throws SQLException { ResultSetMetaData metaData = rs.getMetaData(); while (rs.next()) { Element row = doc.createElement("Row"); results.appendChild(row); for (int i = 1; i <= metaData.getColumnCount(); i++) { String columnName = metaData.getColumnName(i); Object value = rs.getObject(i) == null ? "" : rs.getObject(i); Element node = doc.createElement(columnName); node.appendChild(doc.createTextNode(value.toString())); row.appendChild(node); } } return stWriter.toString(); } }); DOMSource domSource = new DOMSource(doc); TransformerFactory tf = TransformerFactory.newInstance(); tf.setAttribute("indent-number", 4); Transformer transformer = tf.newTransformer(); transformer.setOutputProperty(OutputKeys.INDENT, "yes"); transformer.setOutputProperty(OutputKeys.OMIT_XML_DECLARATION, "yes"); transformer.setOutputProperty(OutputKeys.METHOD, "xml"); transformer.setOutputProperty(OutputKeys.ENCODING, "UTF-8");//"ISO-8859-1"); StreamResult sr = new StreamResult(stWriter); transformer.transform(domSource, sr); return stWriter.toString(); }
From source file:br.bookmark.db.util.ResultSetUtils.java
/** * Returns next record of result set as a Map. * The keys of the map are the column names, * as returned by the metadata./* www.j a v a 2 s.c o m*/ * The values are the columns as Objects. * * @param resultSet The ResultSet to process. * @exception SQLException if an error occurs. */ public static Map getMap(ResultSet resultSet) throws SQLException { // Acquire resultSet MetaData ResultSetMetaData metaData = resultSet.getMetaData(); int cols = metaData.getColumnCount(); // Create hashmap, sized to number of columns HashMap row = new HashMap(cols, 1); // Transfer record into hashmap if (resultSet.next()) { for (int i = 1; i <= cols; i++) { row.put(metaData.getColumnName(i), resultSet.getObject(i)); } } // end while return ((Map) row); }
From source file:io.cloudslang.content.database.services.SQLQueryLobService.java
public static boolean executeSqlQueryLob(SQLInputs sqlInputs) throws Exception { if (StringUtils.isEmpty(sqlInputs.getSqlCommand())) { throw new Exception("command input is empty."); }/*from www. ja v a 2 s .c o m*/ boolean isLOB = false; ConnectionService connectionService = new ConnectionService(); try (final Connection connection = connectionService.setUpConnection(sqlInputs)) { StringBuilder strColumns = new StringBuilder(sqlInputs.getStrColumns()); connection.setReadOnly(true); Statement statement = connection.createStatement(sqlInputs.getResultSetType(), sqlInputs.getResultSetConcurrency()); statement.setQueryTimeout(sqlInputs.getTimeout()); ResultSet results = statement.executeQuery(sqlInputs.getSqlCommand()); ResultSetMetaData mtd = results.getMetaData(); int iNumCols = mtd.getColumnCount(); for (int i = 1; i <= iNumCols; i++) { if (i > 1) strColumns.append(sqlInputs.getStrDelim()); strColumns.append(mtd.getColumnLabel(i)); } sqlInputs.setStrColumns(strColumns.toString()); int nr = -1; while (results.next()) { nr++; final StringBuilder strRowHolder = new StringBuilder(); for (int i = 1; i <= iNumCols; i++) { if (i > 1) strRowHolder.append(sqlInputs.getStrDelim()); Object columnObject = results.getObject(i); if (columnObject != null) { String value; if (columnObject instanceof java.sql.Clob) { isLOB = true; final File tmpFile = File.createTempFile("CLOB_" + mtd.getColumnLabel(i), ".txt"); copyInputStreamToFile( new ReaderInputStream(results.getCharacterStream(i), StandardCharsets.UTF_8), tmpFile); if (sqlInputs.getLRowsFiles().size() == nr) { sqlInputs.getLRowsFiles().add(nr, new ArrayList<String>()); sqlInputs.getLRowsNames().add(nr, new ArrayList<String>()); } sqlInputs.getLRowsFiles().get(nr).add(tmpFile.getAbsolutePath()); sqlInputs.getLRowsNames().get(nr).add(mtd.getColumnLabel(i)); value = "(CLOB)..."; } else { value = results.getString(i); if (sqlInputs.isNetcool()) value = SQLUtils.processNullTerminatedString(value); } strRowHolder.append(value); } else strRowHolder.append("null"); } sqlInputs.getLRows().add(strRowHolder.toString()); } } return isLOB; }
From source file:net.firejack.platform.core.utils.db.DBUtils.java
private static void insertDataToTargetTable(TablesMapping mapping, Connection sourceConnection, Connection targetConnection) throws SQLException { Map<Column, Column> columnMapping = mapping.getColumnMapping(); if (columnMapping.isEmpty()) { logger.warn("No columns are detected - no data to insert."); } else {/*from w ww . j a v a2s . c o m*/ ResultSet rs = selectDataFromSource(sourceConnection, mapping); String insertQuery = populateInsertQuery(mapping); PreparedStatement insertStatement = targetConnection.prepareStatement(insertQuery); targetConnection.setAutoCommit(false); try { int currentStep = 1; while (rs.next()) { for (int i = 1; i <= columnMapping.size(); i++) { insertStatement.setObject(i, rs.getObject(i)); } insertStatement.addBatch(); if (++currentStep > DEFAULT_BATCH_SIZE) { insertStatement.executeBatch(); targetConnection.commit(); currentStep = 1; } } if (currentStep != 1) { insertStatement.executeBatch(); targetConnection.commit(); } } catch (SQLException e) { logger.error(e.getMessage(), e); targetConnection.rollback(); } finally { insertStatement.close(); rs.close(); } } }
From source file:com.mirth.connect.server.controllers.tests.TestUtils.java
public static List<?> selectColumn(String query) { Connection connection = null; Statement statement = null;/*from ww w. j a va2 s .co m*/ ResultSet resultSet = null; try { connection = getConnection(); statement = connection.createStatement(); resultSet = statement.executeQuery(query); List<Object> col = new ArrayList<Object>(); while (resultSet.next()) { col.add(resultSet.getObject(1)); } return col; } catch (Exception e) { throw new RuntimeException(e); } finally { DbUtils.closeQuietly(resultSet); DbUtils.closeQuietly(statement); DbUtils.closeQuietly(connection); } }
From source file:mangotiger.sql.SQL.java
public static Map<String, List<Object>> map(final Connection connection, final String sql, final Object[] parameters) throws SQLException { ResultSet resultSet = null; PreparedStatement statement = null; try {/*w ww . j a va 2 s.c o m*/ final Map<String, List<Object>> map = new TreeMap<String, List<Object>>(); statement = newPreparedStatement(connection, sql, parameters); resultSet = statement.executeQuery(); final ResultSetMetaData metaData = resultSet.getMetaData(); final int columnCount = metaData.getColumnCount(); for (int i = 0; i < columnCount; ++i) { final String columnName = metaData.getColumnName(i); map.put(columnName, new ArrayList<Object>()); } while (resultSet.next()) { for (int i = 0; i < columnCount; ++i) { final String columnName = metaData.getColumnName(i); map.get(columnName).add(resultSet.getObject(1)); } } return map; } catch (SQLException e) { log().error("unable to execute: " + asString(sql, parameters), e); throw e; } finally { close(statement, resultSet); } }
From source file:com.thinkbiganalytics.util.JdbcCommon.java
/** * converts a JDBC result set to an Avro stream * * @param rs The result set of the JDBC query * @param outStream The output stream to for the Avro formatted records * @return the number of rows converted to Avro format * @throws SQLException if errors occur while reading data from the database * @throws IOException if unable to convert to Avro format *///from w w w .j av a 2 s.co m public static long convertToAvroStream(final ResultSet rs, final OutputStream outStream) throws SQLException, IOException { final Schema schema = createSchema(rs); final GenericRecord rec = new GenericData.Record(schema); final DatumWriter<GenericRecord> datumWriter = new GenericDatumWriter<>(schema); try (final DataFileWriter<GenericRecord> dataFileWriter = new DataFileWriter<>(datumWriter)) { dataFileWriter.create(schema, outStream); final ResultSetMetaData meta = rs.getMetaData(); final int nrOfColumns = meta.getColumnCount(); long nrOfRows = 0; while (rs.next()) { for (int i = 1; i <= nrOfColumns; i++) { final int javaSqlType = meta.getColumnType(i); final Object value = rs.getObject(i); if (value == null) { rec.put(i - 1, null); } else if (javaSqlType == BINARY || javaSqlType == VARBINARY || javaSqlType == LONGVARBINARY || javaSqlType == ARRAY || javaSqlType == BLOB || javaSqlType == CLOB) { // bytes requires little bit different handling byte[] bytes = rs.getBytes(i); ByteBuffer bb = ByteBuffer.wrap(bytes); rec.put(i - 1, bb); } else if (value instanceof Byte) { // tinyint(1) type is returned by JDBC driver as java.sql.Types.TINYINT // But value is returned by JDBC as java.lang.Byte // (at least H2 JDBC works this way) // direct put to avro record results: // org.apache.avro.AvroRuntimeException: Unknown datum type java.lang.Byte rec.put(i - 1, ((Byte) value).intValue()); } else if (value instanceof BigDecimal || value instanceof BigInteger) { // Avro can't handle BigDecimal and BigInteger as numbers - it will throw an AvroRuntimeException such as: "Unknown datum type: java.math.BigDecimal: 38" rec.put(i - 1, value.toString()); } else if (value instanceof Number || value instanceof Boolean) { rec.put(i - 1, value); } else { // The different types that we support are numbers (int, long, double, float), // as well as boolean values and Strings. Since Avro doesn't provide // timestamp types, we want to convert those to Strings. So we will cast anything other // than numbers or booleans to strings by using the toString() method. rec.put(i - 1, value.toString()); } } dataFileWriter.append(rec); nrOfRows += 1; } return nrOfRows; } }
From source file:edu.ucsb.nceas.MCTestCase.java
protected static Vector<Hashtable<String, Object>> dbSelect(String sqlStatement, String methodName) throws SQLException { Vector<Hashtable<String, Object>> resultVector = new Vector<Hashtable<String, Object>>(); DBConnectionPool connPool = DBConnectionPool.getInstance(); DBConnection dbconn = DBConnectionPool.getDBConnection(methodName); int serialNumber = dbconn.getCheckOutSerialNumber(); PreparedStatement pstmt = null; debug("Selecting from db: " + sqlStatement); pstmt = dbconn.prepareStatement(sqlStatement); pstmt.execute();//from w ww . j a v a2s.com ResultSet resultSet = pstmt.getResultSet(); ResultSetMetaData rsMetaData = resultSet.getMetaData(); int numColumns = rsMetaData.getColumnCount(); debug("Number of data columns: " + numColumns); while (resultSet.next()) { Hashtable<String, Object> hashTable = new Hashtable<String, Object>(); for (int i = 1; i <= numColumns; i++) { if (resultSet.getObject(i) != null) { hashTable.put(rsMetaData.getColumnName(i), resultSet.getObject(i)); } } debug("adding data row to result vector"); resultVector.add(hashTable); } resultSet.close(); pstmt.close(); DBConnectionPool.returnDBConnection(dbconn, serialNumber); return resultVector; }