Java 类org.springframework.jdbc.core.PreparedStatementSetter 实例源码

@Override
public int saveAccessToken(final AccessToken accessToken) {
    final String sql = "insert into oauth_access_token(token_id,token_expired_seconds,authentication_id," +
            "username,client_id,token_type,refresh_token_expired_seconds,refresh_token) values (?,?,?,?,?,?,?,?) ";

    return jdbcTemplate.update(sql, new PreparedStatementSetter() {
        @Override
        public void setValues(PreparedStatement ps) throws SQLException {
            ps.setString(1, accessToken.tokenId());
            ps.setInt(2, accessToken.tokenExpiredSeconds());
            ps.setString(3, accessToken.authenticationId());

            ps.setString(4, accessToken.username());
            ps.setString(5, accessToken.clientId());
            ps.setString(6, accessToken.tokenType());

            ps.setInt(7, accessToken.refreshTokenExpiredSeconds());
            ps.setString(8, accessToken.refreshToken());
        }
    });
}

@Override
public int saveUsers(final Users users) {
    String sql = " insert into users(guid,create_time, username,password) values (?,?,?,?) ";
    this.jdbcTemplate.update(sql, new PreparedStatementSetter() {
        @Override
        public void setValues(PreparedStatement ps) throws SQLException {
            ps.setString(1, users.guid());
            ps.setTimestamp(2, new Timestamp(users.createTime().getTime()));
            ps.setString(3, users.username());

            ps.setString(4, users.password());
        }
    });

    return this.jdbcTemplate.queryForObject("select id from users where guid = ?", new Object[]{users.guid()}, Integer.class);
}

@Override
public void save(String graphName, String version, IPixelCut xInfo) throws GraphNotExistsException {
    Long graphVersionId = getGraphVersionId(graphName, version);
    getJdbcTemplate().update("INSERT INTO " + schema + TABLE_NAME + " (segment_id, graphversion_id, start_cut_right, start_cut_left, end_cut_right, end_cut_left) VALUES (?,?,?,?,?,?)",
        new PreparedStatementSetter() {

            @Override
            public void setValues(PreparedStatement ps) throws SQLException {
                int pos = 1;
                ps.setLong(pos++, xInfo.getSegmentId());
                ps.setLong(pos++, graphVersionId);
                ps.setDouble(pos++, xInfo.getStartCutRight());
                ps.setDouble(pos++, xInfo.getStartCutLeft());
                ps.setDouble(pos++, xInfo.getEndCutRight());
                ps.setDouble(pos++, xInfo.getEndCutLeft());
            }

        }
    );
}

@Override
public void update(String graphName, String version, IPixelCut xInfo) throws GraphNotExistsException {
    Long graphVersionId = getGraphVersionId(graphName, version);
    getJdbcTemplate().update("UPDATE " + schema + TABLE_NAME + " SET start_cut_right=?, start_cut_left=?, end_cut_right=?, end_cut_left=? WHERE segment_id=? AND graphversion_id=?",
        new PreparedStatementSetter() {

            @Override
            public void setValues(PreparedStatement ps) throws SQLException {
                int pos = 1;
                ps.setDouble(pos++, xInfo.getStartCutRight());
                ps.setDouble(pos++, xInfo.getStartCutLeft());
                ps.setDouble(pos++, xInfo.getEndCutRight());
                ps.setDouble(pos++, xInfo.getEndCutLeft());
                ps.setLong(pos++, xInfo.getSegmentId());
                ps.setLong(pos++, graphVersionId);
            }

        }
    );
}

@Override
public void save(String graphName, String version, IRoadDamage xInfo) throws GraphNotExistsException {
    Long graphVersionId = getGraphVersionId(graphName, version);
    getJdbcTemplate().update("INSERT INTO " + schema + TABLE_NAME + " (segment_id, graphversion_id, direction_tow, start_offset, end_offset, type) VALUES (?,?,?,?,?,?)",
        new PreparedStatementSetter() {

            @Override
            public void setValues(PreparedStatement ps) throws SQLException {
                int pos = 1;
                ps.setLong(pos++, xInfo.getSegmentId());
                ps.setLong(pos++, graphVersionId);
                ps.setBoolean(pos++, xInfo.isDirectionTow());
                ps.setDouble(pos++, xInfo.getStartOffset());
                ps.setDouble(pos++, xInfo.getEndOffset());
                ps.setString(pos++, xInfo.getType());
            }

        }
    );
}

