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
/** * Did patient have any encounters during this time period? * @param patientID/* ww w . ja va 2 s . c o m*/ * @param beginDate * @param endDate * @param conn * @return * @throws ServletException */ public static ResultSet getPatientEncounters(Long patientID, Date beginDate, Date endDate, Connection conn) throws ServletException { ResultSet rs = null; try { String sql = "SELECT id FROM encounter" + " WHERE patient_id = ? \n" + "AND date_visit >= ? AND date_visit <= ? \n" + "ORDER BY date_visit"; PreparedStatement ps = conn.prepareStatement(sql); ps.setLong(1, patientID); ps.setDate(2, beginDate); ps.setDate(3, endDate); rs = ps.executeQuery(); } catch (Exception ex) { ex.printStackTrace(); } return rs; }
From source file:quiz.CsvLoader.java
/** * Parse CSV file using OpenCSV library and load in * given database table. /*from ww w.j a v a 2 s . c o m*/ * @param csvFile Input CSV file * @param tableName Database table name to import data * @param truncateBeforeLoad Truncate the table before inserting * new records. * @throws Exception */ public void loadCSV(String csvFile, String tableName, boolean truncateBeforeLoad) throws Exception { CSVReader csvReader = null; if (null == this.connection) { throw new Exception("Not a valid connection."); } try { csvReader = new CSVReader(new FileReader(csvFile), this.seprator); } catch (Exception e) { e.printStackTrace(); throw new Exception("Error occured while executing file. " + e.getMessage()); } String[] headerRow = csvReader.readNext(); if (null == headerRow) { throw new FileNotFoundException( "No columns defined in given CSV file." + "Please check the CSV file format."); } String questionmarks = StringUtils.repeat("?,", headerRow.length); questionmarks = (String) questionmarks.subSequence(0, questionmarks.length() - 1); String query = SQL_INSERT.replaceFirst(TABLE_REGEX, tableName); query = query.replaceFirst(KEYS_REGEX, StringUtils.join(headerRow, ",")); query = query.replaceFirst(VALUES_REGEX, questionmarks); System.out.println("Query: " + query); String[] nextLine; Connection con = null; PreparedStatement ps = null; try { con = this.connection; con.setAutoCommit(false); ps = con.prepareStatement(query); if (truncateBeforeLoad) { //delete data from table before loading csv con.createStatement().execute("DELETE FROM " + tableName); } final int batchSize = 1000; int count = 0; Date date = null; while ((nextLine = csvReader.readNext()) != null) { if (null != nextLine) { int index = 1; for (String string : nextLine) { date = DateUtils.stringToDate(string); if (null != date) { ps.setDate(index++, new java.sql.Date(date.getTime())); } else { ps.setString(index++, string); } } ps.addBatch(); } if (++count % batchSize == 0) { ps.executeBatch(); } } ps.executeBatch(); // insert remaining records con.commit(); } catch (Exception e) { con.rollback(); e.printStackTrace(); throw new Exception("Error occured while loading data from file to database." + e.getMessage()); } finally { if (null != ps) ps.close(); if (null != con) con.close(); csvReader.close(); } }
From source file:org.rti.zcore.dar.dao.InventoryDAO.java
/** * For each stock item, there are StockReport values for BalanceBF(balance), OnHand(balance), * AdditionsTotal(stockControlAdditionsTotal);DeletionsTotal(stockControlDeletionsTotal), and TotalDispensed(dispensed). * @param conn//from ww w . jav a2 s.co m * @param siteId - runs query for all sites if siteId = null or 0 * @param endDate - if calculating Balance Brought forward (BBF); null is getting current Balance. * @return HashMap<Long,StockReport> for each stock item. * @throws ClassNotFoundException * @throws IOException * @throws ServletException * @throws SQLException * @throws ObjectNotFoundException */ public static HashMap<Long, StockReport> getBalanceMap(Connection conn, Integer siteId, Date beginDate, Date endDate) throws ClassNotFoundException, IOException, ServletException, SQLException, ObjectNotFoundException { Integer stockControlAdditionsTotal = 0; Integer stockControlDeletionsTotal = 0; Integer dispensed = 0; Long itemId = null; StockReport stockReport = null; log.debug(" this is date " + endDate); //if (itemId == 16 || itemId == 12 || itemId == 13 || itemId == 1 || itemId == 3) { HashMap<Long, StockReport> map = new HashMap<Long, StockReport>(); String datePartStock = ""; String datePartEncounter = ""; if (endDate != null) { datePartStock = "AND date_of_record < ? "; datePartEncounter = "AND encounter.date_visit < ? "; } String siteIdPart = "AND site_id = ? "; if (siteId == null || siteId == 0) { siteIdPart = ""; } log.debug(" we are sitePart ID"); /* if (endDate != null) { String sql = "SELECT item_id, MIN(encounter.date_visit) " + "FROM patient_item, encounter, patient " + "WHERE encounter.id = patient_item.encounter_id AND encounter.patient_id = patient.id " + siteIdPart; PreparedStatement ps = conn.prepareStatement(sql); if (siteId == null || siteId == 0) { if (endDate != null) { ps.setDate(1, endDate); } } else { ps.setInt(1, siteId); if (endDate != null) { ps.setDate(2, endDate); } } }*/ String sql = "SELECT item_id, 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) " + siteIdPart + datePartStock + // "AND item_id <= 20 " + "GROUP BY item_id"; PreparedStatement ps = conn.prepareStatement(sql); log.debug(" Ps1 Sql created " + endDate); if (siteId == null || siteId == 0) { log.debug(" site id is null ama 0"); if (endDate != null) { log.debug(" date is available"); ps.setDate(1, endDate); } log.debug(" we passeddate issue"); } else { log.debug(" site id is this " + siteId); ps.setInt(1, siteId); if (endDate != null) { ps.setDate(2, endDate); } } ResultSet rs = ps.executeQuery(); log.debug(" rs was excecuted"); while (rs.next()) { itemId = rs.getLong("item_id"); log.debug(" we have itemid " + itemId); stockControlAdditionsTotal = rs.getInt("stockControlAdditionsTotal"); log.debug(" we have controlTotal " + stockControlAdditionsTotal); if (map.get(itemId) == null) { stockReport = new StockReport(); log.debug(" we ahave loaded stockreport "); } else { stockReport = (StockReport) map.get(itemId); } stockReport.setAdditionsTotal(stockControlAdditionsTotal); map.put(itemId, stockReport); //here1 log.debug(" we have put on map "); } sql = "SELECT item_id, 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) " + siteIdPart + datePartStock + //"AND item_id <= 20 " + "GROUP BY item_id"; ps = conn.prepareStatement(sql); if (siteId == null || siteId == 0) { if (endDate != null) { log.debug(" date2 is available"); ps.setDate(1, endDate); } } else { ps.setInt(1, siteId); if (endDate != null) { ps.setDate(2, endDate); } } rs = ps.executeQuery(); log.debug(" rs2 was excecuted"); while (rs.next()) { itemId = rs.getLong("item_id"); log.debug(" we have itemId2 " + itemId); stockControlDeletionsTotal = rs.getInt("stockControlDeletionsTotal"); log.debug(" wths isdeletion total " + stockControlDeletionsTotal); if (map.get(itemId) == null) { log.debug(" mapitem id is null " + itemId); stockReport = new StockReport(); } else { stockReport = (StockReport) map.get(itemId); log.debug(" we loaded stock report"); } stockReport.setDeletionsTotal(stockControlDeletionsTotal); log.debug(" detelitiontotal2 was set"); //here map.put(itemId, stockReport); //here1 log.debug(" we mapped itemid2"); } // rs.close(); String encounterSiteIdPart = "AND encounter.site_id = ? "; if (siteId == null || siteId == 0) { log.debug(" site id 3 is null or 0"); encounterSiteIdPart = ""; } log.debug(" the encounter site id " + encounterSiteIdPart); //do not enable this //sql="CREATE INDEX MYpatient_item ON patient_item(item_id)"; //ps = conn.prepareStatement(sql); //ps.executeUpdate(); log.debug(" the index was created "); sql = "SELECT item_id, SUM(patient_item.dispensed) AS dispensed " + "FROM patient_item, encounter, patient " + "WHERE encounter.id = patient_item.encounter_id " + "AND encounter.patient_id = patient.id " + encounterSiteIdPart + datePartEncounter + //"AND item_id <= 20 " + "GROUP BY item_id"; ps = conn.prepareStatement(sql); log.debug(" ps4 was created " + sql); if (siteId == null || siteId == 0) { log.debug(" site id 4 is null or 0 " + endDate); if (endDate != null) { log.debug(" date4 is not null " + endDate); ps.setDate(1, endDate); log.debug(" we set the date"); } } else { log.debug(" site id has something >0"); ps.setInt(1, siteId); if (endDate != null) { ps.setDate(2, endDate); } } try { //log.debug(" we are bout to Rs Ps "+ps.getFetchSize()); log.debug(" we are bout to Rs Ps "); //rs = ps.executeQuery(); rs = ps.executeQuery(); log.debug(" rs querry was created "); while (rs.next()) { log.debug(" rs querry excecuted and returning resulting result set "); itemId = rs.getLong("item_id"); dispensed = rs.getInt("dispensed"); log.debug(" item id from query: " + itemId + "quantity dispensed : " + dispensed); if (map.get(itemId) == null) { stockReport = new StockReport(); } else { stockReport = (StockReport) map.get(itemId); } stockReport.setTotalDispensed(dispensed); map.put(itemId, stockReport); } rs.close(); } // catch (SQLException e) { // TODO Auto-generated catch block log.debug(" this is the sql beats: " + e); e.printStackTrace(); } log.debug(" db operation ok"); Set encSet = map.entrySet(); for (Iterator iterator = encSet.iterator(); iterator.hasNext();) { Map.Entry entry = (Map.Entry) iterator.next(); Long key = (Long) entry.getKey(); stockReport = (StockReport) entry.getValue(); Integer additionsTotal = 0; Integer deletionsTotal = 0; Integer totalDispensed = 0; if (stockReport.getAdditionsTotal() != null) { additionsTotal = stockReport.getAdditionsTotal(); } if (stockReport.getDeletionsTotal() != null) { deletionsTotal = stockReport.getDeletionsTotal(); } if (stockReport.getTotalDispensed() != null) { totalDispensed = stockReport.getTotalDispensed(); } Integer balance = additionsTotal - (deletionsTotal + totalDispensed); stockReport.setBalanceBF(balance); stockReport.setOnHand(balance); } return map; }
From source file:org.rti.zcore.dar.report.ZEPRSUtils.java
/** * @param patientID The id associated with this patient * @param beginDate The first date of the report's time frame * @param endDate The last date of the report's time frame * @return Returns all Encounters associated with this patient during the time frame * @throws ServletException// www. j a va 2 s. co m */ protected static ResultSet getPatientEncounters(int patientID, Date beginDate, Date endDate, int siteID) throws ServletException { Connection conn = null; ResultSet rs = null; try { conn = getZEPRSConnection(); // Retrieve all Encounter records for this form String sql = "SELECT * FROM encounter WHERE patient_id = ? " + "AND date_visit >= ? AND date_visit <= ? AND site_id = ? ORDER BY date_visit"; PreparedStatement ps = conn.prepareStatement(sql); ps.setInt(1, patientID); ps.setDate(2, beginDate); ps.setDate(3, endDate); ps.setInt(4, siteID); rs = ps.executeQuery(); } catch (Exception ex) { throw new ServletException("Cannot retrieve database connection", ex); } return rs; }
From source file:nl.tudelft.stocktrader.mysql.MySQLCustomerDAO.java
public boolean insertAccount(Account accountBean) throws DAOException { PreparedStatement insertAccount = null; boolean insertSuccess = false; try {/*w w w . j ava2 s . com*/ insertAccount = sqlConnection.prepareStatement(SQL_INSERT_ACCOUNT); insertAccount.setBigDecimal(1, accountBean.getOpenBalance()); insertAccount.setInt(2, accountBean.getLogoutCount()); insertAccount.setBigDecimal(3, accountBean.getBalance()); insertAccount.setDate(4, StockTraderUtility.convertToSqlDate(accountBean.getLastLogin())); insertAccount.setInt(5, accountBean.getLoginCount()); insertAccount.setString(6, accountBean.getUserID()); insertAccount.executeUpdate(); insertSuccess = true; } catch (SQLException e) { insertSuccess = false; throw new DAOException("", e); } finally { if (insertAccount != null) { try { insertAccount.close(); } catch (SQLException e) { logger.debug("", e); } } } return insertSuccess; }
From source file:org.rti.zcore.dar.report.ZEPRSUtils.java
/** * Fetches date_next_appt value from routineante table * @param conn//from ww w . j a va 2 s .com * @param patientId * @param dateVisit * @return ResultSet */ protected static ResultSet getNextAncVisitDate(Connection conn, Long patientId, Date dateVisit) { ResultSet rs = null; String condition = ""; try { String sql = "SELECT date_next_appt\n" + "FROM zeprs.routineante, zeprs.encounter\n" + "WHERE encounter.id = routineante.id\n" + "AND encounter.patient_id = ?\n" + "AND encounter.date_visit = ?\n" + "LIMIT 1;\n"; PreparedStatement ps = conn.prepareStatement(sql); ps.setInt(1, patientId.intValue()); ps.setDate(2, dateVisit); rs = ps.executeQuery(); } catch (Exception ex) { log.error(ex); } return rs; }
From source file:com.thinkmore.framework.orm.hibernate.SimpleHibernateDao.java
public void setParameters(PreparedStatement ps, int j, Object value) throws SQLException { if (value != null) { if (value instanceof java.lang.Integer) { ps.setInt(j, (Integer) value); } else if (value instanceof java.lang.Long) { ps.setLong(j, (Long) value); } else if (value instanceof java.util.Date) { ps.setTimestamp(j, new java.sql.Timestamp(((Date) value).getTime())); } else if (value instanceof java.sql.Date) { ps.setDate(j, new java.sql.Date(((Date) value).getTime())); } else if (value instanceof java.lang.String) { ps.setString(j, value.toString()); } else if (value instanceof java.lang.Double) { ps.setDouble(j, (Double) value); } else if (value instanceof java.lang.Byte) { ps.setByte(j, (Byte) value); } else if (value instanceof java.lang.Character) { ps.setString(j, value.toString()); } else if (value instanceof java.lang.Float) { ps.setFloat(j, (Float) value); } else if (value instanceof java.lang.Boolean) { ps.setBoolean(j, (Boolean) value); } else if (value instanceof java.lang.Short) { ps.setShort(j, (Short) value); } else {//from w w w.j ava 2 s . c o m ps.setObject(j, value); } } else { ps.setNull(j, Types.NULL); } }
From source file:ui.Analyze.java
private Number getRugi(LocalDate l2) throws SQLException { double dou = 0; java.sql.PreparedStatement p = d.getPS("select sum(sat*jum)as oke from memasok where tgl=?"); p.setDate(1, Date.valueOf(l2)); java.sql.ResultSet r = p.executeQuery(); if (r.next()) dou = r.getDouble("oke"); r.close();//from w ww .j a v a2 s.c o m p.close(); return dou; }
From source file:org.rti.zcore.dar.report.ZEPRSUtils.java
/** * This can accept 0 for siteId, which happens when user selects "All sites" in reports. * @param formID The id of the form//from w ww.jav a 2 s. c o m * @param sql sql for the query * @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 patient Encounters pertaining * to this form during the time frame. Orders by date_visit, which is useful for monthly reports */ protected static ResultSet getEncounters(String sql, int formID, Date beginDate, Date endDate, int siteID, Connection conn) { ResultSet rs = null; try { PreparedStatement ps = conn.prepareStatement(sql); ps.setString(1, Integer.toString(formID)); ps.setDate(2, beginDate); ps.setDate(3, endDate); if (siteID != 0) { ps.setInt(4, siteID); } rs = ps.executeQuery(); } catch (Exception ex) { log.error(ex); } return rs; }
From source file:org.rti.zcore.dar.report.ZEPRSUtils.java
/** * @param formID The id of the form/* w ww.j a v a 2 s . c o m*/ * @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 Specify groupby * @param conn * @return Returns a ResultSet containing all patient Encounters pertaining * to this form during the time frame */ protected static ResultSet getEncountersGroupBy(int formID, String table, Date beginDate, Date endDate, int siteID, String groupBy, Connection conn) { ResultSet rs = null; // Retrieve all Encounter records for this form try { String sql = "SELECT * FROM encounter," + table + " WHERE encounter.id = " + table + ".id\n" + "AND form_id = ? AND date_visit >= ? AND date_visit <= ? AND site_id = ?\n" + "GROUP BY " + groupBy + " ORDER BY " + groupBy; 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(); } catch (SQLException e) { e.printStackTrace(); } return rs; }