使用JdbcTemplate实现Oracle分页查询

使用JdbcTemplate实现Oracle分页查询

1.简介

JdbcTemplate是Spring框架提供的一个强大的数据库访问工具，它封装了JDBC的细节，简化了开发人员与数据库之间的交互。它提供了一种优雅且高效的方式来执行SQL语句，并且对各种数据库提供了统一的访问接口。但是有一个缺点，就是需要自己写分页逻辑。本文实现了使用NamedParameterJdbcTemplate实现了Oracle分页。

2.NamedParameterJdbcTemplate和JdbcTemplate

NamedParameterJdbcTemplate和JdbcTemplate是Spring框架提供的两种数据库访问工具，它们在实现上有一些区别。

1. 参数绑定方式：
   • JdbcTemplate使用?占位符进行参数绑定，参数值通过数组传递给SQL语句执行方法。
   • NamedParameterJdbcTemplate使用命名参数进行参数绑定，参数值通过Map或SqlParameterSource对象传递给SQL语句执行方法。通过使用具有名称的参数，我们可以更直观地阅读和编写SQL语句。
2. 参数重用性：
   • JdbcTemplate不支持参数的重用，即每次执行SQL语句时都需要重新绑定参数。
   • NamedParameterJdbcTemplate支持参数的重用，可以在多次执行SQL语句时共享相同的参数对象，提高了效率。
3. SQL语句书写方式：
   • JdbcTemplate需要在SQL语句中使用问号占位符来指定参数位置，例如SELECT * FROM your_table WHERE id = ?。
   • NamedParameterJdbcTemplate可以在SQL语句中使用命名参数，例如SELECT * FROM your_table WHERE id = :id。
4. 可读性和维护性：
   • NamedParameterJdbcTemplate在处理较复杂的SQL语句时具有更好的可读性和易于维护性。通过使用具有描述性名称的命名参数，可以使SQL语句更加清晰和易于理解。

3.Oracle实现分页

Oracle数据库使用ROWNUM来进行分页，我们只需在查询语句中添加ROWNUM的限制条件即可。实现分页之前要根据当前页和每页显示数量推导出开始行号和结束行号，可以用以下公式：

​ 开始行号 = (当前页码 - 1) * 每页显示数量

​ 结束行号 = 当前页码 * 每页显示数量

实例SQL语句：

SELECT * FROM (
    SELECT t.*, ROWNUM AS rn
    FROM (
        SELECT * 
        FROM your_table
        ORDER BY your_column
    ) t WHERE ROWNUM <= :endRow -- 结束行号
)
WHERE rn > :startRow; -- 开始行号

有了以上的公式和sql，我们可以实现一下代码，前提：必须要有currentPage和pageSize。废话不多说，直接上Java代码

JdbcPage.java

@Data
public class JdbcPage<T> {
    /**
     * 总行数
     */
    private int totalRows;

    /**
     * 每页显示数量
     */
    private int pageSize;

    /**
     * 当前页码
     */
    private int currentPage;

    /**
     * 总行数
     */
    private int totalPages;

    /**
     * 数据
     */
    private List<T> list;

    public JdbcPage(int totalRows, int pageSize, int currentPage, List<T> list) {
        this.totalRows = totalRows;
        this.pageSize = pageSize;
        this.currentPage = currentPage;
        this.list = list;
        // 计算总页数
        this.totalPages = totalRows % pageSize == 0 ? totalRows / pageSize:(totalRows/pageSize)+1;
    }
}

HttpClientUtil.java

@Component
public class JdbcTemplateUtil {
    @Autowired
    private NamedParameterJdbcTemplate jdbcTemplate;

    /**
     * Oracle分页，参数参考NamedParameterJdbcTemplate.query方法
     * @param sql       需要分页的sql
     * @param paramMap  sql的参数，currentPage和pageSize必传
     * @param rowMapper 返回实体类
     * @return
     * @param <T>
     */
    public <T> JdbcPage<T> pageList(String sql, Map<String, Object> paramMap, RowMapper<T> rowMapper){
        int currentPage  = (int) paramMap.getOrDefault("currentPage","-1");
        int pageSize  = (int) paramMap.getOrDefault("pageSize","-1");
        if (currentPage == -1 || pageSize == -1) {
            throw new IllegalArgumentException("请检查参数，缺少currentPage或pageSize！");
        }
        // 查询总页数
        String countSql = "SELECT count(*) from (" + sql + ")";
        Integer totalRows = jdbcTemplate.queryForObject(countSql, paramMap, Integer.class);

        // 显示的行数大于总行数，直接查询
        if (pageSize > totalRows) {
            List<T> list = jdbcTemplate.query(sql, paramMap,rowMapper);
            return new JdbcPage<>(totalRows,pageSize,currentPage,list);
        }

        // 封装分页sql
        String pageSql;
        // 第一页查询，直接用rowNum即可
        if (currentPage == 1) {
            pageSql = "select * from (" + sql + ") where rownum <= :rowNum";
            paramMap.put("rowNum",pageSize);
        }else {
            pageSql = "SELECT * FROM ( SELECT row_.*, ROWNUM rownum_ FROM(" + sql + ") row_ WHERE ROWNUM <= :endRow ) WHERE rownum_ > :startRow";
            paramMap.put("startRow",(currentPage - 1) * pageSize);
            paramMap.put("endRow",currentPage * pageSize);
        }
        List<T> list = jdbcTemplate.query(pageSql, paramMap,rowMapper);
        return new JdbcPage<>(totalRows,pageSize,currentPage,list);
    }
}

测试类：MyTest.java

@SpringBootTest
class MyTest {
    @Autowired
    private JdbcTemplateUtil jdbcTemplateUtil;

    @Test
    void contextLoads() {
        String sql = "SELECT * FROM T_USER";
        Map<String,Object> params = new HashMap<>();
        params.put("currentPage",1);
        params.put("pageSize",10);
        JdbcPage<User> userJdbcPage = jdbcTemplateUtil.pageList(sql, params, new BeanPropertyRowMapper<>(User.class));
        System.out.println(userJdbcPage);
    }

}