jdbctemple分页实现

package com.lc.jiaotong.framework.core.page;

import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.lang.NonNull;
import org.springframework.lang.Nullable;
import org.springframework.stereotype.Component;
import org.springframework.util.ObjectUtils;

import java.util.ArrayList;
import java.util.List;
import java.util.Objects;


@Component
@Slf4j
public class PageUtil {

    private final JdbcTemplate jdbcTemplate;
    private final NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Autowired
    public PageUtil(JdbcTemplate jdbcTemplate, NamedParameterJdbcTemplate namedParameterJdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
        this.namedParameterJdbcTemplate = namedParameterJdbcTemplate;
    }

    public <T> PageObject<T> queryPageObject(@NonNull String sql,
                                             @Nullable Object[] queryArgs, BeanPropertyRowMapper<T> rowMapper,
                                             int currentPage, int pageSize) {
        if (currentPage <= 0) throw new RuntimeException("当前页数必须大于1");
        if (pageSize <= 0) throw new RuntimeException("每页大小必须大于1");
        int totalSize = 0;
        try {
            Integer _totalSize = jdbcTemplate.queryForObject("select count(*) from (" + sql + " ) total ", Integer.class, queryArgs);
            totalSize = Objects.isNull(_totalSize) ? 0 : _totalSize;
        } catch (DataAccessException e) {
            log.error(e.getLocalizedMessage());
        }

        if (totalSize == 0) {
            return PageObject.<T>builder().currentPage(1).pageSize(pageSize)
                    .queryList(new ArrayList<>()).queryListSize(0).totalPage(0)
                    .totalSize(0).build();
        }
        int totalPage = totalSize % pageSize == 0 ? totalSize / pageSize : totalSize / pageSize + 1;
        int offset = (currentPage - 1) * pageSize;
        sql = sql + " limit " + pageSize + " offset " + offset;
        List<T> queryList = jdbcTemplate.query(sql, rowMapper, queryArgs);
        return PageObject.<T>builder().currentPage(currentPage)
                .pageSize(pageSize).queryList(queryList)
                .queryListSize(queryList.size()).totalPage(totalPage)
                .totalSize(totalSize).build();
    }

    public <T> Object queryPageObject(@NonNull String sql,
                                      SqlParameterSource paramSource,
                                      RowMapper<T> rowMapper,
                                      Integer currentPage,
                                      Integer pageSize) {
        if (ObjectUtils.isEmpty(currentPage) || ObjectUtils.isEmpty(pageSize)) {
            return namedParameterJdbcTemplate.query(sql, paramSource, rowMapper);
        }

        if (currentPage <= 0) throw new RuntimeException("当前页数必须大于1");
        if (pageSize <= 0) throw new RuntimeException("每页大小必须大于1");
        int totalSize = 0;
        try {
            Integer _totalSize = namedParameterJdbcTemplate.queryForObject("select count(*) from (" + sql + " ) total ", paramSource, Integer.class);
            totalSize = Objects.isNull(_totalSize) ? 0 : _totalSize;
        } catch (DataAccessException e) {
            log.error(e.getLocalizedMessage());
        }

        if (totalSize == 0) {
            return PageObject.<T>builder().currentPage(1).pageSize(pageSize)
                    .queryList(new ArrayList<>()).queryListSize(0).totalPage(0)
                    .totalSize(0).build();
        }
        int totalPage = totalSize % pageSize == 0 ? totalSize / pageSize : totalSize / pageSize + 1;
        int offset = (currentPage - 1) * pageSize;
        sql = sql + " limit " + pageSize + " offset " + offset;
        List<T> queryList = namedParameterJdbcTemplate.query(sql, paramSource, rowMapper);
        return PageObject.<T>builder().currentPage(currentPage)
                .pageSize(pageSize).queryList(queryList)
                .queryListSize(queryList.size()).totalPage(totalPage)
                .totalSize(totalSize).build();
    }

