@GetMapping("/querySum")
public TableDataInfo display(HttpServletRequest request,Integer pageNum,Integer pageSize)
{
//type 1 个人 2 机构
String type = request.getParameter("type");
String start = request.getParameter("start");
String end = request.getParameter("end");
List<Download> list = new ArrayList<>();
String userid = UserInfoBySt.userid;
String tenantid = UserInfoBySt.tenantid;
int totalCnt = 0;
List<JournalismSumDto> journalismSumDtos=new ArrayList<>();
if(StringUtils.isNotBlank(tenantid)&& StringUtils.isNotBlank(userid)){
if(pageNum==null) {
pageNum = 1;
}
if(pageSize==null) {
pageSize = 10;
}
int startRow = (pageNum-1)*pageSize;
StringBuffer countSql = new StringBuffer("select count(1) ");
StringBuffer resSql = new StringBuffer("SELECT a.id,a.click_count clickCount,a.download_count downloadCount,b.title name,b.publish publish,c.NAME creator ,b.displayTime displaytime");
StringBuffer sql = new StringBuffer(" FROM _ext.tb_jou a,journa b,template2.user_ c where a.id=b.journalismId and c.USERID=b.creator and b.deleteflag='0' ");
List<Object> params = new ArrayList<Object>();
sql.append(" and b.displayTime between ? and ? ");
if(StringUtils.isNotBlank(start)){
params.add(start);
}else{
params.add("1900-07-01 00:00:00");
}
if(StringUtils.isNotBlank(end)){
params.add(end);
}else{
params.add("2100-07-01 00:00:00");
}
if("1".equals(type)){
sql.append("and b.creator = ? ");
params.add(userid);
}
if("2".equals(type)){
sql.append("and b.organizationid=? ");
params.add(tenantid);
}
countSql.append(sql);
totalCnt = jdbcTemplate.queryForObject(countSql.toString(),Integer.class,params.toArray());
resSql.append(sql).append(" order by b.displayTime desc limit ?,? ");
params.add(startRow);
params.add(pageSize);
RowMapper<JournalismSumDto> rowMapper=new BeanPropertyRowMapper<>(JournalismSumDto.class);
journalismSumDtos = this.jdbcTemplate.query(resSql.toString(),rowMapper, params.toArray());
}
TableDataInfo tableDataInfo = new TableDataInfo(journalismSumDtos, totalCnt);
tableDataInfo.setCode(HttpStatus.SUCCESS);
tableDataInfo.setMsg("查询成功");
return tableDataInfo;
}
JDBCTemplate分页
于 2022-09-06 18:29:18 首次发布