利用SimpleJdbcTemplate进行增删改查

来源：互联网发布：java架构师岗位职责编辑：程序博客网时间：2024/06/18 13:07
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;          }                }
0 0