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.dao.FJThreadDao.java
public void create(IFJThread thread, IFJPost post) throws IOException, DBException, SQLException, ConfigurationException { String createThreadQuery = getCreateThreadQuery(); Connection conn = null;/*from w w w .j a v a2 s . c o m*/ PreparedStatement st = null; boolean error = true; try { conn = getConnection(); conn.setAutoCommit(false); st = conn.prepareStatement(createThreadQuery, new String[] { "id" }); st.setLong(1, thread.getAuthId()); st.setString(2, thread.getHead()); Date date = new Date(); st.setDate(3, new java.sql.Date(date.getTime())); st.setDate(4, new java.sql.Date(date.getTime())); st.setString(5, thread.getNick()); st.setInt(6, thread.getType().getType()); st.executeUpdate(); ResultSet idRs = st.getGeneratedKeys(); if (idRs.next()) { Long threadId = idRs.getLong(1); thread.setId(threadId); post.setThreadId(threadId); post.getHead().setThreadId(threadId); post.getHead().setCreateTime(date.getTime()); FJPostDao postDao = new FJPostDao(); Long postId = postDao.create(post, conn, false); thread.setLastPostId(postId); thread.setLastPostAuthId(post.getHead().getAuth()); thread.setLastPostTime(new Date(post.getHead().getCreateTime())); thread.setLastPostNick(post.getHead().getAuthor().getNick()); update(thread, conn); if (thread instanceof FJQuestionThread) { FJQuestNodeDao answersDao = new FJQuestNodeDao(); FJQuestionThread questionThread = (FJQuestionThread) thread; List<IQuestNode> answers = questionThread.getAnswers(); QuestNode question = new QuestNode(); question.setNode(questionThread.getQuestion()); question.setGol(0); question.setHead(threadId); question.setNumb(0); question.setType(0); question.setUserId((long) 0); answersDao.create(question, conn); for (int answerIndex = 0; answerIndex < answers.size(); answerIndex++) { IQuestNode answer = answers.get(answerIndex); answer.setHead(threadId); answersDao.create(answer, conn); } } } else { throw new DBException("Thread wasn't created"); } error = false; } finally { writeFinally(conn, st, error); } }
From source file:lcn.module.batch.core.item.database.support.MethodMapItemPreparedStatementSetter.java
/** * params ? ? sqlType PreparedStatement? ?? */// w w w .j a v a2s . co m public void setValues(T item, PreparedStatement ps, String[] params, String[] sqlTypes, Map<String, Method> methodMap) throws SQLException { ReflectionSupport<T> reflector = new ReflectionSupport<T>(); for (int i = 0; i < params.length; i++) { try { if (sqlTypes[i].equals("String")) { ps.setString(i + 1, (String) reflector.invokeGettterMethod(item, params[i], methodMap)); } else if (sqlTypes[i].equals("int")) { ps.setInt(i + 1, (Integer) reflector.invokeGettterMethod(item, params[i], methodMap)); } else if (sqlTypes[i].equals("double")) { ps.setDouble(i + 1, (Double) reflector.invokeGettterMethod(item, params[i], methodMap)); } else if (sqlTypes[i].equals("Date")) { ps.setDate(i + 1, (Date) reflector.invokeGettterMethod(item, params[i], methodMap)); } else if (sqlTypes[i].equals("byte")) { ps.setByte(i + 1, (Byte) reflector.invokeGettterMethod(item, params[i], methodMap)); } else if (sqlTypes[i].equals("short")) { ps.setShort(i + 1, (Short) reflector.invokeGettterMethod(item, params[i], methodMap)); } else if (sqlTypes[i].equals("boolean")) { ps.setBoolean(i + 1, (Boolean) reflector.invokeGettterMethod(item, params[i], methodMap)); } else if (sqlTypes[i].equals("long")) { ps.setLong(i + 1, (Long) reflector.invokeGettterMethod(item, params[i], methodMap)); } else if (sqlTypes[i].equals("Float")) { ps.setFloat(i + 1, (Float) reflector.invokeGettterMethod(item, params[i], methodMap)); } else if (sqlTypes[i].equals("BigDecimal")) { ps.setBigDecimal(i + 1, (BigDecimal) reflector.invokeGettterMethod(item, params[i], methodMap)); } else if (sqlTypes[i].equals("byte[]")) { ps.setBytes(i + 1, (byte[]) reflector.invokeGettterMethod(item, params[i], methodMap)); } else { throw new SQLException(); } } catch (IllegalArgumentException e) { ReflectionUtils.handleReflectionException(e); } } }
From source file:com.novartis.opensource.yada.adaptor.OracleAdaptor.java
/** * Enables checking for {@link JDBCAdaptor#ORACLE_DATE_FMT} if {@code val} does not conform to {@link JDBCAdaptor#STANDARD_DATE_FMT} * @since 5.1.1//from w w w . ja va 2 s. c om */ @Override protected void setDateParameter(PreparedStatement pstmt, int index, char type, String val) throws SQLException { if (EMPTY.equals(val) || val == null) { pstmt.setNull(index, java.sql.Types.DATE); } else { SimpleDateFormat sdf = new SimpleDateFormat(STANDARD_DATE_FMT); ParsePosition pp = new ParsePosition(0); Date dateVal = sdf.parse(val, pp); if (dateVal == null) { sdf = new SimpleDateFormat(ORACLE_DATE_FMT); pp = new ParsePosition(0); dateVal = sdf.parse(val, pp); } if (dateVal != null) { long t = dateVal.getTime(); java.sql.Date sqlDateVal = new java.sql.Date(t); pstmt.setDate(index, sqlDateVal); } } }
From source file:org.rti.zcore.dar.report.ZEPRSUtils.java
/** * @param beginDate The first date of the report's time frame * @param endDate The last date of the report's time frame * @param siteID The site id/* w w w . j av a 2s. co m*/ * @param conn * @return Returns all Encounters for the time frame and site */ protected static ResultSet getEncounters(Date beginDate, Date endDate, int siteID, Connection conn) { ResultSet rs = null; int count = 0; try { // Retrieve all Encounter records for this time period String sql = "SELECT e.id, e.patient_id, e.form_id, e.last_modified, e.created, e.last_modified_by, " + "e.created_by, e.site_id, e.flow_id, e.date_visit, e.pregnancy_id, e.referral_id, p.parent_id\n" + "FROM encounter e, patient p\n" + "WHERE e.patient_id = p.id\n" + "AND date_visit >= ? AND date_visit <= ? AND e.site_id = ?\n" + "ORDER BY date_visit\n"; PreparedStatement ps = conn.prepareStatement(sql); ps.setDate(1, beginDate); ps.setDate(2, endDate); ps.setInt(3, siteID); rs = ps.executeQuery(); } catch (SQLException e) { e.printStackTrace(); } return rs; }
From source file:egovframework.rte.bat.core.item.database.support.EgovMethodMapItemPreparedStatementSetter.java
/** * params ? ? sqlType PreparedStatement? ?? *//*from w ww .j a va2s . c o m*/ public void setValues(T item, PreparedStatement ps, String[] params, String[] sqlTypes, Map<String, Method> methodMap) throws SQLException { EgovReflectionSupport<T> reflector = new EgovReflectionSupport<T>(); for (int i = 0; i < params.length; i++) { try { if (sqlTypes[i].equals("String")) { ps.setString(i + 1, (String) reflector.invokeGettterMethod(item, params[i], methodMap)); } else if (sqlTypes[i].equals("int")) { ps.setInt(i + 1, (Integer) reflector.invokeGettterMethod(item, params[i], methodMap)); } else if (sqlTypes[i].equals("double")) { ps.setDouble(i + 1, (Double) reflector.invokeGettterMethod(item, params[i], methodMap)); } else if (sqlTypes[i].equals("Date")) { ps.setDate(i + 1, (Date) reflector.invokeGettterMethod(item, params[i], methodMap)); } else if (sqlTypes[i].equals("byte")) { ps.setByte(i + 1, (Byte) reflector.invokeGettterMethod(item, params[i], methodMap)); } else if (sqlTypes[i].equals("short")) { ps.setShort(i + 1, (Short) reflector.invokeGettterMethod(item, params[i], methodMap)); } else if (sqlTypes[i].equals("boolean")) { ps.setBoolean(i + 1, (Boolean) reflector.invokeGettterMethod(item, params[i], methodMap)); } else if (sqlTypes[i].equals("long")) { ps.setLong(i + 1, (Long) reflector.invokeGettterMethod(item, params[i], methodMap)); } else if (sqlTypes[i].equals("Float")) { ps.setFloat(i + 1, (Float) reflector.invokeGettterMethod(item, params[i], methodMap)); } else if (sqlTypes[i].equals("BigDecimal")) { ps.setBigDecimal(i + 1, (BigDecimal) reflector.invokeGettterMethod(item, params[i], methodMap)); } else if (sqlTypes[i].equals("byte[]")) { ps.setBytes(i + 1, (byte[]) reflector.invokeGettterMethod(item, params[i], methodMap)); } else { throw new SQLException(); } } catch (IllegalArgumentException e) { ReflectionUtils.handleReflectionException(e); } } }
From source file:org.rti.zcore.dar.report.ZEPRSUtils.java
/** * This search all UTH sites/*from ww w .j a va2s. c om*/ * @param table The table that stores the form's submissions * @param beginDate The first date of the report's time frame * @param endDate The last date of the report's time frame * @param groupBy Use groupby to fetch only one record per patient? * @param conn * @param groupBy * @return Returns a ResultSet containing all patient Encounters pertaining * to this form during the time frame * @throws ServletException */ protected static ResultSet getEncountersUth(String table, Date beginDate, Date endDate, Connection conn, boolean groupBy) throws ServletException { ResultSet rs = null; try { // Retrieve all Encounter records for this form String sql = "SELECT * FROM " + table + " " + "JOIN encounter ON encounter.id = " + table + ".id " + "JOIN site ON site.id = encounter.site_id " + "AND date_visit >= ? AND date_visit <= ? " + "AND site_type_id = 2"; if (groupBy) { sql = sql + " GROUP BY patient_id"; } PreparedStatement ps = conn.prepareStatement(sql); ps.setDate(1, beginDate); ps.setDate(2, endDate); rs = ps.executeQuery(); } catch (Exception ex) { throw new ServletException(ex); } return rs; }
From source file:org.wso2.carbon.identity.core.dao.OpenIDUserRPDAO.java
/** * Updates the Relying Party if exists, if not, then creates a new Relying * Party/* w w w. j ava 2 s.co m*/ * * @param rpdo */ public void update(OpenIDUserRPDO rpdo) { Connection connection = IdentityDatabaseUtil.getDBConnection(); PreparedStatement prepStmt = null; try { if (isUserRPExist(connection, rpdo)) { // we should update the entry prepStmt = connection.prepareStatement(OpenIDSQLQueries.UPDATE_USER_RP); prepStmt.setString(1, rpdo.getUserName()); prepStmt.setInt(2, IdentityTenantUtil.getTenantIdOfUser(rpdo.getUserName())); prepStmt.setString(3, rpdo.getRpUrl()); prepStmt.setString(4, rpdo.isTrustedAlways() ? "TRUE" : "FALSE"); prepStmt.setDate(5, new java.sql.Date(rpdo.getLastVisit().getTime())); prepStmt.setInt(6, rpdo.getVisitCount() + 1); prepStmt.setString(7, rpdo.getDefaultProfileName()); prepStmt.execute(); connection.commit(); } else { // we should create the entry if (log.isDebugEnabled()) { log.debug("Failed to update RP: " + rpdo.getRpUrl() + " for user: " + rpdo.getUserName() + ". " + "Entry does not exist in the database."); } } } catch (SQLException e) { log.error("Failed to update RP: " + rpdo.getRpUrl() + " for user: " + rpdo.getUserName() + " Error while accessing the database", e); } finally { IdentityDatabaseUtil.closeStatement(prepStmt); IdentityDatabaseUtil.closeConnection(connection); } }
From source file:org.rti.zcore.dar.report.ZEPRSUtils.java
/** * @param beginDate The first date of the report's time frame * @param endDate The last date of the report's time frame * @param siteID The site id//from ww w. ja v a2 s . com * @param conn * @return Returns a count of all Encounters pertaining to this form for the time frame and site */ protected static int getEncountersCount(Date beginDate, Date endDate, int siteID, Connection conn) { // Connection conn = null; ResultSet rs = null; int count = 0; try { // conn = getZEPRSConnection(); // Retrieve all Encounter records for this time period String sql = "SELECT COUNT(DISTINCT(date_visit), patient_id) AS visits\n" + "FROM encounter\n" + "WHERE date_visit >= ? AND date_visit <= ? AND site_id = ?\n"; PreparedStatement ps = conn.prepareStatement(sql); ps.setDate(1, beginDate); ps.setDate(2, endDate); ps.setInt(3, siteID); rs = ps.executeQuery(); while (rs.next()) { //count = count + rs.getDate(1); count = rs.getInt("visits"); } rs.close(); } catch (SQLException e) { e.printStackTrace(); } return count; }
From source file:org.openmrs.util.databasechange.MoveDeletedHL7sChangeSet.java
/** * @see CustomTaskChange#execute(Database) */// w ww .ja v a2 s .c om @Override public void execute(Database database) throws CustomChangeException { JdbcConnection connection = (JdbcConnection) database.getConnection(); StringBuilder getDeletedHL7sSql = new StringBuilder(); getDeletedHL7sSql .append("SELECT hl7_source, hl7_source_key, hl7_data, date_created, uuid, hl7_in_archive_id"); getDeletedHL7sSql.append(" FROM hl7_in_archive WHERE message_state="); getDeletedHL7sSql.append(HL7Constants.HL7_STATUS_DELETED); StringBuilder insertHL7Sql = new StringBuilder(); insertHL7Sql.append("INSERT INTO hl7_in_queue"); insertHL7Sql.append(" (hl7_source, hl7_source_key, hl7_data, date_created, uuid, message_state)"); insertHL7Sql.append(" VALUES (?, ?, ?, ?, ?, "); insertHL7Sql.append(HL7Constants.HL7_STATUS_DELETED); insertHL7Sql.append(")"); PreparedStatement insertStatement = null; PreparedStatement deleteStatement = null; try { insertStatement = connection.prepareStatement(insertHL7Sql.toString()); deleteStatement = connection.prepareStatement("DELETE FROM hl7_in_archive WHERE hl7_in_archive_id=?"); // iterate over deleted HL7s ResultSet archives = connection.createStatement().executeQuery(getDeletedHL7sSql.toString()); while (archives.next()) { // add to the queue insertStatement.setString(1, archives.getString(1)); // set hl7_source insertStatement.setString(2, archives.getString(2)); // set hl7_source_key insertStatement.setString(3, archives.getString(3)); // set hl7_data insertStatement.setDate(4, archives.getDate(4)); // set date_created insertStatement.setString(5, archives.getString(5)); // set uuid insertStatement.executeUpdate(); // remove from the archives deleteStatement.setInt(1, archives.getInt(6)); deleteStatement.executeUpdate(); } // cleanup if (insertStatement != null) { insertStatement.close(); } if (deleteStatement != null) { deleteStatement.close(); } } catch (SQLException e) { throw new CustomChangeException("Unable to move deleted HL7s from archive table to queue table", e); } catch (DatabaseException dbex) { throw new CustomChangeException("Unable to move deleted HL7s from archive table to queue table", dbex); } }
From source file:org.rti.zcore.dar.report.ZEPRSUtils.java
protected static ResultSet getPostnatalMothers(Date beginDate, Date endDate, int siteID, Connection conn) throws ServletException { ResultSet rs = null;/*w ww .j av a 2 s .c om*/ try { if (siteID == 0) { String sql = "SELECT DISTINCT patient_id FROM encounter, patient " + "WHERE encounter.patient_id = patient.id " + "AND patient_id IN (SELECT patient_id from encounter where (flow_id = 3) OR (flow_id =4))\n" + "AND patient_id IN (SELECT patient_id from encounter where flow_id = 1)\n" + "AND date_visit >= ? " + "AND date_visit <= ? AND parent_id is null "; PreparedStatement ps = conn.prepareStatement(sql); ps.setDate(1, beginDate); ps.setDate(2, endDate); rs = ps.executeQuery(); } else { String sql = "SELECT DISTINCT patient_id FROM encounter, patient " + "WHERE encounter.patient_id = patient.id " + "AND patient_id IN (SELECT patient_id from encounter where (flow_id = 3) OR (flow_id =4))\n" + "AND patient_id IN (SELECT patient_id from encounter where flow_id = 1)\n" + "AND date_visit >= ? " + "AND date_visit <= ? AND parent_id is null AND encounter.site_id = ?"; PreparedStatement ps = conn.prepareStatement(sql); ps.setDate(1, beginDate); ps.setDate(2, endDate); ps.setInt(3, siteID); rs = ps.executeQuery(); } } catch (Exception ex) { log.error(ex); } return rs; }