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