Example usage for java.sql ResultSet getBigDecimal

List of usage examples for java.sql ResultSet getBigDecimal

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

Introduction

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

Prototype

BigDecimal getBigDecimal(String columnLabel) throws SQLException;

Source Link

Document

Retrieves the value of the designated column in the current row of this ResultSet object as a java.math.BigDecimal with full precision.

Usage

From source file:org.ojbc.adapters.analyticsstaging.custody.dao.AnalyticalDatastoreDAOImpl.java

private Address buildAddress(ResultSet rs) throws SQLException {
    Address address = new Address(DaoUtils.getInteger(rs, "locationID"));
    address.setStreetNumber(rs.getString("streetNumber"));
    address.setStreetName(rs.getString("streetName"));
    address.setAddressSecondaryUnit(rs.getString("addressSecondaryUnit"));
    address.setCity(rs.getString("city"));
    address.setState(rs.getString("State"));
    address.setPostalcode(rs.getString("postalcode"));
    address.setLocationLatitude(rs.getBigDecimal("LocationLatitude"));
    address.setLocationLongitude(rs.getBigDecimal("LocationLongitude"));
    return address;
}

From source file:CSVWriter.java

private String getColumnValue(ResultSet rs, int colType, int colIndex) throws SQLException, IOException {

    String value = "";

    switch (colType) {
    case Types.BIT:
    case Types.JAVA_OBJECT:
        value = handleObject(rs.getObject(colIndex));
        break;//from ww  w .  j  a  va2s.c o  m
    case Types.BOOLEAN:
        boolean b = rs.getBoolean(colIndex);
        value = Boolean.valueOf(b).toString();
        break;
    case NCLOB: // todo : use rs.getNClob
    case Types.CLOB:
        Clob c = rs.getClob(colIndex);
        if (c != null) {
            value = read(c);
        }
        break;
    case Types.BIGINT:
        value = handleLong(rs, colIndex);
        break;
    case Types.DECIMAL:
    case Types.DOUBLE:
    case Types.FLOAT:
    case Types.REAL:
    case Types.NUMERIC:
        value = handleBigDecimal(rs.getBigDecimal(colIndex));
        break;
    case Types.INTEGER:
    case Types.TINYINT:
    case Types.SMALLINT:
        value = handleInteger(rs, colIndex);
        break;
    case Types.DATE:
        value = handleDate(rs, colIndex);
        break;
    case Types.TIME:
        value = handleTime(rs.getTime(colIndex));
        break;
    case Types.TIMESTAMP:
        value = handleTimestamp(rs.getTimestamp(colIndex));
        break;
    case NVARCHAR: // todo : use rs.getNString
    case NCHAR: // todo : use rs.getNString
    case LONGNVARCHAR: // todo : use rs.getNString
    case Types.LONGVARCHAR:
    case Types.VARCHAR:
    case Types.CHAR:
        value = rs.getString(colIndex);
        break;
    default:
        value = "";
    }

    if (value == null) {
        value = "";
    }

    return value;

}

From source file:com.cws.us.pws.dao.impl.CareersReferenceDAOImpl.java

/**
 * @see com.cws.us.pws.dao.interfaces.ICareersReferenceDAO#getCareerList(String) throws SQLException
 *//*from   ww  w.  j  a va2s . c  om*/
