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:ch.rgw.tools.JdbcLink.java

public boolean dumpTable(BufferedWriter w, String name) throws Exception {
    Stm stm = getStatement();//from  ww w.ja  v a  2  s.c om
    ResultSet res = stm.query("SELECT * from " + name);
    ResultSetMetaData rm = res.getMetaData();
    int cols = rm.getColumnCount();
    String[] ColNames = new String[cols];
    int[] colTypes = new int[cols];
    w.write("CREATE TABLE " + name + "(");
    for (int i = 0; i < cols; i++) {
        ColNames[i] = rm.getColumnName(i + 1);
        colTypes[i] = rm.getColumnType(i + 1);
        w.write(ColNames[i] + " " + colTypes[i] + ",\n");
    }
    w.write(");");

    while ((res != null) && (res.next() == true)) {
        w.write("INSERT INTO " + name + " (");
        for (int i = 0; i < cols; i++) {
            w.write(ColNames[i]);
            if (i < cols - 1) {
                w.write(",");
            }
        }
        w.write(") VALUES (");
        for (int i = 0; i < cols; i++) {
            Object o = res.getObject(ColNames[i]);
            switch (JdbcLink.generalType(colTypes[i])) {
            case JdbcLink.INTEGRAL:
                if (o == null) {
                    w.write("0");
                } else {
                    w.write(Integer.toString(((Integer) o).intValue()));
                }
                break;
            case JdbcLink.TEXT:
                if (o == null) {
                    w.write(JdbcLink.wrap("null"));
                } else {
                    w.write(JdbcLink.wrap((String) o));
                }
                break;

            default:
                String t = o.getClass().getName();
                log.log("Unknown type " + t, Log.ERRORS);
                throw new Exception("Cant write " + t);

            }
            if (i < cols - 1) {
                w.write(",");
            }
        }
        w.write(");");
        w.newLine();
    }
    res.close();
    releaseStatement(stm);
    return true;
}

From source file:edu.ku.brc.specify.conversion.BasicSQLUtils.java

/**
 * @param sql// w w  w .  j  a  v  a  2 s . co  m
 * @return
 */
public static Object[] getRow(final Connection connection, final String sql) {
    Object[] row = null;
    Statement stmt = null;
    try {
        stmt = connection.createStatement();
        ResultSet rs = stmt.executeQuery(sql);
        if (rs.next()) {
            row = new Object[rs.getMetaData().getColumnCount()];

            for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
                row[i - 1] = rs.getObject(i);
            }
        }
        rs.close();

    } catch (Exception ex) {
        ex.printStackTrace();

    } finally {
        if (stmt != null) {
            try {
                stmt.close();

            } catch (Exception ex) {
            }
        }
    }

    return row;
}

From source file:edu.ku.brc.specify.conversion.BasicSQLUtils.java

/**
 * @param sql/*from   www  .j  a v a  2 s. co m*/
 * @return
 */
public static Object[] queryForRow(final Connection connection, final String sql) {
    Object[] row = null;
    Statement stmt = null;
    try {
        stmt = connection.createStatement();
        ResultSet rs = stmt.executeQuery(sql);
        if (rs.next()) {
            row = new Object[rs.getMetaData().getColumnCount()];

            for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
                row[i - 1] = rs.getObject(i);
            }
        }
        rs.close();

    } catch (Exception ex) {
        ex.printStackTrace();

    } finally {
        if (stmt != null) {
            try {
                stmt.close();

            } catch (Exception ex) {
            }
        }
    }

    return row;
}

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

/**
 * @param cmpRow/*from  ww w .j  a v a2 s  .c  om*/
 * @param rs
 * @throws SQLException
 */