@Override
public void update(String graphName, String version, IRoadDamage xInfo) throws GraphNotExistsException {
    Long graphVersionId = getGraphVersionId(graphName, version);
    getJdbcTemplate().update("UPDATE " + schema + TABLE_NAME + " SET start_offset=?, end_offset=?, type=? WHERE segment_id=? AND direction_tow=? AND graphversion_id=?",
        new PreparedStatementSetter() {

            @Override
            public void setValues(PreparedStatement ps) throws SQLException {
                int pos = 1;
                ps.setDouble(pos++, xInfo.getStartOffset());
                ps.setDouble(pos++, xInfo.getEndOffset());
                ps.setString(pos++, xInfo.getType());
                ps.setLong(pos++, xInfo.getSegmentId());
                ps.setBoolean(pos++, xInfo.isDirectionTow());
                ps.setLong(pos++, graphVersionId);
            }

        }
    );
}

@Override
public int saveAccessToken(final AccessToken accessToken) {
    final String sql = "insert into oauth_access_token(token_id,token_expired_seconds,authentication_id," +
            "username,client_id,token_type,refresh_token_expired_seconds,refresh_token) values (?,?,?,?,?,?,?,?) ";

    return jdbcTemplate.update(sql, new PreparedStatementSetter() {
        @Override
        public void setValues(PreparedStatement ps) throws SQLException {
            ps.setString(1, accessToken.tokenId());
            ps.setInt(2, accessToken.tokenExpiredSeconds());
            ps.setString(3, accessToken.authenticationId());

            ps.setString(4, accessToken.username());
            ps.setString(5, accessToken.clientId());
            ps.setString(6, accessToken.tokenType());

            ps.setInt(7, accessToken.refreshTokenExpiredSeconds());
            ps.setString(8, accessToken.refreshToken());
        }
    });
}

public Object doInTransaction(TransactionStatus status) {
    try {
        failedDatas.clear(); // 先清理
        processedDatas.clear();
        interceptor.transactionBegin(context, Arrays.asList(data), dbDialect);
        JdbcTemplate template = dbDialect.getJdbcTemplate();
        int affect = template.update(data.getSql(), new PreparedStatementSetter() {

            public void setValues(PreparedStatement ps) throws SQLException {
                doPreparedStatement(ps, dbDialect, lobCreator, data);
            }
        });
        interceptor.transactionEnd(context, Arrays.asList(data), dbDialect);
        return affect;
    } finally {
        lobCreator.close();
    }
}

/**
 * Locates the primary key IDs specified in "findNow", adding AclImpl instances with StubAclParents to the
 * "acls" Map.
 *
 * @param acls the AclImpls (with StubAclParents)
 * @param findNow Long-based primary keys to retrieve
 * @param sids
 */
private void lookupPrimaryKeys(final Map<Serializable, Acl> acls, final Set<Long> findNow, final List<Sid> sids) {
    Assert.notNull(acls, "ACLs are required");
    Assert.notEmpty(findNow, "Items to find now required");

    String sql = computeRepeatingSql(lookupPrimaryKeysWhereClause, findNow.size());

    Set<Long> parentsToLookup = jdbcTemplate.query(sql,
        new PreparedStatementSetter() {
            public void setValues(PreparedStatement ps) throws SQLException {
                int i = 0;

                for (Long toFind : findNow) {
                    i++;
                    ps.setLong(i, toFind);
                }
            }
        }, new ProcessResultSet(acls, sids));

    // Lookup the parents, now that our JdbcTemplate has released the database connection (SEC-547)
    if (parentsToLookup.size() > 0) {
        lookupPrimaryKeys(acls, parentsToLookup, sids);
    }
}