@Override
public List<Object[]> getCareerList(final String lang) throws SQLException {
    final String methodName = ICareersReferenceDAO.CNAME
            + "#getCareerList(final String lang) throws SQLException";

    if (DEBUG) {
        DEBUGGER.debug(methodName);
        DEBUGGER.debug("Value: {}", lang);
    }

    Connection sqlConn = null;
    ResultSet resultSet = null;
    CallableStatement stmt = null;
    List<Object[]> results = null;

    try {
        sqlConn = this.dataSource.getConnection();

        if (DEBUG) {
            DEBUGGER.debug("Connection: {}", sqlConn);
        }

        if (sqlConn.isClosed()) {
            throw new SQLException("Unable to obtain connection to application datasource");
        }

        stmt = sqlConn.prepareCall("{ CALL getCareersList(?) }");
        stmt.setString(1, lang);

        if (DEBUG) {
            DEBUGGER.debug("CallableStatement: {}", stmt);
        }

        if (!(stmt.execute())) {
            throw new SQLException("PreparedStatement is null. Cannot execute.");
        }

        resultSet = stmt.getResultSet();

        if (DEBUG) {
            DEBUGGER.debug("ResultSet: {}", resultSet);
        }

        if (resultSet.next()) {
            resultSet.beforeFirst();
            results = new ArrayList<Object[]>();

            while (resultSet.next()) {
                Object[] data = new Object[] { resultSet.getString(1), // REQ_ID
                        resultSet.getString(2), // POST_DATE
                        resultSet.getString(3), // UNPOST_DATE
                        resultSet.getString(4), // JOB_TITLE
                        resultSet.getBigDecimal(5), // JOB_SHORT_DESC
                        resultSet.getString(6), // JOB_DESCRIPTION
                };

                results.add(data);
            }

            if (DEBUG) {
                DEBUGGER.debug("results: {}", results);
            }
        }
    } catch (SQLException sqx) {
        ERROR_RECORDER.error(sqx.getMessage(), 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();
        }
    }

    if (DEBUG) {
        DEBUGGER.debug("results: {}", results);
    }

    return results;
}

From source file:org.cloudgraph.rdb.service.RDBDataConverter.java

private Object convertFrom(ResultSet rs, int columnIndex, int sourceType, Property property)
        throws SQLException {
    Object result = null;/*w  w  w  .j a v a2 s . c  o m*/
    if (!property.getType().isDataType())
        throw new IllegalArgumentException("expected data type property, not " + property.toString());
    DataType targetDataType = DataType.valueOf(property.getType().getName());
    switch (targetDataType) {
    case String:
    case URI:
    case Month:
    case MonthDay:
    case Day:
    case Time:
    case Year:
    case YearMonth:
    case YearMonthDay:
    case Duration:
        result = rs.getString(columnIndex);
        break;
    case Date:
        java.sql.Timestamp ts = rs.getTimestamp(columnIndex);
        if (ts != null)
            result = new java.util.Date(ts.getTime());
        break;
    case DateTime:
        ts = rs.getTimestamp(columnIndex);
        if (ts != null) {
            // format DateTime String for SDO
            java.util.Date date = new java.util.Date(ts.getTime());
            result = DataConverter.INSTANCE.getDateTimeFormat().format(date);
        }
        break;
    case Decimal:
        result = rs.getBigDecimal(columnIndex);
        break;
    case Bytes:
        if (sourceType != Types.BLOB) {
            result = rs.getBytes(columnIndex);
        } else if (sourceType == Types.BLOB) {
            Blob blob = rs.getBlob(columnIndex);
            if (blob != null) {
                long blobLen = blob.length(); // for debugging
                // Note: blob.getBytes(columnIndex, blob.length()); is
                // somehow truncating the array
                // by something like 14 bytes (?!!) even though
                // blob.length() returns the expected length
                // using getBinaryStream which is preferred anyway
                InputStream is = blob.getBinaryStream();
                try {
                    byte[] bytes = IOUtils.toByteArray(is);
                    long len = bytes.length; // for debugging
                    result = bytes;
                } catch (IOException e) {
                    throw new RDBServiceException(e);
                } finally {
                    try {
                        is.close();
                    } catch (IOException e) {
                        log.error(e.getMessage(), e);
                    }
                }
            }
        }
        break;
    case Byte:
        result = rs.getByte(columnIndex);
        break;
    case Boolean:
        result = rs.getBoolean(columnIndex);
        break;
    case Character:
        result = rs.getInt(columnIndex);
        break;
    case Double:
        result = rs.getDouble(columnIndex);
        break;
    case Float:
        result = rs.getFloat(columnIndex);
        break;
    case Int:
        result = rs.getInt(columnIndex);
        break;
    case Integer:
        result = new BigInteger(rs.getString(columnIndex));
        break;
    case Long:
        result = rs.getLong(columnIndex);
        break;
    case Short:
        result = rs.getShort(columnIndex);
        break;
    case Strings:
        String value = rs.getString(columnIndex);
        if (value != null) {
            String[] values = value.split("\\s");
            List<String> list = new ArrayList<String>(values.length);
            for (int i = 0; i < values.length; i++)
                list.add(values[i]); // what no Java 5 sugar for this ??
            result = list;
        }
        break;
    case Object:
    default:
        result = rs.getObject(columnIndex);
        break;
    }
    return result;
}

