private SimpleJdbcTemplate simpleJdbcTemplate;
//查询实体对象:
public AdPostInfoBean selectByKey(int apId, int targetId, int targetType) {
String sql = "select * from adpostinfo where apid=? and targetid=? and targettype=?";
try {
return this.simpleJdbcTemplate.queryForObject(sql, ParameterizedBeanPropertyRowMapper
.newInstance(AdPostInfoBean.class), apId, targetId, targetType);
}
catch (EmptyResultDataAccessException e) {
return null;
}
}
//查询列表
public List<AdPostInfoBean> selectByApid(int apId) {
String sql = "select * from adpostinfo where apid=?";
return this.simpleJdbcTemplate.query(sql, ParameterizedBeanPropertyRowMapper
.newInstance(AdPostInfoBean.class), apId);
}
//删除
public int deleteByApId(int apId) {
String sql = "delete from adpostinfo where apid=?";
return this.simpleJdbcTemplate.update(sql, apId);
}
//添加
public int insert(AdPlayInfoBean adPlayInfo){
String sql = "insert into adplayinfo (APID, PBID, ModifyTime, Status) values (:apId, :pbId, :modifytime, :status)";
KeyHolder keyHolder = new GeneratedKeyHolder();
this.simpleJdbcTemplate.getNamedParameterJdbcOperations().update(
sql,
new BeanPropertySqlParameterSource(adPlayInfo),
keyHolder
);
return keyHolder.getKey().intValue();
}
//普通修改
public int updateStatusByPbId(int pbId, int oldStatus, int newStatus){
String sql = "update adplayinfo set Status = ? where pbId = ? and Status = ?";
return this.simpleJdbcTemplate.update(sql, newStatus, pbId, oldStatus);
}
//批量修改
public int[] update(List<AdPostInfoBean> adPostInfos) {
if (adPostInfos == null)
return new int[] {};
String sql = "update adpostinfo set postuser:=postuser,posttime=:posttime,poststatus=:poststatus"
+ " where apid=:apid and targetid=:targetid and targettype=:targettype";
return this.simpleJdbcTemplate.batchUpdate(sql, SqlParameterSourceUtils
.createBatch(adPostInfos.toArray()));
}
//查询表数量
public int getUsedRotateTime(int pbid){
StringBuffer sqlbuf = new StringBuffer("select sum(Length) from ADDataRotator a"
+ " join ADPlanRotator b on a.apid = b.apid"
+ " join ADPlayInfo c on c.apid = b.apid where 1=1");
sqlbuf.append(" and a.ADType = ").append(PlayTaskType.video);
sqlbuf.append(" and c.status > 0 and b.RotatorCycle > 0 and c.PBID = ?");
String sql = sqlbuf.toString();
try{
return this.simpleJdbcTemplate.queryForInt(sql, pbid);
}
catch (EmptyResultDataAccessException e) {
return 0;
}
}
利用SimpleJdbcTemplate进行增删改查
最新推荐文章于 2021-11-21 13:59:24 发布