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.slemarchand.sqlqueryscripting.scripting.sqlquery.SQLQueryExecutor.java

private List<List<Object>> _execQuery(String sqlQuery, int maxRows, List<String> columnLabels)
        throws SQLException {

    List<List<Object>> rows = null;

    Connection con = null;//from ww  w.  ja v a  2  s . c  o  m
    Statement stmt = null;
    ResultSet rs = null;

    try {
        con = DataAccess.getConnection();

        con.setAutoCommit(false); // Prevent data updates

        stmt = con.createStatement();
        stmt.setMaxRows(maxRows);
        rs = stmt.executeQuery(sqlQuery);

        ResultSetMetaData md = rs.getMetaData();
        int cc = md.getColumnCount();

        rows = new ArrayList<List<Object>>(cc);

        columnLabels.clear();

        for (int c = 1; c <= cc; c++) {
            String cl = md.getColumnLabel(c);
            columnLabels.add(cl);
        }

        while (rs.next()) {
            List<Object> row = new ArrayList<Object>(cc);
            for (int c = 1; c <= cc; c++) {
                Object value = rs.getObject(c);
                row.add(value);
            }
            rows.add(row);
        }

    } finally {
        DataAccess.cleanUp(con, stmt, rs);
    }

    return rows;
}

From source file:edu.ku.brc.specify.toycode.mexconabio.CopyPlantsFromGBIF.java

/**
 * /*from w w  w .  j a v  a2  s  .co m*/
 */