From source file:nl.tudelft.stocktrader.mysql.MySQLCustomerDAO.java

public List<Order> getOrders(String userId, boolean top, int maxTop, int maxDefault) throws DAOException {
    PreparedStatement selectOrdersById = null;
    try {/*from  w  w  w.j a v  a 2s  .c  om*/
        String sqlQuery;
        if (top) {
            sqlQuery = "SELECT " + SQL_SELECT_ORDERS_BY_ID + " LIMIT " + maxTop;
        } else {
            sqlQuery = "SELECT " + SQL_SELECT_ORDERS_BY_ID + " LIMIT " + maxDefault;
        }
        selectOrdersById = sqlConnection.prepareStatement(sqlQuery);
        selectOrdersById.setString(1, userId);
        ResultSet rs = selectOrdersById.executeQuery();
        List<Order> orders = new ArrayList<Order>();

        try {
            while (rs.next()) {
                int orderId = rs.getInt(1);
                Calendar openDate = StockTraderUtility.convertToCalendar(rs.getDate(4));
                Calendar completionDate = null;
                try {
                    if (rs.getDate(5) != null) {
                        completionDate = StockTraderUtility.convertToCalendar(rs.getDate(5));
                    } else {
                        completionDate = Calendar.getInstance();
                        completionDate.setTimeInMillis(0);
                    }
                } catch (SQLException e) {
                    logger.debug("", e);
                    completionDate = Calendar.getInstance();
                    completionDate.setTimeInMillis(0);
                }

                Order orderBean = new Order(orderId, rs.getString(2), rs.getString(3), openDate, completionDate,
                        rs.getDouble(6), rs.getBigDecimal(7), rs.getBigDecimal(8), rs.getString(9));
                orders.add(orderBean);
            }

        } finally {
            try {
                rs.close();
            } catch (SQLException e) {
                logger.debug("", e);
            }
        }
        return orders;

    } catch (SQLException e) {
        throw new DAOException("", e);
    } finally {
        if (selectOrdersById != null) {
            try {
                selectOrdersById.close();
            } catch (SQLException e) {
                logger.debug("", e);
            }
        }
    }
}

From source file:com.intuit.it.billing.data.BillingDAOImpl.java

/**
 * getAllocationsList//w  w  w.  jav a  2 s.c  o  m
 * 
 * To be used in a caching method where we are pulling all of the allocations at once.  The way we can do this
 * is to merge a date range based set of billing history records with a date range set of allocations.
 * <p/>
 * <p/>
 * <b>DATABASE PROCEDURE:</b>
 *  
 * @code
 *     FUNCTION fn_get_allocations(
 *           customer IN VARCHAR2,
 *           start_date IN DATE,
 *           end_date   IN DATE )
 *        RETURN ref_cursor;
 * @endcode
 * <p/>
 * <b>DATABASE RESULT SET:</b>
 * <ul>
 *    <li>ALLOCATION_DATE,</li>
 *    <li>ALLOCATION_T, </li>
 *    <li>ALLOCATION_AMT,</li>
 *    <li>AR_ITEM_NO, </li>
 *    <li>BILL_ITEM_NO, </li>
 *    <li>ITEM_DESCRIPTION, </li>
 *    <li>ITEM_CODE,</li> 
 *    <li>AR_ITEM_DATE, </li>
 *    <li>BILL_ITEM_DATE </li>
 *    <li>LICENSE</li>
 *  </ul>
 *  
 * @param customer  :  The Customer.accountNo account number of the customer who's allocations we need
 * @param startDate : The starting date of the allocation - to be merged with a billing history record set
 * @param endDate  :  The ending date of the allocation - to be merged with a billing history record set
 * 
 * @return A list of Allocation objects. 
 * 
 */
