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:org.forumj.dbextreme.db.service.test.user.tests.ReadUserTest.java
private static void prepareStatement(PreparedStatement st, IUser user) throws SQLException { st.setString(1, user.getNick());//from w ww. jav a 2 s . c om st.setString(2, user.getPass()); st.setString(3, user.getEmail()); st.setString(4, user.getName()); st.setString(5, user.getFam()); st.setString(6, user.getSex()); st.setDate(7, user.getBith()); st.setString(8, user.getPass2()); st.setInt(9, user.getShowMail() ? 1 : 0); st.setInt(10, user.getShowName() ? 1 : 0); st.setString(11, user.getCity()); st.setInt(12, user.getShowCity() ? 1 : 0); st.setString(13, user.getCountry()); st.setInt(14, user.getShowCountry() ? 1 : 0); st.setInt(15, user.getShowSex() ? 1 : 0); st.setString(16, user.getIcq()); st.setInt(17, user.getShowIcq() ? 1 : 0); st.setInt(18, user.getShowBithday() ? 1 : 0); st.setInt(19, user.getLanguge().getCode()); st.setInt(20, user.getHideIp() ? 1 : 0); st.setInt(21, user.getView()); st.setInt(22, user.getPp()); st.setInt(23, user.getPt()); st.setString(24, user.getAvatar()); st.setInt(25, user.getShowAvatar() ? 1 : 0); st.setInt(26, user.getAvatarApproved() ? 1 : 0); st.setInt(27, user.getWantSeeAvatars() ? 1 : 0); st.setInt(28, user.getTimeZone()); st.setString(29, user.getFooter()); st.setInt(30, user.getBan()); st.setInt(31, user.getActivateCode()); st.setInt(32, user.getIsActive() ? 1 : 0); }
From source file:org.rti.zcore.dar.report.MonthlyArtReport.java
/** * Get ART regimen records (regimen code field) for this period/site * @param conn//from w w w .j a va 2s.c o m * @param siteID * @param beginDate * @param endDate * @param orderBy TODO * @return * @throws ServletException */ public static ResultSet getArtRegimens(Connection conn, int siteID, Date beginDate, Date endDate, String orderBy) throws ServletException { ResultSet rs = null; String dateRange = "AND date_visit >= ? AND date_visit <= ? "; if (endDate == null) { dateRange = "AND date_visit = ?"; } try { if (siteID == 0) { String sql = "SELECT encounter.id AS id, date_visit, patient_id, district_patient_id, " + "first_name, surname, sex, encounter.site_id, age_at_first_visit, age_category, " + "encounter.created_by AS created_by, " + "regimen.code AS code, age_at_first_visit AS age, encounter.created, " + "regimen.name AS name, regimen_group_id " + "FROM art_regimen, encounter, regimen, patient " + "WHERE encounter.id = art_regimen.id " + "AND art_regimen.regimen_1 = regimen.id " + "AND encounter.patient_id = patient.id " + dateRange + "ORDER BY created " + orderBy + " , surname"; PreparedStatement ps = conn.prepareStatement(sql); ps.setDate(1, beginDate); if (endDate != null) { ps.setDate(2, endDate); } rs = ps.executeQuery(); } else { String sql = "SELECT encounter.id AS id, date_visit, patient_id, district_patient_id, " + "first_name, surname, sex, encounter.site_id, age_at_first_visit, age_category, " + "encounter.created_by AS created_by, " + "regimen.code AS code, age_at_first_visit AS age, encounter.created, " + "regimen.name AS name, regimen_group_id " + "FROM art_regimen, encounter, regimen, patient " + "WHERE encounter.id = art_regimen.id " + "AND art_regimen.regimen_1 = regimen.id " + "AND encounter.patient_id = patient.id " + dateRange + "AND encounter.site_id = ? " + "ORDER BY created " + orderBy + ", surname"; PreparedStatement ps = conn.prepareStatement(sql); ps.setDate(1, beginDate); if (endDate != null) { ps.setDate(2, endDate); ps.setInt(3, siteID); } else { ps.setInt(2, siteID); } rs = ps.executeQuery(); } } catch (Exception ex) { log.error(ex); } return rs; }
From source file:com.streamsets.pipeline.lib.jdbc.multithread.TableReadContext.java
private static void setParamVal(PreparedStatement ps, int paramIdx, int sqlType, String paramVal) throws SQLException, StageException { Utils.checkState(OffsetQueryUtil.SQL_TYPE_TO_FIELD_TYPE.containsKey(sqlType), Utils.format("Unsupported Partition Offset Type: {}", sqlType)); //All Date/Time Types are stored as long offsets //Parse string to get long. switch (sqlType) { case Types.TIME: ps.setTime(paramIdx, new java.sql.Time(Long.valueOf(paramVal))); break;/*from www.ja v a2 s. co m*/ case Types.DATE: ps.setDate(paramIdx, new java.sql.Date(Long.valueOf(paramVal))); break; case Types.TIMESTAMP: Timestamp ts = TableContextUtil.getTimestampForOffsetValue(paramVal); ps.setTimestamp(paramIdx, ts); break; default: ps.setObject(paramIdx, Field.create(OffsetQueryUtil.SQL_TYPE_TO_FIELD_TYPE.get(sqlType), paramVal).getValue()); } }
From source file:com.useekm.indexing.postgis.IndexedStatement.java
public static void addNewBatch(PreparedStatement stat, IndexedStatement indexedStatement) throws SQLException { int idx = 1;/*from w w w. j av a 2 s .co m*/ if (indexedStatement.objectDate != null) stat.setDate(idx++, new java.sql.Date(indexedStatement.objectDate.getTime())); else stat.setDate(idx++, null); stat.setString(idx++, indexedStatement.objectLanguage); stat.setObject(idx++, indexedStatement.objectSpatial); stat.setString(idx++, indexedStatement.objectString); stat.setString(idx++, indexedStatement.objectTsVectorConfig); stat.setString(idx++, indexedStatement.objectType); stat.setBoolean(idx++, indexedStatement.objectUri); stat.setString(idx++, indexedStatement.predicate); stat.setString(idx++, indexedStatement.subject); stat.addBatch(); }
From source file:yelpproject.DatabaseConnection.java
public static void insertIntoYelpUserTable(java.sql.Date yelping_since, JSONObject votes, long review_count, String name, String user_id, int friends, long fans, double average_stars, String type, JSONObject compliments, JSONArray elite) throws SQLException { PreparedStatement preparedStatement = null; String insertTableSQL = "INSERT INTO YELP_USER" + " VALUES(?,?,?,?,?,?,?,?,?,?,?)"; try {//from ww w .j a v a 2s . co m if (connection == null) { connection = getDBConnection(); } preparedStatement = connection.prepareStatement(insertTableSQL); preparedStatement.setDate(1, yelping_since); preparedStatement.setString(2, String.valueOf(votes)); preparedStatement.setString(3, name); preparedStatement.setLong(4, review_count); preparedStatement.setString(5, user_id); preparedStatement.setInt(6, friends); preparedStatement.setLong(7, fans); preparedStatement.setDouble(8, average_stars); preparedStatement.setString(9, type); preparedStatement.setString(10, String.valueOf(compliments)); preparedStatement.setString(11, String.valueOf(elite)); // execute insert SQL stetement preparedStatement.executeUpdate(); System.out.println("Record is inserted into YELP_USER table!"); } catch (SQLException e) { System.out.println(e.getMessage()); } finally { if (preparedStatement != null) { preparedStatement.close(); } } }
From source file:com.novartis.opensource.yada.util.YADAUtils.java
/** * One-liner execution of a sql statement, returning an SQL {@link java.sql.ResultSet}. * <strong>Note: This method opens a db connection but DOES NOT CLOSE IT. * Use the static method {@link ConnectionFactory#releaseResources(ResultSet)} to close it from * the calling method</strong>//from ww w . j a v a 2 s . c om * @param sql the query to execute * @param params the data values to map to query columns * @return a {@link java.sql.ResultSet} object containing the result of the query * @throws YADAConnectionException when the datasource is inaccessible * @throws YADASQLException when the JDBC configuration or execution fails */ public static ResultSet executePreparedStatement(String sql, Object[] params) throws YADAConnectionException, YADASQLException { ResultSet rs = null; try { Connection c = ConnectionFactory.getConnectionFactory().getConnection(ConnectionFactory.YADA_APP); PreparedStatement p = c.prepareStatement(sql); for (int i = 1; i <= params.length; i++) { Object param = params[i - 1]; if (param instanceof String) { p.setString(i, (String) param); } else if (param instanceof Date) { p.setDate(i, (Date) param); } else if (param instanceof Integer) { p.setInt(i, ((Integer) param).intValue()); } else if (param instanceof Float) { p.setFloat(i, ((Float) param).floatValue()); } } rs = p.executeQuery(); } catch (SQLException e) { throw new YADASQLException(e.getMessage(), e); } return rs; }
From source file:com.keybox.manage.db.SessionAuditDB.java
/** * deletes audit history for users if after time set in properties file * * @param con DB connection//from ww w .j a v a 2 s .c om */ public static void deleteAuditHistory(Connection con) { try { //delete logs with no terminal entries PreparedStatement stmt = con.prepareStatement( "delete from session_log where id not in (select session_id from terminal_log)"); stmt.execute(); //take today's date and subtract how many days to keep history Calendar cal = Calendar.getInstance(); cal.add(Calendar.DATE, (-1 * Integer.parseInt(AppConfig.getProperty("deleteAuditLogAfter")))); //subtract java.sql.Date date = new java.sql.Date(cal.getTimeInMillis()); stmt = con.prepareStatement("delete from session_log where session_tm < ?"); stmt.setDate(1, date); stmt.execute(); DBUtils.closeStmt(stmt); } catch (Exception e) { log.error(e.toString(), e); } }
From source file:org.rti.zcore.dar.report.OIDailyActivityReport.java
/** * Retrieve all Encounter records for this form 132 * @param conn//from w w w . ja va 2 s .c om * @param siteID * @param beginDate * @param endDate * @return * @throws ServletException */ protected static ResultSet getOIEncounters(Connection conn, int siteID, Date beginDate, Date endDate) throws ServletException { ResultSet rs = null; String dateRange = "AND date_visit >= ? AND date_visit <= ? "; if (endDate == null) { dateRange = "AND date_visit = ?"; } try { if (siteID == 0) { String sql = "SELECT encounter.id AS id, date_visit, patient_id, district_patient_id, " + "first_name, surname, encounter.site_id, encounter.created_by AS created_by, " + "age_at_first_visit AS age, age_category, encounter.created " + "FROM encounter, patient, patient_item " + "WHERE encounter.patient_id = patient.id " + "AND encounter.id = patient_item.encounter_id " + "AND (patient_item.item_id = 51 OR " + "patient_item.item_id = 52 OR " + "patient_item.item_id = 50 OR " + "patient_item.item_id = 49 OR " + "patient_item.item_id = 44 OR " + "patient_item.item_id = 48 OR " + "patient_item.item_id = 47 OR " + "patient_item.item_id = 46 OR " + "patient_item.item_id = 174 OR " + "patient_item.item_id = 45 OR " + "patient_item.item_id = 35 OR " + "patient_item.item_id = 37 OR " + "patient_item.item_id = 36 OR " + "patient_item.item_id = 39 OR " + "patient_item.item_id = 38 OR " + "patient_item.item_id = 40 OR " + "patient_item.item_id = 41 OR " + "patient_item.item_id = 42 OR " + "patient_item.item_id = 43 OR " + "patient_item.item_id = 53) " + "AND form_id = 132\n" + dateRange + "ORDER BY patient_id DESC, date_visit DESC"; PreparedStatement ps = conn.prepareStatement(sql); ps.setDate(1, beginDate); if (endDate != null) { ps.setDate(2, endDate); } rs = ps.executeQuery(); } else { String sql = "SELECT encounter.id AS id, date_visit, patient_id, district_patient_id, " + "first_name, surname, encounter.site_id, encounter.created_by AS created_by, " + "age_at_first_visit AS age, age_category, encounter.created " + "FROM encounter, patient, patient_item " + "WHERE encounter.patient_id = patient.id " + "AND encounter.id = patient_item.encounter_id " + "AND (patient_item.item_id = 51 OR " + "patient_item.item_id = 52 OR " + "patient_item.item_id = 50 OR " + "patient_item.item_id = 49 OR " + "patient_item.item_id = 44 OR " + "patient_item.item_id = 48 OR " + "patient_item.item_id = 47 OR " + "patient_item.item_id = 46 OR " + "patient_item.item_id = 174 OR " + "patient_item.item_id = 45 OR " + "patient_item.item_id = 35 OR " + "patient_item.item_id = 37 OR " + "patient_item.item_id = 36 OR " + "patient_item.item_id = 39 OR " + "patient_item.item_id = 38 OR " + "patient_item.item_id = 40 OR " + "patient_item.item_id = 41 OR " + "patient_item.item_id = 42 OR " + "patient_item.item_id = 43 OR " + "patient_item.item_id = 53) " + "AND form_id = 132\n" + dateRange + "AND encounter.site_id = ? " + "ORDER BY patient_id DESC, date_visit DESC"; PreparedStatement ps = conn.prepareStatement(sql); ps.setDate(1, beginDate); if (endDate != null) { ps.setDate(2, endDate); ps.setInt(3, siteID); } else { ps.setInt(2, siteID); } rs = ps.executeQuery(); } } catch (Exception ex) { log.error(ex); } return rs; }
From source file:yelpproject.DatabaseConnection.java
public static void insertIntoReviewTable(JSONObject votes, String user_id, String review_id, long stars, java.sql.Date date, String text, String type, String business_id) throws SQLException { PreparedStatement preparedStatement = null; String insertTableSQL = "INSERT INTO REVIEW" + " VALUES(?,?,?,?,?,?,?,?)"; try {//from w w w . j av a 2s. co m if (connection == null) { connection = getDBConnection(); } preparedStatement = connection.prepareStatement(insertTableSQL); // preparedStatement.setString(1, String.valueOf(votes)); preparedStatement.setString(2, user_id); preparedStatement.setString(3, review_id); preparedStatement.setLong(4, stars); preparedStatement.setString(6, text); preparedStatement.setString(5, type); preparedStatement.setString(7, business_id); preparedStatement.setDate(8, date); // execute insert SQL stetement preparedStatement.executeUpdate(); System.out.println("Record is inserted into REVIEW table!"); } catch (SQLException e) { System.out.println(e.getMessage()); } finally { if (preparedStatement != null) { preparedStatement.close(); } } }
From source file:org.rti.zcore.dar.report.ZEPRSSharedItems.java
protected static int getTotalNeonatalDeathsUTH(Date beginDate, Date endDate, Connection conn) { ResultSet rs = null;//from w w w. j a va2s.com int count = 0; try { String sql = "SELECT COUNT(DISTINCT(encounter.patient_id), patient.parent_id, encounter.pregnancy_id, labour_admission_encounter_id) " + "FROM encounter, patient, pregnancy, site " + "WHERE encounter.patient_id = patient.id " + "AND encounter.pregnancy_id = pregnancy.id " + "AND site.id = encounter.site_id " + "AND date_visit >= ? " + "AND date_visit <= ? " + "AND patient.parent_id IS NOT NULL " + "AND dead=1"; PreparedStatement ps = conn.prepareStatement(sql); ps.setDate(1, beginDate); ps.setDate(2, endDate); rs = ps.executeQuery(); while (rs.next()) { count = rs.getInt(1); } } catch (Exception ex) { log.error(ex); } return count; }