/**
 * 生成PreparedStatementSetter对象.
 * 
 * @return PreparedStatementSetter
 */
public PreparedStatementSetter getParameters() {
    if (list.size() == 0) {
        return null;
    }
    PreparedStatementSetter param = new PreparedStatementSetter() {
        @Override
        public void setValues(PreparedStatement pstmt) {
            try {
                StatementParameter.this.setValues(pstmt);
            }
            catch (SQLException e) {
                throw new InvalidParamDataAccessException(e);
            }
        }

    };
    return param;
}

public void addConfigInfo(final ConfigInfo configInfo) {
    final Timestamp time = TimeUtils.getCurrentTime();

    this.jt.update(
        "insert into config_info (data_id,group_id,content,md5,gmt_create,gmt_modified) values(?,?,?,?,?,?)",
        new PreparedStatementSetter() {
            public void setValues(PreparedStatement ps) throws SQLException {
                int index = 1;
                ps.setString(index++, configInfo.getDataId());
                ps.setString(index++, configInfo.getGroup());
                ps.setString(index++, configInfo.getContent());
                ps.setString(index++, configInfo.getMd5());
                ps.setTimestamp(index++, time);
                ps.setTimestamp(index++, time);
            }
        });
}

public void updateConfigInfo(final ConfigInfo configInfo) {
    final Timestamp time = TimeUtils.getCurrentTime();

    this.jt.update("update config_info set content=?,md5=?,gmt_modified=? where data_id=? and group_id=?",
        new PreparedStatementSetter() {

            public void setValues(PreparedStatement ps) throws SQLException {
                int index = 1;
                ps.setString(index++, configInfo.getContent());
                ps.setString(index++, configInfo.getMd5());
                ps.setTimestamp(index++, time);
                ps.setString(index++, configInfo.getDataId());
                ps.setString(index++, configInfo.getGroup());
            }
        });
}

@Override
public void closeActivity(final String activityId, final Status orignStatus, final Status targetStatus) {
    log.info("--->start to closeActivity:{},turn status from:{} to:{}", activityId, orignStatus, targetStatus);
    transactionTemplate.execute(new TransactionCallbackWithoutResult() {

        @Override
        protected void doInTransactionWithoutResult(TransactionStatus status) {
            jdbcTemplate.update(ActivitySqlConstance.commit_rollback_activity_by_activity_id,
                    new PreparedStatementSetter() {

                        @Override
                        public void setValues(PreparedStatement ps) throws SQLException {
                            ps.setString(1, targetStatus.name());
                            ps.setTimestamp(2, new Timestamp(new Date().getTime()));
                            ps.setString(3, activityId);
                            ps.setString(4, orignStatus.name());
                        }
                    });
        }
    });
}

@Override
public void saveUser(final User user) {
    final String sql = " insert into user_(guid,archived,create_time,email,password,username,phone) " +
            " values (?,?,?,?,?,?,?) ";
    this.jdbcTemplate.update(sql, new PreparedStatementSetter() {

        @Override
        public void setValues(PreparedStatement ps) throws SQLException {
            ps.setString(1, user.guid());
            ps.setBoolean(2, user.archived());

            ps.setTimestamp(3, Timestamp.valueOf(user.createTime()));
            ps.setString(4, user.email());

            ps.setString(5, user.password());
            ps.setString(6, user.username());

            ps.setString(7, user.phone());
        }
    });
}

@Override
public void updateUser(final User user) {
    final String sql = " update user_ set username = ?, password = ?, phone = ?,email = ? where guid = ? ";
    this.jdbcTemplate.update(sql, new PreparedStatementSetter() {
        @Override
        public void setValues(PreparedStatement ps) throws SQLException {
            ps.setString(1, user.username());
            ps.setString(2, user.password());

            ps.setString(3, user.phone());
            ps.setString(4, user.email());

            ps.setString(5, user.guid());
        }
    });
}

