List of usage examples for java.sql PreparedStatement setDate
void setDate(int parameterIndex, java.sql.Date x) throws SQLException;
java.sql.Date value using the default time zone of the virtual machine that is running the application. From source file:com.sinet.gage.dao.DomainsRepository.java
/** * /* w ww . ja v a2 s. c o m*/ * @param domains */ public void insertDomains(List<Domain> domains) { try { jdbcTemplate.batchUpdate(DOMAINS_INSERT_SQL, new BatchPreparedStatementSetter() { public int getBatchSize() { if (domains == null) return 0; return domains.size(); } @Override public void setValues(PreparedStatement ps, int i) throws SQLException { Domain domain = domains.get(i); ps.setLong(1, domain.getDomainId()); ps.setObject(2, domain.getGuid()); ps.setString(3, domain.getDomainName()); ps.setString(4, domain.getLoginPrefix()); ps.setLong(5, domain.getFlag()); ps.setString(6, domain.getDomainType()); ps.setLong(7, domain.getParentDomainId()); ps.setString(8, domain.getParentDomainName()); ps.setLong(9, domain.getStateDomainId()); ps.setString(10, domain.getStateDomainName()); ps.setString(11, domain.getLicenseType()); ps.setString(12, domain.getLicensePoolType()); ps.setInt(13, domain.getNoOfLicense()); ps.setBoolean(14, domain.isPilot()); ps.setDate(15, domain.getPilotStartDate()); ps.setDate(16, domain.getPilotEndDate()); ps.setBoolean(17, domain.isFullSubscription()); ps.setObject(18, domain.getSubscriptionStartDate()); ps.setObject(19, domain.getSubscriptionEndDate()); ps.setLong(20, domain.getCreatorUserId()); ps.setTimestamp(21, domain.getCreationDate()); ps.setLong(22, domain.getModifierUserId()); ps.setTimestamp(23, domain.getModifiedDate()); } }); } catch (Exception e) { log.error("Error in inserting Domains", e); } }
From source file:org.jtotus.database.LocalJDBC.java
public double[] fetchPeriod(String tableName, DateTime startDate, DateTime endDate, String type) { BigDecimal retValue = null;// www . java 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.wso2.carbon.registry.core.jdbc.dao.JDBCRatingsDAO.java
public void updateRating(ResourceImpl resourceImpl, int rateID, int rating) throws RegistryException { JDBCDatabaseTransaction.ManagedRegistryConnection conn = JDBCDatabaseTransaction.getConnection(); PreparedStatement ps = null; try {/* w w w . j ava 2 s . c o m*/ String sql = "UPDATE REG_RATING SET REG_RATING=?, REG_RATED_TIME=? WHERE REG_ID=? " + "AND REG_TENANT_ID=?"; long now = System.currentTimeMillis(); ps = conn.prepareStatement(sql); ps.setInt(1, rating); ps.setDate(2, new Date(now)); ps.setInt(3, rateID); ps.setInt(4, CurrentSession.getTenantId()); ps.executeUpdate(); } catch (SQLException e) { String msg = "Failed to update the rating id " + rateID + " of resource " + resourceImpl.getPath() + " to value " + rating + ". " + e.getMessage(); log.error(msg, e); throw new RegistryException(msg, e); } finally { try { if (ps != null) { ps.close(); } } catch (SQLException ex) { String msg = RegistryConstants.RESULT_SET_PREPARED_STATEMENT_CLOSE_ERROR; log.error(msg, ex); } } }
From source file:cz.lbenda.dataman.db.RowDesc.java
@SuppressWarnings("ConstantConditions") private <T> void putToPS(ColumnDesc columnDesc, T value, PreparedStatement ps, int position) throws SQLException { if (value == null) { ps.setObject(position, null);//from w w w. jav a2 s . c o m return; } BinaryData bd = value instanceof BinaryData ? (BinaryData) value : null; switch (columnDesc.getDataType()) { case STRING: ps.setString(position, (String) value); break; case BOOLEAN: ps.setBoolean(position, (Boolean) value); break; case TIMESTAMP: ps.setTimestamp(position, (Timestamp) value); break; case DATE: ps.setDate(position, (Date) value); break; case TIME: ps.setTime(position, (Time) value); break; case BYTE: ps.setByte(position, (Byte) value); break; case SHORT: ps.setShort(position, (Short) value); break; case INTEGER: ps.setInt(position, (Integer) value); break; case LONG: ps.setLong(position, (Long) value); break; case FLOAT: ps.setFloat(position, (Float) value); break; case DOUBLE: ps.setDouble(position, (Double) value); break; case DECIMAL: ps.setBigDecimal(position, (BigDecimal) value); break; case UUID: ps.setBytes(position, AbstractHelper.uuidToByteArray((UUID) value)); break; case ARRAY: throw new UnsupportedOperationException("The saving changes in ARRAY isn't supported."); // ps.setArray(position, (Array) value); break; // FIXME the value isn't in type java.sql.Array case BYTE_ARRAY: if (bd == null || bd.isNull()) { ps.setBytes(position, null); } else { try { ps.setBytes(position, IOUtils.toByteArray(bd.getInputStream())); } catch (IOException e) { throw new SQLException(e); } } break; case CLOB: if (bd == null || bd.isNull()) { ps.setNull(position, Types.CLOB); } else { ps.setClob(position, bd.getReader()); } break; case BLOB: if (bd == null || bd.isNull()) { ps.setNull(position, Types.BLOB); } else { ps.setBlob(position, bd.getInputStream()); } break; case OBJECT: ps.setObject(position, value); } }
From source file:org.ojbc.adapters.analyticaldatastore.dao.AnalyticalDatastoreDAOImpl.java
@Override public Integer saveArrest(final Arrest arrest) { log.debug("Inserting row into Arrest table"); final String arrestInsertStatement = "INSERT into ARREST ( PersonID,IncidentID,ArrestDate,ArrestTime,ArrestingAgencyName, ReportingSystem) values (?,?,?,?,?,?)"; KeyHolder keyHolder = new GeneratedKeyHolder(); jdbcTemplate.update(new PreparedStatementCreator() { public PreparedStatement createPreparedStatement(Connection connection) throws SQLException { PreparedStatement ps = connection.prepareStatement(arrestInsertStatement, new String[] { "PersonID", "IncidentID", "ArrestDate", "ArrestTime", "ArrestingAgencyName", "ReportingSystem" }); ps.setInt(1, arrest.getPersonID()); ps.setInt(2, arrest.getIncidentID()); ps.setDate(3, new java.sql.Date(arrest.getArrestDate().getTime())); ps.setTime(4, new java.sql.Time(arrest.getArrestDate().getTime())); ps.setString(5, arrest.getArrestingAgencyName()); ps.setString(6, arrest.getReportingSystem()); return ps; }//from ww w .jav a2s. com }, keyHolder); return keyHolder.getKey().intValue(); }
From source file:org.apache.phoenix.query.BaseTest.java
public static void upsertRow(Connection conn, String fullTableName, int index, boolean firstRowInBatch) throws SQLException { String upsert = "UPSERT INTO " + fullTableName + " VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; PreparedStatement stmt = conn.prepareStatement(upsert); stmt.setString(1, firstRowInBatch ? "firstRowInBatch_" : "" + "varchar" + index); stmt.setString(2, "char" + index); stmt.setInt(3, index);//from www . j a v a 2 s . co m stmt.setLong(4, index); stmt.setBigDecimal(5, new BigDecimal(index)); Date date = DateUtil.parseDate("2015-01-01 00:00:00"); stmt.setDate(6, date); stmt.setString(7, "varchar_a"); stmt.setString(8, "chara"); stmt.setInt(9, index + 1); stmt.setLong(10, index + 1); stmt.setBigDecimal(11, new BigDecimal(index + 1)); stmt.setDate(12, date); stmt.setString(13, "varchar_b"); stmt.setString(14, "charb"); stmt.setInt(15, index + 2); stmt.setLong(16, index + 2); stmt.setBigDecimal(17, new BigDecimal(index + 2)); stmt.setDate(18, date); stmt.executeUpdate(); }
From source file:org.openmrs.util.databasechange.ConceptReferenceTermChangeSet.java
/** * Convenience method that inserts rows into the concept reference term table. The * concept_map_id values becomes the concept_reference_term_id values * //from w w w.ja va2 s .co m * @param connection the current database connection * @param listOfPropertyValueMaps a list of property and value maps for the objects to insert * @throws CustomChangeException */ private void insertRows(JdbcConnection connection, List<Map<String, Object>> listOfPropertyValueMaps) throws CustomChangeException { if (CollectionUtils.isNotEmpty(listOfPropertyValueMaps)) { PreparedStatement pStmt = null; try { connection.setAutoCommit(false); pStmt = connection.prepareStatement("INSERT INTO concept_reference_term" + "(concept_reference_term_id, concept_source_id, code, description, creator, date_created, retired, uuid) " + "VALUES(?, ?, ?, ?, ?, ?, ?, ?)"); for (Map<String, Object> propertyValueMap : listOfPropertyValueMaps) { pStmt.setInt(1, (Integer) propertyValueMap.get("termId")); pStmt.setInt(2, (Integer) propertyValueMap.get("sourceId")); pStmt.setString(3, propertyValueMap.get("code").toString()); pStmt.setString(4, (propertyValueMap.get("description") == null) ? null : propertyValueMap.get("description").toString()); pStmt.setInt(5, (Integer) propertyValueMap.get("creator")); pStmt.setDate(6, (Date) propertyValueMap.get("dateCreated")); pStmt.setBoolean(7, false); pStmt.setString(8, propertyValueMap.get("uuid").toString()); pStmt.addBatch(); } try { int[] updateCounts = pStmt.executeBatch(); for (int i = 0; i < updateCounts.length; i++) { if (updateCounts[i] > -1) { log.debug("Successfully executed: updateCount=" + updateCounts[i]); } else if (updateCounts[i] == Statement.SUCCESS_NO_INFO) { log.debug("Successfully executed; No Success info"); } else if (updateCounts[i] == Statement.EXECUTE_FAILED) { log.warn("Failed to execute update"); } } log.debug("Committing updates..."); connection.commit(); } catch (BatchUpdateException be) { log.warn("Error generated while processsing batch update", be); int[] updateCounts = be.getUpdateCounts(); for (int i = 0; i < updateCounts.length; i++) { if (updateCounts[i] > -1) { log.warn("Executed with exception: updateCount=" + updateCounts[i]); } else if (updateCounts[i] == Statement.SUCCESS_NO_INFO) { log.warn("Executed with exception; No Success info"); } else if (updateCounts[i] == Statement.EXECUTE_FAILED) { log.warn("Failed to execute update with exception"); } } try { log.warn("Rolling back batch", be); connection.rollback(); } catch (Exception rbe) { log.warn("Error generated while rolling back batch update", be); } //marks the changeset as a failed one throw new CustomChangeException( "Failed to generate concept reference terms from existing concept mappings."); } } catch (DatabaseException e) { throw new CustomChangeException("Error generated", e); } catch (SQLException e) { throw new CustomChangeException("Error generated", e); } finally { //reset to auto commit mode try { connection.setAutoCommit(true); } catch (DatabaseException e) { log.warn("Failed to reset auto commit back to true", e); } if (pStmt != null) { try { pStmt.close(); } catch (SQLException e) { log.warn("Failed to close the prepared statement object"); } } } } else log.error("List of property value maps is null or empty"); }
From source file:org.sakaiproject.dash.jobs.DashAggregateJob.java
public long collectPastSiteEvents(String siteId, Date initialDate, Date finalDate) { List<Event> eventsQueue = new ArrayList<Event>(); Connection connection = getEventDbConnection(); PreparedStatement st = null; ResultSet rs = null;/*from w w w . ja v a 2s .c o m*/ boolean processOk = true; long counter = 0; long opStart = System.currentTimeMillis(); try { st = connection.prepareStatement(sqlPastSiteEvents); st.setString(1, siteId); // CONTEXT = ? st.setString(2, "/presence/" + siteId + "-presence"); // REF = ? st.setDate(3, new java.sql.Date(initialDate.getTime())); // EVENT_DATE >= ? st.setDate(4, new java.sql.Date(finalDate.getTime())); // EVENT_DATE <= ? rs = st.executeQuery(); while (rs.next()) { Date date = null; String event = null; String ref = null; String context = null; String sessionUser = null; String sessionId = null; try { //If an exception is launched, iteration is not aborted but no event is added to event queue date = new Date(rs.getTimestamp("EVENT_DATE").getTime()); event = rs.getString("EVENT"); ref = rs.getString("REF"); sessionUser = rs.getString("SESSION_USER"); sessionId = rs.getString("SESSION_ID"); context = rs.getString("CONTEXT"); EventCopy eventcopy = new EventCopy(date, event, ref, context, sessionUser, sessionId, ' ', 0); eventsQueue.add(eventcopy); counter++; } catch (Exception e) { if (LOG.isDebugEnabled()) LOG.debug("Ignoring " + event + ", " + ref + ", " + date + ", " + sessionUser + ", " + sessionId + " due to: " + e.toString()); } } // process events boolean processedOk = true; for (Event event : eventsQueue) { if (LOG.isDebugEnabled()) { LOG.debug("Dashboard Event Processing Thread is processing event: " + event.getEvent()); } EventProcessor eventProcessor = dashboardLogic.getEventProcessor(event.getEvent()); LOG.info("Found " + counter + " events, starting processing."); SecurityAdvisor advisor = new DashboardAggregateSecurityAdvisor(); sakaiProxy.pushSecurityAdvisor(advisor); try { if (eventProcessor != null) { eventProcessor.processEvent(event); LOG.debug("Event " + event.getEvent() + " successfully processed."); } else { LOG.debug("No processor to process event " + event.getEvent()); } } catch (Exception e) { LOG.warn("Error processing event: " + event, e); processedOk = false; } finally { sakaiProxy.popSecurityAdvisor(advisor); sakaiProxy.clearThreadLocalCache(); } } eventsQueue.clear(); if (!processedOk) { String returnMessage = "An error occurred while processing/persisting events to db - please check your logs."; LOG.error(returnMessage); throw new Exception(returnMessage); } } catch (SQLException e) { LOG.error("Unable to collect past site events", e); } catch (Exception e) { LOG.error("Unable to collect past site due to an unknown cause", e); } finally { try { if (rs != null) try { rs.close(); } catch (SQLException e) { } } finally { try { if (st != null) try { st.close(); } catch (SQLException e) { } } finally { closeEventDbConnection(connection); } } } long opEnd = System.currentTimeMillis(); LOG.info("Collected " + counter + " past events for site " + siteId + " in " + (opEnd - opStart) / 1000 + " seconds."); return counter; }
From source file:org.ensembl.healthcheck.util.ConnectionBasedSqlTemplateImpl.java
private void bindParamsToPreparedStatement(PreparedStatement st, Object[] arguments) throws SQLException { int i = 0;//from w ww . j a v a 2 s . com if (arguments != null) { for (Object arg : arguments) { i++; if (arg == null) { st.setNull(i, Types.NULL); } else if (arg instanceof String) { st.setString(i, (String) arg); } else if (arg instanceof Integer) { st.setInt(i, (Integer) arg); } else if (arg instanceof Boolean) { st.setBoolean(i, (Boolean) arg); } else if (arg instanceof Short) { st.setShort(i, (Short) arg); } else if (arg instanceof Date) { st.setTimestamp(i, new java.sql.Timestamp(((Date) arg).getTime())); } else if (arg instanceof java.sql.Date) { st.setDate(i, new java.sql.Date(((Date) arg).getTime())); } else if (arg instanceof Double) { st.setDouble(i, (Double) arg); } else if (arg instanceof Long) { st.setLong(i, (Long) arg); } else if (arg instanceof BigDecimal) { st.setObject(i, arg); } else if (arg instanceof BigInteger) { st.setObject(i, arg); } else { // Object try { ByteArrayOutputStream bytesS = new ByteArrayOutputStream(); ObjectOutputStream out = new ObjectOutputStream(bytesS); out.writeObject(arg); out.close(); byte[] bytes = bytesS.toByteArray(); bytesS.close(); st.setBytes(i, bytes); } catch (IOException e) { throw new SQLException( "Could not serialize object " + arg + " for use in a PreparedStatement "); } } } } }
From source file:org.ojbc.adapters.analyticaldatastore.dao.AnalyticalDatastoreDAOImpl.java
@Override public Integer saveDisposition(final Disposition inboundDisposition) { log.debug("Inserting row into Disposition table"); KeyHolder keyHolder = new GeneratedKeyHolder(); jdbcTemplate.update(new PreparedStatementCreator() { public PreparedStatement createPreparedStatement(Connection connection) throws SQLException { String dispositionInsertStatement = ""; String[] insertArgs = null; //No disposition ID provided in POJO if (inboundDisposition.getDispositionID() == null) { dispositionInsertStatement = "INSERT into Disposition (PersonID,DispositionTypeID,IncidentCaseNumber,DispositionDate,ArrestingAgencyORI," + "SentenceTermDays,SentenceFineAmount,InitialChargeCode, FinalChargeCode, RecordType,IsProbationViolation,IsProbationViolationOnOldCharge,RecidivismEligibilityDate, DocketChargeNumber,InitialChargeCode1, FinalChargeCode1) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"; insertArgs = new String[] { "PersonID", "DispositionTypeID", "IncidentCaseNumber", "DispositionDate,ArrestingAgencyORI," + "SentenceTermDays", "SentenceFineAmount", "InitialChargeCode", "FinalChargeCode", "RecordType", "IsProbationViolation", "IsProbationViolationOnOldCharge", "RecidivismEligibilityDate", "DocketChargeNumber", "InitialChargeCode1", "FinalChargeCode1" }; }//from w w w. j a va 2 s .co m //Disposition ID provided in POJO else { dispositionInsertStatement = "INSERT into Disposition (PersonID,DispositionTypeID,IncidentCaseNumber,DispositionDate,ArrestingAgencyORI," + "SentenceTermDays,SentenceFineAmount,InitialChargeCode, FinalChargeCode, RecordType,IsProbationViolation,IsProbationViolationOnOldCharge,RecidivismEligibilityDate, DocketChargeNumber, InitialChargeCode1, FinalChargeCode1,DispositionID) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"; insertArgs = new String[] { "PersonID", "DispositionTypeID", "IncidentCaseNumber", "DispositionDate,ArrestingAgencyORI," + "SentenceTermDays", "SentenceFineAmount", "InitialChargeCode", "FinalChargeCode", "RecordType", "IsProbationViolation", "IsProbationViolationOnOldCharge", "RecidivismEligibilityDate", "DocketChargeNumber", "InitialChargeCode1", "FinalChargeCode1", "DispositionID" }; } PreparedStatement ps = connection.prepareStatement(dispositionInsertStatement, insertArgs); ps.setInt(1, inboundDisposition.getPersonID()); ps.setInt(2, inboundDisposition.getDispositionTypeID()); ps.setString(3, inboundDisposition.getIncidentCaseNumber()); ps.setDate(4, new java.sql.Date(inboundDisposition.getDispositionDate().getTime())); ps.setString(5, inboundDisposition.getArrestingAgencyORI()); if (inboundDisposition.getSentenceTermDays() != null) { ps.setBigDecimal(6, inboundDisposition.getSentenceTermDays()); } else { ps.setNull(6, java.sql.Types.NULL); } if (inboundDisposition.getSentenceFineAmount() != null) { ps.setFloat(7, inboundDisposition.getSentenceFineAmount()); } else { ps.setNull(7, java.sql.Types.NULL); } ps.setString(8, inboundDisposition.getInitialChargeCode()); ps.setString(9, inboundDisposition.getFinalChargeCode()); ps.setString(10, String.valueOf(inboundDisposition.getRecordType())); if (inboundDisposition.getIsProbationViolation() != null) { ps.setString(11, String.valueOf(inboundDisposition.getIsProbationViolation())); } else { ps.setNull(11, java.sql.Types.NULL); } if (inboundDisposition.getIsProbationViolationOnOldCharge() != null) { ps.setString(12, String.valueOf(inboundDisposition.getIsProbationViolationOnOldCharge())); } else { ps.setNull(12, java.sql.Types.NULL); } if (inboundDisposition.getRecidivismEligibilityDate() != null) { ps.setDate(13, new java.sql.Date(inboundDisposition.getRecidivismEligibilityDate().getTime())); } else { ps.setNull(13, java.sql.Types.NULL); } ps.setString(14, inboundDisposition.getDocketChargeNumber()); ps.setString(15, inboundDisposition.getInitialChargeRank()); ps.setString(16, inboundDisposition.getFinalChargeRank()); if (inboundDisposition.getDispositionID() != null) { ps.setInt(17, inboundDisposition.getDispositionID()); } return ps; } }, keyHolder); Integer returnValue = null; if (inboundDisposition.getDispositionID() != null) { returnValue = inboundDisposition.getDispositionID(); } else { returnValue = keyHolder.getKey().intValue(); } return returnValue; }