    public <T> Object queryPageObjectORC(@NonNull String sql,
                                         SqlParameterSource paramSource,
                                         RowMapper<T> rowMapper,
                                         Integer currentPage,
                                         Integer pageSize) {
        if (ObjectUtils.isEmpty(currentPage) || ObjectUtils.isEmpty(pageSize)) {
            return namedParameterJdbcTemplate.query(sql, paramSource, rowMapper);
        }

        if (currentPage <= 0) throw new RuntimeException("当前页数必须大于1");
        if (pageSize <= 0) throw new RuntimeException("每页大小必须大于1");
        int totalSize = 0;
        try {
            Integer _totalSize = namedParameterJdbcTemplate.queryForObject("select count(*) from (" + sql + " ) total ", paramSource, Integer.class);
            totalSize = Objects.isNull(_totalSize) ? 0 : _totalSize;
        } catch (DataAccessException e) {
            log.error(e.getLocalizedMessage());
        }
        if (totalSize == 0) {
            return PageObject.<T>builder().currentPage(1).pageSize(pageSize)
                    .queryList(new ArrayList<>()).queryListSize(0).totalPage(0)
                    .totalSize(0).build();
        }
        int totalPage = totalSize % pageSize == 0 ? totalSize / pageSize : totalSize / pageSize + 1;
        sql = "SELECT\n" +
                "\t* \n" +
                "FROM\n" +
                "\t( SELECT ROWNUM AS rowno, t.* FROM (" + sql + ") t WHERE ROWNUM <= " + currentPage * pageSize + " ) table_alias \n" +
                "WHERE\n" +
                "\ttable_alias.rowno >= " + ((currentPage - 1) * pageSize + 1);
        List<T> queryList = namedParameterJdbcTemplate.query(sql, paramSource, rowMapper);
        return PageObject.<T>builder().currentPage(currentPage)
                .pageSize(pageSize).queryList(queryList)
                .queryListSize(queryList.size()).totalPage(totalPage)
                .totalSize(totalSize).build();
    }

    public <T> PageObject<T> queryPageObjectORC(@NonNull String sql,
                                             @Nullable Object[] queryArgs, BeanPropertyRowMapper<T> rowMapper,
                                             int currentPage, int pageSize) {
        if (currentPage <= 0) throw new RuntimeException("当前页数必须大于1");
        if (pageSize <= 0) throw new RuntimeException("每页大小必须大于1");
        int totalSize = 0;
        try {
            Integer _totalSize = jdbcTemplate.queryForObject("select count(*) from (" + sql + " ) total ", Integer.class, queryArgs);
            totalSize = Objects.isNull(_totalSize) ? 0 : _totalSize;
        } catch (DataAccessException e) {
            log.error(e.getLocalizedMessage());
        }

        if (totalSize == 0) {
            return PageObject.<T>builder().currentPage(1).pageSize(pageSize)
                    .queryList(new ArrayList<>()).queryListSize(0).totalPage(0)
                    .totalSize(0).build();
        }
        int totalPage = totalSize % pageSize == 0 ? totalSize / pageSize : totalSize / pageSize + 1;
        sql = "SELECT\n" +
                "\t* \n" +
                "FROM\n" +
                "\t( SELECT ROWNUM AS rowno, t.* FROM (" + sql + ") t WHERE ROWNUM <= " + currentPage * pageSize + " ) table_alias \n" +
                "WHERE\n" +
                "\ttable_alias.rowno >= " + ((currentPage - 1) * pageSize + 1);
        List<T> queryList = jdbcTemplate.query(sql, rowMapper, queryArgs);
        return PageObject.<T>builder().currentPage(currentPage)
                .pageSize(pageSize).queryList(queryList)
                .queryListSize(queryList.size()).totalPage(totalPage)
                .totalSize(totalSize).build();
    }
}

sql=原sql

1.mysql：

        int offset = (currentPage - 1) * pageSize;
        sql = sql + " limit " + pageSize + " offset " + offset;

2.oracle：

        sql = "SELECT\n" +
                "\t* \n" +
                "FROM\n" +
                "\t( SELECT ROWNUM AS rowno, t.* FROM (" + sql + ") t WHERE ROWNUM <= " + currentPage * pageSize + " ) table_alias \n" +
                "WHERE\n" +
                "\ttable_alias.rowno >= " + ((currentPage - 1) * pageSize + 1);