@Override
public List<Allocation> getAllocationsList(String customer, Date startDate, Date endDate) throws JSONException {

    List<Allocation> allocs = new ArrayList<Allocation>();

    java.sql.Date sqlStartDate = new java.sql.Date(startDate.getTime());
    java.sql.Date sqlEndDate = new java.sql.Date(endDate.getTime());

    String query = "begin ? := billing_inquiry.fn_get_allocations( ?, ?, ? ); end;";

    Connection conn = null;
    ResultSet rs = null;

    // DB Connection
    try {
        conn = this.getConnection();
    } catch (SQLException e) {
        throw JSONException.sqlError(e);
    } catch (NamingException e) {
        throw JSONException.namingError(e.toString());
    }

    try {
        CallableStatement stmt = conn.prepareCall(query);
        stmt.registerOutParameter(1, OracleTypes.CURSOR);
        stmt.setString(2, customer);
        stmt.setDate(3, sqlStartDate);
        stmt.setDate(4, sqlEndDate);

        stmt.execute();
        rs = (ResultSet) stmt.getObject(1);

        while (rs.next()) {

            Allocation a = new Allocation();
            a.setAllocatedFromItem(rs.getString("AR_ITEM_NO"));
            a.setAllocatedToItem(rs.getString("BILL_ITEM_NO"));
            a.setAllocationAmount(rs.getBigDecimal("ALLOCATION_AMT"));
            a.setAllocationDate(rs.getTimestamp("ALLOCATION_DATE"));
            a.setItemCode(rs.getString("ITEM_CODE"));
            a.setItemDescription(rs.getString("ITEM_DESCRIPTION"));
            a.setLicense(rs.getString("LICENSE"));
            allocs.add(a);
        }
        conn.close();
        rs.close();

    } catch (SQLException e) {
        throw JSONException.sqlError(e);
    }

    if (allocs == null || allocs.isEmpty()) {
        throw JSONException.noDataFound("Null set returned - no data found");
    }

    return allocs;
}

From source file:com.intuit.it.billing.data.BillingDAOImpl.java

/**
 * getBillingHistory is to be used to get  get the billing history of a given customer.
 * <p/>//from w  w  w  .j a  va2s  . c om
 * <p/>
 * <b>DATABASE PROCEDURE:</b>
 *  
 * @code
 *  FUNCTION fn_get_history(
 *    customer   IN VARCHAR2,
 *    start_date IN DATE,
 *    end_date   IN DATE,
 *    page       IN INTEGER,
 *    records    IN INTEGER )
 *  RETURN ref_cursor;
 *  @endcode
 * <p/>
 * <b>DATABASE RESULT SET:</b>
 * <ul>
 *    <li>ITEM_ID,</li>
 *    <li>BILL_ITEM_NO,</li>
 *    <li>AR_ACCOUNT_NO,</li>
 *    <li>ACCOUNT_NO,</li>
 *    <li>ORDER_NO,</li>
 *    <li>ORDER_LINE_NO,</li>
 *    <li>EVENT_TYPE,</li>
 *    <li>CHARGE_TYPE,</li>
 *    <li> CURRENCY,</li>
 *    <li>CREATED_DATE,</li>
 *    <li>BILL_DATE,</li>
 *    <li>DUE_DATE,</li>
 *    <li>STATUS,</li>
 *    <li>REASON_CODE,</li>
 *    <li>ITEM_TOTAL,</li>
 *    <li>ITEM_DUE,</li>
 *    <li>ITEM_DISPUTED,</li>
 *    <li>ITEM_BASE,</li>
 *    <li>ITEM_TAX,</li>
 *    <li>ITEM_DESCRIPTION,</li>
 *    <li>ITEM_CODE,</li>
 *    <li>LICENSE,</li>
 *    <li>PAY_TYPE,</li>
 *    <li>PAY_DESCR,</li>
 *    <li>PAY_ACCT_TYPE,
 *    <li>PAY_PSON,</li>
 *    <li>QUANTITY </li>
 *  </ul>
 *  
 * @param customer  :  The Customer.accountNo of the customer we want history for
 * @param startDate : The starting date of the allocation - to be merged with a billing history record set
 * @param endDate   :  The ending date of the allocation - to be merged with a billing history record set
 * @param skip      :  Starting record for server side paging
 * @param pageSize  :  How many records to retrieve 
 * 
 * @return A list of LineItem objects in reverse date order sort
 */
