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.ZEPRSSharedItems.java

protected static int getTotalMaternalDeathsUTH(Date beginDate, Date endDate, Connection conn) {
    ResultSet rs = null;//from   w w  w.j av  a  2s . c  o  m
    int count = 0;
    try {

        String sql = "SELECT COUNT(DISTINCT(encounter.patient_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_death >= ? " + "AND date_death <= ? " + "AND patient.parent_id IS NULL "
                + "AND site_type_id = 2 " + "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;
}

---

From source file:las.DBConnector.java

/**
 * Load CSV test data into SQL Table//from   w  w w  .  j a  v a 2 s .c  o  m
 *
 * example for clearFirst: boolean check =
 * DBConnector.checkDataExistedInTable("MEMBERS");
 *
 * if (check) { DBConnector.loadCSVIntoTable("src/resources/members.csv",
 * "MEMBERS", true); System.out.println("Test data inserted into MEMBERS
 * table"); }
 *
 * ignore createNewReader, since it uses for loadCSVIntoTable, don't modify
 * it
 *
 * Getter and Setter provided for Separator to set your own separator inside
 * your CSV File
 *
 * @param csvFile : src/resources/xxx.csv (put your csv file under this
 * path)
 * @param tableName: TABLENAME (All in capital letters)
 * @param clearFirst true = if data not existed in SQL Table, write test
 * data inside false = if data exisited in SQL Table, don't write again.
 * @throws java.lang.Exception
 */
public static void loadCSVIntoTable(String csvFile, String tableName, boolean clearFirst) throws Exception {

    CSVReader csvReader = null;
    if (null == DBConnector.conn) {
        throw new Exception("Not a valid connection.");
    }
    try {

        csvReader = DBConnector.getInstance().createNewReader(csvFile);

    } catch (ClassNotFoundException | SQLException | FileNotFoundException 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);

    String[] nextLine;
    PreparedStatement ps = null;
    try {
        conn.setAutoCommit(false);
        ps = conn.prepareStatement(query);

        if (clearFirst) {
            //delete data from table before loading csv
            conn.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
        conn.commit();
    } catch (SQLException e) {
        conn.rollback();
        e.printStackTrace();
        throw new Exception("Error occured while loading data from file to database." + e.getMessage());
    } finally {
        if (null != ps) {
            ps.close();
        }

        csvReader.close();
    }
}

---

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

protected static ResultSet getNeonatalDeaths(Date beginDate, Date endDate, int siteID, Connection conn) {
    ResultSet rs = null;//from   w ww  . ja  v  a 2  s  .c  om

    try {
        // Retrieve all Encounter records for this form for mothers

        if (siteID == 0) {
            String sql = "SELECT DISTINCT encounter.patient_id, patient.parent_id, encounter.pregnancy_id, labour_admission_encounter_id "
                    + "FROM encounter, patient, pregnancy " + "WHERE encounter.patient_id = patient.id "
                    + "AND encounter.pregnancy_id = pregnancy.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();
        } else {
            String sql = "SELECT DISTINCT encounter.patient_id, patient.parent_id, encounter.pregnancy_id, labour_admission_encounter_id "
                    + "FROM encounter, patient, pregnancy " + "WHERE encounter.patient_id = patient.id "
                    + "AND encounter.pregnancy_id = pregnancy.id " + "AND date_visit >= ? "
                    + "AND date_visit <= ? " + "AND patient.parent_id IS NOT NULL "
                    + "AND encounter.site_id = ? " + "AND dead=1";
            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.ZEPRSSharedItems.java

protected static int getTotalDeaths(Date beginDate, Date endDate, int siteID, Connection conn) {
    ResultSet rs = null;//from   w  w  w  . ja v  a2  s.  c o  m
    int count = 0;
    try {
        // Retrieve all Encounter records for this form for mothers

        if (siteID == 0) {
            String sql = "SELECT COUNT(DISTINCT(encounter.patient_id), encounter.pregnancy_id, labour_admission_encounter_id) "
                    + "FROM encounter, patient, pregnancy " + "WHERE encounter.patient_id = patient.id "
                    + "AND encounter.pregnancy_id = pregnancy.id " + "AND date_death >= ? "
                    + "AND date_death <= ? " + "AND dead=1";

            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setDate(1, beginDate);
            ps.setDate(2, endDate);
            rs = ps.executeQuery();
        } else {
            String sql = "SELECT COUNT(DISTINCT(encounter.patient_id), encounter.pregnancy_id, labour_admission_encounter_id) "
                    + "FROM encounter, patient, pregnancy " + "WHERE encounter.patient_id = patient.id "
                    + "AND encounter.pregnancy_id = pregnancy.id " + "AND date_death >= ? "
                    + "AND date_death <= ? " + "AND encounter.site_id = ? " + "AND dead=1";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setDate(1, beginDate);
            ps.setDate(2, endDate);
            ps.setInt(3, siteID);
            rs = ps.executeQuery();
        }
        while (rs.next()) {
            count = rs.getInt(1);
        }

    } catch (Exception ex) {
        log.error(ex);
    }

    return count;
}

---

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

protected static int getTotalNeonatalDeaths(Date beginDate, Date endDate, int siteID, Connection conn) {
    ResultSet rs = null;/*from ww  w  . ja  v  a  2 s. com*/
    int count = 0;
    try {
        // Retrieve all Encounter records for this form for mothers

        if (siteID == 0) {
            String sql = "SELECT COUNT(DISTINCT(encounter.patient_id), patient.parent_id, encounter.pregnancy_id, labour_admission_encounter_id) "
                    + "FROM encounter, patient, pregnancy " + "WHERE encounter.patient_id = patient.id "
                    + "AND encounter.pregnancy_id = pregnancy.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();
        } else {
            String sql = "SELECT COUNT(DISTINCT(encounter.patient_id), patient.parent_id, encounter.pregnancy_id, labour_admission_encounter_id) "
                    + "FROM encounter, patient, pregnancy " + "WHERE encounter.patient_id = patient.id "
                    + "AND encounter.pregnancy_id = pregnancy.id " + "AND date_visit >= ? "
                    + "AND date_visit <= ? " + "AND patient.parent_id IS NOT NULL "
                    + "AND encounter.site_id = ? " + "AND dead=1";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setDate(1, beginDate);
            ps.setDate(2, endDate);
            ps.setInt(3, siteID);
            rs = ps.executeQuery();
        }
        while (rs.next()) {
            count = rs.getInt(1);
        }

    } catch (Exception ex) {
        log.error(ex);
    }

    return count;
}

---

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

protected static int getTotalMaternalDeaths(Date beginDate, Date endDate, int siteID, Connection conn) {
    ResultSet rs = null;//from   ww w  . jav a 2s  .c  om
    int count = 0;
    try {
        // Retrieve all Encounter records for this form for mothers

        if (siteID == 0) {
            String sql = "SELECT COUNT(DISTINCT(encounter.patient_id), encounter.pregnancy_id, labour_admission_encounter_id) "
                    + "FROM encounter, patient, pregnancy " + "WHERE encounter.patient_id = patient.id "
                    + "AND encounter.pregnancy_id = pregnancy.id " + "AND date_death >= ? "
                    + "AND date_death <= ? " + "AND patient.parent_id IS NULL " + "AND dead=1";

            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setDate(1, beginDate);
            ps.setDate(2, endDate);
            rs = ps.executeQuery();
        } else {
            String sql = "SELECT COUNT(DISTINCT(encounter.patient_id), encounter.pregnancy_id, labour_admission_encounter_id) "
                    + "FROM encounter, patient, pregnancy " + "WHERE encounter.patient_id = patient.id "
                    + "AND encounter.pregnancy_id = pregnancy.id " + "AND date_death >= ? "
                    + "AND date_death <= ? " + "AND patient.parent_id IS NULL " + "AND encounter.site_id = ? "
                    + "AND dead=1";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setDate(1, beginDate);
            ps.setDate(2, endDate);
            ps.setInt(3, siteID);
            rs = ps.executeQuery();
        }
        while (rs.next()) {
            count = rs.getInt(1);
        }

    } catch (Exception ex) {
        log.error(ex);
    }

    return count;
}

---

From source file:com.aurel.track.dbase.MigrateTo37.java

private static void addHistoryTransaction(PreparedStatement pstmtHistoryTransaction, int transactionID,
        Integer workItemID, Integer changedBy, Date lastEdit) {
    try {//from w  w w.  ja v a2s .com
        pstmtHistoryTransaction.setInt(1, transactionID);
        pstmtHistoryTransaction.setInt(2, workItemID);
        pstmtHistoryTransaction.setInt(3, changedBy);
        if (lastEdit == null) {
            pstmtHistoryTransaction.setDate(4, null);
        } else {
            pstmtHistoryTransaction.setTimestamp(4, new java.sql.Timestamp(lastEdit.getTime()));
        }
        pstmtHistoryTransaction.setString(5, UUID.randomUUID().toString());
        pstmtHistoryTransaction.executeUpdate();
    } catch (Exception e) {
        LOGGER.error("Adding a transaction  with transactionID " + transactionID + " workItemID " + workItemID
                + " changedBy " + changedBy + " at " + lastEdit + " failed with " + e.getMessage(), e);
        System.err.println(ExceptionUtils.getStackTrace(e));
    }
}

---

From source file:com.aurel.track.dbase.MigrateTo37.java

private static void addBaseLineChange(PreparedStatement pstmtBaseLineChange, Integer fieldChangeID,
        Integer transactionID, int fieldKey, Date newDate, Date oldDate) {
    try {//from  w  w w .j  a  v  a 2s  . c o  m
        pstmtBaseLineChange.setInt(1, fieldChangeID);
        pstmtBaseLineChange.setInt(2, fieldKey);
        pstmtBaseLineChange.setInt(3, transactionID);
        if (newDate == null) {
            pstmtBaseLineChange.setDate(4, null);
        } else {
            pstmtBaseLineChange.setDate(4, new java.sql.Date(newDate.getTime()));
        }
        if (oldDate == null) {
            pstmtBaseLineChange.setDate(5, null);
        } else {
            pstmtBaseLineChange.setDate(5, new java.sql.Date(oldDate.getTime()));
        }
        pstmtBaseLineChange.setInt(6, ValueType.DATE);
        pstmtBaseLineChange.setString(7, UUID.randomUUID().toString());
        pstmtBaseLineChange.executeUpdate();
    } catch (SQLException e) {
        LOGGER.error("Adding a field change for base line with transactionID " + transactionID
                + " fieldChangeID " + fieldChangeID + " fieldKey " + fieldKey + " newDate " + newDate
                + " oldDate " + oldDate + " failed with " + e.getMessage(), e);
        System.err.println(ExceptionUtils.getStackTrace(e));
    }
}

---

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

protected static ResultSet getDeliveries(Date beginDate, Date endDate, int siteID, String orderBy,
        Connection conn) {/*from w  w w  . j a  va  2 s .c o  m*/

    ResultSet deliveries = null;
    /*try {
    // First, get the form id for the Delivery Summary form
    int formID = 66;
    deliveries = ZEPRSUtils.getEncountersGroupBy(formID, "deliverysum", beginDate, endDate, siteID, groupBy, conn);
    } catch (Exception e) {
    log.error(e);
    }*/

    // Retrieve all Encounter records for this form
    try {
        // First, get the form id for the Delivery Summary form
        int formID = 66;
        String sql = "SELECT * FROM encounter, deliverysum\n" + "WHERE encounter.id = deliverysum.id\n"
                + "AND form_id = ? AND date_visit >= ? AND date_visit <= ? AND site_id = ?\n" + "ORDER BY "
                + orderBy;
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, Integer.toString(formID));
        ps.setDate(2, beginDate);
        ps.setDate(3, endDate);
        ps.setInt(4, siteID);
        deliveries = ps.executeQuery();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return deliveries;
}

---

From source file:com.senior.g40.service.AccidentService.java

public boolean saveAccident(Accident acc) {
    try {/* w w w.ja v  a  2 s  .  c o  m*/
        Connection conn = ConnectionBuilder.getConnection();
        String sqlCmd = "INSERT INTO `accident` "
                + "(`userId`, `date`, `time`, `latitude`, `longtitude`, `accCode`, `forceDetect`, `speedDetect`) "
                + "VALUES " + "(?, ?, ?, ?, ?, ?, ?, ?);";
        PreparedStatement pstm = conn.prepareStatement(sqlCmd);
        pstm.setLong(1, acc.getUserId());
        pstm.setDate(2, acc.getDate());
        pstm.setString(3, acc.getTime());
        pstm.setFloat(4, acc.getLatitude());
        pstm.setFloat(5, acc.getLongtitude());
        pstm.setFloat(6, acc.getForceDetect());
        pstm.setFloat(7, acc.getSpeedDetect());
        pstm.setString(8, String.valueOf(acc.getAccCode()));
        if (pstm.executeUpdate() != 0) {
            conn.close();
            return true;
        }
    } catch (SQLException ex) {
        Logger.getLogger(AccidentService.class.getName()).log(Level.SEVERE, null, ex);
    }
    return false;
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• »