public void processNullKingdom() {
    PrintWriter pw = null;
    try {
        pw = new PrintWriter("gbif_plants_from_null.log");

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    }

    System.out.println("----------------------- Searching NULL ----------------------- ");

    String gbifWhereStr = "FROM raw WHERE kingdom IS NULL";

    long startTime = System.currentTimeMillis();

    String cntGBIFSQL = "SELECT COUNT(*) " + gbifWhereStr;// + " LIMIT 0,1000";
    String gbifSQL = gbifSQLBase + gbifWhereStr;

    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 {
        pw = new PrintWriter("gbif_plants_from_null.log");

        pStmt = dstConn.prepareStatement(pSQL);

        System.out.println("Total Records: " + totalRecs);
        pw.println("Total Records: " + totalRecs);

        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:com.dsf.dbxtract.cdc.journal.JournalExecutor.java

private void copyResultsetToMap(ResultSet rs, List<Map<String, Object>> result) throws SQLException {

    if (rs == null)
        throw new SQLException("result is null");
    if (result == null)
        throw new NullPointerException("result map is null");

    while (rs.next()) {
        if (journalColumns == null) {
            journalColumns = new ArrayList<>();
            for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) {
                journalColumns.add(rs.getMetaData().getColumnLabel(i + 1).toLowerCase());
            }//from  w w w. j a v  a 2 s.  c  o  m
        }
        Map<String, Object> map = new HashMap<>();
        for (String col : journalColumns) {
            map.put(col, rs.getObject(col));
        }
        result.add(map);
    }
}

From source file:edu.ku.brc.specify.tasks.subpane.images.CollectionDataFetcher.java

/**
 * @param rs/*w  w w . ja v a 2  s. c o m*/
 * @param tableId
 * @return
 * @throws SQLException 
 */
private List<Triple<String, String, Object>> readDataIntoMap(final ResultSet rs, final int tableId)
        throws SQLException {
    List<BubbleDisplayInfo> displayInfos = bciHash.get(tableId);
    List<Triple<String, String, Object>> dataList = new ArrayList<Triple<String, String, Object>>();
    if (rs != null) {
        if (rs.next()) {
            ResultSetMetaData rsmd = rs.getMetaData();

            for (int i = 1; i < rsmd.getColumnCount(); i++) {
                BubbleDisplayInfo bdi = displayInfos.get(i - 1);
                Object val = rs.getObject(i);

                if (bdi.getColTblId() == Attachment.getClassTableId()
                        && bdi.getColumnName().equals("OrigFilename")) {
                    val = FilenameUtils.getName(val.toString());
                }

                if (bdi.getFormatter() != null) {
                    val = bdi.getFormatter().formatToUI(val);

                } else if (val instanceof Calendar) {
                    val = scrDateFormat.format((Calendar) val);

                } else if (val instanceof Date) {
                    val = scrDateFormat.format((Date) val);

                } else if (val instanceof BigDecimal) {
                    val = StringUtils.stripEnd(val.toString(), "0");

                } else if (bdi.getFieldInfo().getPickListName() != null) {
                    PickListIFace pl = PickListDBAdapterFactory.getInstance()
                            .getPickList(bdi.getFieldInfo().getPickListName());
                    if (pl != null) {
                        for (PickListItemIFace pli : pl.getItems()) {
                            if (pli.getValue() != null && pli.getValue().equals(val)) {
                                val = pli.getTitle();
                                break;
                            }
                        }
                    }
                }
                String title = getColumnTitle(bdi, tableId) + ": ";
                dataList.add(new Triple<String, String, Object>(bdi.getFieldInfo().getColumn(), title, val));
            }
            //System.out.println(rs.getObject(rsmd.getColumnCount()));
            dataList.add(new Triple<String, String, Object>("Id", "Id", rs.getObject(rsmd.getColumnCount())));
        }
        rs.close();
    }
    return dataList;
}

From source file:com.nextep.designer.dbgm.services.impl.DataService.java

/**
 * Fills the specified result collection from the starting result set column to the ending
 * column. A boolean is returned to indicate whether all column values where null.
 * /*from w w w.  ja v a2 s  .co  m*/
 * @param results
 * @param rset
 * @param startCol
 * @param endCol
 * @return
 * @throws SQLException
 */
private boolean fillResults(Collection<Object> results, ResultSet rset, int startCol, int endCol)
        throws SQLException {
    boolean allNull = true;
    for (int i = startCol; i <= endCol; i++) {
        final Object o = rset.getObject(i);
        if (allNull) {
            // Avoiding startcol here as it will always be our rowid
            allNull = (o == null || i == startCol);
        }
        results.add(o);
    }
    return !allNull;
}

From source file:edu.ku.brc.specify.dbsupport.BuildFromGeonames.java

/**
 * @param rs//from  w  ww  . j  ava 2  s .c  om
 * @param rankId
 * @param earthId
 * @return
 * @throws SQLException
 */
private boolean buildInsert(final ResultSet rs, final int rankId, final int earthId) throws SQLException {
    rowData.clear();
    for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) {
        rowData.add(rs.getObject(i + 1));
    }
    return buildInsert(rowData, rankId, earthId);
}

From source file:gr.seab.r2rml.beans.Generator.java

private String getStringValue(String field, ResultSet rs) {
    String result = null;/*www  . ja  v  a  2s. com*/
    try {
        if (rs.getObject(field) == null)
            return null;

        BaseDatatype fieldDataType = findFieldDataType(field, rs);

        if (fieldDataType != null && fieldDataType.getURI().equals(XSDDatatype.XSDdate.getURI())) {
            result = xsdDateFormat.format(rs.getDate(field));
        } else {
            result = rs.getString(field);
        }
    } catch (Exception e) {
        log.error("Failed to get value as string for column " + field, e);
    }
    return result;
}

From source file:com.netspective.axiom.policy.AnsiDatabasePolicy.java

