使用JdbcTemplate实现Oracle分页查询
1.简介
JdbcTemplate是Spring框架提供的一个强大的数据库访问工具,它封装了JDBC的细节,简化了开发人员与数据库之间的交互。它提供了一种优雅且高效的方式来执行SQL语句,并且对各种数据库提供了统一的访问接口。但是有一个缺点,就是需要自己写分页逻辑。本文实现了使用NamedParameterJdbcTemplate实现了Oracle分页。
2.NamedParameterJdbcTemplate和JdbcTemplate
NamedParameterJdbcTemplate
和JdbcTemplate
是Spring框架提供的两种数据库访问工具,它们在实现上有一些区别。
- 参数绑定方式:
JdbcTemplate
使用?
占位符进行参数绑定,参数值通过数组传递给SQL语句执行方法。NamedParameterJdbcTemplate
使用命名参数进行参数绑定,参数值通过Map或SqlParameterSource对象传递给SQL语句执行方法。通过使用具有名称的参数,我们可以更直观地阅读和编写SQL语句。
- 参数重用性:
JdbcTemplate
不支持参数的重用,即每次执行SQL语句时都需要重新绑定参数。NamedParameterJdbcTemplate
支持参数的重用,可以在多次执行SQL语句时共享相同的参数对象,提高了效率。
- SQL语句书写方式:
JdbcTemplate
需要在SQL语句中使用问号占位符来指定参数位置,例如SELECT * FROM your_table WHERE id = ?
。NamedParameterJdbcTemplate
可以在SQL语句中使用命名参数,例如SELECT * FROM your_table WHERE id = :id
。
- 可读性和维护性:
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);
}
}