Example usage for java.sql PreparedStatement setTimestamp

List of usage examples for java.sql PreparedStatement setTimestamp

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. PreparedStatement
  6. setTimestamp

Introduction

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

Prototype

void setTimestamp(int parameterIndex, java.sql.Timestamp x) throws SQLException;

Source Link

Document

Sets the designated parameter to the given java.sql.Timestamp value.

Usage

From source file:net.duckling.ddl.service.resource.dao.ResourceDAOImpl.java

@Override
public void update(final List<Resource> res) {
    getJdbcTemplate().batchUpdate(SQL_UPDATE + BY_IDTIDTYPE, new BatchPreparedStatementSetter() {

        @Override// w  w  w  .j  a v  a  2s  .  co  m
        public void setValues(PreparedStatement ps, int index) throws SQLException {
            Resource r = res.get(index);
            int i = 0;
            ps.setString(++i, r.getTitle());
            ps.setString(++i, r.getLastEditor());
            ps.setString(++i, r.getLastEditorName());
            ps.setTimestamp(++i, new Timestamp(r.getLastEditTime().getTime()));
            ps.setInt(++i, r.getLastVersion());
            ps.setString(++i, r.getFileType());
            ps.setInt(++i, r.getBid());
            ps.setString(++i, r.getStatus());
            ps.setLong(++i, r.getSize());
            ps.setInt(++i, r.getRid());
            ps.setInt(++i, r.getTid());
            ps.setString(++i, r.getItemType());
            ps.setBoolean(i++, r.isShared());
        }

        @Override
        public int getBatchSize() {
            return res.size();
        }

    });
}

From source file:net.pms.dlna.DLNAMediaDatabase.java

public synchronized void updateThumbnail(String name, long modified, int type, DLNAMediaInfo media) {
    Connection conn = null;// w w  w  . ja  v  a 2s  .  c o m
    PreparedStatement ps = null;
    try {
        conn = getConnection();
        ps = conn.prepareStatement("UPDATE FILES SET THUMB = ? WHERE FILENAME = ? AND MODIFIED = ?");
        ps.setString(2, name);
        ps.setTimestamp(3, new Timestamp(modified));
        if (media != null) {
            ps.setBytes(1, media.getThumb());
        } else {
            ps.setNull(1, Types.BINARY);
        }
        ps.executeUpdate();
    } catch (SQLException se) {
        if (se.getErrorCode() == 23001) {
            logger.debug("Duplicate key while inserting this entry: " + name + " into the database: "
                    + se.getMessage());
        } else {
            logger.error(null, se);
        }
    } finally {
        close(ps);
        close(conn);
    }
}

From source file:at.alladin.rmbt.statisticServer.UsageJSONResource.java

private JSONObject getClassicUsage(Timestamp begin, Timestamp end) throws SQLException, JSONException {
    JSONObject returnObj = new JSONObject();
    JSONArray sums = new JSONArray();
    JSONArray values = new JSONArray();
    returnObj.put("sums", sums);
    returnObj.put("values", values);

    HashMap<String, Long> fieldSums = new HashMap<>();
    fieldSums.put("tests", new Long(0));
    fieldSums.put("clients", new Long(0));
    fieldSums.put("ips", new Long(0));

    PreparedStatement ps;
    ResultSet rs;/*from  www  . ja va2  s  .  com*/

    final String select = "date_trunc('day', time) _day, count(uid) count_tests, count(DISTINCT client_id) count_clients, count(DISTINCT client_public_ip) count_ips";
    final String where = "status='FINISHED' AND deleted=false";
    final String sql = "SELECT " + select + " FROM test WHERE " + where
            + " AND time >= ? AND time < ? GROUP BY _day ORDER BY _day ASC";
    ps = conn.prepareStatement(sql);
    ps.setTimestamp(1, begin);
    ps.setTimestamp(2, end);
    rs = ps.executeQuery();

    while (rs.next()) {
        JSONObject entry = new JSONObject();
        entry.put("day", rs.getDate("_day").getTime());

        JSONArray currentEntryValues = new JSONArray();
        entry.put("values", currentEntryValues);

        JSONObject jTests = new JSONObject();
        jTests.put("field", "tests");
        jTests.put("value", rs.getLong("count_tests"));
        currentEntryValues.put(jTests);

        JSONObject jClients = new JSONObject();
        jClients.put("field", "clients");
        jClients.put("value", rs.getLong("count_clients"));
        currentEntryValues.put(jClients);

        JSONObject jIPs = new JSONObject();
        jIPs.put("field", "ips");
        jIPs.put("value", rs.getLong("count_ips"));
        currentEntryValues.put(jIPs);

        fieldSums.put("tests", fieldSums.get("tests") + rs.getLong("count_tests"));
        fieldSums.put("clients", fieldSums.get("clients") + rs.getLong("count_clients"));
        fieldSums.put("ips", fieldSums.get("ips") + rs.getLong("count_ips"));

        //get some structure in there

        values.put(entry);
    }

    rs.close();
    ps.close();

    //add field sums
    for (String field : fieldSums.keySet()) {
        JSONObject obj = new JSONObject();
        obj.put("field", field);
        obj.put("sum", fieldSums.get(field));
        sums.put(obj);
    }

    return returnObj;
}