public void reverseEngineer(Writer writer, Connection conn, String catalog, String schemaPattern)
        throws IOException, SQLException {
    Map dataTypesMap = prepareJdbcTypeInfoMap();
    DatabaseMetaData dbmd = conn.getMetaData();
    TextUtils textUtils = TextUtils.getInstance();

    writer.write("<?xml version=\"1.0\"?>\n\n");
    writer.write("<!-- Reverse engineered by Axiom\n");
    writer.write("     driver: " + dbmd.getDriverName() + "\n");
    writer.write("     driver-version: " + dbmd.getDriverVersion() + "\n");
    writer.write("     product: " + dbmd.getDatabaseProductName() + "\n");
    writer.write("     product-version: " + dbmd.getDatabaseProductVersion() + "\n");

    writer.write("     available catalogs:");
    ResultSet rs = null;
    try {//w  w w .j a va 2 s  . co  m
        rs = dbmd.getCatalogs();
        while (rs.next()) {
            writer.write(" " + rs.getObject(1).toString());
        }
    } finally {
        if (rs != null)
            rs.close();
    }

    writer.write("\n");

    writer.write("     available schemas:");
    try {
        rs = dbmd.getSchemas();
        while (rs.next()) {
            writer.write(" " + rs.getObject(1).toString());
        }
    } finally {
        if (rs != null)
            rs.close();
    }
    writer.write("\n");
    writer.write("-->\n\n");

    writer.write("<component xmlns:xdm=\"http://www.netspective.org/Framework/Commons/XMLDataModel\">\n");
    writer.write("    <xdm:include resource=\"com/netspective/axiom/conf/axiom.xml\"/>\n");
    writer.write("    <schema name=\"" + catalog + "." + schemaPattern + "\">\n");

    Map dbmdTypeInfoByName = new HashMap();
    Map dbmdTypeInfoByJdbcType = new HashMap();
    ResultSet typesRS = null;
    try {
        typesRS = dbmd.getTypeInfo();
        while (typesRS.next()) {
            int colCount = typesRS.getMetaData().getColumnCount();
            Object[] typeInfo = new Object[colCount];
            for (int i = 1; i <= colCount; i++)
                typeInfo[i - 1] = typesRS.getObject(i);
            dbmdTypeInfoByName.put(typesRS.getString(1), typeInfo);
            dbmdTypeInfoByJdbcType.put(new Integer(typesRS.getInt(2)), typeInfo);
        }
    } finally {
        if (typesRS != null)
            typesRS.close();
    }

    ResultSet tables = null;
    try {
        tables = dbmd.getTables(catalog, schemaPattern, null, new String[] { "TABLE" });
        while (tables.next()) {
            String tableNameOrig = tables.getString(3);
            String tableName = textUtils.fixupTableNameCase(tableNameOrig);

            writer.write("        <table name=\"" + tableName + "\">\n");

            Map primaryKeys = new HashMap();
            ResultSet pkRS = null;
            try {
                pkRS = dbmd.getPrimaryKeys(null, null, tableNameOrig);
                while (pkRS.next()) {
                    primaryKeys.put(pkRS.getString(4), pkRS.getString(5));
                }

            } catch (Exception e) {
                // driver may not support this function
            } finally {
                if (pkRS != null)
                    pkRS.close();
            }

            Map fKeys = new HashMap();
            ResultSet fkRS = null;
            try {
                fkRS = dbmd.getImportedKeys(null, null, tableNameOrig);
                while (fkRS.next()) {
                    fKeys.put(fkRS.getString(8), textUtils.fixupTableNameCase(fkRS.getString(3)) + "."
                            + fkRS.getString(4).toLowerCase());
                }
            } catch (Exception e) {
                // driver may not support this function
            } finally {
                if (fkRS != null)
                    fkRS.close();
            }

            // we keep track of processed columns so we don't duplicate them in the XML
            Set processedColsMap = new HashSet();
            ResultSet columns = null;
            try {
                columns = dbmd.getColumns(null, null, tableNameOrig, null);
                while (columns.next()) {
                    String columnNameOrig = columns.getString(4);
                    if (processedColsMap.contains(columnNameOrig))
                        continue;
                    processedColsMap.add(columnNameOrig);

                    String columnName = columnNameOrig.toLowerCase();

                    writer.write("            <column name=\"" + columnName + "\"");
                    try {
                        if (fKeys.containsKey(columnNameOrig))
                            writer.write(" lookup-ref=\"" + fKeys.get(columnNameOrig) + "\"");
                        else {
                            short jdbcType = columns.getShort(5);
                            String dataType = (String) dataTypesMap.get(new Integer(jdbcType));
                            if (dataType == null)
                                dataType = Short.toString(jdbcType);
                            writer.write(" type=\"" + dataType + "\"");
                        }

                        if (primaryKeys.containsKey(columnNameOrig))
                            writer.write(" primary-key=\"yes\"");

                        if (columns.getString(18).equals("NO"))
                            writer.write(" required=\"yes\"");

                        String defaultValue = columns.getString(13);
                        if (defaultValue != null)
                            writer.write(" default=\"" + defaultValue + "\"");

                        String remarks = columns.getString(12);
                        if (remarks != null)
                            writer.write(" descr=\"" + remarks + "\"");

                    } catch (Exception e) {
                    }

                    writer.write("/>\n");
                }
            } finally {
                if (columns != null)
                    columns.close();
            }

            writer.write("        </table>\n");
        }
    } finally {
        tables.close();
    }

    writer.write("    </schema>\n");
    writer.write("</component>");
}

