Example usage for java.sql PreparedStatement setDate

Introduction

In this page you can find the example usage for java.sql PreparedStatement setDate.

Prototype

void setDate(int parameterIndex, java.sql.Date x) throws SQLException;

Source Link

Document

Sets the designated parameter to the given java.sql.Date value using the default time zone of the virtual machine that is running the application.

Usage

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 conn/*  w ww.j a  v  a  2 s . c  o  m*/
 * @return Returns a ResultSet containing patient_ids representing all
 *         patients who had at least one Encounter during the time frame
 * @throws ServletException
 */
protected static ResultSet getUniqueVisits(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 for mothers

        if (siteID == 0) {
            String sql = "SELECT DISTINCT patient_id FROM encounter, patient "
                    + "WHERE encounter.patient_id = patient.id 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 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;
}

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 conn/*from   w  ww. j av a 2  s  . co m*/
 * @return Returns a ResultSet containing patient_ids representing all
 *         patients who delivered during the time frame
 * @throws ServletException
 */
protected static ResultSet getDeliveredMothers(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 for mothers

        if (siteID == 0) {
            String sql = "SELECT DISTINCT patient_id FROM encounter, patient "
                    + "WHERE encounter.patient_id = patient.id AND date_visit >= ? "
                    + "AND date_visit <= ? AND parent_id is null " + "AND encounter.form_id=66";
            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 date_visit >= ? "
                    + "AND date_visit <= ? AND parent_id is null AND encounter.site_id = ? "
                    + "AND encounter.form_id=66";
            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;
}

From source file:org.rti.zcore.dar.report.ZEPRSUtils.java

/**
 * Fetch a list of scheduled visits that fall between this range.
 * @param beginDate//from  w  w w .ja v  a  2 s .co  m
 * @param endDate
 * @param siteID
 * @param conn
 * @return
 * @throws ServletException
 */
protected static ResultSet getScheduledVisits(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 for mothers

        if (siteID == 0) {
            String sql = "SELECT DISTINCT patient_id, appointment_date "
                    + "FROM encounter, patient, appointment " + "WHERE encounter.patient_id = patient.id "
                    + "AND appointment.id=encounter.id " + "AND appointment_date >= ? "
                    + "AND appointment_date <= ? " + "ORDER BY appointment_date ASC";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setDate(1, beginDate);
            ps.setDate(2, endDate);
            rs = ps.executeQuery();
        } else {
            String sql = "SELECT DISTINCT patient_id, appointment_date "
                    + "FROM encounter, patient, appointment " + "WHERE encounter.patient_id = patient.id "
                    + "AND appointment.id=encounter.id " + "AND appointment_date >= ? "
                    + "AND appointment_date <= ? AND encounter.site_id = ? " + "ORDER BY appointment_date ASC";
            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;
}

From source file:org.jtotus.database.LocalJDBC.java

public BigDecimal fetchData(String tableName, DateTime date, String column) {
    BigDecimal retValue = null;//from  ww  w .  j  a  v a 2  s  . c om
    PreparedStatement pstm = null;
    Connection connection = null;
    ResultSet results = null;

    try {
        connection = getConnection();
        String statement = "SELECT " + column + " FROM " + this.normTableName(tableName) + " WHERE DATE=?";

        this.createTable(connection, this.normTableName(tableName));

        pstm = connection.prepareStatement(statement);

        java.sql.Date sqlDate = new java.sql.Date(date.getMillis());
        pstm.setDate(1, sqlDate);

        if (debug) {
            System.out.printf("Fetching:'%s' from'%s' Time" + date.toDate() + " Stm:%s\n", column, tableName,
                    statement);
        }

        results = pstm.executeQuery();

        //            System.out.printf("Results:%d :%d :%s (%d)\n",results.getType(), results.findColumn(column), results.getMetaData().getColumnLabel(1),java.sql.Types.DOUBLE);

        if (results.next()) {
            retValue = results.getBigDecimal(column);
        }

    } catch (SQLException ex) {
        System.err.printf("LocalJDBC Unable to find date for:'%s' from'%s' Time" + date.toDate() + "\n", column,
                tableName);
        //   Logger.getLogger(LocalJDBC.class.getName()).log(Level.SEVERE, null, ex);
    } finally {
        try {
            if (results != null) {
                results.close();
                results = null;
            }

            if (pstm != null) {
                pstm.close();
                pstm = null;
            }

            if (connection != null) {
                connection.close();
                connection = null;
            }
        } catch (SQLException ex) {
            Logger.getLogger(LocalJDBC.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    return retValue;
}

From source file:risk_mgnt_manager.StressCSVLoader.java

/**
 * Parse CSV file using OpenCSV library and load in 
 * given database table. /*from ww  w  . j a  va  2 s. c o  m*/
 * @param csvFile Input CSV file
 * @param tableName Database table name to import data
 * @throws Exception
 */
public void loadCSV(String csvFile, String tableName, String filename) 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 col_Names = "Point_in_Time, Portfolio, Fut_Variation, Opt_Variation, Total_Variation, Percent_of_Ledger_Balance, Ledger_Balance";

    String query = SQL_INSERT.replaceFirst(TABLE_REGEX, tableName);
    //query = query.replaceFirst(KEYS_REGEX, StringUtils.join(headerRow, ","));
    query = query.replaceFirst(KEYS_REGEX, col_Names);
    query = query.replaceFirst(VALUES_REGEX, questionmarks);

    String update = "UPDATE mgex_riskmgnt." + tableName + " SET Imported_File_Name = '" + filename + "' Where "
            + "Imported_File_Name IS NULL";

    System.out.println("Query: " + query);
    System.out.println("Update: " + update);

    String[] nextLine;
    Connection con = null;
    Connection con2 = null;
    PreparedStatement ps = null;
    PreparedStatement ps2 = null;
    try {
        con = this.connection;
        con.setAutoCommit(false);
        ps = con.prepareStatement(query);

        con2 = this.connection;
        con2.setAutoCommit(false);
        ps2 = con2.prepareStatement(update);

        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 = DateUtill.convertToDate(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
        ps2.executeUpdate();
        con.commit();
        con2.commit();
    } catch (Exception e) {
        con.rollback();
        con2.rollback();
        e.printStackTrace();
        throw new Exception("Error occured while loading data from file to database." + e.getMessage());
    } finally {
        if (null != ps)
            ps.close();
        ps2.close();
        if (null != con)
            con.close();
        con2.close();

        csvReader.close();
    }
}

From source file:org.rti.zcore.dar.report.ZEPRSUtils.java

/**
 * Get MCH mothers - not in labour, having submitted a routine ante visit
 * @param beginDate//from  w  ww  .ja v  a 2  s.c  o  m
 * @param endDate
 * @param siteID
 * @param conn
 * @return
 * @throws ServletException
 */
protected static ResultSet getMCHMothers(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 for mothers
        // No longer checking if mother is in flow_id = 7 - Labour - because problem/laobur visit is in that flow

        if (siteID == 0) {
            String sql = "SELECT DISTINCT patient_id FROM encounter, patient "
                    + "WHERE encounter.patient_id = patient.id "
                    + "AND patient_id NOT 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 "
                    + "ORDER BY date_visit DESC";

            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 NOT 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 = ? "
                    + "ORDER BY date_visit DESC";
            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;
}

From source file:org.rti.zcore.dar.report.ZEPRSUtils.java

/**
 * Only return mothers who have had thier first visit.
 * @param beginDate/*  w ww. jav  a2 s  .co m*/
 * @param endDate
 * @param siteID
 * @param conn
 * @return
 * @throws ServletException
 */
protected static ResultSet getMCHMothersSingleVisit(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 for mothers
        // No longer checking if mother is in flow_id = 7 - Labour - because problem/laobur visit is in that flow

        if (siteID == 0) {
            String sql = "SELECT DISTINCT patient_id, COUNT(encounter.id) AS cnt " + "FROM encounter, patient "
                    + "WHERE encounter.patient_id = patient.id "
                    + "AND patient_id NOT 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 form_id=80 "
                    + "GROUP BY encounter.patient_id";

            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setDate(1, beginDate);
            ps.setDate(2, endDate);
            rs = ps.executeQuery();
        } else {
            String sql = "SELECT DISTINCT patient_id, COUNT(encounter.id) AS cnt " + "FROM encounter, patient "
                    + "WHERE encounter.patient_id = patient.id "
                    + "AND patient_id NOT 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 = ? " + "AND form_id=80 "
                    + "GROUP BY encounter.patient_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;
}

From source file:risk_mgnt_manager.CSVLoader.java

/**
 * Parse CSV file using OpenCSV library and load in 
 * given database table. // w  ww.  j a  va 2  s  .  c o  m
 * @param csvFile Input CSV file
 * @param table Database table name to import data
 * @param col_Names
 * @param id
 * @param header
 */
public void loadCSV(String csvFile, String table, String col_Names, int id, boolean header) 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;

    if (header == true) {
        headerRow = csvReader.readNext();
    } else {
        headerRow = col_Names.split(",");
    }

    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, table);
    //System.out.println(header);

    //query = query.replaceFirst(KEYS_REGEX, StringUtils.join(headerRow, ","));
    query = query.replaceFirst(KEYS_REGEX, col_Names);
    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 (id == 1) {
            //delete data from table before loading csv
            con.createStatement().execute("DELETE FROM " + table);
        }

        final int batchSize = 4096;
        int count = 0;
        Date date = null;
        while ((nextLine = csvReader.readNext()) != null) {

            if (null != nextLine) {
                int index = 1;
                for (String string : nextLine) {
                    //date = DateUtill.convertToDate(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:ui.Analyze.java

private Number getUntung(LocalDate l2) throws SQLException {
    org.joda.money.Money m = org.joda.money.Money.zero(CurrencyUnit.of("IDR"));
    java.sql.PreparedStatement p = d.getPS("select total from jual where tgl=?");
    p.setDate(1, Date.valueOf(l2));
    java.sql.ResultSet r = p.executeQuery();
    while (r.next())
        m = m.plus(org.joda.money.Money.parse(r.getString("total")));
    r.close();/* w ww . ja v  a2  s  .  c o  m*/
    p.close();
    return m.getAmount().longValue();
}

From source file:consultor.CSVLoader.java

/**
 * Parse CSV file using OpenCSV library and load in 
 * given database table. //www  .j  a  v a  2s.  co 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 = DateUtil.convertToDate(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();
    }
}