public void fillSNIBRow(final Object[] cmpRow, final ResultSet rs) throws SQLException {
    //   1          2           3        4                5             6         7          8          9               10            11       12       13        14     15       16         17                18
    // IdSNIB, CatalogNumber, Genus, Species, Cataegoryinfraspecies, Latitude, Longitude, Country, LastNameFather, LastNameMother, FirstName, State, Locality, `Year`, `Month`, `Day`, CollectorNumber, InstitutionAcronym FROM angiospermas "; 

    cmpRow[CATNUM_INX] = rs.getString(2);
    cmpRow[COLNUM_INX] = rs.getString(17);
    cmpRow[GENUS_INX] = rs.getString(3);
    cmpRow[SPECIES_INX] = rs.getString(4);
    cmpRow[SUBSPECIES_INX] = rs.getString(5);
    cmpRow[LOCALITY_INX] = rs.getString(13);
    cmpRow[LATITUDE_INX] = rs.getString(6);
    cmpRow[LONGITUDE_INX] = rs.getString(7);
    cmpRow[YEAR_INX] = getIntToStr(rs.getObject(14));
    cmpRow[MON_INX] = getIntToStr(rs.getObject(15));
    cmpRow[DAY_INX] = getIntToStr(rs.getObject(16));
    cmpRow[COUNTRY_INX] = rs.getString(16);
    cmpRow[STATE_INX] = rs.getString(12);
    cmpRow[INST_INX] = rs.getString(18);

    String fatherName = rs.getString(9);
    String motherName = rs.getString(10);
    String firstName = rs.getString(11);

    boolean hasFather = StringUtils.isNotEmpty(fatherName);
    boolean hasMother = StringUtils.isNotEmpty(motherName);
    boolean hasFirst = StringUtils.isNotEmpty(firstName);

    sb.setLength(0);
    if (hasFather) {
        sb.append(fatherName);
    }
    if (hasMother) {
        if (hasFather)
            sb.append(", ");
        sb.append(motherName);
    }
    if (hasFirst) {
        if (hasFather || hasMother)
            sb.append(", ");
        sb.append(firstName);
    }
    cmpRow[COLLECTOR_INX] = sb.toString();

    for (int i = COLNUM_INX; i < SCORE_INX; i++) {
        if (cmpRow[i] != null) {
            cmpRow[i] = ((String) cmpRow[i]).trim();
        }
    }
}

From source file:com.itemanalysis.jmetrik.graph.nicc.NonparametricCurveAnalysis.java

public void evaluate() throws SQLException {
    kernelRegression = new TreeMap<VariableAttributes, KernelRegressionItem>();
    for (VariableAttributes v : variables) {
        KernelRegressionItem kItem = new KernelRegressionItem(v, kernelFunction, bandwidth,
                uniformDistributionApproximation);
        kernelRegression.put(v, kItem);/*from   w  ww .  ja  va2 s  .  c  o  m*/
    }

    ResultSet rs = null;
    Statement stmt = null;

    try {
        //connect to db
        Table sqlTable = new Table(tableName.getNameForDatabase());
        SelectQuery select = new SelectQuery();
        for (VariableAttributes v : variables) {
            select.addColumn(sqlTable, v.getName().nameForDatabase());
        }
        select.addColumn(sqlTable, regressorVariable.getName().nameForDatabase());
        if (hasGroupVariable)
            select.addColumn(sqlTable, groupByVariable.getName().nameForDatabase());

        stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        rs = stmt.executeQuery(select.toString());

        KernelRegressionItem kernelRegressionItem;
        Object itemResponse;
        Double score;
        Object tempGroup;
        String group;

        while (rs.next()) {
            //increment kernel regression objects
            //omit examinees with missing data
            score = rs.getDouble(regressorVariable.getName().nameForDatabase());
            if (!rs.wasNull()) {
                for (VariableAttributes v : kernelRegression.keySet()) {
                    kernelRegressionItem = kernelRegression.get(v);
                    itemResponse = rs.getObject(v.getName().nameForDatabase());
                    if (itemResponse != null)
                        kernelRegressionItem.increment(score, itemResponse);
                }
            }
            updateProgress();
        }
    } catch (SQLException ex) {
        throw ex;
    } finally {
        if (rs != null)
            rs.close();
        if (stmt != null)
            stmt.close();
    }

    this.firePropertyChange("progress-ind-on", null, null);

}

From source file:com.thinkbiganalytics.hive.service.HiveService.java

