Example usage for java.sql ResultSet getObject

List of usage examples for java.sql ResultSet getObject

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. ResultSet
  6. getObject

Introduction

In this page you can find the example usage for java.sql ResultSet getObject.

Prototype

Object getObject(String columnLabel) throws SQLException;

Source Link

Document

Gets the value of the designated column in the current row of this ResultSet object as an Object in the Java programming language.

Usage

From source file:com.alfaariss.oa.engine.user.provisioning.storage.external.jdbc.JDBCExternalStorage.java

/**
 * Returns the values of the supplied fields for the supplied id.
 * @see IExternalStorage#getFields(java.lang.String, java.util.List)
 *//*from ww  w .  j av  a  2 s .  c  o  m*/
public Hashtable<String, Object> getFields(String id, List<String> fields) throws UserException {
    Hashtable<String, Object> htReturn = new Hashtable<String, Object>();

    Connection oConnection = null;
    PreparedStatement oPreparedStatement = null;
    ResultSet oResultSet = null;

    try {
        if (fields.size() == 0) {
            _logger.debug("No fields requested for id: " + id);
            return htReturn;
        }

        StringBuffer sbFields = new StringBuffer();

        for (String sField : fields) {
            if (sbFields.length() > 0)
                sbFields.append(",");
            sbFields.append(sField);
        }

        StringBuffer sbQuery = new StringBuffer("SELECT ");
        sbQuery.append(sbFields);
        sbQuery.append(" FROM ");
        sbQuery.append(_sTableName);
        sbQuery.append(" WHERE ");
        sbQuery.append(_sColumnUserId);
        sbQuery.append("=?");

        oConnection = _oDataSource.getConnection();
        oPreparedStatement = oConnection.prepareStatement(sbQuery.toString());
        oPreparedStatement.setString(1, id);
        oResultSet = oPreparedStatement.executeQuery();
        if (!oResultSet.next()) {
            _logger.error("No result with query: " + sbQuery.toString());
            throw new UserException(SystemErrors.ERROR_RESOURCE_RETRIEVE);
        }

        for (String sField : fields) {
            Object oValue = oResultSet.getObject(sField);
            if (oValue != null)
                htReturn.put(sField, oValue);
        }
    } catch (UserException e) {
        throw e;
    } catch (SQLException e) {
        StringBuffer sbError = new StringBuffer("Could not retrieve fields with names '");
        sbError.append(fields.toString());
        sbError.append("' for id: ");
        sbError.append(id);
        _logger.error(sbError.toString(), e);
        throw new UserException(SystemErrors.ERROR_RESOURCE_RETRIEVE, e);
    } catch (Exception e) {
        StringBuffer sbError = new StringBuffer("Could not retrieve fields with names '");
        sbError.append(fields.toString());
        sbError.append("' for id: ");
        sbError.append(id);

        _logger.fatal(sbError.toString(), e);
        throw new UserException(SystemErrors.ERROR_INTERNAL, e);
    } finally {
        try {
            if (oResultSet != null)
                oResultSet.close();
        } catch (Exception e) {
            _logger.error("Could not close resultset", e);
        }

        try {
            if (oPreparedStatement != null)
                oPreparedStatement.close();
        } catch (Exception e) {
            _logger.error("Could not close statement", e);
        }

        try {
            if (oConnection != null)
                oConnection.close();
        } catch (Exception e) {
            _logger.error("Could not close connection", e);
        }
    }

    return htReturn;
}

From source file:com.glaf.core.jdbc.QueryHelper.java

public Map<String, Object> toMap(ResultSet rs) throws SQLException {
    Map<String, Object> result = new CaseInsensitiveHashMap();
    ResultSetMetaData rsmd = rs.getMetaData();
    int count = rsmd.getColumnCount();
    for (int i = 1; i <= count; i++) {
        String columnName = rsmd.getColumnLabel(i);
        if (StringUtils.isEmpty(columnName)) {
            columnName = rsmd.getColumnName(i);
        }/*from www .ja v a2s .  c  om*/
        Object object = rs.getObject(i);
        columnName = columnName.toLowerCase();
        String name = StringTools.camelStyle(columnName);
        result.put(name, object);
        result.put(columnName, object);
    }
    return result;
}

From source file:mil.army.usace.data.nativequery.rdbms.NativeRdbmsQuery.java

