List of usage examples for java.sql ResultSet getBigDecimal
BigDecimal getBigDecimal(String columnLabel) throws SQLException;
ResultSet object as a java.math.BigDecimal with full precision. From source file:nl.tudelft.stocktrader.mysql.MySQLCustomerDAO.java
public List<Order> getCompletedOrders(String userId) throws DAOException { PreparedStatement selectClosedOrders = null; PreparedStatement updateClosedOrders = null; try {//from ww w.j a v a2s. co m selectClosedOrders = sqlConnection.prepareStatement(SQL_SELECT_CLOSED_ORDERS); selectClosedOrders.setString(1, userId); ResultSet rs = selectClosedOrders.executeQuery(); List<Order> closedOrders = new ArrayList<Order>(); try { while (rs.next()) { int orderId = rs.getInt(1); Calendar openDate = StockTraderUtility.convertToCalendar(rs.getDate(4)); Calendar completionDate = null; try { completionDate = StockTraderUtility.convertToCalendar(rs.getDate(5)); } catch (SQLException e) { logger.debug("", e); completionDate = Calendar.getInstance(); completionDate.setTimeInMillis(0); } Order closedOrderBean = new Order(orderId, rs.getString(2), rs.getString(3), openDate, completionDate, rs.getDouble(6), rs.getBigDecimal(7), rs.getBigDecimal(8), rs.getString(9)); closedOrderBean.setOrderStatus(StockTraderUtility.ORDER_STATUS_CLOSED); closedOrders.add(closedOrderBean); } } finally { try { rs.close(); } catch (SQLException e) { logger.debug("", e); } } if (!closedOrders.isEmpty()) { updateClosedOrders = sqlConnection.prepareStatement(SQL_UPDATE_CLOSED_ORDERS); updateClosedOrders.setString(1, userId); updateClosedOrders.executeUpdate(); } return closedOrders; } catch (SQLException e) { throw new DAOException("", e); } finally { if (selectClosedOrders != null) { try { selectClosedOrders.close(); } catch (SQLException e) { logger.debug("", e); } } if (updateClosedOrders != null) { try { selectClosedOrders.close(); } catch (SQLException e) { logger.debug("", e); } } } }
From source file:org.sakaiproject.webservices.SakaiReport.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;/*w w w . j a v a 2 s . 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; case Types.VARBINARY: case Types.BINARY: value = handleRaw(rs.getBytes(colIndex)); break; default: value = ""; } if (value == null) { value = ""; } return value; }
From source file:com.cnd.greencube.server.dao.jdbc.JdbcDAO.java
@SuppressWarnings("rawtypes") private Object getColumnValue(ResultSet rs, ResultSetMetaData meta, int index, Class clazz) throws Exception { Object value = null;//from ww w. j a v a 2s .c o m int type = meta.getColumnType(index); if (clazz == String.class) { value = rs.getString(index); } else if (clazz == Integer.class) { value = rs.getInt(index); } else if (clazz == Boolean.class) { value = rs.getBoolean(index); } else if (clazz == byte[].class) { if (type == Types.BLOB) value = rs.getBlob(index); else value = rs.getBytes(index); } else if (clazz == Long.class) { value = rs.getLong(index); } else if (clazz == BigInteger.class) { value = rs.getBigDecimal(index); } else if (clazz == Float.class) { value = rs.getFloat(index); } else if (clazz == Double.class) { value = rs.getDouble(index); } else if (clazz == java.util.Date.class) { Timestamp time = rs.getTimestamp(index); if (time == null) value = null; else { value = new java.util.Date(time.getTime()); } } else if (clazz == java.sql.Date.class) { value = rs.getDate(index); } else if (clazz == java.sql.Time.class) { value = rs.getTime(index); } else if (clazz == java.sql.Timestamp.class) { value = rs.getTimestamp(index); } else { throw new Exception("Cannote determin this column type:" + meta.getColumnName(index)); } return value; }
From source file:com.erbjuder.logger.server.rest.util.ResultSetConverter.java
private JSONArray toJSONArray(ResultSet rs, JSONArray json) throws Exception { String temp = null;//from w w w . jav a 2 s. co m try { // we will need the column names, this will save the table meta-data like column nmae. java.sql.ResultSetMetaData rsmd = rs.getMetaData(); //loop through the ResultSet while (rs.next()) { //figure out how many columns there are int numColumns = rsmd.getColumnCount(); //each row in the ResultSet will be converted to a JSON Object JSONObject obj = new JSONObject(); // loop through all the columns and place them into the JSON Object for (int i = 1; i < numColumns + 1; i++) { String column_name = rsmd.getColumnName(i); if (rsmd.getColumnType(i) == java.sql.Types.ARRAY) { obj.put(column_name, rs.getArray(column_name).toString()); } else if (rsmd.getColumnType(i) == java.sql.Types.BIGINT) { obj.put(column_name, rs.getBigDecimal(column_name).toBigInteger().toString()); } else if (rsmd.getColumnType(i) == java.sql.Types.BOOLEAN) { obj.put(column_name, ((Boolean) rs.getBoolean(column_name)).toString()); } else if (rsmd.getColumnType(i) == java.sql.Types.BLOB) { obj.put(column_name, rs.getBlob(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.DOUBLE) { obj.put(column_name, ((Double) rs.getDouble(column_name)).toString()); } else if (rsmd.getColumnType(i) == java.sql.Types.FLOAT) { obj.put(column_name, ((Float) rs.getFloat(column_name)).toString()); } else if (rsmd.getColumnType(i) == java.sql.Types.INTEGER) { obj.put(column_name, ((Integer) rs.getInt(column_name)).toString()); } else if (rsmd.getColumnType(i) == java.sql.Types.NVARCHAR) { obj.put(column_name, rs.getNString(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.VARCHAR) { // temp = rs.getString(column_name); //saving column data to temp variable // temp = ESAPI.encoder().canonicalize(temp); //decoding data to base state // temp = ESAPI.encoder().encodeForHTML(temp); //encoding to be browser safe // obj.put(column_name, temp); //putting data into JSON object // obj.put(column_name, rs.getNString(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.TINYINT) { obj.put(column_name, ((Integer) rs.getInt(column_name)).toString()); } else if (rsmd.getColumnType(i) == java.sql.Types.SMALLINT) { obj.put(column_name, ((Integer) rs.getInt(column_name)).toString()); } else if (rsmd.getColumnType(i) == java.sql.Types.DATE) { obj.put(column_name, rs.getDate(column_name).toString()); } else if (rsmd.getColumnType(i) == java.sql.Types.TIME) { obj.put(column_name, TimeStampUtils.dateTimeToString(rs.getTime(column_name))); } else if (rsmd.getColumnType(i) == java.sql.Types.TIMESTAMP) { obj.put(column_name, TimeStampUtils.timeStampToString(rs.getTimestamp(column_name))); } else if (rsmd.getColumnType(i) == java.sql.Types.NUMERIC) { obj.put(column_name, rs.getBigDecimal(column_name).toString()); } else { obj.put(column_name, rs.getObject(column_name)); } } //end foreach json.add(obj); } //end while } catch (Exception e) { e.printStackTrace(); } return json; //return JSON array }
From source file:org.carlspring.tools.csv.dao.CSVDao.java
private String getField(ResultSet resultSet, int i, Field field) throws SQLException { // Handle primitives if (field.getType().equals("int")) { return String.valueOf(resultSet.getInt(i)); } else if (field.getType().equals("long")) { return String.valueOf(resultSet.getLong(i)); } else if (field.getType().equals("float")) { return String.valueOf(resultSet.getFloat(i)); } else if (field.getType().equals("double")) { return String.valueOf(resultSet.getDouble(i)); } else if (field.getType().equals("boolean")) { return String.valueOf(resultSet.getBoolean(i)); }//from w w w . j a v a 2 s . co m // Handle objects else if (field.getType().equals("java.lang.String")) { return String.valueOf(resultSet.getString(i)); } else if (field.getType().equals("java.sql.Date")) { return String.valueOf(resultSet.getDate(i)); } else if (field.getType().equals("java.sql.Timestamp")) { return String.valueOf(resultSet.getTimestamp(i)); } else if (field.getType().equals("java.math.BigDecimal")) { return String.valueOf(resultSet.getBigDecimal(i)); } return null; }
From source file:cn.clickvalue.cv2.model.rowmapper.BeanPropertyRowMapper.java
/** * Retrieve a JDBC column value from a ResultSet, using the specified value type. * <p>Uses the specifically typed ResultSet accessor methods, falling back to * {@link #getResultSetValue(java.sql.ResultSet, int)} for unknown types. * <p>Note that the returned value may not be assignable to the specified * required type, in case of an unknown type. Calling code needs to deal * with this case appropriately, e.g. throwing a corresponding exception. * @param rs is the ResultSet holding the data * @param index is the column index/*w w w . j a v a 2 s . com*/ * @param requiredType the required value type (may be <code>null</code>) * @return the value object * @throws SQLException if thrown by the JDBC API */ public static Object getResultSetValue(ResultSet rs, int index, Class requiredType) throws SQLException { if (requiredType == null) { return getResultSetValue(rs, index); } Object value = null; boolean wasNullCheck = false; // Explicitly extract typed value, as far as possible. if (String.class.equals(requiredType)) { value = rs.getString(index); } else if (boolean.class.equals(requiredType) || Boolean.class.equals(requiredType)) { value = Boolean.valueOf(rs.getBoolean(index)); wasNullCheck = true; } else if (byte.class.equals(requiredType) || Byte.class.equals(requiredType)) { value = Byte.valueOf(rs.getByte(index)); wasNullCheck = true; } else if (short.class.equals(requiredType) || Short.class.equals(requiredType)) { value = Short.valueOf(rs.getShort(index)); wasNullCheck = true; } else if (int.class.equals(requiredType) || Integer.class.equals(requiredType)) { value = Integer.valueOf(rs.getInt(index)); wasNullCheck = true; } else if (long.class.equals(requiredType) || Long.class.equals(requiredType)) { value = Long.valueOf(rs.getLong(index)); wasNullCheck = true; } else if (float.class.equals(requiredType) || Float.class.equals(requiredType)) { value = Float.valueOf(rs.getFloat(index)); wasNullCheck = true; } else if (double.class.equals(requiredType) || Double.class.equals(requiredType) || Number.class.equals(requiredType)) { value = Double.valueOf(rs.getDouble(index)); wasNullCheck = true; } else if (byte[].class.equals(requiredType)) { value = rs.getBytes(index); } else if (java.sql.Date.class.equals(requiredType)) { value = rs.getDate(index); } else if (java.sql.Time.class.equals(requiredType)) { value = rs.getTime(index); } else if (java.sql.Timestamp.class.equals(requiredType) || java.util.Date.class.equals(requiredType)) { value = rs.getTimestamp(index); } else if (BigDecimal.class.equals(requiredType)) { value = rs.getBigDecimal(index); } else if (Blob.class.equals(requiredType)) { value = rs.getBlob(index); } else if (Clob.class.equals(requiredType)) { value = rs.getClob(index); } else { // Some unknown type desired -> rely on getObject. value = getResultSetValue(rs, index); } // Perform was-null check if demanded (for results that the // JDBC driver returns as primitives). if (wasNullCheck && value != null && rs.wasNull()) { value = null; } return value; }
From source file:nl.tudelft.stocktrader.derby.DerbyCustomerDAO.java
public List<Order> getCompletedOrders(String userId) throws DAOException { PreparedStatement selectClosedOrders = null; PreparedStatement updateClosedOrders = null; try {// w ww . java 2s.c om selectClosedOrders = sqlConnection.prepareStatement(SQL_SELECT_COMPLETED_ORDERS); selectClosedOrders.setString(1, userId); ResultSet rs = selectClosedOrders.executeQuery(); List<Order> closedOrders = new ArrayList<Order>(); try { while (rs.next()) { int orderId = rs.getInt(1); Calendar openDate = StockTraderUtility.convertToCalendar(rs.getDate(4)); Calendar completionDate = null; try { completionDate = StockTraderUtility.convertToCalendar(rs.getDate(5)); } catch (SQLException e) { logger.debug("", e); completionDate = Calendar.getInstance(); completionDate.setTimeInMillis(0); } Order closedOrderBean = new Order(orderId, rs.getString(2), rs.getString(3), openDate, completionDate, rs.getDouble(6), rs.getBigDecimal(7), rs.getBigDecimal(8), rs.getString(9)); closedOrderBean.setOrderStatus(StockTraderUtility.ORDER_STATUS_CLOSED); closedOrders.add(closedOrderBean); } } finally { try { rs.close(); } catch (SQLException e) { logger.debug("", e); } } if (!closedOrders.isEmpty()) { updateClosedOrders = sqlConnection.prepareStatement(SQL_UPDATE_CLOSED_ORDERS); updateClosedOrders.setString(1, userId); updateClosedOrders.executeUpdate(); } return closedOrders; } catch (SQLException e) { throw new DAOException("", e); } finally { if (selectClosedOrders != null) { try { selectClosedOrders.close(); } catch (SQLException e) { logger.debug("", e); } } if (updateClosedOrders != null) { try { selectClosedOrders.close(); } catch (SQLException e) { logger.debug("", e); } } } }
From source file:org.kawanfw.test.api.client.InsertAndUpdateBlobTest.java
/** * Test that the blob was were correctly inserted * /*from www .j a va2s .c o m*/ * @param connection */ public void selectBlobTest(Connection connection, String originalFileName, String shaHexa) throws Exception { int customer_id; int item_id; String description; BigDecimal cost_price; Date date_placed; Timestamp date_shipped; boolean is_delivered; int quantity; String sql = "select * from orderlog where customer_id >= ? and item_id >= ? "; PreparedStatement prepStatement = connection.prepareStatement(sql); int i = 1; prepStatement.setInt(i++, 1); prepStatement.setInt(i++, 1); ResultSet rs = prepStatement.executeQuery(); MessageDisplayer.display(""); InputStream in = null; OutputStream out = null; SqlUtil sqlUtil = new SqlUtil(connection); while (rs.next()) { File originalBlobFile = SqlTestParms.getFileFromUserHome(originalFileName); // Do the rs.getBinaryStream("jpeg_image") first (INGRES constraint) File file = createTempFile(originalBlobFile.toString()); try { in = rs.getBinaryStream("jpeg_image"); if (in != null) { out = new BufferedOutputStream(new FileOutputStream(file)); IOUtils.copy(in, out); } else { MessageDisplayer.display("jpeg_image column is null!"); } } finally { IOUtils.closeQuietly(in); IOUtils.closeQuietly(out); } i = 1; customer_id = rs.getInt(i++); item_id = rs.getInt(i++); description = rs.getString(i++); cost_price = rs.getBigDecimal(i++); date_placed = rs.getDate(i++); date_shipped = rs.getTimestamp(i++); // pass the image i++; if (sqlUtil.isIngres()) { is_delivered = (rs.getInt(i++) == 1) ? true : false; } else { is_delivered = rs.getBoolean(i++); } quantity = rs.getInt(i++); MessageDisplayer.display(""); MessageDisplayer.display("customer_id : " + customer_id); MessageDisplayer.display("item_id : " + item_id); MessageDisplayer.display("description : " + description); MessageDisplayer.display("cost_price : " + cost_price); MessageDisplayer.display("date_placed : " + date_placed); MessageDisplayer.display("date_shipped: " + date_shipped); MessageDisplayer.display("jpeg_image : " + "content stored in file: " + file); MessageDisplayer.display("is_delivered: " + is_delivered); MessageDisplayer.display("quantity : " + quantity); // Compute the hash of the file Sha1Util sha1 = new Sha1Util(); String shaHexaNew = sha1.getHexFileHash(file); Assert.assertEquals(shaHexa, shaHexaNew); file.delete(); MessageDisplayer.display(""); MessageDisplayer.display("Ok, SHA-1 value of read file " + file + " is same as inserted file " + SqlTestParms.getFileFromUserHome(originalFileName)); } prepStatement.close(); rs.close(); MessageDisplayer.display("Select done!"); }
From source file:org.kawanfw.test.api.client.InsertAndUpdateBlobTestPsqlOID.java
/** * Test that the blob was were correctly inserted * /*from ww w. jav a2 s .co m*/ * @param connection */ public void selectBlobTest(Connection connection, String originalFileName, String shaHexa) throws Exception { int customer_id; int item_id; String description; BigDecimal cost_price; Date date_placed; Timestamp date_shipped; boolean is_delivered; int quantity; String sql = "select * from orderlog_2 where customer_id >= ? and item_id >= ? "; PreparedStatement prepStatement = connection.prepareStatement(sql); int i = 1; prepStatement.setInt(i++, 1); prepStatement.setInt(i++, 1); ResultSet rs = prepStatement.executeQuery(); MessageDisplayer.display(""); InputStream in = null; OutputStream out = null; SqlUtil sqlUtil = new SqlUtil(connection); while (rs.next()) { File originalBlobFile = SqlTestParms.getFileFromUserHome(originalFileName); // Do the rs.getBinaryStream("jpeg_image") first (INGRES constraint) File file = createTempFile(originalBlobFile.toString()); try { in = rs.getBinaryStream("jpeg_image"); if (in != null) { out = new BufferedOutputStream(new FileOutputStream(file)); IOUtils.copy(in, out); } else { MessageDisplayer.display("jpeg_image column is null!"); } } finally { IOUtils.closeQuietly(in); IOUtils.closeQuietly(out); } i = 1; customer_id = rs.getInt(i++); item_id = rs.getInt(i++); description = rs.getString(i++); cost_price = rs.getBigDecimal(i++); date_placed = rs.getDate(i++); date_shipped = rs.getTimestamp(i++); // pass the image i++; if (sqlUtil.isIngres()) { is_delivered = (rs.getInt(i++) == 1) ? true : false; } else { is_delivered = rs.getBoolean(i++); } quantity = rs.getInt(i++); MessageDisplayer.display(""); MessageDisplayer.display("customer_id : " + customer_id); MessageDisplayer.display("item_id : " + item_id); MessageDisplayer.display("description : " + description); MessageDisplayer.display("cost_price : " + cost_price); MessageDisplayer.display("date_placed : " + date_placed); MessageDisplayer.display("date_shipped: " + date_shipped); MessageDisplayer.display("jpeg_image : " + "content stored in file: " + file); MessageDisplayer.display("is_delivered: " + is_delivered); MessageDisplayer.display("quantity : " + quantity); // Compute the hash of the file Sha1Util sha1 = new Sha1Util(); String shaHexaNew = sha1.getHexFileHash(file); Assert.assertEquals(shaHexa, shaHexaNew); file.delete(); MessageDisplayer.display(""); MessageDisplayer.display("Ok, SHA-1 value of read file " + file + " is same as inserted file " + SqlTestParms.getFileFromUserHome(originalFileName)); } prepStatement.close(); rs.close(); MessageDisplayer.display("Select done!"); }
From source file:com.intuit.it.billing.data.BillingDAOImpl.java
/** * @section dao_section class BillingDAOImpl * getAllocation//from w w w. j a v 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_item_allocations( * item_no IN VARCHAR2 ) * 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. * @throws JSONException * * * */ @Override public List<Allocation> getAllocation(String itemNo) throws JSONException { List<Allocation> allocs = new ArrayList<Allocation>(); String query = "begin ? := billing_inquiry.fn_get_item_allocations( ? ); end;"; //TODO: configure ResultSet rs = null; Connection conn = 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, itemNo); 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; }