List of usage examples for java.sql PreparedStatement setBigDecimal
void setBigDecimal(int parameterIndex, BigDecimal x) throws SQLException;
java.math.BigDecimal value. From source file:org.hyperic.hq.measurement.server.session.DataManagerImpl.java
/** * This method is called to perform 'updates' for any inserts that failed. * /*from ww w .ja v a 2 s . co m*/ * @return The data insert result containing the data points that were not * updated. */ private List<DataPoint> updateData(Connection conn, List<DataPoint> data) { PreparedStatement stmt = null; List<DataPoint> left = new ArrayList<DataPoint>(); Map<String, List<DataPoint>> buckets = MeasRangeObj.getInstance().bucketData(data); for (Entry<String, List<DataPoint>> entry : buckets.entrySet()) { String table = entry.getKey(); List<DataPoint> dpts = entry.getValue(); try { // TODO need to set synchronous commit to off stmt = conn.prepareStatement( "UPDATE " + table + " SET value = ? WHERE timestamp = ? AND measurement_id = ?"); for (DataPoint pt : dpts) { Integer metricId = pt.getMeasurementId(); MetricValue val = pt.getMetricValue(); BigDecimal bigDec; bigDec = new BigDecimal(val.getValue()); stmt.setBigDecimal(1, getDecimalInRange(bigDec, metricId)); stmt.setLong(2, val.getTimestamp()); stmt.setInt(3, metricId.intValue()); stmt.addBatch(); } int[] execInfo = stmt.executeBatch(); left.addAll(getRemainingDataPoints(dpts, execInfo)); } catch (BatchUpdateException e) { left.addAll(getRemainingDataPointsAfterBatchFail(dpts, e.getUpdateCounts())); } catch (SQLException e) { // If the batch update is not within a transaction, then we // don't know which of the updates completed successfully. // Assume they all failed. left.addAll(dpts); if (log.isDebugEnabled()) { log.debug("A general SQLException occurred during the update. " + "Assuming that none of the " + dpts.size() + " data points were updated.", e); } } finally { DBUtil.closeStatement(LOG_CTX, stmt); } } return left; }
From source file:org.jobjects.dao.annotation.ManagerTools.java
protected final void setAll(PreparedStatement pstmt, int i, Object obj) throws SQLException { if (obj instanceof Boolean) { pstmt.setBoolean(i, ((Boolean) obj).booleanValue()); }/* ww w .j a v a 2 s . c om*/ if (obj instanceof Byte) { pstmt.setByte(i, ((Byte) obj).byteValue()); } if (obj instanceof Short) { pstmt.setShort(i, ((Short) obj).shortValue()); } if (obj instanceof Integer) { pstmt.setInt(i, ((Integer) obj).intValue()); } if (obj instanceof Long) { pstmt.setLong(i, ((Long) obj).longValue()); } if (obj instanceof Float) { pstmt.setFloat(i, ((Float) obj).floatValue()); } if (obj instanceof Double) { pstmt.setDouble(i, ((Double) obj).doubleValue()); } if (obj instanceof Timestamp) { pstmt.setTimestamp(i, ((Timestamp) obj)); } if (obj instanceof Date) { pstmt.setDate(i, ((Date) obj)); } if (obj instanceof BigDecimal) { pstmt.setBigDecimal(i, ((BigDecimal) obj)); } if (obj instanceof String) { pstmt.setString(i, ((String) obj)); } }
From source file:org.kawanfw.test.api.client.InsertAndUpdateBlobTest.java
/** * Insert a blob/*from www. j a v a 2 s .com*/ * * @throws Exception * it any Exception occurs */ public void insertLoopPrepStatement(Connection connection, int numberToInsert, File blobFile) throws Exception { // We can now use our Remote JDBC Connection as a regular Connection! connection.setAutoCommit(false); // We will do all our remote insert in a SQL Transaction try { String sql = "insert into orderlog values ( ?, ?, ?, ?, ?, ?, ?, ?, ? )"; // Create a new Prepared Statement PreparedStatement prepStatement = null; MessageDisplayer.display(""); MessageDisplayer.display("Inserting " + numberToInsert + " orderlog..."); for (int customerId = 1; customerId < numberToInsert + 1; customerId++) { int i = 1; long theTime = new java.util.Date().getTime(); // We will insert a Blob (the image of the product). // The transfer will be done in streaming both on the client // and on the Servlet Server: we can upload/download very big // files. // InputStream in = new BufferedInputStream(new // FileInputStream(blobFile)); InputStream in = new FileInputStream(blobFile); prepStatement = connection.prepareStatement(sql); prepStatement.setInt(i++, customerId); prepStatement.setInt(i++, customerId); prepStatement.setString(i++, "Item Description No " + customerId); prepStatement.setBigDecimal(i++, new BigDecimal(customerId)); prepStatement.setDate(i++, new java.sql.Date(theTime)); prepStatement.setTimestamp(i++, new Timestamp(theTime)); prepStatement.setBinaryStream(i++, in, (int) blobFile.length()); // prepStatement.setBoolean(i++, false); SqlUtil sqlUtil = new SqlUtil(connection); if (sqlUtil.isIngres() || sqlUtil.isPostgreSQL()) { prepStatement.setInt(i++, 0); } else { prepStatement.setBoolean(i++, false); } prepStatement.setInt(i++, customerId); // SystemOutHandle.display("Before executeUpdate..."); prepStatement.executeUpdate(); prepStatement.close(); in.close(); } // We do either everything in a single transaction or nothing connection.commit(); // Commit is propagated on Server MessageDisplayer.display("Remote Commit Done on AceQL Server!"); } catch (Exception e) { connection.rollback(); throw e; } finally { connection.setAutoCommit(true); } }
From source file:org.kawanfw.test.api.client.InsertAndUpdateBlobTestNew.java
/** * Insert a blob/*from w w w. j a va2s. c om*/ * * @throws Exception * it any Exception occurs */ public void insertLoopPrepStatement(Connection connection, int numberToInsert, File blobFile) throws Exception { // We can now use our Remote JDBC Connection as a regular Connection! connection.setAutoCommit(false); // We will do all our remote insert in a SQL Transaction try { String sql = "insert into orderlog values ( ?, ?, ?, ?, ?, ?, ?, ?, ? )"; // Create a new Prepared Statement PreparedStatement prepStatement = null; MessageDisplayer.display(""); MessageDisplayer.display("Inserting " + numberToInsert + " orderlog..."); for (int customerId = 1; customerId < numberToInsert + 1; customerId++) { int i = 1; long theTime = new java.util.Date().getTime(); // We will insert a Blob (the image of the product). // The transfer will be done in streaming both on the client // and on the Servlet Server: we can upload/download very big // files. InputStream in = null; OutputStream out = null; try { in = new FileInputStream(blobFile); Blob blob = connection.createBlob(); out = blob.setBinaryStream(1); IOUtils.copy(in, out); prepStatement = connection.prepareStatement(sql); prepStatement.setInt(i++, customerId); prepStatement.setInt(i++, customerId); prepStatement.setString(i++, "Item Description No " + customerId); prepStatement.setBigDecimal(i++, new BigDecimal(customerId)); prepStatement.setDate(i++, new java.sql.Date(theTime)); prepStatement.setTimestamp(i++, new Timestamp(theTime)); prepStatement.setBlob(i++, blob); SqlUtil sqlUtil = new SqlUtil(connection); if (sqlUtil.isIngres()) { prepStatement.setInt(i++, 0); } else { prepStatement.setBoolean(i++, false); } prepStatement.setInt(i++, customerId); // SystemOutHandle.display("Before executeUpdate..."); prepStatement.executeUpdate(); // Close and free are important to delete temp files prepStatement.close(); blob.free(); } finally { IOUtils.closeQuietly(in); IOUtils.closeQuietly(out); } } // We do either everything in a single transaction or nothing connection.commit(); // Commit is propagated on Server MessageDisplayer.display("Remote Commit Done on AceQL Server!"); } catch (Exception e) { connection.rollback(); throw e; } finally { connection.setAutoCommit(true); } }
From source file:org.kawanfw.test.api.client.InsertAndUpdateBlobTestPsqlOID.java
/** * Insert a blob/*from www. j a v a2 s . co m*/ * * @throws Exception * it any Exception occurs */ public void insertLoopPrepStatement(Connection connection, int numberToInsert, File blobFile) throws Exception { // We can now use our Remote JDBC Connection as a regular Connection! connection.setAutoCommit(false); // We will do all our remote insert in a SQL Transaction try { String sql = "insert into orderlog_2 values ( ?, ?, ?, ?, ?, ?, ?, ?, ? )"; // Create a new Prepared Statement PreparedStatement prepStatement = null; MessageDisplayer.display(""); MessageDisplayer.display("Inserting " + numberToInsert + " orderlog_2..."); for (int customerId = 1; customerId < numberToInsert + 1; customerId++) { int i = 1; long theTime = new java.util.Date().getTime(); // We will insert a Blob (the image of the product). // The transfer will be done in streaming both on the client // and on the Servlet Server: we can upload/download very big // files. // InputStream in = new BufferedInputStream(new // FileInputStream(blobFile)); InputStream in = new FileInputStream(blobFile); prepStatement = connection.prepareStatement(sql); prepStatement.setInt(i++, customerId); prepStatement.setInt(i++, customerId); prepStatement.setString(i++, "Item Description No " + customerId); prepStatement.setBigDecimal(i++, new BigDecimal(customerId)); prepStatement.setDate(i++, new java.sql.Date(theTime)); prepStatement.setTimestamp(i++, new Timestamp(theTime)); prepStatement.setBinaryStream(i++, in, (int) blobFile.length()); // prepStatement.setBoolean(i++, false); SqlUtil sqlUtil = new SqlUtil(connection); if (sqlUtil.isIngres() || sqlUtil.isPostgreSQL()) { prepStatement.setInt(i++, 0); } else { prepStatement.setBoolean(i++, false); } prepStatement.setInt(i++, customerId); // SystemOutHandle.display("Before executeUpdate..."); prepStatement.executeUpdate(); prepStatement.close(); in.close(); } // We do either everything in a single transaction or nothing connection.commit(); // Commit is propagated on Server MessageDisplayer.display("Remote Commit Done on AceQL Server!"); } catch (Exception e) { connection.rollback(); throw e; } finally { connection.setAutoCommit(true); } }
From source file:org.kawanfw.test.api.client.InsertAndUpdatePrepStatementTest.java
/** * Update the values of a row with an increase factor and a new datetime * //from www. j a v a 2 s. c o m * @param connection * @param customerId * @param itemId * @throws Exception */ private void updateValues(Connection connection, int customerId, int itemId) throws Exception { String sql = "update orderlog set " + " date_placed = ? " + " , date_shipped = ? " + " , cost_price = ? " + " , is_delivered = ? " + " , quantity = ? " + " where customer_id = ? and item_id = ?"; PreparedStatement prepStatement = connection.prepareStatement(sql); long newTime = (new java.util.Date()).getTime(); Date datePlaced = new Date(newTime); dateShippedUpdated = new Timestamp(newTime); MessageDisplayer.display("dateShippedUpdated : " + dateShippedUpdated); MessageDisplayer .display("dateShippedUpdated.substring.(0, 19): " + dateShippedUpdated.toString().substring(0, 19)); int i = 1; prepStatement.setDate(i++, datePlaced); prepStatement.setTimestamp(i++, dateShippedUpdated); // We use the increase factor prepStatement.setBigDecimal(i++, new BigDecimal(customerId * increaseFactor)); SqlUtil sqlUtil = new SqlUtil(connection); if (sqlUtil.isIngres() || sqlUtil.isPostgreSQL()) { prepStatement.setInt(i++, 1); } else { prepStatement.setBoolean(i++, true); } prepStatement.setInt(i++, customerId * increaseFactor * 2); // Key value prepStatement.setInt(i++, customerId); prepStatement.setInt(i++, itemId); prepStatement.executeUpdate(); prepStatement.close(); }
From source file:org.kawanfw.test.api.client.InsertAndUpdatePrepStatementTest.java
/** * Do a 100 row insert inside a loop// w ww . ja va2s .c o m * * @param connection * the AceQL Connection * * @throws Exception * it any Exception occurs */ public void insertLoopPrepStatement(Connection connection, int numberToInsert) throws Exception { // We can now use our Remote JDBC Connection as a regular Connection! connection.setAutoCommit(false); // We will do all our remote insert in a SQL Transaction try { String sql = "insert into orderlog values ( ?, ?, ?, ?, ?, ?, ?, ?, ? )"; // Create a new Prepared Statement PreparedStatement prepStatement = null; MessageDisplayer.display(""); MessageDisplayer.display("Inserting " + numberToInsert + " orderlog..."); SqlUtil sqlUtil = new SqlUtil(connection); for (int customerId = 1; customerId < numberToInsert + 1; customerId++) { int i = 1; long theTime = new java.util.Date().getTime(); prepStatement = connection.prepareStatement(sql); prepStatement.setInt(i++, customerId); prepStatement.setInt(i++, customerId); prepStatement.setString(i++, "Item Description No " + customerId); prepStatement.setBigDecimal(i++, new BigDecimal(customerId)); prepStatement.setDate(i++, new java.sql.Date(theTime)); prepStatement.setTimestamp(i++, new Timestamp(theTime)); prepStatement.setBytes(i++, null); // No Blob in this example. if (sqlUtil.isIngres() || sqlUtil.isPostgreSQL()) { prepStatement.setInt(i++, 0); } else { prepStatement.setBoolean(i++, false); } prepStatement.setInt(i++, customerId); prepStatement.executeUpdate(); prepStatement.close(); } // We do either everything in a single transaction or nothing connection.commit(); // Commit is propagated on Server MessageDisplayer.display("Remote Commit Done on AceQL Server!"); } catch (Exception e) { connection.rollback(); throw e; } finally { connection.setAutoCommit(true); } }
From source file:org.kuali.kfs.gl.batch.dataaccess.impl.LedgerPreparedStatementCachingDaoJdbc.java
public void insertAccountBalance(final AccountBalance accountBalance, final Timestamp currentTimestamp) { new InsertingJdbcWrapper<AccountBalance>() { @Override/* ww w . ja va 2s .c o m*/ protected void populateStatement(PreparedStatement preparedStatement) throws SQLException { preparedStatement.setInt(1, accountBalance.getUniversityFiscalYear()); preparedStatement.setString(2, accountBalance.getChartOfAccountsCode()); preparedStatement.setString(3, accountBalance.getAccountNumber()); preparedStatement.setString(4, accountBalance.getSubAccountNumber()); preparedStatement.setString(5, accountBalance.getObjectCode()); preparedStatement.setString(6, accountBalance.getSubObjectCode()); preparedStatement.setBigDecimal(7, accountBalance.getCurrentBudgetLineBalanceAmount().bigDecimalValue()); preparedStatement.setBigDecimal(8, accountBalance.getAccountLineActualsBalanceAmount().bigDecimalValue()); preparedStatement.setBigDecimal(9, accountBalance.getAccountLineEncumbranceBalanceAmount().bigDecimalValue()); preparedStatement.setTimestamp(10, currentTimestamp); } }.execute(AccountBalance.class); }
From source file:org.kuali.kfs.gl.batch.dataaccess.impl.LedgerPreparedStatementCachingDaoJdbc.java
public void updateAccountBalance(final AccountBalance accountBalance, final Timestamp currentTimestamp) { new UpdatingJdbcWrapper<AccountBalance>() { @Override// w w w . j av a 2s . co m protected void populateStatement(PreparedStatement preparedStatement) throws SQLException { preparedStatement.setBigDecimal(1, accountBalance.getCurrentBudgetLineBalanceAmount().bigDecimalValue()); preparedStatement.setBigDecimal(2, accountBalance.getAccountLineActualsBalanceAmount().bigDecimalValue()); preparedStatement.setBigDecimal(3, accountBalance.getAccountLineEncumbranceBalanceAmount().bigDecimalValue()); preparedStatement.setTimestamp(4, currentTimestamp); preparedStatement.setInt(5, accountBalance.getUniversityFiscalYear()); preparedStatement.setString(6, accountBalance.getChartOfAccountsCode()); preparedStatement.setString(7, accountBalance.getAccountNumber()); preparedStatement.setString(8, accountBalance.getSubAccountNumber()); preparedStatement.setString(9, accountBalance.getObjectCode()); preparedStatement.setString(10, accountBalance.getSubObjectCode()); } }.execute(AccountBalance.class); }
From source file:org.kuali.kfs.gl.batch.dataaccess.impl.LedgerPreparedStatementCachingDaoJdbc.java
public void insertBalance(final Balance balance, final Timestamp currentTimestamp) { new InsertingJdbcWrapper<Balance>() { @Override//from w w w . ja v a 2 s . co m protected void populateStatement(PreparedStatement preparedStatement) throws SQLException { preparedStatement.setInt(1, balance.getUniversityFiscalYear()); preparedStatement.setString(2, balance.getChartOfAccountsCode()); preparedStatement.setString(3, balance.getAccountNumber()); preparedStatement.setString(4, balance.getSubAccountNumber()); preparedStatement.setString(5, balance.getObjectCode()); preparedStatement.setString(6, balance.getSubObjectCode()); preparedStatement.setString(7, balance.getBalanceTypeCode()); preparedStatement.setString(8, balance.getObjectTypeCode()); preparedStatement.setBigDecimal(9, balance.getAccountLineAnnualBalanceAmount().bigDecimalValue()); preparedStatement.setBigDecimal(10, balance.getBeginningBalanceLineAmount().bigDecimalValue()); preparedStatement.setBigDecimal(11, balance.getContractsGrantsBeginningBalanceAmount().bigDecimalValue()); preparedStatement.setBigDecimal(12, balance.getMonth1Amount().bigDecimalValue()); preparedStatement.setBigDecimal(13, balance.getMonth2Amount().bigDecimalValue()); preparedStatement.setBigDecimal(14, balance.getMonth3Amount().bigDecimalValue()); preparedStatement.setBigDecimal(15, balance.getMonth4Amount().bigDecimalValue()); preparedStatement.setBigDecimal(16, balance.getMonth5Amount().bigDecimalValue()); preparedStatement.setBigDecimal(17, balance.getMonth6Amount().bigDecimalValue()); preparedStatement.setBigDecimal(18, balance.getMonth7Amount().bigDecimalValue()); preparedStatement.setBigDecimal(19, balance.getMonth8Amount().bigDecimalValue()); preparedStatement.setBigDecimal(20, balance.getMonth9Amount().bigDecimalValue()); preparedStatement.setBigDecimal(21, balance.getMonth10Amount().bigDecimalValue()); preparedStatement.setBigDecimal(22, balance.getMonth11Amount().bigDecimalValue()); preparedStatement.setBigDecimal(23, balance.getMonth12Amount().bigDecimalValue()); preparedStatement.setBigDecimal(24, balance.getMonth13Amount().bigDecimalValue()); preparedStatement.setTimestamp(25, currentTimestamp); } }.execute(Balance.class); }