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:edu.harvard.i2b2.analysis.security.HighEncryption.java

public boolean setDatabaseKey(Connection sConn, String masterKey, String sFilename, String newKey, String token,
        String username) throws Exception {
    if (token == null)
        token = "";

    String eNewKey = cipher.encrypt(newKey);

    if (!newKey.equals(cipher.decrypt(eNewKey)))
        throw new Exception("Error verifying decrpyt is eqal to encrypt");

    PreparedStatement setKey = sConn
            .prepareStatement("INSERT INTO key (name, description, token, userinserted, dateinserted) VALUES "
                    + "(?, ?, ?, ?, ?)");
    setKey.setString(1, sFilename);//from  w ww . ja v  a 2 s  . co m
    setKey.setString(2, eNewKey);
    setKey.setString(3, token);
    setKey.setString(4, username);
    setKey.setDate(5, new java.sql.Date(new java.util.Date().getTime()));

    int count = setKey.executeUpdate();
    if (count == 1)
        return true;
    else
        return false;
}

---

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/*w  ww  . j  a v a  2 s. 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 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
 * @throws ServletException
 */
protected static ResultSet getEncounters(int formID, String table, Date beginDate, Date endDate, int siteID,
        Connection conn) throws ServletException {

    ResultSet rs = null;

    try {

        // Retrieve all Encounter records for this form
        String sql = "SELECT * FROM encounter," + table + " WHERE encounter.id = " + table + ".id "
                + "AND form_id = ? AND date_visit >= ? AND date_visit <= ? ";
        if (siteID != 0) {
            sql = sql + "AND site_id = ?";
        }
        sql = sql + " ORDER BY date_visit";
        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

/**
 * This is a special version of this query appropriate for forms like safe motherhood that have many date entry fields.
 *
 * @param formID    The id of the form/*from w  ww. jav 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 dateField The date field to use for the query
 * @param conn
 * @return Returns a ResultSet containing all patient Encounters pertaining
 *         to this form during the time frame.
 * @throws ServletException
 */
protected static ResultSet getEncounters(int formID, String table, Date beginDate, Date endDate, int siteID,
        String dateField, 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 " + table + "." + dateField + ">= ?" + "AND " + table + "."
                + dateField + "<= ?" + "AND site_id = ? " + "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();
    } catch (Exception ex) {
        // throw new ServletException("Cannot retrieve database connection", ex);
        log.error(ex);
    }

    return rs;
}

---

From source file:com.cedarsoftware.ncube.NCubeManager.java

/**
 * Retrieve all n-cubes that have a name that matches the SQL like statement, within the specified app, status,
 * version, and system date.//from  ww w .  jav  a  2 s.  com
 */
public static Object[] getNCubes(Connection connection, String app, String version, String status,
        String sqlLike, Date sysDate) {
    validate(connection, app, version);
    validateStatus(status);

    if (sqlLike == null) {
        sqlLike = "%";
    }

    if (sysDate == null) {
        sysDate = new Date();
    }

    PreparedStatement stmt = null;
    try {
        java.sql.Date systemDate = new java.sql.Date(sysDate.getTime());
        stmt = connection.prepareStatement(
                "SELECT n_cube_id, n_cube_nm, notes_bin, version_no_cd, status_cd, app_cd, create_dt, update_dt, "
                        + "create_hid, update_hid, sys_effective_dt, sys_expiration_dt, business_effective_dt, business_expiration_dt FROM n_cube WHERE n_cube_nm LIKE ? AND app_cd = ? AND version_no_cd = ? AND status_cd = ? AND sys_effective_dt <= ? AND (sys_expiration_dt IS NULL OR sys_expiration_dt >= ?)");
        stmt.setString(1, sqlLike);
        stmt.setString(2, app);
        stmt.setString(3, version);
        stmt.setString(4, status);
        stmt.setDate(5, systemDate);
        stmt.setDate(6, systemDate);

        ResultSet rs = stmt.executeQuery();
        List<NCubeInfoDto> records = new ArrayList<NCubeInfoDto>();

        while (rs.next()) {
            NCubeInfoDto dto = new NCubeInfoDto();
            dto.id = Long.toString(rs.getLong("n_cube_id"));
            dto.name = rs.getString("n_cube_nm");
            byte[] notes = rs.getBytes("notes_bin");
            dto.notes = new String(notes == null ? "".getBytes() : notes, "UTF-8");
            dto.version = rs.getString("version_no_cd");
            dto.status = rs.getString("status_cd");
            dto.app = rs.getString("app_cd");
            dto.createDate = rs.getDate("create_dt");
            dto.updateDate = rs.getDate("update_dt");
            dto.createHid = rs.getString("create_hid");
            dto.updateHid = rs.getString("update_hid");
            dto.sysEffDate = rs.getDate("sys_effective_dt");
            dto.sysEndDate = rs.getDate("sys_expiration_dt");
            dto.bizEffDate = rs.getDate("business_effective_dt");
            dto.bizExpDate = rs.getDate("business_expiration_dt");
            records.add(dto);
        }
        return records.toArray();
    } catch (Exception e) {
        String s = "Unable to fetch NCubes matching '" + sqlLike + "' from database";
        LOG.error(s, e);
        throw new RuntimeException(s, e);
    } finally {
        jdbcCleanup(stmt);
    }
}

---

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

/**
 * Gets encounters for mother only.//from  w  w w.ja v a  2s .  co  m
 * This can accept 0 for siteId, which happens when user selects "All sites" in reports.
 * @param formID    The id of the form
 * @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 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
 * @throws ServletException
 */
protected static ResultSet getEncountersMother(int formID, String table, Date beginDate, Date endDate,
        int siteID, Connection conn) throws ServletException {

    ResultSet rs = null;

    try {

        // Retrieve all Encounter records for this form
        String sql = "SELECT * FROM patient, encounter," + table + " " + "WHERE encounter.id = " + table
                + ".id " + "AND encounter.patient_id = patient.id " + "AND patient.parent_id IS NULL "
                + "AND form_id = ? AND date_visit >= ? AND date_visit <= ? ";
        if (siteID != 0) {
            sql = sql + "AND encounter.site_id = ?";
        }
        sql = sql + " ORDER BY date_visit";
        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

/**
 * Gets encounters for children only.//from w  w w.j  av a  2 s.c o m
 * This can accept 0 for siteId, which happens when user selects "All sites" in reports.
 * @param formID    The id of the form
 * @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 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
 * @throws ServletException
 */
protected static ResultSet getEncountersChildren(int formID, String table, Date beginDate, Date endDate,
        int siteID, Connection conn) throws ServletException {

    ResultSet rs = null;

    try {

        // Retrieve all Encounter records for this form
        String sql = "SELECT * FROM patient, encounter," + table + " " + "WHERE encounter.id = " + table
                + ".id " + "AND encounter.patient_id = patient.id " + "AND patient.parent_id IS NOT NULL "
                + "AND form_id = ? AND date_visit >= ? AND date_visit <= ? ";
        if (siteID != 0) {
            sql = sql + "AND encounter.site_id = ?";
        }
        sql = sql + " ORDER BY date_visit";
        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

/**
 * This can accept 0 for siteId, which happens when user selects "All sites" in reports.
 * @param formID    The id of the form// w  ww  .j a  va2  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   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 getEncounters(int formID, String table, Date beginDate, Date endDate, int siteID,
        Connection conn, boolean groupBy) 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 "
                + "AND form_id = ? AND date_visit >= ? AND date_visit <= ? ";
        if (siteID != 0) {
            sql = sql + "AND site_id = ?";
        }
        if (groupBy) {
            sql = sql + " GROUP BY patient_id";
        }
        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) {
        throw new ServletException("Cannot retrieve database connection", ex);
    }
    return rs;
}

---

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

public void storeData(String stockName, DateTime date, BigDecimal value, String type) {
    PreparedStatement pstm = null;
    Connection connection = null;
    try {//from w w w  .j ava  2s  . c o m

        String table = this.normTableName(stockName);
        connection = this.getConnection();
        //upsert
        this.createTable(connection, table);

        String query = "MERGE INTO " + table + " (ID,DATE," + type + ") VALUES((SELECT ID FROM " + table
                + " ID WHERE DATE=?), ?, ?)";
        pstm = connection.prepareStatement(query);

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

        System.out.printf("Inserting :%f :%s time:%s\n", value.doubleValue(), stockName,
                date.toDate().toString());
        pstm.setDouble(3, value.doubleValue());
        pstm.execute();

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

    }

}

---

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

/**
 * Fetches the most recent patient regimen during the date range.
 * @param conn//w  w w .  ja v  a2 s . c o m
 * @param patientId
 * @param beginDate
 * @param endDate
 * @return
 * @throws ServletException
 */
public static ResultSet getPatientArtRegimen(Connection conn, Long patientId, Date beginDate, Date endDate)
        throws ServletException {
    ResultSet rs = null;
    String dateRange = "AND date_visit >= ? AND date_visit <= ? ";
    if (endDate == null) {
        dateRange = "AND date_visit = ?";
    }
    try {
        String sql = "SELECT encounter.id AS id, regimen.code AS code, regimen.name AS name, regimen.id AS regimenId "
                + "FROM art_regimen, encounter, regimen  " + "WHERE encounter.id = art_regimen.id "
                + "AND art_regimen.regimen_1 = regimen.id " + "AND encounter.patient_id = ? " + dateRange
                + " ORDER BY encounter.id DESC";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setMaxRows(1);
        ps.setLong(1, patientId);
        ps.setDate(2, beginDate);
        if (endDate != null) {
            ps.setDate(3, endDate);
        }
        rs = ps.executeQuery();
    } catch (Exception ex) {
        log.error(ex);
    }
    return rs;
}

---

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

/**
 * No pregnancyId or siteId//ww w .j  av  a  2s. c o m
 * @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
 * @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 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"
                + "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);

        rs = ps.executeQuery();
    } catch (Exception ex) {
        ex.printStackTrace();
    }
    return rs;
}

---

• «
• 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
• »