springBoot jdbcTemplate分页 多条件查询 postgresql数据库

springBoot jdbcTemplate分页 多条件查询 postgresql数据库

PageVo

@Data
public class PageVo<T> {

    private Integer total;        //总记录数
    private List<T> list;    //结果集
    private Integer pageNum;    // 第几页
    private Integer pageSize;    // 每页记录数
    private Integer pages;        // 总页数

}

PageUtil

/**
 * @创建人 jiangchun
 * @创建时间 2019/10/23
 * @描述
 */
@Slf4j
@Component
public class PageUtil {

    @Resource
    private JdbcTemplate jdbcTemplate;

    public PageVo queryForPage(String sql, Integer pageNum, Integer pageSize, Object[] params, RowMapper rowMapper) throws DataAccessException {
        if (pageNum == null || pageSize == null) {
            return null;
        }
        if (pageNum < 1) {
            pageNum = 1;
        }
        if (pageSize < 1) {
            pageSize = 10;
        }
        PageVo result = new PageVo();

        //获取记录条数
        String countSql = "select count(1) as count from (" + sql + ") temp";
        log.info("countSql {}", countSql);

        List countList = jdbcTemplate.queryForList(countSql, params, Integer.class);

        result.setTotal((Integer) countList.get(0));
        result.setPageNum(pageNum);
        result.setPageSize(pageSize);

        int pageCount = result.getTotal() % result.getPageSize();
        result.setPages(pageCount == 0 ? (result.getTotal() / result.getPageSize()) : (result.getTotal() / result.getPageSize() + 1));

        sql += parseLimit(result);
        log.info("queryLimitSql {}", sql);

        List data = jdbcTemplate.query(sql, params, rowMapper);
        result.setList(data);
        return result;
    }

    private String parseLimit(PageVo pagination) {

        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append(" limit ");
        stringBuffer.append(pagination.getPageSize());

        stringBuffer.append(" offset ");
        stringBuffer.append(pagination.getPageSize()*(pagination.getPageNum()-1));

        return stringBuffer.toString();
    }


}

dao层使用

@Repository
public class demoDao {
    @Resource
    private PageUtil pageUtil;
    
    public PageVo getList(SearchParam searchParam) {
        ArrayList params = new ArrayList();
        String sql = "select * from user where 1=1 ";
        if (searchParam.getName() != null) {
            sql += " and name like '%?%'";
            params.add(searchParam.getName());
        }
        if (searchParam.getCreateTime() != null) {
            sql += " and create_Time >= ?";
            params.add(searchParam.getCreateTime());
        }
        sql += " order by id desc";

        Integer pageNum = searchParam.getPageNum();
        Integer pageSize = searchParam.getPageSize();
        PageVo res = pageUtil.queryForPage(sql, pageNum, pageSize, params.toArray(), new BeanPropertyRowMapper<>(User.class));
        return res;
    }
}

NamedParameterJdbcTemplate版

PageUtils

/**
 * @创建人 jiangchun
 * @创建时间 2019/10/25
 * @描述
 */
@Log4j2
@Component
public class PageUtils {

    @Resource
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    public PageVo queryForPage(String sql, Integer pageNum, Integer pageSize, Object param, RowMapper rowMapper) throws DataAccessException {
        if (pageNum == null || pageSize == null) {
            return null;
        }
        if (pageNum < 1) {
            pageNum = 1;
        }
        if (pageSize < 1) {
            pageSize = 10;
        }
        PageVo result = new PageVo();

        //获取记录条数
        String countSql = "select count(1) as count from (" + sql + ") temp";
        log.info("countSql {}", countSql);

        List countList = namedParameterJdbcTemplate.queryForList(countSql, new BeanPropertySqlParameterSource(param), Integer.class);

        result.setTotal((Integer) countList.get(0));
        result.setPageNum(pageNum);
        result.setPageSize(pageSize);

        int pageCount = result.getTotal() % result.getPageSize();
        result.setPages(pageCount == 0 ? (result.getTotal() / result.getPageSize()) : (result.getTotal() / result.getPageSize() + 1));

        sql += parseLimit(result);

        List data = namedParameterJdbcTemplate.query(sql, new BeanPropertySqlParameterSource(param), rowMapper);
        result.setList(data);
        return result;
    }

    private String parseLimit(PageVo pagination) {

        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append(" limit ");
        stringBuffer.append(pagination.getPageSize());

        stringBuffer.append(" offset ");
        stringBuffer.append(pagination.getPageSize()*(pagination.getPageNum()-1));

        return stringBuffer.toString();
    }
}

使用

@Repository
public class ReportReviewDao {
    @Resource
    private PageUtils pageUtils;
    
    public PageVo getList(ReportReviewSearchParam reportSearchParam) {
        String sql = "select * from user where 1=1 ";
        if (searchParam.getName() != null) {
            sql += " and name like '%:name%'";
        }
        if (searchParam.getCreateTime() != null) {
            sql += " and create_Time >= :createTime";
        }
        sql += " order by id desc";

        Integer pageNum = reportSearchParam.getPageNum();
        Integer pageSize = reportSearchParam.getPageSize();
        PageVo res = pageUtils.queryForPage(sql, pageNum, pageSize, reportSearchParam, new BeanPropertyRowMapper<>(User.class));
        return res;
    }
}