From source file:com.buckwa.dao.impl.excise4.Form24DaoImpl.java

@Override
public void updateStep(final String form24Id, final String nexStep) {

    try {/*from   w  ww.ja v a 2  s. c o  m*/
        final String userName = BuckWaUtils.getUserNameFromContext();
        final String fullName = BuckWaUtils.getFullNameFromContext();
        final Timestamp currentDate = new java.sql.Timestamp(System.currentTimeMillis());
        final String docNo = ExciseHelper.getDocNo();

        logger.info(" docNo:" + docNo + "  userName:" + userName + "  fuulName:" + fullName + " currentDate+"
                + currentDate);

        if (ExciseConstants.FORM24_STEP_2.equals(nexStep)) {
            jdbcTemplate.update(new PreparedStatementCreator() {
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    PreparedStatement ps = connection.prepareStatement(""
                            + "  update  form24  set step=?,doc_no=?,audit_by=?,audit_by_full_name=?,audit_date=? where form24_id=? "
                            + "", Statement.RETURN_GENERATED_KEYS);
                    ps.setString(1, nexStep);
                    ps.setString(2, docNo);
                    ps.setString(3, userName);
                    ps.setString(4, fullName);
                    ps.setTimestamp(5, currentDate);
                    ps.setLong(6, new Long(form24Id));
                    return ps;
                }
            });

        } else {
            jdbcTemplate.update(" update form24 set step ='" + nexStep + "' where form24_id=" + form24Id);
        }

    } catch (BuckWaException ex) {
        ex.printStackTrace();
        //throw ex;
    }
}

From source file:no.polaric.aprsdb.MyDBSession.java

/**
  * Get trail poiint for a given station and a given time. 
  */// w  w w.ja  v  a2s .  c  om
