封装JDBC分页查询返回泛型结果集

1、引入maven依赖

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
		    <groupId>org.springframework</groupId>
		    <artifactId>spring-jdbc</artifactId>
		    <version>4.3.16.RELEASE</version>
		</dependency>

2、直接上代码

package cn.toroot.bj.core.constant;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.List;
import java.util.Map;

/**
 * 当要查询指定数据源的数据时，需继承此类
 * @author Mr peng
 * @date 2020-7-2 15:35:12
 */
public class BaseService {

    protected Logger logger = LoggerFactory.getLogger(this.getClass());

    /**
     * 分页查询
     * @param jt JdbcTemplate
     * @param countSql 计算总数SQL
     * @param sql SQL
     * @param args 参数
     * @param pageNo 第几页
     * @param pageSize 一页多少条
     * @param elementType 返回的数据类型
     * @param <E>
     * @return Page
     */
    protected <E> Page<E>  queryPage(final JdbcTemplate jt, final String countSql, final String sql, final Object args[], int pageNo, final int pageSize, Class<E> elementType) {
        // determine how many rows are available
        final int total = jt.queryForObject(countSql, Integer.class, args);

        // create the page object
        if (pageNo > 0) {
            pageNo--;
        }
        Pageable pageable = new PageRequest(pageNo, pageSize);
        // 注意此处不能用jt.queryForList(sql, args, elementType);
        if(elementType == null){
            String sql1 = sql + " limit " + pageNo * pageSize + "," + pageSize;
            List<Map<String, Object>> list =  jt.queryForList(sql1,args);
            return new PageImpl<E>((List<E>) list, pageable, total);
        }else{
            List<E> list = jt.query(sql + " limit " + pageNo * pageSize + "," + pageSize, args, new BeanPropertyRowMapper<E>(elementType));
            return new PageImpl<E>( list, pageable, total);
        }
    }

    /**
     * 此方法会自动生成算总数的SQL，大多数情况可用此方法
     * @param jt JdbcTemplate
     * @param sql SQL
     * @param args 参数
     * @param pageNo 第几页
     * @param pageSize 一页多少条
     * @param elementType 返回的数据类型
     * @param <E>
     * @return Page
     */
    protected <E> Page<E> queryPage(final JdbcTemplate jt, final String sql, final Object args[], int pageNo, final int pageSize, Class<E> elementType) {
        String countSql = "select count(*) from (" + sql + " )";
        return this.queryPage(jt, countSql, sql, args, pageNo, pageSize, elementType);
    }

    protected <E> Page<E> queryPage(final JdbcTemplate jt, final String sql, final Object args[], int pageNo, final int pageSize) {
        return this.queryPage(jt, sql, args, pageNo, pageSize, null);
    }
}