- 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进行增删改查
最新推荐文章于 2020-11-29 12:23:56 发布