public Visitor mapRow(ResultSet rs, int rowNum) throws SQLException {
Visitor visitor = new Visitor();
visitor.setId(rs.getInt("id"));
visitor.setIp(rs.getString("ip"));
visitor.setCreate_time(rs.getTimestamp("create_time"));
visitor.setUa(rs.getString("ua"));
return visitor;
}
/**
* 更新
*
* @param visitor 访客对象
* @return long 返回受影响的列
*/
@CacheEvict(value = "Visitor", allEntries = true)
public long update(Visitor visitor) {
final String sql =
" update " + Visitor.TableName() + " set ip=?,create_time=?,ua=? where id=? ";
log.info(sql);
return jdbcTemplate.update(
sql, visitor.getIp(), visitor.getCreate_time(), visitor.getUa(), visitor.getId());
}
/**
* 根据id来删除Visitor对象
*
* @param visitorId 文章对象id
* @return boolean
*/
@CacheEvict(value = "Visitor", allEntries = true)
public boolean delete(long visitorId) {
final String sql = " delete from " + Visitor.TableName() + " where id=? ";
log.info(sql);
int result = jdbcTemplate.update(sql, visitorId);
return result > 0;
}
/**
* 分页列出文章
*
* @param pageIndex 页码
* @param pageSize 页数
* @return list
*/
@Cacheable(value = "Visitor", key = "'listByPage'+#pageIndex+#pageSize")
public List<Visitor> listByPage(int pageIndex, int pageSize) {
final String sql =
" select * from " + Visitor.TableName() + " order by create_time desc limit ?,? ";
log.info(sql);
return jdbcTemplate.query(sql, new VisitorRowMapper(), (pageIndex - 1) * pageSize, pageSize);
}
/**
* 增加
*
* @param visitor visitor对象
* @return 主键long
*/
@CacheEvict(value = "Visitor", allEntries = true)
public long add(final Visitor visitor) {
KeyHolder keyHolder = new GeneratedKeyHolder();
final String sql =
" insert into " + Visitor.TableName() + " ( ip,create_time,ua ) values(?,?,?) ";
log.info(sql);
jdbcTemplate.update(
new PreparedStatementCreator() {
public PreparedStatement createPreparedStatement(Connection connection)
throws SQLException {
PreparedStatement ps =
connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
ps.setString(1, visitor.getIp());
ps.setTimestamp(2, visitor.getCreate_time());
ps.setString(3, visitor.getUa());
return ps;
}
},
keyHolder);
return keyHolder.getKey().longValue();
}
/**
* Visitor
*
* @param visitorId visitor的id
* @return visitor
*/
@Cacheable(value = "Visitor", key = "'find'+#visitorId")
public Visitor find(long visitorId) {
final String sql = " select * from " + Visitor.TableName() + " where id=? ";
log.info(sql);
return jdbcTemplate.queryForObject(sql, new VisitorRowMapper(), visitorId);
}
/**
* 统计所有的访客数
*
* @return long
*/
@Cacheable(value = "Visitor", key = "'count'")
public long count() {
final String sql = " select count(id) from " + Visitor.TableName();
log.info(sql);
return jdbcTemplate.queryForObject(sql, Long.class);
}