@Override
public List<LineItem> getBillingHistory(String cust, Date startDate, Date endDate, Integer startPage,
        Integer pageSize) throws JSONException {

    List<LineItem> history = new ArrayList<LineItem>();

    java.sql.Date sqlStartDate = new java.sql.Date(startDate.getTime());
    java.sql.Date sqlEndDate = new java.sql.Date(endDate.getTime());

    String query = "begin ? := billing_inquiry.fn_get_history( ?, ?, ?, ?, ? ); end;";

    Connection conn = null;
    ResultSet rs = null;

    // DB Connection
    try {
        conn = this.getConnection();
    } catch (SQLException e) {
        throw JSONException.sqlError(e);
    } catch (NamingException e) {
        throw JSONException.namingError(e.toString());
    }

    try {

        CallableStatement stmt = conn.prepareCall(query);
        stmt.registerOutParameter(1, OracleTypes.CURSOR);
        stmt.setString(2, cust);
        stmt.setDate(3, sqlStartDate);
        stmt.setDate(4, sqlEndDate);
        stmt.setInt(5, startPage);
        stmt.setInt(6, pageSize);

        stmt.execute();
        rs = (ResultSet) stmt.getObject(1);

        while (rs.next()) {

            LineItem l = new LineItem();
            l.setRowId(rs.getInt("ROW_ID"));
            l.setBaseAmount(rs.getBigDecimal("ITEM_BASE"));
            l.setItemTotal(rs.getBigDecimal("ITEM_TOTAL"));
            l.setItemDue(rs.getBigDecimal("ITEM_DUE"));
            l.setItemDisputed(rs.getBigDecimal("ITEM_DISPUTED"));
            l.setTaxAmount(rs.getBigDecimal("ITEM_TAX"));
            l.setBillDate(rs.getTimestamp("BILL_DATE"));
            l.setBillItemNo(rs.getString("BILL_ITEM_NO"));
            l.setBillTo(rs.getString("AR_ACCOUNT_NO"));
            l.setChargeType(rs.getString("CHARGE_TYPE"));
            l.setCreatedDate(rs.getTimestamp("CREATED_DATE"));
            l.setCurrency(rs.getString("CURRENCY"));
            l.setDueDate(rs.getTimestamp("DUE_DATE"));
            l.setEventType(rs.getString("EVENT_TYPE"));
            l.setItemCode(rs.getString("ITEM_CODE"));
            l.setItemDescription(rs.getString("ITEM_DESCRIPTION"));
            l.setLicense(rs.getString("LICENSE"));
            l.setItemID(rs.getString("ITEM_ID"));
            l.setOrderLine(rs.getString("ORDER_LINE_NO"));
            l.setOrderNo(rs.getString("ORDER_NO"));
            l.setPayAccountType(rs.getString("PAY_ACCT_TYPE"));
            l.setPayDescription(rs.getString("PAY_DESCR"));
            l.setPayType(rs.getString("PAY_TYPE"));
            l.setpSON(rs.getString("PAY_PSON"));
            l.setQuantity(rs.getInt("QUANTITY"));
            l.setReasonCode(rs.getString("REASON_CODE"));
            l.setStatus(rs.getInt("STATUS"));
            history.add(l);
        }

        conn.close();
        rs.close();

    } catch (SQLException e) {
        throw JSONException.sqlError(e);
    }

    if (history == null || history.isEmpty()) {
        throw JSONException.noDataFound("Null set returned - no data found");
    }

    return history;
}

From source file:org.jtotus.database.LocalJDBC.java