public QueryResult query(String query) throws DataAccessException {
    final DefaultQueryResult queryResult = new DefaultQueryResult(query);
    final List<QueryResultColumn> columns = new ArrayList<>();
    final Map<String, Integer> displayNameMap = new HashMap<>();
    if (query != null && !query.toLowerCase().startsWith("show")) {
        query = safeQuery(query);/* w  w w  .  j  av a2 s  .c  o  m*/
    }
    try {
        //  Setting in order to query complex formats like parquet
        jdbcTemplate.execute("set hive.optimize.index.filter=false");
        jdbcTemplate.query(query, new RowMapper<Map<String, Object>>() {
            @Override
            public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
                if (columns.isEmpty()) {
                    ResultSetMetaData rsMetaData = rs.getMetaData();
                    for (int i = 1; i <= rsMetaData.getColumnCount(); i++) {
                        String colName = rsMetaData.getColumnName(i);
                        DefaultQueryResultColumn column = new DefaultQueryResultColumn();
                        column.setField(rsMetaData.getColumnName(i));
                        String displayName = rsMetaData.getColumnLabel(i);
                        column.setHiveColumnLabel(displayName);
                        //remove the table name if it exists
                        displayName = StringUtils.substringAfterLast(displayName, ".");
                        Integer count = 0;
                        if (displayNameMap.containsKey(displayName)) {
                            count = displayNameMap.get(displayName);
                            count++;
                        }
                        displayNameMap.put(displayName, count);
                        column.setDisplayName(displayName + "" + (count > 0 ? count : ""));

                        column.setTableName(StringUtils.substringAfterLast(rsMetaData.getColumnName(i), "."));
                        column.setDataType(ParserHelper.sqlTypeToHiveType(rsMetaData.getColumnType(i)));
                        columns.add(column);
                    }
                    queryResult.setColumns(columns);
                }
                Map<String, Object> row = new LinkedHashMap<>();
                for (QueryResultColumn column : columns) {
                    row.put(column.getDisplayName(), rs.getObject(column.getHiveColumnLabel()));
                }
                queryResult.addRow(row);
                return row;
            }
        });

    } catch (DataAccessException dae) {
        dae.printStackTrace();
        throw dae;
    }
    return queryResult;

}

From source file:QueryConnector.java

private void loadData(XSpreadsheet sheet, CellAddress startCell, Query query, boolean saveConnectionInfo)
        throws Exception {
    XCellRange cellRange = UnoRuntime.queryInterface(XCellRange.class, sheet);
    com.sun.star.lang.Locale locale = new com.sun.star.lang.Locale();
    //effettuo la query sul db
    Class.forName(query.getDriverClass());
    Connection con = DriverManager.getConnection(query.getURL(), query.getUsername(), query.getPassword());
    Statement stmt = con.createStatement();
    ResultSet result = stmt.executeQuery(query.getQuery());
    ResultSetMetaData metaData = result.getMetaData();
    int columnCount = metaData.getColumnCount();
    //intestazione del risultato (nomi delle colonne)
    for (int i = 1, j = startCell.Column; i <= columnCount; i++, j++) {
        XCell curCellHeader = cellRange.getCellByPosition(j, startCell.Row);
        XTextRange currentCellHeaderText = UnoRuntime.queryInterface(XTextRange.class, curCellHeader);
        currentCellHeaderText.setString(metaData.getColumnLabel(i));
        if (saveConnectionInfo)
            setCellUserProperty(curCellHeader, QUERY_PROPERTY, query.getName());
    }/*from   ww  w .j av  a2s  .  c  o m*/
    //contenuto della query
    int rowCount = 1;
    int rowIdx = startCell.Row + 1;
    while (result.next()) {
        for (int i = 1, j = startCell.Column; i <= columnCount; i++, j++) {
            XCell curCellData = cellRange.getCellByPosition(j, rowIdx);
            XPropertySet cellDataProps = UnoRuntime.queryInterface(XPropertySet.class, curCellData);
            XTextRange currentCellDataText = UnoRuntime.queryInterface(XTextRange.class, curCellData);
            Object cellValue = result.getObject(i);
            if (cellValue instanceof java.sql.Date || cellValue instanceof java.sql.Time
                    || cellValue instanceof java.util.Date) {
                cellDataProps.setPropertyValue("NumberFormat",
                        this.numberFormats.queryKey("GG/MM/AAAA HH.MM.SS", locale, true));
                curCellData.setValue(dateValue(DATE_FORMATTER.format((java.util.Date) cellValue)));
            } else if (cellValue instanceof Number) {
                cellDataProps.setPropertyValue("NumberFormat", this.numberFormats.queryKey("#", locale, true));
                Number number = (Number) cellValue;
                curCellData.setValue(number.doubleValue());
            } else if (cellValue == null) {
                currentCellDataText.setString("");
            } else
                currentCellDataText.setString(cellValue.toString());
            if (saveConnectionInfo)
                setCellUserProperty(curCellData, QUERY_PROPERTY, query.getName());
        }
        rowCount++;
        rowIdx++;
    }
    result.close();
    stmt.close();
    con.close();
    //aggiono le informazioni sul range dei dati selezionato nella query
    query.setPositionAndArea(startCell.Column, startCell.Row, columnCount, rowCount);
    //salvo le informazioni sul documento
    if (saveConnectionInfo)
        settings.saveQuery(query);
}

