Example usage for java.sql PreparedStatement 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, Calendar cal) throws SQLException;

Source Link

Document

Sets the designated parameter to the given java.sql.Timestamp value, using the given Calendar object.

Usage

From source file:net.solarnetwork.node.dao.jdbc.AbstractJdbcDatumDao.java

/**
 * Execute a SQL update to delete data that has already been "uploaded" and
 * is older than a specified number of hours.
 * /*from  ww  w .j a  v a 2s.co m*/
 * <p>
 * This executes SQL from the {@code sqlDeleteOld} property, setting a
 * single timestamp parameter as the current time minus {@code hours} hours.
 * The general idea is for the SQL to join to some "upload" table to find
 * the rows in the "datum" table that have been uploaded and are older than
 * the specified number of hours. For example:
 * </p>
 * 
 * <pre>
 * DELETE FROM solarnode.sn_some_datum p WHERE p.id IN 
 * (SELECT pd.id FROM solarnode.sn_some_datum pd 
 * INNER JOIN solarnode.sn_some_datum_upload u 
 * ON u.power_datum_id = pd.id WHERE pd.created < ?)
 * </pre>
 * 
 * @param hours
 *        the number of hours hold to delete
 * @return the number of rows deleted
 */
protected int deleteUploadedDataOlderThanHours(final int hours) {
    return getJdbcTemplate().update(new PreparedStatementCreator() {

        @Override
        public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
            String sql = getSqlResource(SQL_RESOURCE_DELETE_OLD);
            log.debug("Preparing SQL to delete old datum [{}] with hours [{}]", sql, hours);
            PreparedStatement ps = con.prepareStatement(sql);
            Calendar c = Calendar.getInstance();
            c.add(Calendar.HOUR, -hours);
            ps.setTimestamp(1, new Timestamp(c.getTimeInMillis()), c);
            return ps;
        }
    });
}

---

From source file:net.solarnetwork.node.dao.jdbc.reactor.JdbcInstructionDao.java

@Override
@Transactional(readOnly = false, propagation = Propagation.REQUIRED)
public int deleteHandledInstructionsOlderThan(final int hours) {
    return getJdbcTemplate().update(new PreparedStatementCreator() {

        @Override//  w  w w  . java 2 s . c om
        public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
            final String sql = getSqlResource(RESOURCE_SQL_DELETE_OLD);
            log.debug("Preparing SQL to delete old instructions [{}] with hours [{}]", sql, hours);
            PreparedStatement ps = con.prepareStatement(sql);
            Calendar c = Calendar.getInstance();
            c.add(Calendar.HOUR, -hours);
            ps.setTimestamp(1, new Timestamp(c.getTimeInMillis()), c);
            return ps;
        }
    });
}

---

From source file:com.jagornet.dhcp.db.JdbcIaAddressDAO.java

public List<IaAddress> findExpiredAddresses(final byte iatype) {
    return getJdbcTemplate().query("select * from iaaddress a"
            + " join identityassoc ia on ia.id=a.identityassoc_id" + " where ia.iatype = ?" + " and a.state != "
            + IaAddress.STATIC + " and a.validendtime < ? order by a.validendtime",
            new PreparedStatementSetter() {
                @Override/*from ww  w . ja va 2s.c  o  m*/
                public void setValues(PreparedStatement ps) throws SQLException {
                    ps.setByte(1, iatype);
                    java.sql.Timestamp ts = new java.sql.Timestamp(new Date().getTime());
                    ps.setTimestamp(2, ts, Util.GMT_CALENDAR);
                }
            }, new IaAddrRowMapper());
}

---

From source file:com.jagornet.dhcp.db.JdbcIaPrefixDAO.java

public List<IaPrefix> findAllOlderThan(Date date) {
    return getJdbcTemplate().query(
            "select * from iaprefix" + " join identityassoc ia on identityassoc_id=ia.id"
                    + " where ia.iatype = ?" + " and validendtime < ? order by validendtime",
            new PreparedStatementSetter() {
                @Override/*ww w  .  j  av a2  s  .com*/
                public void setValues(PreparedStatement ps) throws SQLException {
                    ps.setByte(1, IdentityAssoc.PD_TYPE);
                    java.sql.Timestamp ts = new java.sql.Timestamp(new Date().getTime());
                    ps.setTimestamp(2, ts, Util.GMT_CALENDAR);
                }
            }, new IaPrefixRowMapper());
}

