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:net.freechoice.dao.impl.DaoPost.java
@DBSpec(dialect = Dialect.PostgreSQL) @Override//from w w w. j a va 2 s .c o m public List<FC_Post> suGetPostsAfter(final Date date, final int length, final int offset, final int limit) { return getJdbcTemplate().query(new PreparedStatementCreator() { @Override public PreparedStatement createPreparedStatement(Connection arg0) throws SQLException { PreparedStatement ps = arg0.prepareStatement( select(length) + " where time_posted::date > ?" + " offset " + offset + " limit " + limit); ps.setDate(1, date); return ps; } }, mapper); }
From source file:net.freechoice.dao.impl.DaoPost.java
@DBSpec(dialect = Dialect.PostgreSQL) @Override/* w w w .ja v a 2 s . co m*/ public List<FC_Post> suGetPostsBefore(final Date date, final int length, final int offset, final int limit) { return getJdbcTemplate().query(new PreparedStatementCreator() { // "status > 1 and (time_posted::date between ? and ?)", @Override public PreparedStatement createPreparedStatement(Connection arg0) throws SQLException { PreparedStatement ps = arg0.prepareStatement( select(length) + " where time_posted::date < ?" + " offset " + offset + " limit " + limit); ps.setDate(1, date); return ps; } }, mapper); }
From source file:org.jtotus.database.LocalJDBC.java
public long entryExists(Connection con, String stockName, DateTime date) { long retValue = 0; PreparedStatement pstm = null; try {/*ww w . j a va2s . co m*/ String statement = "SELECT ID FROM " + this.normTableName(stockName) + " WHERE DATE=?"; pstm = con.prepareStatement(statement); java.sql.Date sqlDate = new java.sql.Date(date.getMillis()); pstm.setDate(1, sqlDate); ResultSet results = pstm.executeQuery(); if (results.next()) { retValue = results.getLong(1); } } catch (SQLException ex) { Logger.getLogger(LocalJDBC.class.getName()).log(Level.SEVERE, null, ex); } finally { if (pstm != null) { try { pstm.close(); } catch (SQLException ex) { Logger.getLogger(LocalJDBC.class.getName()).log(Level.SEVERE, null, ex); } } } return retValue; }
From source file:org.psystems.dicom.browser.server.stat.StatClientRequestsChartServlet.java
/** * @param connection//from w w w. j av a2 s. c om * @param series * @param metrica * @param timeBegin * @param timeEnd * @param dataset * @throws SQLException */ private void getMetrics(Connection connection, String series, String metrica, long timeBegin, long timeEnd, DefaultCategoryDataset dataset) throws SQLException { PreparedStatement stmt = null; try { // String dateStr = format.format(calendar.getTime()); // System.out.println("!!! " + dateStr); SimpleDateFormat format = new SimpleDateFormat("dd.MM.yyyy"); stmt = connection.prepareStatement("SELECT METRIC_VALUE_LONG, METRIC_DATE" + " FROM WEBDICOM.DAYSTAT WHERE METRIC_NAME = ? and " + " METRIC_DATE BETWEEN ? AND ? "); stmt.setString(1, metrica); stmt.setDate(2, new java.sql.Date(timeBegin)); stmt.setDate(3, new java.sql.Date(timeEnd)); ResultSet rs = stmt.executeQuery(); while (rs.next()) { long value = rs.getLong("METRIC_VALUE_LONG"); Date date = rs.getDate("METRIC_DATE"); String dateStr = format.format(date.getTime()); String category = dateStr; dataset.addValue(value, series, category); // System.out.println("!!!! " + metrica + "=" + dateStr + "=" // + value); } rs.close(); } finally { if (stmt != null) stmt.close(); } }
From source file:org.apache.phoenix.end2end.index.IndexMaintenanceIT.java
private void helpTestCreateAndUpdate(boolean mutable, boolean localIndex) throws Exception { String dataTableName = generateUniqueName(); String fullDataTableName = INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + dataTableName; String indexName = generateUniqueName(); Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); Connection conn = DriverManager.getConnection(getUrl(), props); try {// w ww.j av a2 s . c om conn.setAutoCommit(false); createDataTable(conn, fullDataTableName, mutable ? "" : "IMMUTABLE_ROWS=true"); populateDataTable(conn, fullDataTableName); // create an expression index String ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX " + indexName + " ON " + fullDataTableName + " ((UPPER(varchar_pk) || '_' || UPPER(char_pk) || '_' || UPPER(varchar_col1) || '_' || UPPER(b.char_col2))," + " (decimal_pk+int_pk+decimal_col2+int_col1)," + " date_pk+1, date1+1, date2+1 )" + " INCLUDE (long_col1, long_col2)"; conn.createStatement().execute(ddl); // run select query with expression in WHERE clause String whereSql = "SELECT long_col1, long_col2 from " + fullDataTableName + " WHERE UPPER(varchar_pk) || '_' || UPPER(char_pk) || '_' || UPPER(varchar_col1) || '_' || UPPER(b.char_col2) = ?" + " AND decimal_pk+int_pk+decimal_col2+int_col1=?" // since a.date1 and b.date2 are NULLABLE and date is fixed width, these expressions are stored as // DECIMAL in the index (which is not fixed width) + " AND date_pk+1=? AND date1+1=? AND date2+1=?"; PreparedStatement stmt = conn.prepareStatement(whereSql); stmt.setString(1, "VARCHAR1_CHAR1 _A.VARCHAR1_B.CHAR1 "); stmt.setInt(2, 3); Date date = DateUtil.parseDate("2015-01-02 00:00:00"); stmt.setDate(3, date); stmt.setDate(4, date); stmt.setDate(5, date); // verify that the query does a range scan on the index table ResultSet rs = stmt.executeQuery("EXPLAIN " + whereSql); assertEquals(localIndex ? "CLIENT PARALLEL 1-WAY RANGE SCAN OVER INDEX_TEST." + dataTableName + " [1,'VARCHAR1_CHAR1 _A.VARCHAR1_B.CHAR1 ',3,'2015-01-02 00:00:00.000',1,420,156,800,000,1,420,156,800,000]\nCLIENT MERGE SORT" : "CLIENT PARALLEL 1-WAY RANGE SCAN OVER INDEX_TEST." + indexName + " ['VARCHAR1_CHAR1 _A.VARCHAR1_B.CHAR1 ',3,'2015-01-02 00:00:00.000',1,420,156,800,000,1,420,156,800,000]", QueryUtil.getExplainPlan(rs)); // verify that the correct results are returned rs = stmt.executeQuery(); assertTrue(rs.next()); assertEquals(1, rs.getInt(1)); assertEquals(1, rs.getInt(2)); assertFalse(rs.next()); // verify all rows in data table are present in index table String indexSelectSql = "SELECT UPPER(varchar_pk) || '_' || UPPER(char_pk) || '_' || UPPER(varchar_col1) || '_' || UPPER(b.char_col2), " + "decimal_pk+int_pk+decimal_col2+int_col1, " + "date_pk+1, date1+1, date2+1, " + "varchar_pk, char_pk, int_pk, long_pk, decimal_pk, " + "long_col1, long_col2 " + "from " + fullDataTableName; rs = conn.createStatement().executeQuery("EXPLAIN " + indexSelectSql); assertEquals( localIndex ? "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + fullDataTableName + " [1]\nCLIENT MERGE SORT" : "CLIENT PARALLEL 1-WAY FULL SCAN OVER INDEX_TEST." + indexName, QueryUtil.getExplainPlan(rs)); rs = conn.createStatement().executeQuery(indexSelectSql); verifyResult(rs, 1); verifyResult(rs, 2); // Insert two more rows to the index data table String upsert = "UPSERT INTO " + fullDataTableName + " VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; stmt = conn.prepareStatement(upsert); insertRow(stmt, 3); insertRow(stmt, 4); conn.commit(); rs = conn.createStatement().executeQuery(indexSelectSql); verifyResult(rs, 1); verifyResult(rs, 2); // verify that two rows added after index was created were also added to // the index table verifyResult(rs, 3); verifyResult(rs, 4); } finally { conn.close(); } }
From source file:org.rti.zcore.dar.dao.InventoryDAO.java
/** * Provides Balance Brought forward for a single item. * If doing a report on many items, use getBalanceMap instead. * @param conn/*from ww w . ja v a2s. com*/ * @param itemId * @param siteId * @param endDate * @return * @throws ClassNotFoundException * @throws IOException * @throws ServletException * @throws SQLException * @throws ObjectNotFoundException */ public static StockReport getBalanceBF(Connection conn, Long itemId, int siteId, Date endDate) throws ClassNotFoundException, IOException, ServletException, SQLException, ObjectNotFoundException { Integer balance = 0; Integer stockControlAdditionsTotal = 0; Integer stockControlDeletionsTotal = 0; Integer dispensed = 0; Integer negAdjustments = 0; Integer posAdjustments = 0; Integer issued = 0; Integer losses = 0; //if (itemId == 16 || itemId == 12 || itemId == 13 || itemId == 1 || itemId == 3) { String sql = "SELECT SUM(change_value) AS stockControlAdditionsTotal " + "FROM stock_control, encounter " + "WHERE encounter.id = stock_control.id " + "AND (type_of_change = 3263 OR type_of_change = 3266) " + "AND item_id = ? " + "AND site_id = ? " + "AND date_of_record < ?"; PreparedStatement ps = conn.prepareStatement(sql); ps.setLong(1, itemId); ps.setInt(2, siteId); ps.setDate(3, endDate); ResultSet rs = ps.executeQuery(); while (rs.next()) { stockControlAdditionsTotal = rs.getInt("stockControlAdditionsTotal"); } // rs.close(); /* sql = "SELECT SUM(change_value) AS issued " + "FROM stock_control, encounter " + "WHERE encounter.id = stock_control.id " + "AND type_of_change = 3264 " + "AND item_id = ? " + "AND site_id = ? " + "AND date_of_record < ?"; ps = conn.prepareStatement(sql); ps.setLong(1, itemId); ps.setInt(2, siteId); ps.setDate(3, endDate); rs = ps.executeQuery(); while (rs.next()) { issued = rs.getInt("issued"); } rs.close();*/ /* sql = "SELECT SUM(change_value) AS losses " + "FROM stock_control, encounter " + "WHERE encounter.id = stock_control.id " + "AND type_of_change = 3265 " + "AND item_id = ? " + "AND site_id = ? " + "AND date_of_record < ?"; ps = conn.prepareStatement(sql); ps.setLong(1, itemId); ps.setInt(2, siteId); ps.setDate(3, endDate); rs = ps.executeQuery(); while (rs.next()) { losses = rs.getInt("losses"); } rs.close();*/ /* sql = "SELECT SUM(change_value) AS negAdjustments " + "FROM stock_control, encounter " + "WHERE encounter.id = stock_control.id " + "AND type_of_change = 3267 " + "AND item_id = ? " + "AND site_id = ? " + "AND date_of_record < ?"; ps = conn.prepareStatement(sql); ps.setLong(1, itemId); ps.setInt(2, siteId); ps.setDate(3, endDate); rs = ps.executeQuery(); while (rs.next()) { negAdjustments = rs.getInt("negAdjustments"); } rs.close();*/ sql = "SELECT SUM(change_value) AS stockControlDeletionsTotal " + "FROM stock_control, encounter " + "WHERE encounter.id = stock_control.id " + "AND (type_of_change = 3264 OR type_of_change = 3265 OR type_of_change = 3267) " + "AND item_id = ? " + "AND site_id = ? " + "AND date_of_record < ?"; ps = conn.prepareStatement(sql); ps.setLong(1, itemId); ps.setInt(2, siteId); ps.setDate(3, endDate); rs = ps.executeQuery(); while (rs.next()) { stockControlDeletionsTotal = rs.getInt("stockControlDeletionsTotal"); } // rs.close(); sql = "SELECT SUM(patient_item.dispensed) AS dispensed " + "FROM patient_item, encounter, patient " + "WHERE encounter.id = patient_item.encounter_id " + "AND encounter.patient_id = patient.id " + "AND item_id = ? " + "AND encounter.site_id = ? " + "AND encounter.date_visit < ?"; /*sql = "SELECT SUM(patient_item.dispensed) AS dispensed " + "FROM patient_item " + "WHERE item_id = ? ";*/ ps = conn.prepareStatement(sql); ps.setLong(1, itemId); ps.setInt(2, siteId); ps.setDate(3, endDate); rs = ps.executeQuery(); while (rs.next()) { dispensed = rs.getInt("dispensed"); } rs.close(); /* sql = "SELECT encounter.id AS id " + "FROM encounter " + "WHERE form_id = 132\n" + "AND encounter.site_id = ? "; ps = conn.prepareStatement(sql); ps.setInt(1, siteId); rs = ps.executeQuery(); while (rs.next()) { Long id = rs.getLong("id"); String innerSql = "SELECT SUM(patient_item.dispensed) AS dispensed " + "FROM patient_item, encounter " + "WHERE patient_item.encounter_id = encounter.id " + "AND patient_item.item_id = ? " + "AND patient_item.encounter_id = ? "; PreparedStatement ps2 = conn.prepareStatement(innerSql); ps2.setLong(1, itemId); ps2.setLong(2, id); ResultSet rs2 = ps2.executeQuery(); while (rs2.next()) { int dispensedItem = rs2.getInt("dispensed"); dispensed = dispensed + dispensedItem; } rs2.close(); } rs.close();*/ //stockControlDeletionsTotal = (issued + losses + negAdjustments); balance = stockControlAdditionsTotal - (stockControlDeletionsTotal + dispensed); //log.debug("itemId: " + itemId + " BBF: " + balance); /* if (itemId == 12) { sql = "SELECT encounter.date_visit, patient_item.dispensed AS dispensed, surname " + "FROM patient_item, encounter, patient " + "WHERE encounter.id = patient_item.encounter_id " + "AND encounter.patient_id = patient.id " + "AND item_id = ? " + "AND encounter.site_id = ? " + "ORDER BY date_visit, surname "; ps = conn.prepareStatement(sql); ps.setLong(1, itemId); ps.setInt(2, siteId); rs = ps.executeQuery(); while (rs.next()) { Date dateVisit = rs.getDate("date_visit"); Integer dispensedItem = rs.getInt("dispensed"); String surname = rs.getString("surname"); log.debug(dateVisit + "," + surname + "," + dispensedItem); } rs.close(); }*/ /*StockControl beginningStockControl = getBeginningStockBalance(conn, 161, itemId, beginDate); Integer balanceBF = beginningStockControl.getBalance(); Integer balance = balanceBF; StockControl beginningStockControlBalance = new StockControl(); beginningStockControlBalance.setBalance(balance); beginningStockControlBalance.setDate_of_record(beginningStockControl.getDate_of_record()); beginningStockControlBalance.setDateVisit(beginningStockControl.getDate_of_record()); beginningStockControlBalance.setNotes("Beginning Balance");*/ StockReport stockReport = new StockReport(); stockReport.setOnHand(balance); stockReport.setTotalDispensed(dispensed); stockReport.setAdditionsTotal(stockControlAdditionsTotal); stockReport.setDeletionsTotal(stockControlDeletionsTotal); stockReport.setBalanceBF(balance); stockReport.setNegAdjustments(negAdjustments); stockReport.setPosAdjustments(posAdjustments); //stockChanges.add(0,beginningStockControlBalance); return stockReport; }
From source file:net.freechoice.dao.impl.DaoPost.java
@DBSpec(dialect = Dialect.PostgreSQL) @Override//from w ww. j av a2 s. c om public List<FC_Post> suGetPostsBetween(final Date start, final Date end, final int length, final int offset, final int limit) { return getJdbcTemplate().query(new PreparedStatementCreator() { // "status > 1 and (time_posted::date between ? and ?)", @Override public PreparedStatement createPreparedStatement(Connection arg0) throws SQLException { PreparedStatement ps = arg0.prepareStatement(select(length) + " where time_posted::date between ? and ?" + " offset " + offset + " limit " + limit); ps.setDate(1, start); ps.setDate(2, end); return ps; } }, mapper); }
From source file:org.apache.cloudstack.storage.image.db.TemplateDataStoreDaoImpl.java
@Override public void expireDnldUrlsForZone(Long dcId) { TransactionLegacy txn = TransactionLegacy.currentTxn(); PreparedStatement pstmt = null; try {/*from w w w . j a va 2s .c o m*/ txn.start(); pstmt = txn.prepareAutoCloseStatement(EXPIRE_DOWNLOAD_URLS_FOR_ZONE); pstmt.setDate(1, new java.sql.Date(-1l));// Set the time before the epoch time. pstmt.setLong(2, dcId); pstmt.executeUpdate(); txn.commit(); } catch (Exception e) { txn.rollback(); s_logger.warn("Failed expiring download urls for dcId: " + dcId, e); } }
From source file:net.freechoice.dao.impl.DaoPost.java
@DBSpec(dialect = Dialect.PostgreSQL) @Override//w w w. java 2 s . c o m public List<FC_Post> getPostsBefore(final Date time, final int length, final int offset, final int limit) { return getJdbcTemplate().query(new PreparedStatementCreator() { @Override public PreparedStatement createPreparedStatement(Connection arg0) throws SQLException { PreparedStatement ps = arg0.prepareStatement(selectFromPost(length, " where is_valud = true and status > 1 and time_posted < ?", TIME_DESCEND, offset, limit)); ps.setDate(1, time); return ps; } }, mapper); }
From source file:org.psystems.dicom.browser.server.stat.StatDailyLoadChartServlet.java
/** * @param connection//w w w . j a v a2 s . c o m * @param series * @param metrica * @param timeBegin * @param timeEnd * @param dataset * @throws SQLException */ private void getMetrics(Connection connection, String series, String metrica, long timeBegin, long timeEnd, DefaultCategoryDataset dataset) throws SQLException { PreparedStatement stmt = null; try { // String dateStr = format.format(calendar.getTime()); // System.out.println("!!! " + dateStr); SimpleDateFormat format = new SimpleDateFormat("dd.MM.yyyy"); stmt = connection.prepareStatement("SELECT METRIC_VALUE_LONG, METRIC_DATE" + " FROM WEBDICOM.DAYSTAT WHERE METRIC_NAME = ? and " + " METRIC_DATE BETWEEN ? AND ? "); stmt.setString(1, metrica); stmt.setDate(2, new java.sql.Date(timeBegin)); stmt.setDate(3, new java.sql.Date(timeEnd)); ResultSet rs = stmt.executeQuery(); while (rs.next()) { long value = rs.getLong("METRIC_VALUE_LONG") / 1000; Date date = rs.getDate("METRIC_DATE"); String dateStr = format.format(date.getTime()); String category = dateStr; dataset.addValue(value, series, category); // System.out.println("!!!! ALL_DCM_SIZE="+dateStr+"="+value); } rs.close(); } finally { if (stmt != null) stmt.close(); } }