From source file:com.myapp.dao.SalesOrderDAO.java

public ArrayList<SalesOrder> getAllOrders() throws SQLException {

    ArrayList<SalesOrder> retVal = null;
    try {//from   w w  w  .j  a  va2  s.com
        // Load the driver.
        Class.forName("org.relique.jdbc.csv.CsvDriver");

        // Create a connection. CSV file is in D:
        Connection conn = DriverManager.getConnection("jdbc:relique:csv:C:");

        // Create a Statement object to execute the query with.
        Statement stmt = conn.createStatement();

        // Select columns from SalesOrder.csv
        ResultSet rs = stmt.executeQuery("SELECT * FROM SalesOrder");

        //loop through rs
        // Clean up
        ResultSetMetaData metaData = rs.getMetaData();
        int resultColumnCount = metaData.getColumnCount();

        while (rs.next()) {
            if (resultColumnCount > 1) {

                SalesOrder so = new SalesOrder();
                so.setSalesOrderID(rs.getString(1));
                so.setRevisionNumber(rs.getString(2));
                so.setOrderDate(rs.getString(3));
                so.setDueDate(rs.getString(4));
                so.setShipDate(rs.getString(5));
                so.setStatus(rs.getString(6));
                so.setOnlineOrderFlag(rs.getString(7));
                so.setSalesOrderNumber(rs.getString(8));
                so.setPurchaseOrderNumber(rs.getString(9));
                so.setAccountNumber(rs.getString(10));
                so.setCustomerID(rs.getString(11));
                so.setSalesPersonID(rs.getString(12));
                so.setTerritoryID(rs.getString(13));
                so.setBillToAddressID(rs.getString(14));
                so.setShipToAddressID(rs.getString(15));
                so.setShipMethodID(rs.getString(16));
                so.setCreditCardID(rs.getString(17));
                so.setCreditCardApprovalCode(rs.getString(18));
                so.setCurrencyRateID(rs.getString(19));
                so.setSubTotal(rs.getString(20));
                so.setTaxAmt(rs.getString(21));
                so.setFreight(rs.getString(22));
                so.setTotalDue(rs.getString(23));
                so.setComment(rs.getString(24));
                so.setModifiedDate(rs.getString(25));
                retVal.add(so);
            } else {
                Object obj = rs.getObject(1);

            }
        }

        conn.close();
    } catch (Exception e) {
        System.out.println("EXCEPTION: " + e.getMessage());
    }

    return retVal;
}

From source file:it.greenvulcano.gvesb.utils.ResultSetUtils.java

/**
 * Returns all values from the ResultSet as an XML.
 * For instance, if the ResultSet has 3 values, the returned XML will have following fields:
 *                                <RowSet>
 *                                  <data>
 *                                    <row>
 *                                      <col>value1</col>
 *                                      <col>value2</col>
 *                                      <col>value3</col>
 *                                    </row>
 *                                    <row>
 *                                      <col>value4</col>
 *                                      <col>value5</col>
 *                                      <col>value6</col>
 *                                    </row>
 *                                  ../*from   w ww . java  2 s.  c o m*/
 *                                    <row>
 *                                      <col>valuex</col>
 *                                      <col>valuey</col>
 *                                      <col>valuez</col>
 *                                    </row>
 *                                  </data>
 *                                </RowSet>
 * @param rs
 * @return
 * @throws Exception
 */