---

From source file:com.jagornet.dhcp.db.JdbcLeaseManager.java

@Override
public List<IaPrefix> findExpiredIaPrefixes() {
    List<DhcpLease> leases = getJdbcTemplate().query("select * from dhcplease" + " where iatype = "
            + IdentityAssoc.PD_TYPE + " and validendtime < ? order by validendtime",
            new PreparedStatementSetter() {
                @Override//from w  ww  .jav a  2  s . c o  m
                public void setValues(PreparedStatement ps) throws SQLException {
                    java.sql.Timestamp ts = new java.sql.Timestamp(new Date().getTime());
                    ps.setTimestamp(1, ts, Util.GMT_CALENDAR);
                }
            }, new DhcpLeaseRowMapper());
    return toIaPrefixes(leases);
}

---

From source file:com.jagornet.dhcp.db.JdbcIaAddressDAO.java

public void update(final IaAddress iaAddr) {
    String updateQuery = "update iaaddress" + " set ipaddress=?," + " starttime=?," + " preferredendtime=?,"
            + " validendtime=?," + " state=?," + " identityassoc_id=?" + " where id=?";
    getJdbcTemplate().update(updateQuery, new PreparedStatementSetter() {
        @Override/*from w w  w . j  a v a  2  s  . c om*/
        public void setValues(PreparedStatement ps) throws SQLException {
            ps.setBytes(1, iaAddr.getIpAddress().getAddress());
            Date start = iaAddr.getStartTime();
            if (start != null) {
                java.sql.Timestamp sts = new java.sql.Timestamp(start.getTime());
                ps.setTimestamp(2, sts, Util.GMT_CALENDAR);
            } else {
                ps.setNull(2, java.sql.Types.TIMESTAMP);
            }
            Date preferred = iaAddr.getPreferredEndTime();
            if (preferred != null) {
                java.sql.Timestamp pts = new java.sql.Timestamp(preferred.getTime());
                ps.setTimestamp(3, pts, Util.GMT_CALENDAR);
            } else {
                ps.setNull(3, java.sql.Types.TIMESTAMP);
            }
            Date valid = iaAddr.getValidEndTime();
            if (valid != null) {
                java.sql.Timestamp vts = new java.sql.Timestamp(valid.getTime());
                ps.setTimestamp(4, vts, Util.GMT_CALENDAR);
            } else {
                ps.setNull(4, java.sql.Types.TIMESTAMP);
            }
            ps.setByte(5, iaAddr.getState());
            ps.setLong(6, iaAddr.getIdentityAssocId());
            ps.setLong(7, iaAddr.getId());
        }
    });
}

---

From source file:com.jagornet.dhcp.db.JdbcLeaseManager.java

protected List<DhcpLease> findExpiredLeases(final byte iatype) {
    return getJdbcTemplate().query("select * from dhcplease" + " where iatype = ?" + " and state != "
            + IaAddress.STATIC + " and validendtime < ? order by validendtime", new PreparedStatementSetter() {
                @Override/*from  w  w  w .j  a  v  a2s .  c o  m*/
                public void setValues(PreparedStatement ps) throws SQLException {
                    ps.setByte(1, iatype);
                    java.sql.Timestamp ts = new java.sql.Timestamp(new Date().getTime());
                    ps.setTimestamp(2, ts, Util.GMT_CALENDAR);
                }
            }, new DhcpLeaseRowMapper());
}

---

From source file:com.jagornet.dhcp.db.JdbcLeaseManager.java

/**
 * Update dhcp lease.//from   w w w  .  ja  va  2s  .  c  om
 *
 * @param lease the lease
 */
