学步园

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