public static Document getResultSetAsDOM(ResultSet rs) throws Exception {
    XMLUtils xml = XMLUtils.getParserInstance();
    try {
        Document doc = xml.newDocument("RowSet");
        Element docRoot = doc.getDocumentElement();

        if (rs != null) {
            try {
                ResultSetMetaData metadata = rs.getMetaData();
                Element data = null;
                Element row = null;
                Element col = null;
                Text text = null;
                String textVal = null;
                while (rs.next()) {
                    boolean restartResultset = false;
                    for (int j = 1; j <= metadata.getColumnCount() && !restartResultset; j++) {
                        col = xml.createElement(doc, "col");
                        restartResultset = false;
                        switch (metadata.getColumnType(j)) {
                        case Types.CLOB: {
                            Clob clob = rs.getClob(j);
                            if (clob != null) {
                                Reader is = clob.getCharacterStream();
                                StringWriter strW = new StringWriter();

                                IOUtils.copy(is, strW);
                                is.close();
                                textVal = strW.toString();
                            } else {
                                textVal = "";
                            }
                        }
                            break;
                        case Types.BLOB: {
                            Blob blob = rs.getBlob(j);
                            if (blob != null) {
                                InputStream is = blob.getBinaryStream();
                                ByteArrayOutputStream baos = new ByteArrayOutputStream();
                                IOUtils.copy(is, baos);
                                is.close();
                                try {
                                    byte[] buffer = Arrays.copyOf(baos.toByteArray(), (int) blob.length());
                                    textVal = new String(Base64.getEncoder().encode(buffer));
                                } catch (SQLFeatureNotSupportedException exc) {
                                    textVal = new String(Base64.getEncoder().encode(baos.toByteArray()));
                                }
                            } else {
                                textVal = "";
                            }
                        }
                            break;
                        case -10: { // OracleTypes.CURSOR
                            Object obj = rs.getObject(j);
                            if (obj instanceof ResultSet) {
                                rs = (ResultSet) obj;
                                metadata = rs.getMetaData();
                            }
                            restartResultset = true;
                        }
                            break;
                        default: {
                            textVal = rs.getString(j);
                            if (textVal == null) {
                                textVal = "";
                            }
                        }
                        }
                        if (restartResultset) {
                            continue;
                        }
                        if (row == null || j == 1) {
                            row = xml.createElement(doc, "row");
                        }
                        if (textVal != null) {
                            text = doc.createTextNode(textVal);
                            col.appendChild(text);
                        }
                        row.appendChild(col);
                    }
                    if (row != null) {
                        if (data == null) {
                            data = xml.createElement(doc, "data");
                        }
                        data.appendChild(row);
                    }
                }
                if (data != null) {
                    docRoot.appendChild(data);
                }
            } finally {
                if (rs != null) {
                    try {
                        rs.close();
                    } catch (Exception exc) {
                        // do nothing
                    }
                    rs = null;
                }
            }
        }

        return doc;
    } finally {
        XMLUtils.releaseParserInstance(xml);
    }
}

From source file:com.p5solutions.core.jpa.orm.rowbinder.EntityRowBinder.java

@Override
public T mapRow(ResultSet rs, int rowNum) throws SQLException {
    EntityDetail<T> entityDetail = getEntityUtility().getEntityDetail(entityClass);
    T entity = entityDetail.newInstance();
    ResultSetMetaData metaData = rs.getMetaData();

    // add the entity to the join filter list.
    getJoinFilter().add(this.currentQueryIdentifier, entity);

    boolean isDebug = logger.isDebugEnabled();

    if (isDebug) {
        // attempt to parse the resultset into the entity.********
        logger.debug("Mapping row for entity type: " + entityClass);
    }//  ww  w . j a v a2  s .  co m

    for (ParameterBinder pb : entityDetail.getParameterBinders()) {

        // skip over transient methods
        if (pb.isTransient()) {
            continue;
        }

        String columnName = pb.getColumnNameAnyJoinOrColumn();

        if (columnName == null) {
            columnName = pb.getBindingNameUpper();
        }

        // find the column index based on column name, case insensitive
        // TODO deprecated as per change to adding meta-data code generation to EntityUtility# 
        int columnIndex = findColumnIndex(metaData, columnName);

        //int columnIndex = pb.getColumnMetaData().getColumnIndex();

        if (columnIndex == -1) {
            NoColumnFoundInResultSetException e = new NoColumnFoundInResultSetException(
                    entityDetail.getEntityClass(), columnName);
            logger.debug(e.toString());
            throw e;
        }

        // get the value
        Object resultValue = rs.getObject(columnIndex);

        if (doColumn(pb, entity, resultValue)) {

            if (isDebug) {
                logger.debug(" -> Column [" + pb.getColumnMetaData().getColumnName() + "] with value ["
                        + (resultValue == null ? " NULL " : resultValue) + "] to path " + pb.getBindingPath());
            }
        } else if (doJoinColumn(pb, entity, resultValue)) {
            // / TODO ??
        }
    }

    return entity;
}