protected void updateDhcpLease(final DhcpLease lease) {
    getJdbcTemplate().update(
            "update dhcplease" + " set state=?," + " starttime=?," + " preferredendtime=?," + " validendtime=?,"
                    + " ia_options=?," + " ipaddr_options=?" + " where ipaddress=?",
            new PreparedStatementSetter() {
                @Override
                public void setValues(PreparedStatement ps) throws SQLException {
                    ps.setByte(1, lease.getState());
                    java.sql.Timestamp sts = new java.sql.Timestamp(lease.getStartTime().getTime());
                    ps.setTimestamp(2, sts, Util.GMT_CALENDAR);
                    java.sql.Timestamp pts = new java.sql.Timestamp(lease.getPreferredEndTime().getTime());
                    ps.setTimestamp(3, pts, Util.GMT_CALENDAR);
                    java.sql.Timestamp vts = new java.sql.Timestamp(lease.getValidEndTime().getTime());
                    ps.setTimestamp(4, vts, Util.GMT_CALENDAR);
                    ps.setBytes(5, encodeOptions(lease.getIaDhcpOptions()));
                    ps.setBytes(6, encodeOptions(lease.getIaAddrDhcpOptions()));
                    ps.setBytes(7, lease.getIpAddress().getAddress());
                }
            });
}

---

From source file:com.jagornet.dhcp.db.JdbcIaPrefixDAO.java

public void update(final IaPrefix iaPrefix) {
    String updateQuery = "update iaprefix" + " set prefixaddress=?," + " prefixlength=?," + " starttime=?,"
            + " preferredendtime=?," + " validendtime=?," + " state=?," + " identityassoc_id=?" + " where id=?";
    getJdbcTemplate().update(updateQuery, new PreparedStatementSetter() {
        @Override/*from   w  w  w .ja v a 2  s  . c  o m*/
        public void setValues(PreparedStatement ps) throws SQLException {
            ps.setBytes(1, iaPrefix.getIpAddress().getAddress());
            ps.setInt(2, iaPrefix.getPrefixLength());
            Date start = iaPrefix.getStartTime();
            if (start != null) {
                java.sql.Timestamp sts = new java.sql.Timestamp(start.getTime());
                ps.setTimestamp(3, sts, Util.GMT_CALENDAR);
            } else {
                ps.setNull(3, java.sql.Types.TIMESTAMP);
            }
            Date preferred = iaPrefix.getPreferredEndTime();
            if (preferred != null) {
                java.sql.Timestamp pts = new java.sql.Timestamp(preferred.getTime());
                ps.setTimestamp(4, pts, Util.GMT_CALENDAR);
            } else {
                ps.setNull(4, java.sql.Types.TIMESTAMP);
            }
            Date valid = iaPrefix.getValidEndTime();
            if (valid != null) {
                java.sql.Timestamp vts = new java.sql.Timestamp(valid.getTime());
                ps.setTimestamp(5, vts, Util.GMT_CALENDAR);
            } else {
                ps.setNull(5, java.sql.Types.TIMESTAMP);
            }
            ps.setByte(6, iaPrefix.getState());
            ps.setLong(7, iaPrefix.getIdentityAssocId());
            ps.setLong(8, iaPrefix.getId());
        }
    });
}

---

From source file:com.jagornet.dhcp.db.JdbcIaManager.java

/**
 * Expire i as.//from ww  w .  j  a v  a  2  s  . c o m
 */
protected void expireIAs() {
    getJdbcTemplate().update("update identityassoc set state=" + IdentityAssoc.EXPIRED
            + " where exists (select 1 from iaaddress where identityassoc_id=identityassoc.id and validendtime<?)"
            + " and not exists (select 1 from iaaddress where identityassoc_id=identityassoc.id and validendtime>=?)",
            new PreparedStatementSetter() {
                @Override
                public void setValues(PreparedStatement ps) throws SQLException {
                    java.sql.Timestamp now = new java.sql.Timestamp((new Date()).getTime());
                    ps.setTimestamp(1, now, Util.GMT_CALENDAR);
                    ps.setTimestamp(2, now, Util.GMT_CALENDAR);
                }
            });
}

---

• «
• 1
• 2
• 3
• »