public <T> List<T> getRecordsAsSimpleCollection(ReturnType returnType, String sql, Class[] returnTypes,
        Object[] params) {/*from  w  w w .  j av a 2 s .  co  m*/
    if (returnTypes.length == 0)
        returnTypes = null;
    PreparedStatement st = null;
    ResultSet rs = null;
    ResultSetMetaData rsMetaData = null;
    try {
        List<T> records = new ArrayList<>();
        st = conn.prepareStatement(sql);
        setParams(st, params);
        rs = st.executeQuery();
        if (returnType == ReturnType.RECORDSET || returnType == ReturnType.RECORDSETMAP)
            rsMetaData = rs.getMetaData();
        while (rs.next()) {
            switch (returnType) {
            case ARRAYLIST:
                if (returnTypes != null)
                    records.add((T) convertType(rs.getObject(1), returnTypes[0]));
                else
                    records.add((T) rs.getObject(1));
                break;
            case ARRAYLIST_TUPLE:
                if (returnTypes != null)
                    records.add((T) new Tuple(convertType(rs.getObject(1), returnTypes[0]),
                            convertType(rs.getObject(2), returnTypes[1])));
                else
                    records.add((T) new Tuple(rs.getObject(1), rs.getObject(2)));
                break;
            case ARRAYLIST_TUPLE3:
                if (returnTypes != null)
                    records.add((T) new Tuple3(convertType(rs.getObject(1), returnTypes[0]),
                            convertType(rs.getObject(2), returnTypes[1]),
                            convertType(rs.getObject(3), returnTypes[2])));
                else
                    records.add((T) new Tuple3(rs.getObject(1), rs.getObject(2), rs.getObject(3)));
                break;
            case RECORDSET:
                ArrayList record = new ArrayList();
                for (int i = 0; i < rsMetaData.getColumnCount(); i++) {
                    record.add((returnTypes == null) ? rs.getObject(i + 1)
                            : convertType(rs.getObject(i + 1), returnTypes[i]));
                }
                ((ArrayList) records).add(record);
                break;
            case RECORDSETMAP:
                HashMap<String, Object> recordmap = new HashMap<>();
                for (int i = 0; i < rsMetaData.getColumnCount(); i++) {
                    recordmap.put(rsMetaData.getColumnName(i + 1), (returnTypes == null) ? rs.getObject(i + 1)
                            : convertType(rs.getObject(i + 1), returnTypes[i]));
                }
                ((ArrayList) records).add(recordmap);
                break;
            }
        }
        return records;
    } catch (Exception ex) {
        throw new NativeQueryException(sql, null, ex);
    } finally {
        closeOnFinally(rs, st);
    }
}

From source file:edu.ku.brc.specify.ui.containers.ContainerTreePanel.java

/**
 * @param containerId/*from   ww w .  j a v  a2s. c  o  m*/
 * @return
 */
