详解jdbcTemplate和namedParameterJdbcTemplate

https://segmentfault.com/a/1190000010907688

https://www.cnblogs.com/xuyuanjia/p/5706372.html

 

 @Autowired
    private JdbcTemplate jdbcTemplate;

@Override
    public PageVO<SpecialVO> fetchSelectSpecialPage(Integer pageNum, Integer pageSize, Long compId, String searchKeyword) {
        StringBuilder sql = new StringBuilder(" from cms_special a where a.del_flag = '0' ");
        if (PsUserUtils.getPsUserId() != 1) {
            Long developerId = userService.fetchUserDeveloperId(PsUserUtils.getPsUserId());
            if (ObjectUtils.isEmpty(developerId)) {
                boolean isWhole = managerService.checkUserIsWhole(PsUserUtils.getPsUserId());
                if (isWhole) {
                    sql.append(" and a.use_type = 0 ");
                } else {
                    Long ownCompId = managerService.getCompByUserId(PsUserUtils.getPsUserId());
                    if (ownCompId != null) {
                        sql.append(" and ((a.comp_id =").append(ownCompId).append(" and a.use_type = 1) or a.use_type = 0) ");
                    } else {
                        sql.append(" and 1 = 0 ");
                    }
                }
            } else {
                if (compId != null) {
                    sql.append(" and ((a.use_type = 1 and a.comp_id =").append(compId).append(") or a.use_type = 0) ");
                } else {
                    sql.append(" and a.use_type = 0 ");
                }
            }
        }
        if(StringUtils.isNotEmpty(searchKeyword)){
            sql.append(" and a.special_name like " + "'%").append(searchKeyword).append("%'");
        }
        StringBuilder sql1 = new StringBuilder("select count(1) ").append(sql);
        //获取总记录
        Integer count = jdbcTemplate.queryForObject(sql1.toString(), Integer.class);
        log.info("fetchSpecialPage sql1: {}", sql1);
        sql.append(" order by a.create_date desc ");
        StringBuilder sql2 = new StringBuilder("select a.* ")
                .append(sql).append(" limit ").append(pageNum*pageSize).append(",").append(pageSize);
        log.info("fetchSpecialPage sql2: {}", sql2);
        List<SpecialVO> list = jdbcTemplate.query(sql2.toString(), SPECIAL_ROW_MAPPER);
        //查询动态数
        loadArticleCount(list);

        return new PageVO(count, list);
    }

    private static final RowMapper<SpecialVO> SPECIAL_ROW_MAPPER = (rs, rowNum) -> {
        SpecialVO vo = new SpecialVO();
        vo.setSpecialLogo(rs.getString("logo"));
        vo.setSort(rs.getInt("sort"));
        vo.setSpecialId(rs.getLong("special_id"));
        vo.setSpecialName(rs.getString("special_name"));
        vo.setUpdateDate(rs.getDate("update_date"));
        return vo;
    };

@ApiModel(description = "动态专题实体")
@Data
public class SpecialVO {

    /** 专题编号 */
    @ApiModelProperty("专题编号")
    private Long specialId;

    /** 专题子项名 */
    @ApiModelProperty("专题子项名")
    private String specialName;

    /** logo */
    @ApiModelProperty("专题logo")
    private String specialLogo;

    /** 排序 */
    @ApiModelProperty("排序")
    private Integer sort;

    /** 动态数 */
    @ApiModelProperty("动态数")
    private Integer articleCount;

    /** 更新时间 */
    @ApiModelProperty("更新时间")
    private Date updateDate;

    /** 文章集合 */
    @ApiModelProperty("动态集合")
    List<DynamicInfoVO> list;

    public static SpecialVO convertToSpecialVO(SpecialEntity entity){
        if(entity == null){
            return null;
        }
        SpecialVO vo = new SpecialVO();
        vo.setSpecialLogo(entity.getLogo());
        vo.setSort(entity.getSort());
        vo.setSpecialId(entity.getSpecialId());
        vo.setSpecialName(entity.getSpecialName());
        vo.setUpdateDate(entity.getUpdateDate());
        return vo;
    }
}