public void addVisitorData(final VisitorData visitorData)
  {
if(visitorData!=null){
    //sysId,erpId,menuPath,visitDate,type,remark            
    jdbcTemplate.update(getInsertSql(),   
               new PreparedStatementSetter() {
            public void setValues(PreparedStatement ps) throws SQLException {   
                ps.setInt(1, visitorData.getSystemId());                              
                ps.setString(2, visitorData.getMenuPath());
                ps.setString(3, visitorData.getErpId());  
                ps.setTimestamp(4, new Timestamp(visitorData.getVisitDate().getTime()));   
                ps.setInt(5, Integer.parseInt(sdfdate.format(visitorData.getVisitDate())));
                ps.setInt(6, Integer.parseInt(sdfhour.format(visitorData.getVisitDate())));
                ps.setInt(7, visitorData.getPathType());
            }   
       });          
}       
  }

public void addConfigInfo(final ConfigInfo configInfo, final String content) {
    final Timestamp time = TimeUtils.getCurrentTime();
    this.jt.update(
        "insert into config_info (data_id,group_id,content,md5,gmt_create,gmt_modified) values(?,?,?,?,?,?)",
        new PreparedStatementSetter() {
            public void setValues(PreparedStatement ps) throws SQLException {
                int index = 1;
                ps.setString(index++, configInfo.getDataId());
                ps.setString(index++, configInfo.getGroup());
                ps.setString(index++, content);
                ps.setString(index++, configInfo.getMd5());
                ps.setTimestamp(index++, time);
                ps.setTimestamp(index++, time);
            }
        });
}

public void addConfigInfo(final ConfigInfo configInfo, final String content) {
    final Timestamp time = TimeUtils.getCurrentTime();
    this.jt.update(
        "insert into config_info (data_id,group_id,content,md5,gmt_create,gmt_modified) values(?,?,?,?,?,?)",
        new PreparedStatementSetter() {
            public void setValues(PreparedStatement ps) throws SQLException {
                int index = 1;
                ps.setString(index++, configInfo.getDataId());
                ps.setString(index++, configInfo.getGroup());
                ps.setString(index++, content);
                ps.setString(index++, configInfo.getMd5());
                ps.setTimestamp(index++, time);
                ps.setTimestamp(index++, time);
            }
        });
}

public void perform(TaskRequest req, TaskResponse res) {
    final DataSource dataSource = DataSourceRetrievalUtil.getDataSource(dataSourcePhrase, connectionPhrase, req, res);

       final JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

       //Setup the parameter setter and row callback handler for this task and the request/response
       final PreparedStatementSetter preparedStatementSetter = new PhraseParameterPreparedStatementSetter(this.parameters, req, res);
       final ResponseMappingRowCallbackHandler rowCallbackHandler = new ResponseMappingRowCallbackHandler(this, req, res);

       //Get the SQL and run the query
       final String finalSql = (String) sql.evaluate(req, res);
       jdbcTemplate.query(finalSql, preparedStatementSetter, rowCallbackHandler);

       if (rowCallbackHandler.getRowCount() == 0) {
           this.performSubtasks(req, res, this.emptyResult);
       }
}

public void addConfigInfo(final ConfigInfo configInfo) {
    final Timestamp time = TimeUtils.getCurrentTime();

    this.jt.update(
        "insert into config_info (data_id,group_id,content,md5,gmt_create,gmt_modified) values(?,?,?,?,?,?)",
        new PreparedStatementSetter() {
            public void setValues(PreparedStatement ps) throws SQLException {
                int index = 1;
                ps.setString(index++, configInfo.getDataId());
                ps.setString(index++, configInfo.getGroup());
                ps.setString(index++, configInfo.getContent());
                ps.setString(index++, configInfo.getMd5());
                ps.setTimestamp(index++, time);
                ps.setTimestamp(index++, time);
            }
        });
}

public void updateConfigInfo(final ConfigInfo configInfo) {
    final Timestamp time = TimeUtils.getCurrentTime();

    this.jt.update("update config_info set content=?,md5=?,gmt_modified=? where data_id=? and group_id=?",
        new PreparedStatementSetter() {

            public void setValues(PreparedStatement ps) throws SQLException {
                int index = 1;
                ps.setString(index++, configInfo.getContent());
                ps.setString(index++, configInfo.getMd5());
                ps.setTimestamp(index++, time);
                ps.setString(index++, configInfo.getDataId());
                ps.setString(index++, configInfo.getGroup());
            }
        });
}