private Integer getTopMostParentId(final Integer containerId) {
    if (containerId != null) {
        PreparedStatement pStmt = null;
        try {
            pStmt = DBConnection.getInstance().getConnection().prepareStatement(GETSQL);

            Integer pContainerId = containerId;
            do {
                pStmt.setInt(1, pContainerId);
                pContainerId = null;

                ResultSet rs = pStmt.executeQuery();
                if (rs.next()) {
                    pContainerId = (Integer) rs.getObject(1);
                }
                rs.close();

            } while (pContainerId != null);

            return pContainerId;

        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                if (pStmt != null)
                    pStmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    return null;
}

From source file:com.gs.obevo.db.apps.reveng.CsvStaticDataWriter.java

private void writeTable(DbPlatform dbtype, PhysicalSchema schema, String tableName, File directory,
        MutableSet<String> updateTimeColumns, final CSVFormat csvFormat) {
    directory.mkdirs();/*from w  ww . jav  a  2  s  . c  om*/
    DaTable table = this.metadataManager.getTableInfo(schema.getPhysicalName(), tableName,
            new DaSchemaInfoLevel().setRetrieveTableColumns(true));
    if (table == null) {
        System.out.println("No data found for table " + tableName);
        return;
    }
    MutableList<String> columnNames = table.getColumns().collect(DaNamedObject.TO_NAME).toList();
    final String updateTimeColumnForTable = updateTimeColumns == null ? null
            : updateTimeColumns.detect(Predicates.in(columnNames));
    if (updateTimeColumnForTable != null) {
        columnNames.remove(updateTimeColumnForTable);
        System.out.println("Will mark " + updateTimeColumnForTable + " as an updateTimeColumn on this table");
    }

    final File tableFile = new File(directory, tableName + ".csv");
    final String selectSql = String.format("SELECT %s FROM %s%s", columnNames.makeString(", "),
            dbtype.getSchemaPrefix(schema), tableName);

    // using the jdbcTempate and ResultSetHandler to avoid sql-injection warnings in findbugs
    sqlExecutor.executeWithinContext(schema, new Procedure<Connection>() {
        @Override
        public void value(Connection conn) {
            sqlExecutor.getJdbcTemplate().query(conn, selectSql, new ResultSetHandler<Void>() {
                @Override
                public Void handle(ResultSet rs) throws SQLException {
                    CSVPrinter writer = null;
                    try {
                        FileWriter fw = new FileWriter(tableFile);
                        writer = new CSVPrinter(fw, csvFormat);

                        if (updateTimeColumnForTable != null) {
                            String metadataLine = String.format("//// METADATA %s=\"%s\"",
                                    TextMarkupDocumentReader.ATTR_UPDATE_TIME_COLUMN, updateTimeColumnForTable);
                            fw.write(metadataLine + "\n"); // writing using the FileWriter directly to avoid having the quotes
                            // delimited
                        }

                        DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
                        DateFormat dateTimeFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");

                        int columnCount = rs.getMetaData().getColumnCount();

                        // print headers
                        for (int i = 1; i <= columnCount; ++i) {
                            writer.print(rs.getMetaData().getColumnName(i));
                        }
                        writer.println();

                        while (rs.next()) {
                            for (int i = 1; i <= columnCount; ++i) {
                                Object object = rs.getObject(i);
                                if (object != null) {
                                    switch (rs.getMetaData().getColumnType(i)) {
                                    case Types.DATE:
                                        object = dateFormat.format(object);
                                        break;
                                    case Types.TIMESTAMP:
                                        object = dateTimeFormat.format(object);
                                        break;
                                    case Types.LONGVARCHAR:
                                    case Types.VARCHAR:
                                    case Types.CHAR:
                                        // escape the string text if declared so that the input CSV can also handle the escapes
                                        if (csvFormat.getEscapeCharacter() != null
                                                && object instanceof String) {
                                            object = ((String) object).replace(
                                                    "" + csvFormat.getEscapeCharacter(),
                                                    "" + csvFormat.getEscapeCharacter()
                                                            + csvFormat.getEscapeCharacter());
                                        }
                                        break;
                                    }
                                }
                                writer.print(object);
                            }

                            writer.println();
                        }

                        writer.flush();
                    } catch (IOException e) {
                        throw new RuntimeException(e);
                    } finally {
                        IOUtils.closeQuietly(writer);
                    }

                    return null;
                }
            });
        }
    });

    int blankFileSize = updateTimeColumnForTable == null ? 1 : 2;

    if (!tableFile.canRead() || FileUtilsCobra.readLines(tableFile).size() <= blankFileSize) {
        System.out.println("No data found for table " + tableName + "; will clean up file");
        FileUtils.deleteQuietly(tableFile);
    }
}

From source file:edu.ku.brc.specify.tools.webportal.BuildSearchIndex2.java

License:asdf

private String getAttachments(Connection conn, String baseTblName, Integer baseKey) throws SQLException {
    String attacherTbl = baseTblName + "attachment";
    String baseTblID = baseTblName + "ID"; //XXX dude... use DBTableMgr stuff...
    String sql = "select att.AttachmentID, att.AttachmentLocation, att.Title, aia.Height, aia.Width from attachment att "
            + "left join attachmentimageattribute aia on aia.AttachmentImageAttributeID "
            + "= att.AttachmentImageAttributeID inner join " + attacherTbl + " oatt on oatt.AttachmentID "
            + "= att.AttachmentID where att.IsPublic and att.MimeType like 'image/%' and oatt." + baseTblID
            + " = " + baseKey;
    Statement stmt = null;//from w  w w.j  a  v a  2  s. co m
    ResultSet rs = null;
    String result = null;
    try {
        stmt = conn.createStatement();
        rs = stmt.executeQuery(sql);
        while (rs.next()) {
            if (result == null) {
                result = "[";
            } else {
                result += ", ";
            }
            String[] flds = { "AttachmentID,no", "AttachmentLocation,yes", "Title,yes", "Height,no",
                    "Width,no" };
            String rec = "";
            for (String fld : flds) {
                String[] def = fld.split(",");
                boolean quote = "yes".equals(def[1]);
                String json = jsonFldVal(rs.getObject(def[0]), quote, def[0]);
                if (json.length() > 0) {
                    if (rec.length() > 0) {
                        rec += ",";
                    }
                    rec += json;
                }
            }
            if (rec.length() > 0) {
                result += "{" + rec + "}";
            }
        }
        if (result != null && result.length() > 0) {
            result += "]";
        }
    } finally {
        if (stmt != null) {
            stmt.close();
        }
        if (rs != null) {
            rs.close();
        }
    }
    return result;
}

From source file:com.alibaba.wasp.jdbc.TestJdbcResultSet.java

public void testColumnLabelColumnName() throws SQLException {
    stat = conn.createStatement();/*  ww  w.ja  v  a2  s .  co  m*/
    stat.executeUpdate("Insert into " + TABLE_NAME + "(column1,column2,column3) values (2,1,'binlijin');");
    ResultSet rs = stat.executeQuery("select column3 as y from test where column1=2 and column3='binlijin' ");
    rs.next();
    rs.getString("column3");
    rs.getString("y");
    rs.close();
    rs = conn.getMetaData().getColumns(null, null, null, null);
    ResultSetMetaData meta = rs.getMetaData();
    int columnCount = meta.getColumnCount();
    String[] columnName = new String[columnCount];
    for (int i = 1; i <= columnCount; i++) {
        columnName[i - 1] = meta.getColumnName(i);
    }
    while (rs.next()) {
        for (int i = 0; i < columnCount; i++) {
            rs.getObject(columnName[i]);
        }
    }
}

From source file:mil.army.usace.data.dataquery.rdbms.RdbmsDataQuery.java

public <T> List<T> getRecordsAsSimpleCollection(ReturnType returnType, String sql, Class[] returnTypes,
        Object[] params) {/*from   ww w. j  a  v  a 2 s  . c  o m*/
    if (returnTypes.length == 0)
        returnTypes = null;
    PreparedStatement st = null;
    ResultSet rs = null;
    ResultSetMetaData rsMetaData = null;
    try {
        List<T> records = new ArrayList<>();
        st = conn.prepareStatement(sql);
        setParams(st, params);
        rs = st.executeQuery();
        if (returnType == ReturnType.RECORDSET || returnType == ReturnType.RECORDSETMAP)
            rsMetaData = rs.getMetaData();
        while (rs.next()) {
            switch (returnType) {
            case ARRAYLIST:
                if (returnTypes != null)
                    records.add((T) ConversionUtility.convertType(rs.getObject(1), returnTypes[0]));
                else
                    records.add((T) rs.getObject(1));
                break;
            case ARRAYLIST_TUPLE:
                if (returnTypes != null)
                    records.add((T) new Tuple(ConversionUtility.convertType(rs.getObject(1), returnTypes[0]),
                            ConversionUtility.convertType(rs.getObject(2), returnTypes[1])));
                else
                    records.add((T) new Tuple(rs.getObject(1), rs.getObject(2)));
                break;
            case ARRAYLIST_TUPLE3:
                if (returnTypes != null)
                    records.add((T) new Tuple3(ConversionUtility.convertType(rs.getObject(1), returnTypes[0]),
                            ConversionUtility.convertType(rs.getObject(2), returnTypes[1]),
                            ConversionUtility.convertType(rs.getObject(3), returnTypes[2])));
                else
                    records.add((T) new Tuple3(rs.getObject(1), rs.getObject(2), rs.getObject(3)));
                break;
            case RECORDSET:
                ArrayList record = new ArrayList();
                for (int i = 0; i < rsMetaData.getColumnCount(); i++) {
                    record.add((returnTypes == null) ? rs.getObject(i + 1)
                            : ConversionUtility.convertType(rs.getObject(i + 1), returnTypes[i]));
                }
                ((ArrayList) records).add(record);
                break;
            case RECORDSETMAP:
                HashMap<String, Object> recordmap = new HashMap<>();
                for (int i = 0; i < rsMetaData.getColumnCount(); i++) {
                    recordmap.put(rsMetaData.getColumnName(i + 1), (returnTypes == null) ? rs.getObject(i + 1)
                            : ConversionUtility.convertType(rs.getObject(i + 1), returnTypes[i]));
                }
                ((ArrayList) records).add(recordmap);
                break;
            }
        }
        return records;
    } catch (Exception ex) {
        throw new DataQueryException(sql, null, ex);
    } finally {
        closeOnFinally(rs, st);
    }
}

From source file:org.jfree.data.jdbc.JDBCCategoryDataset.java

/**
 * Populates the dataset by executing the supplied query against the
 * existing database connection.  If no connection exists then no action
 * is taken.//from  ww w .ja  v  a 2s.  c  o  m
 * <p>
 * The results from the query are extracted and cached locally, thus
 * applying an upper limit on how many rows can be retrieved successfully.
 *
 * @param con  the connection.
 * @param query  the query.
 *
 * @throws SQLException if there is a problem executing the query.
 */
public void executeQuery(Connection con, String query) throws SQLException {

    Statement statement = null;
    ResultSet resultSet = null;
    try {
        statement = con.createStatement();
        resultSet = statement.executeQuery(query);
        ResultSetMetaData metaData = resultSet.getMetaData();

        int columnCount = metaData.getColumnCount();

        if (columnCount < 2) {
            throw new SQLException("JDBCCategoryDataset.executeQuery() : insufficient columns "
                    + "returned from the database.");
        }

        // Remove any previous old data
        int i = getRowCount();
        while (--i >= 0) {
            removeRow(i);
        }

        while (resultSet.next()) {
            // first column contains the row key...
            Comparable rowKey = resultSet.getString(1);
            for (int column = 2; column <= columnCount; column++) {

                Comparable columnKey = metaData.getColumnName(column);
                int columnType = metaData.getColumnType(column);

                switch (columnType) {
                case Types.TINYINT:
                case Types.SMALLINT:
                case Types.INTEGER:
                case Types.BIGINT:
                case Types.FLOAT:
                case Types.DOUBLE:
                case Types.DECIMAL:
                case Types.NUMERIC:
                case Types.REAL: {
                    Number value = (Number) resultSet.getObject(column);
                    if (this.transpose) {
                        setValue(value, columnKey, rowKey);
                    } else {
                        setValue(value, rowKey, columnKey);
                    }
                    break;
                }
                case Types.DATE:
                case Types.TIME:
                case Types.TIMESTAMP: {
                    Date date = (Date) resultSet.getObject(column);
                    Number value = new Long(date.getTime());
                    if (this.transpose) {
                        setValue(value, columnKey, rowKey);
                    } else {
                        setValue(value, rowKey, columnKey);
                    }
                    break;
                }
                case Types.CHAR:
                case Types.VARCHAR:
                case Types.LONGVARCHAR: {
                    String string = (String) resultSet.getObject(column);
                    try {
                        Number value = Double.valueOf(string);
                        if (this.transpose) {
                            setValue(value, columnKey, rowKey);
                        } else {
                            setValue(value, rowKey, columnKey);
                        }
                    } catch (NumberFormatException e) {
                        // suppress (value defaults to null)
                    }
                    break;
                }
                default:
                    // not a value, can't use it (defaults to null)
                    break;
                }
            }
        }

        fireDatasetChanged(new DatasetChangeInfo());
        //TODO: fill in real change info
    } finally {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (Exception e) {
                // report this?
            }
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (Exception e) {
                // report this?
            }
        }
    }
}

From source file:com.itemanalysis.psychometrics.rasch.JMLE.java

/**
 * Summarizes data into a TestFreqeuncyTable and stores scored responses in two way byte array
 * in a two way byte array.//from  w ww .j  a v a  2s  .  c  o  m
 *
 * @throws SQLException
 */
public void summarizeData(ResultSet rs) throws SQLException {
    Object response = null;
    byte responseScore = 0;
    try {
        int r = 0;
        int c = 0;
        while (rs.next()) {
            c = 0;
            for (VariableAttributes v : variables) {//columns in data will be in same order as variables
                response = rs.getObject(v.getName().nameForDatabase());
                if ((response == null || response.equals("") || response.equals("NA")) && ignoreMissing) {
                    data[r][c] = -1;//code for omitted responses
                } else {
                    responseScore = (byte) v.getItemScoring().computeItemScore(response);
                    table.increment(v.getName(), v.getSubscale(true), responseScore);
                    data[r][c] = responseScore;
                }
                c++;
            }
            r++;
        }
    } catch (SQLException ex) {
        throw new SQLException(ex);
    }
}