public double[] fetchPeriod(String tableName, DateTime startDate, DateTime endDate, String type) {
    BigDecimal retValue = null;//from   ww  w .  j  av a 2 s .  c  o  m
    PreparedStatement pstm = null;
    java.sql.Date retDate = null;
    ResultSet results = null;
    ArrayList<Double> closingPrices = new ArrayList<Double>(600);
    Connection connection = null;

    try {
        String query = "SELECT " + type + ", DATE FROM " + this.normTableName(tableName)
                + " WHERE DATE>=? AND DATE<=? ORDER BY DATE ASC";
        // this.createTable(connection, this.normTableName(tableName));

        connection = this.getConnection();
        pstm = connection.prepareStatement(query, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);

        java.sql.Date startSqlDate = new java.sql.Date(startDate.getMillis());
        pstm.setDate(1, startSqlDate);

        java.sql.Date endSqlDate = new java.sql.Date(endDate.getMillis());
        pstm.setDate(2, endSqlDate);

        DateIterator dateIter = new DateIterator(startDate, endDate);

        results = pstm.executeQuery();
        DateTime dateCheck;

        if (debug) {
            System.out.printf("start data %s end date: %s\n", startSqlDate.toString(), endSqlDate.toString());
        }

        while (dateIter.hasNext()) {
            dateCheck = dateIter.nextInCalendar();

            if (results.next()) {
                retValue = results.getBigDecimal(1);
                retDate = results.getDate(2);

                DateTime compCal = new DateTime(retDate.getTime());
                if (compCal.getDayOfMonth() == dateCheck.getDayOfMonth()
                        && compCal.getMonthOfYear() == dateCheck.getMonthOfYear()
                        && compCal.getYear() == dateCheck.getYear()) {
                    closingPrices.add(retValue.doubleValue());
                    continue;
                } else {
                    results.previous();
                }
            }

            BigDecimal failOverValue = getFetcher().fetchData(tableName, dateCheck, type);
            if (failOverValue != null) {
                closingPrices.add(failOverValue.doubleValue());
            }
        }

    } catch (SQLException ex) {
        System.err.printf("LocalJDBC Unable to find date for:'%s' from'%s' Time" + startDate.toDate() + "\n",
                "Cosing Price", tableName);
        ex.printStackTrace();
        SQLException xp = null;
        while ((xp = ex.getNextException()) != null) {
            xp.printStackTrace();
        }

    } finally {
        try {
            if (results != null)
                results.close();
            if (pstm != null)
                pstm.close();
            if (connection != null)
                connection.close();
            //                System.out.printf("Max connect:%d in use:%d\n",mainPool.getMaxConnections(), mainPool.getActiveConnections());
            //                mainPool.dispose();

        } catch (SQLException ex) {
            Logger.getLogger(LocalJDBC.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    return ArrayUtils.toPrimitive(closingPrices.toArray(new Double[0]));
}

From source file:org.jumpmind.db.sql.JdbcSqlTemplate.java

@SuppressWarnings("unchecked")
public <T> T getObjectFromResultSet(ResultSet rs, Class<T> clazz) throws SQLException {
    T result;/*ww  w  .j a  v  a 2  s. c  o m*/
    if (Date.class.isAssignableFrom(clazz)) {
        result = (T) rs.getTimestamp(1);
    } else if (String.class.isAssignableFrom(clazz)) {
        result = (T) rs.getString(1);
    } else if (Long.class.isAssignableFrom(clazz)) {
        result = (T) new Long(rs.getLong(1));
    } else if (Integer.class.isAssignableFrom(clazz)) {
        result = (T) new Integer(rs.getInt(1));
    } else if (Float.class.isAssignableFrom(clazz)) {
        result = (T) new Float(rs.getFloat(1));
    } else if (Double.class.isAssignableFrom(clazz)) {
        result = (T) new Double(rs.getDouble(1));
    } else if (BigDecimal.class.isAssignableFrom(clazz)) {
        result = (T) rs.getBigDecimal(1);
    } else {
        result = (T) rs.getObject(1);
    }
    return result;
}