/**
 * 生成PreparedStatementSetter对象.
 * 
 * @return PreparedStatementSetter
 */
public PreparedStatementSetter getParameters() {
    if (list.size() == 0) {
        return null;
    }
    PreparedStatementSetter param = new PreparedStatementSetter() {
        @Override
        public void setValues(PreparedStatement pstmt) {
            try {
                StatementParameter.this.setValues(pstmt);
            }
            catch (SQLException e) {
                throw new InvalidParamDataAccessException(e);
            }
        }

    };
    return param;
}

public int updatePwd(String id,String passwd){
    final String id2 = id;
    int res = 0;
    final String passwd2 = passwd;
    try{
        res = this.jdbcTemplate.update("update organization_user set password=? where id=?", new PreparedStatementSetter(){
           public void setValues(PreparedStatement ps) throws SQLException{
               ps.setString(1,passwd2);
               ps.setString(2,id2);
           }
    });
    return res;
    }catch(EmptyResultDataAccessException e){
      e.printStackTrace();
      return res;
    }
}

public void updateUser(final Users user) {
    validateUser(user);

    getJdbcTemplate().update(updateUserSql, new PreparedStatementSetter() {
        public void setValues(PreparedStatement ps) throws SQLException {
            ps.setString(1, user.getPassword());
            ps.setBoolean(2, user.isEnabled());
            ps.setInt(3, user.getFailedLoginAttempts());
            String lockoutTime=(user.getLockoutTime()==null)?
                    "NULL":new SimpleDateFormat("yyyy/MM/dd HH:mm:ss").format(user.getLockoutTime().getTime());
            ps.setString(4, lockoutTime);
            ps.setString(5, user.getUsername());

        }
    });

        deleteUserAuthorities(user.getUsername());
        insertUserAuthorities(user);
}

public void createGroup(final String groupName, final List<GrantedAuthority> authorities) {
    Assert.hasText(groupName);
    Assert.notNull(authorities);

    logger.debug("Creating new group '" + groupName + "' with authorities " +
            AuthorityUtils.authorityListToSet(authorities));

    getJdbcTemplate().update(insertGroupSql, groupName);

    final int groupId = findGroupId(groupName);

    for (GrantedAuthority a : authorities) {
        final String authority = a.getAuthority();
        getJdbcTemplate().update(insertGroupAuthoritySql, new PreparedStatementSetter() {
            public void setValues(PreparedStatement ps) throws SQLException {
                ps.setInt(1, groupId);
                ps.setString(2, authority);
            }
        });
    }
}

@Test
public void saveNewWithAttributes() {
    JdbcOperationsSessionRepository.JdbcSession session = this.repository
            .createSession();
    session.setAttribute("testName", "testValue");

    this.repository.save(session);

    assertThat(session.isNew()).isFalse();
    assertPropagationRequiresNew();
    verify(this.jdbcOperations, times(1)).update(startsWith("INSERT"),
            isA(PreparedStatementSetter.class));
    verify(this.jdbcOperations, times(1)).batchUpdate(
            and(startsWith("INSERT"), contains("ATTRIBUTE_BYTES")),
            isA(BatchPreparedStatementSetter.class));
}

@Test
@SuppressWarnings("unchecked")
public void getSessionNotFound() {
    String sessionId = "testSessionId";
    given(this.jdbcOperations.query(isA(String.class),
            isA(PreparedStatementSetter.class), isA(ResultSetExtractor.class)))
            .willReturn(Collections.emptyList());

    JdbcOperationsSessionRepository.JdbcSession session = this.repository
            .findById(sessionId);

    assertThat(session).isNull();
    assertPropagationRequiresNew();
    verify(this.jdbcOperations, times(1)).query(isA(String.class),
            isA(PreparedStatementSetter.class), isA(ResultSetExtractor.class));
}