public Trail.Item getTrailPoint(String src, java.util.Date t) throws java.sql.SQLException {
    _log.debug("MyDbSession", "getTrailPoint: " + src + ", " + df.format(t));
    /* Left outer join with AprsPacket to get path where available */
    PreparedStatement stmt = getCon().prepareStatement(
            " SELECT pr.time, position, speed, course, path, ipath, nopkt FROM \"PosReport\" AS pr"
                    + " LEFT JOIN \"AprsPacket\" AS ap ON pr.src = ap.src AND pr.rtime = ap.time "
                    + " WHERE pr.src=? AND pr.time > ? AND pr.time < ?",
            ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
    stmt.setString(1, src);
    stmt.setTimestamp(2, date2ts(t, -1200));
    stmt.setTimestamp(3, date2ts(t, +1200));
    ResultSet rs = stmt.executeQuery();
    if (rs.first()) {
        String p = "";
        if (rs.getBoolean("nopkt"))
            p = "(ext)";
        else {
            String path = rs.getString("path");
            String ipath = rs.getString("ipath");
            if (path == null)
                p = "?";
            else {
                p = path;
                if (ipath != null && ipath.length() > 1)
                    p = p + (p.length() > 1 ? "," : "") + ipath;
            }
        }

        return new Trail.Item(rs.getTimestamp("time"), getRef(rs, "position"), rs.getInt("speed"),
                rs.getInt("course"), p);
    } else
        return null;
}

From source file:com.skycloud.management.portal.admin.sysmanage.dao.impl.UserManageDaoImpl.java

@Override
public int udpateUser(final TUserBO user) throws SQLException {
    StringBuilder sql = new StringBuilder("update T_SCS_USER set NAME=?,DEPT_ID=?,ROLE_ID=?,");
    sql.append(" EMAIL=?,PHONE=?,MOBILE=?,FAX=?,");
    sql.append(" LASTUPDATE_DT=?,STATE=?,CHECK_CODE=?");
    if (StringUtils.isNotEmpty(user.getPwd())) {
        sql.append(" ,PWD=?");
    }//from  w ww  .ja va2  s  . c  o  m
    sql.append(" where ID=?");
    int result = 0;
    try {
        result = this.getJdbcTemplate().update(sql.toString(), new PreparedStatementSetter() {
            int i = 1;

            public void setValues(PreparedStatement ps) throws SQLException {
                ps.setString(i++, user.getName());
                ps.setInt(i++, user.getDeptId());
                ps.setInt(i++, user.getRoleId());
                ps.setString(i++, user.getEmail());
                ps.setString(i++, user.getPhone());
                ps.setString(i++, user.getMobile());
                ps.setString(i++, user.getFax());
                ps.setTimestamp(i++, new Timestamp(user.getLastupdateDt().getTime()));
                ps.setInt(i++, user.getState());
                ps.setString(i++, user.getCheckCode());//fix buf 3040 
                if (StringUtils.isNotEmpty(user.getPwd())) {
                    ps.setString(i++, DegistUtil.md5(user.getPwd()));
                }
                ps.setInt(i++, user.getId());
            }
        });
    } catch (Exception e) {
        throw new SQLException(" " + user.getLastupdateDt()
                + "  " + e.getMessage());
    }
    return result;
}

From source file:at.alladin.rmbt.statisticServer.UsageJSONResource.java

/**
 * Returns the statistics for used network name for a specific timespan [begin, end)
 * @param begin select all tests with time >= begin
 * @param end select all tests with time < end
 * @return the structurized JSON object/*  w ww  .jav  a 2 s . c o  m*/
 * @throws SQLException
 * @throws JSONException
 */
private JSONObject getNetworkGroupName(Timestamp begin, Timestamp end) throws SQLException, JSONException {
    JSONObject returnObj = new JSONObject();
    JSONArray sums = new JSONArray();
    JSONArray values = new JSONArray();
    returnObj.put("sums", sums);
    returnObj.put("values", values);

    HashMap<String, Long> fieldSums = new HashMap<>();

    PreparedStatement ps;
    ResultSet rs;

    final String sql = "SELECT date_trunc('day', time) _day, COALESCE(network_group_name,'null') \"version\", count(network_group_name) count_group_name"
            + " FROM test" + " WHERE status='FINISHED' AND deleted=false AND time >= ? AND time < ?"
            + " GROUP BY _day, network_group_name " + " HAVING count(network_group_name) > 0 "
            + " ORDER BY _day ASC";

    ps = conn.prepareStatement(sql);
    ps.setTimestamp(1, begin);
    ps.setTimestamp(2, end);

    rs = ps.executeQuery();

    //one array-item for each day
    long currentTime = -1;
    JSONObject currentEntry = null;
    JSONArray currentEntryValues = null;
    while (rs.next()) {

        //new item, of a new day is reached
        long newTime = rs.getDate("_day").getTime();
        if (currentTime != newTime) {
            currentTime = newTime;
            currentEntry = new JSONObject();
            currentEntryValues = new JSONArray();
            currentEntry.put("day", rs.getDate("_day").getTime());
            currentEntry.put("values", currentEntryValues);
            values.put(currentEntry);
        }

        //disable null-values
        String version = rs.getString("version");
        long count = rs.getLong("count_group_name");
        if (version.isEmpty()) {
            version = "empty";
        }

        //add value to sum
        if (!fieldSums.containsKey(version)) {
            fieldSums.put(version, new Long(0));
        }
        fieldSums.put(version, fieldSums.get(version) + count);

        JSONObject current = new JSONObject();
        current.put("field", version);
        current.put("value", count);
        currentEntryValues.put(current);
    }

    rs.close();
    ps.close();

    //add field sums
    for (String field : fieldSums.keySet()) {
        JSONObject obj = new JSONObject();
        obj.put("field", field);
        obj.put("sum", fieldSums.get(field));
        sums.put(obj);
    }

    return returnObj;
}

From source file:at.alladin.rmbt.statisticServer.UsageJSONResource.java

/**
 * Returns the statistics for used network type for a specific timespan [begin, end)
 * @param begin select all tests with time >= begin
 * @param end select all tests with time < end
 * @return the structurized JSON object//from   w w w. j a v  a 2s .c  o m
 * @throws SQLException
 * @throws JSONException
 */
private JSONObject getNetworkGroupType(Timestamp begin, Timestamp end) throws SQLException, JSONException {
    JSONObject returnObj = new JSONObject();
    JSONArray sums = new JSONArray();
    JSONArray values = new JSONArray();
    returnObj.put("sums", sums);
    returnObj.put("values", values);

    HashMap<String, Long> fieldSums = new HashMap<>();

    PreparedStatement ps;
    ResultSet rs;

    final String sql = "SELECT date_trunc('day', time) _day, COALESCE(network_group_type,'null') \"version\", count(network_group_type) count_group_type"
            + " FROM test" + " WHERE status='FINISHED' AND deleted=false AND time >= ? AND time < ?"
            + " GROUP BY _day, network_group_type " + " HAVING count(network_group_type) > 0 "
            + " ORDER BY _day ASC";

    ps = conn.prepareStatement(sql);
    ps.setTimestamp(1, begin);
    ps.setTimestamp(2, end);

    rs = ps.executeQuery();

    //one array-item for each day
    long currentTime = -1;
    JSONObject currentEntry = null;
    JSONArray currentEntryValues = null;
    while (rs.next()) {

        //new item, of a new day is reached
        long newTime = rs.getDate("_day").getTime();
        if (currentTime != newTime) {
            currentTime = newTime;
            currentEntry = new JSONObject();
            currentEntryValues = new JSONArray();
            currentEntry.put("day", rs.getDate("_day").getTime());
            currentEntry.put("values", currentEntryValues);
            values.put(currentEntry);
        }

        //disable null-values
        String version = rs.getString("version");
        long count = rs.getLong("count_group_type");
        if (version.isEmpty()) {
            version = "empty";
        }

        //add value to sum
        if (!fieldSums.containsKey(version)) {
            fieldSums.put(version, new Long(0));
        }
        fieldSums.put(version, fieldSums.get(version) + count);

        JSONObject current = new JSONObject();
        current.put("field", version);
        current.put("value", count);
        currentEntryValues.put(current);
    }

    rs.close();
    ps.close();

    //add field sums
    for (String field : fieldSums.keySet()) {
        JSONObject obj = new JSONObject();
        obj.put("field", field);
        obj.put("sum", fieldSums.get(field));
        sums.put(obj);
    }

    return returnObj;
}

From source file:com.concursive.connect.web.modules.documents.dao.FileFolder.java

/**
 * Updates the folder meta data/*  w w  w. ja v  a 2s . co  m*/
 *
 * @param db Description of the Parameter
 * @return Description of the Return Value
 * @throws SQLException Description of the Exception
 */
public int update(Connection db) throws SQLException {
    int resultCount = 0;
    if (!isValid()) {
        return -1;
    }
    String sql = "UPDATE project_folders " + "SET subject = ?, description = ?, display = ? "
            + "WHERE folder_id = ? " + "AND modified = ?";
    int i = 0;
    PreparedStatement pst = db.prepareStatement(sql);
    pst.setString(++i, subject);
    pst.setString(++i, description);
    DatabaseUtils.setInt(pst, ++i, display);
    pst.setInt(++i, this.getId());
    pst.setTimestamp(++i, this.getModified());
    resultCount = pst.executeUpdate();
    pst.close();
    return resultCount;
}

From source file:org.eevolution.form.VSCRP.java

int getLoad(int S_Resource_ID, Timestamp start, Timestamp end) {
    int load = 0;
    String sql = "SELECT SUM( CASE WHEN ow.DurationUnit = 's'  THEN 1 * (onode.QueuingTime + onode.SetupTime + (onode.Duration * (o.QtyOrdered - o.QtyDelivered - o.QtyScrap)) + onode.MovingTime + onode.WaitingTime) WHEN ow.DurationUnit = 'm' THEN 60 * (onode.QueuingTime + onode.SetupTime + (onode.Duration * (o.QtyOrdered - o.QtyDelivered - o.QtyScrap)) + onode.MovingTime + onode.WaitingTime) WHEN ow.DurationUnit = 'h'  THEN 3600 * (onode.QueuingTime + onode.SetupTime + (onode.Duration * (o.QtyOrdered - o.QtyDelivered - o.QtyScrap)) + onode.MovingTime + onode.WaitingTime) WHEN ow.DurationUnit = 'Y'  THEN 31536000 *  (onode.QueuingTime + onode.SetupTime + (onode.Duration * (o.QtyOrdered - o.QtyDelivered - o.QtyScrap)) + onode.MovingTime + onode.WaitingTime) WHEN ow.DurationUnit = 'M' THEN 2592000 * (onode.QueuingTime + onode.SetupTime + (onode.Duration * (o.QtyOrdered - o.QtyDelivered - o.QtyScrap)) + onode.MovingTime + onode.WaitingTime) WHEN ow.DurationUnit = 'D' THEN 86400 END ) AS Load FROM MPC_Order_Node onode INNER JOIN MPC_Order_Workflow ow ON (ow.MPC_Order_Workflow_ID =  onode.MPC_Order_Workflow_ID) INNER JOIN MPC_Order o ON (o.MPC_Order_ID = onode.MPC_Order_ID)  WHERE onode.S_Resource_ID = ?  AND onode.AD_Client_ID = ? AND  trunc(?) BETWEEN trunc(onode.DateStartSchedule) AND trunc(onode.DateFinishSchedule)";
    //String sql = "SELECT SUM( CASE WHEN ow.DurationUnit = 's'  THEN 1 * (onode.QueuingTime + onode.SetupTime + (onode.Duration * (o.QtyOrdered - o.QtyDelivered - o.QtyScrap)) + onode.MovingTime + onode.WaitingTime) WHEN ow.DurationUnit = 'm' THEN 60 * (onode.QueuingTime + onode.SetupTime + (onode.Duration * (o.QtyOrdered - o.QtyDelivered - o.QtyScrap)) + onode.MovingTime + onode.WaitingTime) WHEN ow.DurationUnit = 'h'  THEN 3600 * (onode.QueuingTime + onode.SetupTime + (onode.Duration * (o.QtyOrdered - o.QtyDelivered - o.QtyScrap)) + onode.MovingTime + onode.WaitingTime) WHEN ow.DurationUnit = 'Y'  THEN 31536000 *  (onode.QueuingTime + onode.SetupTime + (onode.Duration * (o.QtyOrdered - o.QtyDelivered - o.QtyScrap)) + onode.MovingTime + onode.WaitingTime) WHEN ow.DurationUnit = 'M' THEN 2592000 * (onode.QueuingTime + onode.SetupTime + (onode.Duration * (o.QtyOrdered - o.QtyDelivered - o.QtyScrap)) + onode.MovingTime + onode.WaitingTime) WHEN ow.DurationUnit = 'D' THEN 86400 END ) AS Load FROM MPC_Order_Node onode INNER JOIN MPC_Order_Workflow ow ON (ow.MPC_Order_Workflow_ID =  onode.MPC_Order_Workflow_ID) INNER JOIN MPC_Order o ON (o.MPC_Order_ID = onode.MPC_Order_ID)  WHERE onode.S_Resource_ID = ?  AND onode.AD_Client_ID = ? AND  trunc(onode.DateStartSchedule) = ?" ;
    System.out.println("SQL :" + sql);
    try {/*from  w  w w. j a  v a 2s .  com*/
        PreparedStatement pstmt = null;
        pstmt = DB.prepareStatement(sql);
        pstmt.setInt(1, S_Resource_ID);
        pstmt.setInt(2, AD_Client_ID);
        pstmt.setTimestamp(3, start);
        System.out.println("Params SQL :" + S_Resource_ID + " " + AD_Client_ID + " " + start);
        ResultSet rs = pstmt.executeQuery();
        while (rs.next()) {
            load = rs.getInt(1);
        }
        rs.close();
        pstmt.close();
        return load;
    } catch (Exception e) {
        log.log(Level.SEVERE, "doIt - " + sql, e);
    }
    return 0;
}