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.rti.zcore.dar.report.ZEPRSUtils.java
/** * Takes pregnancyId/*from w w w. java 2 s .co m*/ * @param patientID The id associated with this patient * @param pregnancyId The pregnancyId associated with this patient * @param formID The id associated a particular form * @param table The table that stores this 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 conn * @return Returns a Resultset containing all Encounters associated with * this patient, pregnancy and form during the time frame * @throws ServletException */ public static ResultSet getPatientEncounters(int patientID, int pregnancyId, int formID, String table, Date beginDate, Date endDate, Connection conn) throws ServletException { // Connection conn = null; ResultSet rs = null; try { // Retrieve all Encounter records for this form String sql = "SELECT * FROM encounter," + table + " WHERE encounter.id = " + table + ".id \n" + "AND form_id = ? AND patient_id = ? \n" + "AND date_visit >= ? AND date_visit <= ? \n" + "AND pregnancy_id = ? \n" + "ORDER BY date_visit"; PreparedStatement ps = conn.prepareStatement(sql); ps.setInt(1, formID); ps.setInt(2, patientID); ps.setDate(3, beginDate); ps.setDate(4, endDate); ps.setInt(5, pregnancyId); rs = ps.executeQuery(); } catch (Exception ex) { ex.printStackTrace(); } return rs; }
From source file:org.rti.zcore.dar.report.ZEPRSUtils.java
/** * @param patientID The id associated with this patient * @param formID The id associated a particular form * @param table The table that stores this 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 conn/*from ww w .j a v a 2 s .c om*/ * @return Returns a Resultset containing all Encounters associated with * this patient and form during the time frame. Order by date_visit - useful for monthly reports * @throws ServletException */ public static ResultSet getPatientEncounters(int patientID, int formID, String table, Date beginDate, Date endDate, int siteID, Connection conn) throws ServletException { // Connection conn = null; ResultSet rs = null; try { // conn = getZEPRSConnection(); // Retrieve all Encounter records for this form String sql = "SELECT * FROM encounter," + table + " WHERE encounter.id = " + table + ".id " + "AND form_id = ? AND patient_id = ? AND date_visit >= ? AND date_visit <= ? AND site_id = ? " + "ORDER BY date_visit"; PreparedStatement ps = conn.prepareStatement(sql); ps.setInt(1, formID); ps.setInt(2, patientID); ps.setDate(3, beginDate); ps.setDate(4, endDate); ps.setInt(5, siteID); rs = ps.executeQuery(); } catch (Exception ex) { throw new ServletException("Cannot retrieve database connection", ex); } return rs; }
From source file:org.rti.zcore.dar.report.ZEPRSUtils.java
/** * @param formID The id associated with the form * @param table The table that stores this 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 siteID The site id/* w w w .j a v a 2 s . co m*/ * @param conn * @return Returns a count of all Encounters pertaining to this form for the time frame and site * @throws ServletException */ protected static int getEncountersCount(int formID, String table, Date beginDate, Date endDate, int siteID, Connection conn) throws ServletException { // Connection conn = null; ResultSet rs = null; int count = 0; try { if (siteID == 0) { String sql = "SELECT count(*) FROM encounter\n" + "WHERE form_id = ? AND date_visit >= ? AND date_visit <= ?\n" + "GROUP BY patient_id"; PreparedStatement ps = conn.prepareStatement(sql); ps.setString(1, Integer.toString(formID)); ps.setDate(2, beginDate); ps.setDate(3, endDate); rs = ps.executeQuery(); } else { String sql = "SELECT count(*) FROM encounter\n" + "WHERE form_id = ? AND date_visit >= ? AND date_visit <= ? AND site_id = ?\n" + "GROUP BY patient_id"; PreparedStatement ps = conn.prepareStatement(sql); ps.setString(1, Integer.toString(formID)); ps.setDate(2, beginDate); ps.setDate(3, endDate); ps.setInt(4, siteID); rs = ps.executeQuery(); } while (rs.next()) { count = count + rs.getInt(1); } rs.close(); } catch (SQLException e) { e.printStackTrace(); } return count; }
From source file:org.rti.zcore.dar.report.ZEPRSUtils.java
/** * Return list of patients where cd4 count <= 250 or is null * @param conn/*from w ww .j a va 2 s. com*/ * @param siteId * @param beginDate * @param endDate * @return */ protected static ResultSet getLowCD4Patients(Connection conn, Long siteId, Date beginDate, Date endDate) { ResultSet rs = null; String condition = ""; if (siteId != null && siteId > 0) { condition = "and encounter.site_id =?\n"; } try { String sql = "SELECT DISTINCT(patient.id) AS patient_id, patient.district_patient_id,\n" + "CONCAT_WS(' ',patient.first_name,patient.surname) AS patient_name, dateLabRequest, " + "cd4count, patient_id\n" + "FROM zeprs.patient, zeprs.encounter, zeprs.labtest\n" + "WHERE encounter.id = labtest.id\n" + "AND patient.id = encounter.patient_id\n" + "AND labType = 3042\n" + "AND (cd4count <=250 OR cd4count IS NULL)\n" + condition + "AND encounter.date_visit >= ?\n" + "AND encounter.date_visit <= ?\n" + "ORDER BY encounter.date_visit DESC, patient.surname, patient.first_name;\n"; PreparedStatement ps = conn.prepareStatement(sql); if (siteId > 0) { ps.setLong(1, siteId); ps.setDate(2, beginDate); ps.setDate(3, endDate); } else { ps.setDate(1, beginDate); ps.setDate(2, endDate); } rs = ps.executeQuery(); } catch (Exception ex) { log.error(ex); } return rs; }
From source file:org.rti.zcore.dar.report.ZEPRSUtils.java
/** * Query patients who had Counsel visits during this time period. * @param conn//from ww w. java 2s . c o m * @param siteId * @param beginDate * @param endDate * @return counselvisit */ protected static ResultSet getPatientsWithCounselVisits(Connection conn, Long siteId, Date beginDate, Date endDate) { ResultSet rs = null; String condition = ""; if (siteId != null && siteId > 0) { condition = "and encounter.site_id =?\n"; } try { String sql = "SELECT DISTINCT(patient.id) AS patient_id, patient.district_patient_id,\n" + "CONCAT_WS(' ',patient.first_name,patient.surname) AS patient_name,\n" + "encounter.date_visit AS datevisit, testDate, fe1.enumeration AS hiv_result\n" + "FROM zeprs.patient, zeprs.patient_status, zeprs.encounter, zeprs.smcounselingvisit\n" + "LEFT JOIN admin.field_enumeration fe1 on fe1.id = smcounselingvisit.hiv_test_result\n" + "WHERE patient.id = patient_status.id\n" + "AND patient.id = encounter.patient_id\n" + "AND encounter.id = smcounselingvisit.id\n" + //"AND patient.hiv_positive > 0 \n" + condition + "AND hiv_test_result = 2940\n" + condition + "AND encounter.date_visit >= ?\n" + "AND encounter.date_visit <= ?\n" + "ORDER BY encounter.date_visit DESC, patient.surname, patient.first_name;\n"; PreparedStatement ps = conn.prepareStatement(sql); if (siteId > 0) { ps.setLong(1, siteId); ps.setDate(2, beginDate); ps.setDate(3, endDate); } else { ps.setDate(1, beginDate); ps.setDate(2, endDate); } rs = ps.executeQuery(); } catch (Exception ex) { log.error(ex); } return rs; }
From source file:org.hibernate.search.test.performance.scenario.TestScenario.java
protected void initDatabase(SessionFactory sf) { log("starting initialize database"); initDatabaseStopWatch.start();//from w ww. java 2 s . c om BatchSupport batchSupport = new BatchSupport(sf, initialOffset); batchSupport.execute("insert into author(id, name) values(?, ?)", initialAutorCount, new BatchCallback() { @Override public void initStatement(PreparedStatement ps, long id) throws SQLException { ps.setLong(1, id); ps.setString(2, "autor" + id); } }); batchSupport.execute( "insert into book(id, title, summary, rating, totalSold, publicationDate) values(?, ?, ?, ?, ?, ?)", initialBookCount, new BatchCallback() { @Override public void initStatement(PreparedStatement ps, long id) throws SQLException { ps.setLong(1, id); ps.setString(2, "title" + id); ps.setString(3, reverse(SUMMARIES[(int) (id % SUMMARIES.length)])); ps.setLong(4, -1); ps.setLong(5, -1); ps.setDate(6, new Date(PUBLICATION_DATE_ZERO.getTime())); } }); batchSupport.execute("insert into book_author(book_id, authors_id) values(?, ?)", initialBookCount, new BatchCallback() { @Override public void initStatement(PreparedStatement ps, long id) throws SQLException { ps.setLong(1, id); ps.setLong(2, initialOffset + (id % initialAutorCount)); } }); initDatabaseStopWatch.stop(); }
From source file:org.rti.zcore.dar.report.ZEPRSUtils.java
protected static ResultSet getDailyReflexReport(Connection conn, Long siteId, Date beginDate, Date endDate) { ResultSet rs = null;/*from w ww . j a v a 2 s. co m*/ String condition = ""; if (siteId != null && siteId > 0) { condition = "and encounter.site_id =?\n"; } try { String sql = "SELECT DISTINCT(patient.id) AS patient_id, patient.district_patient_id,\n" + "CONCAT_WS(' ',patient.first_name,patient.surname) AS patient_name,\n" + "fe1.enumeration AS who_stage, fe2.enumeration AS referred_art_clinic,\n" + "fe3.enumeration AS regimen, regimen_visit_date, cd4.cd4count, hgb.resultsNumeric AS hgb_result,\n" + "encounter.date_visit AS datevisit\n" + "FROM zeprs.patient, zeprs.patient_status, zeprs.encounter\n" + "LEFT JOIN zeprs.arvregimen ON encounter.id = arvregimen.id\n" + "LEFT JOIN admin.field_enumeration fe1 on fe1.id = arvregimen.who_stage\n" + "LEFT JOIN admin.field_enumeration fe2 on fe2.id = arvregimen.referred_art_clinic\n" + "LEFT JOIN admin.field_enumeration fe3 on fe3.id = arvregimen.regimen\n" + "LEFT JOIN zeprs.labtest cd4 ON encounter.id = cd4.id AND cd4.labType = 3042\n" + "LEFT JOIN zeprs.labtest hgb ON encounter.id = hgb.id AND (hgb.labType = 2925 OR hgb.labType = 2926)\n" + "WHERE patient.id = patient_status.id\n" + "AND patient.id = encounter.patient_id\n" + "AND patient.hiv_positive > 0\n" + condition + "AND encounter.date_visit >= ?\n" + "AND encounter.date_visit <= ?\n" + "ORDER BY encounter.date_visit DESC, patient.surname, patient.first_name;"; PreparedStatement ps = conn.prepareStatement(sql); if (siteId > 0) { ps.setLong(1, siteId); ps.setDate(2, beginDate); ps.setDate(3, endDate); } else { ps.setDate(1, beginDate); ps.setDate(2, endDate); } rs = ps.executeQuery(); } catch (Exception ex) { log.error(ex); } return rs; }
From source file:org.wso2.carbon.identity.core.dao.OpenIDUserRPDAO.java
/** * Creates a Relying Party and associates it with the User. * If the entry exist, then update with the new data * * @param rpdo//from w w w . j ava 2 s .com */ public void createOrUpdate(OpenIDUserRPDO rpdo) { // first we try to get DO from the database. Return null if no data OpenIDUserRPDO existingdo = getOpenIDUserRP(rpdo.getUserName(), rpdo.getRpUrl()); Connection connection = IdentityDatabaseUtil.getDBConnection(); PreparedStatement prepStmt = null; try { if (existingdo != null) { // data found in the database // we should update the entry prepStmt = connection.prepareStatement(OpenIDSQLQueries.UPDATE_USER_RP); prepStmt.setString(5, rpdo.getUserName()); prepStmt.setInt(6, IdentityTenantUtil.getTenantIdOfUser(rpdo.getUserName())); prepStmt.setString(7, rpdo.getRpUrl()); prepStmt.setString(1, rpdo.isTrustedAlways() ? "TRUE" : "FALSE"); // we set the new current date prepStmt.setDate(2, new java.sql.Date(new Date().getTime())); // we increment the value which is in the database prepStmt.setInt(3, existingdo.getVisitCount() + 1); // increase visit count prepStmt.setString(4, rpdo.getDefaultProfileName()); prepStmt.execute(); connection.commit(); } else { // data not found, we should create the entry prepStmt = connection.prepareStatement(OpenIDSQLQueries.STORE_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"); // we set the current date prepStmt.setDate(5, new java.sql.Date(new Date().getTime())); // ok, this is the first visit prepStmt.setInt(6, 1); prepStmt.setString(7, rpdo.getDefaultProfileName()); prepStmt.execute(); connection.commit(); } } catch (SQLException e) { log.error("Failed to store RP: " + rpdo.getRpUrl() + " for user: " + rpdo.getUserName() + " Error while accessing the database", e); } finally { IdentityDatabaseUtil.closeStatement(prepStmt); IdentityDatabaseUtil.closeConnection(connection); } }
From source file:com.esofthead.mycollab.module.project.service.ibatis.GanttAssignmentServiceImpl.java
@Override public void massUpdatePredecessors(Integer taskSourceId, final List<TaskPredecessor> predecessors, Integer sAccountId) {//from w ww .ja va2s .c o m Lock lock = DistributionLockUtil.getLock("task-service" + sAccountId); try { PredecessorMapper predecessorMapper = ApplicationContextUtil.getSpringBean(PredecessorMapper.class); PredecessorExample ex = new PredecessorExample(); ex.createCriteria().andSourceidEqualTo(taskSourceId); predecessorMapper.deleteByExample(ex); JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource); final long now = new GregorianCalendar().getTimeInMillis(); if (lock.tryLock(30, TimeUnit.SECONDS)) { jdbcTemplate.batchUpdate( "INSERT INTO `m_prj_predecessor`(`sourceType`, `descType`, `predestype`,`lagDay`, " + "`sourceId`,`descId`, `createdTime`) VALUES (?, ?, ?, ?, ?, ?, ?)", new BatchPreparedStatementSetter() { @Override public void setValues(PreparedStatement preparedStatement, int i) throws SQLException { preparedStatement.setString(1, predecessors.get(i).getSourcetype()); preparedStatement.setString(2, predecessors.get(i).getDesctype()); preparedStatement.setString(3, predecessors.get(i).getPredestype()); preparedStatement.setInt(4, predecessors.get(i).getLagday()); preparedStatement.setInt(5, predecessors.get(i).getSourceid()); preparedStatement.setInt(6, predecessors.get(i).getDescid()); preparedStatement.setDate(7, new Date(now)); } @Override public int getBatchSize() { return predecessors.size(); } }); } } catch (Exception e) { throw new MyCollabException(e); } finally { DistributionLockUtil.removeLock("task-service" + sAccountId); lock.unlock(); } }
From source file:org.rti.zcore.dar.struts.action.FormAction.java
/** * Check formId to see what is the next form to be served. * * @param request/*from ww w.ja v a 2 s .c o m*/ * @param mapping * @param patientId * @param eventUuid * @param dynaForm * @param session * @param formId * @param vo Useful when you need to pass the encounterId in the url parameters * @return the next page/form */ private ActionForward createForward(HttpServletRequest request, ActionMapping mapping, Long patientId, String eventUuid, DynaValidatorForm dynaForm, HttpSession session, int formId, EncounterData vo) { BaseSessionSubject sessionPatient = null; Principal user = request.getUserPrincipal(); String username = user.getName(); String formName = mapping.getParameter().trim(); Connection conn = null; Form nextForm = new Form(); Integer maxRows = 0; if (request.getParameter("maxRows") != null) { maxRows = Integer.decode(request.getParameter("maxRows")); } else if (request.getAttribute("maxRows") != null) { maxRows = Integer.decode(request.getAttribute("maxRows").toString()); } else { switch (formId) { case 128: maxRows = 0; break; case 129: maxRows = 0; break; case 130: maxRows = 0; break; case 131: maxRows = 0; break; case 181: maxRows = 0; break; default: maxRows = 20; break; } } try { conn = DatabaseUtils.getZEPRSConnection(username); try { sessionPatient = SessionUtil.getInstance(session).getSessionPatient(); } catch (SessionUtil.AttributeNotFoundException e) { //log.error("Unable to get TimsSessionSubject"); } Form form = (Form) DynaSiteObjects.getForms().get(Long.valueOf(formId)); Long formTypeId = form.getFormTypeId(); // part of reload prevention scheme: resetToken(request); dynaForm.reset(mapping, request); StrutsUtils.removeFormBean(mapping, request); ActionForward forwardForm = null; String forwardString = null; switch (formId) { case 1: // Patient Registration forwardString = "/ArtRegimen/new.do?patientId=" + patientId.toString(); break; case 137: // ART Regimen forwardString = "/PatientItem/new.do?patientId=" + patientId.toString(); break; case 132: // Dispensary //forwardString = "/patientTask.do?flowId=" + flowId.toString(); forwardString = "/Appointment/new.do?patientId=" + patientId.toString(); //forwardString = "/chart.do?patientId=" + patientId.toString() + "&formId=" + formId; break; /*case 136: forwardString = "/chart.do?patientId=" + patientId.toString() + "&formId=" + formId; break;*/ case 136: // PatientCondition forwardString = "/PatientCondition/new.do?patientId=" + patientId.toString(); break; case 179: // Appt if (Constants.APPOINTMENT_COUNT_THRESHOLD != null) { Appointment appt = (Appointment) vo; Date apptDate = appt.getAppointment_date(); java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat( org.rti.zcore.Constants.DATE_FORMAT_SHORT); sdf.setTimeZone(TimeZone.getDefault()); String apptDateStr = sdf.format(appt.getAppointment_date().getTime()); ResultSet rs = null; Integer countAppts = 0; String warningMessage = ""; try { String sql = "SELECT COUNT(encounter.id) AS countAppts " + "FROM encounter, appointment " + "WHERE appointment.id=encounter.id " + "AND appointment_date = ? "; PreparedStatement ps = conn.prepareStatement(sql); ps.setDate(1, apptDate); rs = ps.executeQuery(); while (rs.next()) { countAppts = rs.getInt("countAppts"); } } catch (Exception ex) { log.error(ex); } Integer apptCountThreshold = Integer.valueOf(Constants.APPOINTMENT_COUNT_THRESHOLD); if (countAppts != null && countAppts > 0) { Integer warningThreshold = apptCountThreshold - 10; if ((countAppts >= warningThreshold) && (countAppts < apptCountThreshold)) { warningMessage = "Warning: Approaching Threshold of " + apptCountThreshold + " Appointments"; } else if (countAppts >= apptCountThreshold) { warningMessage = "Warning: Passed Threshold of " + apptCountThreshold + " Appointments."; } forwardString = "/Appointment/new.do?patientId=" + patientId.toString() + "&warningMessage=" + warningMessage + "&countAppts=" + countAppts + "&apptDate=" + apptDateStr; } else { forwardString = "/Appointment/new.do?patientId=" + patientId.toString() + "&countAppts=" + countAppts + "&apptDate=" + apptDateStr; } } else { forwardString = "/Appointment/new.do?patientId=" + patientId.toString(); } break; case 180: // Appt forwardString = "/PatientStatus_changes/new.do?patientId=" + patientId.toString(); break; default: switch (formTypeId.intValue()) { case 5: // admin forwardString = "/admin/records/list.do?formId=" + formId + "&maxRows=" + maxRows; break; case 7: // charts forwardString = "/chart.do?id=" + formId; break; case 8: // lists forwardString = "/recordList.do?formId=" + formId + "&patientId=" + patientId.toString(); break; default: if (sessionPatient != null) { Long flowId = sessionPatient.getCurrentFlowId(); forwardString = "/patientTask.do?flowId=" + flowId.toString(); } else { forwardString = "/home.do"; } break; } break; } forwardForm = new ActionForward(forwardString); forwardForm.setRedirect(true); return forwardForm; } catch (ServletException e) { log.error(e); } finally { try { if (conn != null && !conn.isClosed()) { conn.close(); } } catch (SQLException e) { log.error(e); } } return (new ActionForward(mapping.getInput())); }