@Test
@SuppressWarnings("unchecked")
public void getSessionExpired() {
    Session expired = this.repository.new JdbcSession();
    expired.setLastAccessedTime(Instant.now().minusSeconds(
            MapSession.DEFAULT_MAX_INACTIVE_INTERVAL_SECONDS + 1));
    given(this.jdbcOperations.query(isA(String.class),
            isA(PreparedStatementSetter.class), isA(ResultSetExtractor.class)))
            .willReturn(Collections.singletonList(expired));

    JdbcOperationsSessionRepository.JdbcSession session = this.repository
            .findById(expired.getId());

    assertThat(session).isNull();
    assertPropagationRequiresNew();
    verify(this.jdbcOperations, times(1)).query(isA(String.class),
            isA(PreparedStatementSetter.class), isA(ResultSetExtractor.class));
    verify(this.jdbcOperations, times(1)).update(startsWith("DELETE"),
            eq(expired.getId()));
}

@Test
@SuppressWarnings("unchecked")
public void getSessionFound() {
    Session saved = this.repository.new JdbcSession("primaryKey", new MapSession());
    saved.setAttribute("savedName", "savedValue");
    given(this.jdbcOperations.query(isA(String.class),
            isA(PreparedStatementSetter.class), isA(ResultSetExtractor.class)))
            .willReturn(Collections.singletonList(saved));

    JdbcOperationsSessionRepository.JdbcSession session = this.repository
            .findById(saved.getId());

    assertThat(session.getId()).isEqualTo(saved.getId());
    assertThat(session.isNew()).isFalse();
    assertThat(session.<String>getAttribute("savedName")).isEqualTo("savedValue");
    assertPropagationRequiresNew();
    verify(this.jdbcOperations, times(1)).query(isA(String.class),
            isA(PreparedStatementSetter.class), isA(ResultSetExtractor.class));
}

@Test
@SuppressWarnings("unchecked")
public void findByIndexNameAndIndexValuePrincipalIndexNameNotFound() {
    String principal = "username";
    given(this.jdbcOperations.query(isA(String.class),
            isA(PreparedStatementSetter.class), isA(ResultSetExtractor.class)))
            .willReturn(Collections.emptyList());

    Map<String, JdbcOperationsSessionRepository.JdbcSession> sessions = this.repository
            .findByIndexNameAndIndexValue(
                    FindByIndexNameSessionRepository.PRINCIPAL_NAME_INDEX_NAME,
                    principal);

    assertThat(sessions).isEmpty();
    assertPropagationRequiresNew();
    verify(this.jdbcOperations, times(1)).query(isA(String.class),
            isA(PreparedStatementSetter.class), isA(ResultSetExtractor.class));
}

/**
 * Expire ia.
 * 
 * @param id the id
 */
protected void expireIA(final Long id)
{   
    getJdbcTemplate().update(
            "update identityassoc set state=" + IdentityAssoc.EXPIRED +
            " where id=?" +
            " and not exists" +
            " (select 1 from iaaddress" +
            " where identityassoc_id=identityassoc.id" +
            " and validendtime>=?)",
            new PreparedStatementSetter() {
                @Override
                public void setValues(PreparedStatement ps)
                        throws SQLException {
                    ps.setLong(1, id);
                    java.sql.Timestamp now = new java.sql.Timestamp((new Date()).getTime());
                    ps.setTimestamp(2, now, Util.GMT_CALENDAR);
                }
            });

}

/**
 * Delete expired ia.
 * 
 * @param id the id
 */
protected void deleteExpiredIA(final Long id)
{   
    getJdbcTemplate().update(
            "delete from identityassoc" +
            " where id=?" +
            " and not exists (select 1 from iaaddress" +
            "                 where identityassoc_id=identityassoc.id" +
            "                 and validendtime is not null and validendtime>=?)",
            new PreparedStatementSetter() {
                @Override
                public void setValues(PreparedStatement ps)
                        throws SQLException {
                    ps.setLong(1, id);
                    java.sql.Timestamp now = new java.sql.Timestamp((new Date()).getTime());
                    ps.setTimestamp(2, now, Util.GMT_CALENDAR);
                }
            });

}

