List of usage examples for java.sql PreparedStatement setByte
void setByte(int parameterIndex, byte x) throws SQLException;
byte value. 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/*www . j a v a 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//from ww w . j a va2 s .co m 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.JdbcIdentityAssocDAO.java
public void create(final IdentityAssoc ia) { /**/*from ww w . j a va 2 s. co m*/ * Note: see https://issues.apache.org/jira/browse/DERBY-3609 * "Formally, Derby does not support getGeneratedKeys since * DatabaseMetaData.supportsGetGeneratedKeys() returns false. * However, Statement.getGeneratedKeys() is partially implemented, * ... since it will only return a meaningful result when an single * row insert is done with INSERT...VALUES" * * Spring has thus provided a workaround as described here: * http://jira.springframework.org/browse/SPR-5306 */ GeneratedKeyHolder newKey = new GeneratedKeyHolder(); getJdbcTemplate().update(new PreparedStatementCreator() { @Override public PreparedStatement createPreparedStatement(Connection conn) throws SQLException { PreparedStatement ps = conn.prepareStatement( "insert into identityassoc" + " (duid, iatype, iaid, state)" + " values (?, ?, ?, ?)", PreparedStatement.RETURN_GENERATED_KEYS); ps.setBytes(1, ia.getDuid()); ps.setByte(2, ia.getIatype()); ps.setLong(3, ia.getIaid()); ps.setByte(4, ia.getState()); return ps; } }, newKey); Number newId = newKey.getKey(); if (newId != null) { ia.setId(newId.longValue()); } }
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/*w w w. ja v a 2s . co 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 ww w. java2 s .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:org.athenasource.framework.unidbi.Datatypes.java
/** * Setting the parameter for the given prepared statement. This method will * cast the value to the object expected type (execept BOOLEAN) as * {@linkplain #getClass(int)}./*from www . ja v a2s. c o m*/ * <p>INSERT, UPDATE should always call this method to set parameters before 'WHERE' keyword.</p> * <p><b>Warining</b>: For parameters after 'WHERE' keyword, always remember 'IS NULL' is not equal to '= NULL'.</p> * * @param index * the parameter index * @param value * the value for the parameter, can be <code>null</code>. * @param unidbType * the datatype of the parameter * @throws SQLException * in case of SQL problems or type conversion fails. */ public static void setParameter(PreparedStatement stmt, int index, Object value, int unidbType) throws SQLException { // Derby needs special handling. boolean isDerby = (stmt instanceof DelegatingPreparedStatement) ? ((DelegatingPreparedStatement) stmt).getDelegate().getClass().getName().contains("derby") : stmt.getClass().getName().contains("derby"); if (value == null) { if (isDerby) { if (unidbType == NCHAR) { stmt.setNull(index, Datatypes.getSQLType(CHAR)); } else if (unidbType == NVARCHAR) { stmt.setNull(index, Datatypes.getSQLType(VARCHAR)); } else { stmt.setNull(index, Datatypes.getSQLType(unidbType)); } } else { stmt.setNull(index, Datatypes.getSQLType(unidbType)); } } else { try { switch (unidbType) { case BOOLEAN: stmt.setByte(index, (byte) (((Number) value).intValue() == 1 ? 1 : 0)); break; case TINYINT: stmt.setByte(index, ((Number) value).byteValue()); break; case SMALLINT: stmt.setShort(index, ((Number) value).shortValue()); break; case INTEGER: stmt.setInt(index, ((Number) value).intValue()); break; case BIGINT: stmt.setLong(index, ((Number) value).longValue()); break; case DECIMAL: stmt.setBigDecimal(index, ((BigDecimal) value)); break; case REAL: stmt.setFloat(index, ((Float) value).floatValue()); break; case DOUBLE: stmt.setDouble(index, ((Double) value).doubleValue()); break; case CHAR: stmt.setString(index, (String) value); break; case NCHAR: if (isDerby) { stmt.setString(index, (String) value); } else { stmt.setNString(index, (String) value); } break; case VARCHAR: stmt.setString(index, (String) value); break; case NVARCHAR: if (isDerby) { stmt.setString(index, (String) value); } else { stmt.setNString(index, (String) value); } break; case CLOB: // Clob/NClob can be represented as String without any problem. - Oct 16, 2008. stmt.setString(index, (String) value); // stmt.setClob(index, ((Clob) value)); break; case NCLOB: if (isDerby) { stmt.setString(index, (String) value); } else { stmt.setNString(index, (String) value); // stmt.setNClob(index, ((NClob) value)); } break; case BLOB: stmt.setBlob(index, ((Blob) value)); break; case TIMESTAMP: stmt.setTimestamp(index, ((Timestamp) value)); break; default: throw new IllegalArgumentException("[!NO SUCH UNIDB DATA TYPE: " + unidbType + "]"); } } catch (ClassCastException cce) { throw new SQLException( "Failed to convert " + value + " (" + value.getClass() + ") to " + getName(unidbType)); } } }
From source file:com.jagornet.dhcp.db.JdbcLeaseManager.java
/** * Find dhcp leases for ia./* w ww. ja v a2 s . co m*/ * * @param duid the duid * @param iatype the iatype * @param iaid the iaid * @return the list */ protected List<DhcpLease> findDhcpLeasesForIA(final byte[] duid, final byte iatype, final long iaid) { return getJdbcTemplate().query("select * from dhcplease" + " where duid = ?" + " and iatype = ?" + " and iaid = ?" + " order by ipaddress", new PreparedStatementSetter() { @Override public void setValues(PreparedStatement ps) throws SQLException { ps.setBytes(1, duid); ps.setByte(2, iatype); ps.setLong(3, iaid); } }, new DhcpLeaseRowMapper()); }
From source file:com.jagornet.dhcp.db.JdbcLeaseManager.java
public void updateIaAddr(final IaAddress iaAddr) { getJdbcTemplate().update("update dhcplease" + " set state = ?," + ((iaAddr instanceof IaPrefix) ? " prefixlen = ?," : "") + " starttime = ?," + " preferredendtime = ?," + " validendtime = ?" + " where ipaddress = ?", new PreparedStatementSetter() { @Override/*from ww w . j av a2 s. co m*/ public void setValues(PreparedStatement ps) throws SQLException { int i = 1; ps.setByte(i++, iaAddr.getState()); if (iaAddr instanceof IaPrefix) { ps.setShort(i++, ((IaPrefix) iaAddr).getPrefixLength()); } Date start = iaAddr.getStartTime(); if (start != null) { java.sql.Timestamp sts = new java.sql.Timestamp(start.getTime()); ps.setTimestamp(i++, sts, Util.GMT_CALENDAR); } else { ps.setNull(i++, java.sql.Types.TIMESTAMP); } Date preferred = iaAddr.getPreferredEndTime(); if (preferred != null) { java.sql.Timestamp pts = new java.sql.Timestamp(preferred.getTime()); ps.setTimestamp(i++, pts, Util.GMT_CALENDAR); } else { ps.setNull(i++, java.sql.Types.TIMESTAMP); } Date valid = iaAddr.getValidEndTime(); if (valid != null) { java.sql.Timestamp vts = new java.sql.Timestamp(valid.getTime()); ps.setTimestamp(i++, vts, Util.GMT_CALENDAR); } else { ps.setNull(i++, java.sql.Types.TIMESTAMP); } ps.setBytes(i++, iaAddr.getIpAddress().getAddress()); } }); }
From source file:com.jagornet.dhcp.db.JdbcLeaseManager.java
/** * Insert dhcp lease./* w ww . j a v a 2 s . c o m*/ * * @param lease the lease */ protected void insertDhcpLease(final DhcpLease lease) { getJdbcTemplate().update("insert into dhcplease" + " (ipaddress, duid, iatype, iaid, prefixlen, state," + " starttime, preferredendtime, validendtime," + " ia_options, ipaddr_options)" + " values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", new PreparedStatementSetter() { @Override public void setValues(PreparedStatement ps) throws SQLException { ps.setBytes(1, lease.getIpAddress().getAddress()); ps.setBytes(2, lease.getDuid()); ps.setByte(3, lease.getIatype()); ps.setLong(4, lease.getIaid()); ps.setShort(5, lease.getPrefixLength()); ps.setByte(6, lease.getState()); java.sql.Timestamp sts = new java.sql.Timestamp(lease.getStartTime().getTime()); ps.setTimestamp(7, sts, Util.GMT_CALENDAR); java.sql.Timestamp pts = new java.sql.Timestamp(lease.getPreferredEndTime().getTime()); ps.setTimestamp(8, pts, Util.GMT_CALENDAR); java.sql.Timestamp vts = new java.sql.Timestamp(lease.getValidEndTime().getTime()); ps.setTimestamp(9, vts, Util.GMT_CALENDAR); ps.setBytes(10, encodeOptions(lease.getIaDhcpOptions())); ps.setBytes(11, encodeOptions(lease.getIaAddrDhcpOptions())); } }); }