From source file:com.datatorrent.contrib.enrich.JDBCLoader.java

protected ArrayList<Object> getDataFrmResult(Object result) throws RuntimeException {
    try {/*from   w w  w  .  jav a2  s .co m*/
        ResultSet resultSet = (ResultSet) result;
        if (resultSet.next()) {
            ResultSetMetaData rsdata = resultSet.getMetaData();
            // If the includefields is empty, populate it from ResultSetMetaData
            if (CollectionUtils.isEmpty(includeFieldInfo)) {
                if (includeFieldInfo == null) {
                    includeFieldInfo = new ArrayList<>();
                }
                for (int i = 1; i <= rsdata.getColumnCount(); i++) {
                    String columnName = rsdata.getColumnName(i);
                    // TODO: Take care of type conversion.
                    includeFieldInfo.add(new FieldInfo(columnName, columnName, FieldInfo.SupportType.OBJECT));
                }
            }

            ArrayList<Object> res = new ArrayList<Object>();
            for (FieldInfo f : includeFieldInfo) {
                res.add(getConvertedData(resultSet.getObject(f.getColumnName()), f));
            }
            return res;
        } else {
            return null;
        }
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }
}

From source file:com.iih5.smartorm.model.DbExecutor.java

/**
 * Model//ww w .  j  a  v  a  2  s.  c  o m
 * @param sql
 * @param paras
 * @param model
 * @param <T>
 * @return
 * @
 */
<T> List<T> queryList(String sql, Object[] paras, final Class<T> model) {
    final Set<String> columnMeta = new HashSet<String>();
    return jdbc.query(sql, paras, new RowMapper<T>() {
        public T mapRow(ResultSet rs, int rowNum) throws SQLException {
            try {
                if (columnMeta.size() == 0) {
                    for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) {
                        String column = rs.getMetaData().getColumnLabel(i + 1);
                        columnMeta.add(column);
                    }
                }
                Model mModel = (Model) model.newInstance();
                Field[] fields = mModel.getClass().getFields();
                if (0 < fields.length) {
                    for (Field f : fields) {
                        if (columnMeta.contains(f.getName())) {
                            f.set(mModel, rs.getObject(f.getName()));
                        }
                    }
                } else {
                    ResultSetMetaData rad = rs.getMetaData();
                    int columnCount = rad.getColumnCount();
                    Map<String, Object> attrs = mModel.getAttrs();
                    for (int i = 1; i <= columnCount; i++) {
                        Object value = rs.getObject(i);
                        attrs.put(rad.getColumnName(i), value);
                    }
                }
                return (T) mModel;
            } catch (Exception e) {
                e.printStackTrace();
            }
            return null;
        }
    });
}