/**
 * Expire i as.
 */
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);
                }
            });
}

public void update(final IdentityAssoc ia)
{
    String updateQuery = "update identityassoc" +
                        " set duid=?," +
                        " iatype=?," +
                        " iaid=?," +
                        " state=?" +
                        " where id=?";
    getJdbcTemplate().update(updateQuery,
            new PreparedStatementSetter() {
        @Override
        public void setValues(PreparedStatement ps) throws SQLException {
            ps.setBytes(1, ia.getDuid());
            ps.setByte(2, ia.getIatype());
            ps.setLong(3, ia.getIaid());
            ps.setByte(4, ia.getState()); 
            ps.setLong(5, ia.getId());
        }
    });
}

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
                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());
}

public List<IaPrefix> findUnusedByRange(final InetAddress startAddr, final InetAddress endAddr)
{
    final long offerExpiration = new Date().getTime() - 12000;  // 2 min = 120 sec = 12000 ms
       return getJdbcTemplate().query(
               "select * from iaprefix" +
               " where ((state=" + IaPrefix.ADVERTISED +
               " and starttime <= ?)" +
               " or (state=" + IaPrefix.EXPIRED +
               " or state=" + IaPrefix.RELEASED + "))" +
               " and prefixaddress >= ? and prefixaddress <= ?" +
               " order by state, validendtime, ipaddress",
               new PreparedStatementSetter() {
                @Override
                public void setValues(PreparedStatement ps) throws SQLException {
                    java.sql.Timestamp ts = new java.sql.Timestamp(offerExpiration);
                    ps.setTimestamp(1, ts);
                    ps.setBytes(2, startAddr.getAddress());
                    ps.setBytes(3, endAddr.getAddress());
                }                   
               },
               new IaPrefixRowMapper());
}

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
                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());
}

public List<IaAddress> findUnusedByRange(final InetAddress startAddr, final InetAddress endAddr)
{
    final long offerExpiration = new Date().getTime() - 12000;  // 2 min = 120 sec = 12000 ms
       return getJdbcTemplate().query(
               "select * from iaaddress" +
               " where ((state=" + IaAddress.ADVERTISED +
               " and starttime <= ?)" +
               " or (state=" + IaAddress.EXPIRED +
               " or state=" + IaAddress.RELEASED + "))" +
               " and ipaddress >= ? and ipaddress <= ?" +
               " order by state, validendtime, ipaddress",
               new PreparedStatementSetter() {
                @Override
                public void setValues(PreparedStatement ps) throws SQLException {
                    java.sql.Timestamp ts = new java.sql.Timestamp(offerExpiration);
                    ps.setTimestamp(1, ts);
                    ps.setBytes(2, startAddr.getAddress());
                    ps.setBytes(3, endAddr.getAddress());
                }                   
               },
               new IaAddrRowMapper());
}

/**
 * Update ia options.
 */
protected void updateIaOptions(final InetAddress inetAddr, 
                                final Collection<DhcpOption> iaOptions)
{
    getJdbcTemplate().update("update dhcplease" +
            " set ia_options=?" +
            " where ipaddress=?",
            new PreparedStatementSetter() {
        @Override
        public void setValues(PreparedStatement ps)
                throws SQLException {
            ps.setBytes(1, encodeOptions(iaOptions));
            ps.setBytes(2, inetAddr.getAddress());
        }
    });
}

/**
 * Update ipaddr options.
 */
protected void updateIpAddrOptions(final InetAddress inetAddr,
                                final Collection<DhcpOption> ipAddrOptions)
{
    getJdbcTemplate().update("update dhcplease" +
            " set ipaddr_options=?" +
            " where ipaddress=?",
            new PreparedStatementSetter() {
        @Override
        public void setValues(PreparedStatement ps)
                throws SQLException {
            ps.setBytes(1, encodeOptions(ipAddrOptions));
            ps.setBytes(2, inetAddr.getAddress());
        }
    });
}