JDBC代码实现之第九版

前言

任何软件都有查询功能，每一个查询都支持分页，因为如果数据太多的话，无论从时效性，稳定性，易用性，还是美观性上，分页查询的必要性都很明确。所以在开发时一定会采用分页的方式。而只要有分页存在，就一定会伴随着排序功能，比如销量排序，综合排序等。所以分页和排序会相互依赖，是软件开发必不可少的组成部分，这里讨论JDBC对分页的支持。

1.分页SQL

• 对于分页来说，不同的数据库，它的分页sql也是不一样的，ORACLE是一种语法，MySQL是另一种语法，SQLServer又是另一种语法，一般在每一种数据库的官方手册里都有所介绍，这里以ORACLE数据库为例来实现分页功能。比如想对员工分页查询的话，首先需要查询出员工的所有数据，并按照ID进行排序：

  select * from emps order by empno;

  以这个查询结果为基准，把它做成一个子查询。在此基础上，我们把当前sql的查询结果别名标记为e，并在这个sql的外面再套一层select，同样，还是查询全部数据，但为其添加一个伪列rownum，取名为r，让这个结果是有序的，最终的目的是希望利用这个伪列r来实现分页，所谓分页，实际就是圈定查询结果的范围，而伪列rownum的作用就是指定这个范围的条件 (如果用ID指定的话是不靠谱的，因为ID可以因为被人为的修改或删除数据，而导致ID不连续，中间被断开，使得每页数据返回的条数不可靠，伪列是我们特意加的一列，所以它肯定是连续的)：

  select e.*, rownum r from (select * from emps oder by empno) e

  最后，我们以查询到的，带有伪列的全部数据为基准，把它再做成一个子查询，在此基础上就可以实现分页。我们在这个sql的外面再加一层select，以伪列rownum为条件指定查询范围，比如指定10<r<=20，从而就实现了ORACLE的分页查询功能：

  select * from (
  select e.*, rownum r from (
  select * from emps oder by empno
  ) e
  ) where r between 11 and 20

  ORACLE的分页语句主要是要利用rownum给我们查询到的结果加一个伪列，让它有序，然后利用它为条件来实现分页。

2.JDBC对分页的实现

我们利用JDBC访问数据库，想要实现分页查询。通常有两种办法，第一种称其为假分页，也有人喜欢叫内存分页，第二种为真分页，也有人喜欢叫物理分页(非内存分页)，通常建议用后者，因为假分页，这是忽悠人的，有着比较大的缺陷。

假分页：

• 在第一次查询时，获取所有数据，并将其存入内存 (在java中想把数据存入内存，只需要将数据存到java对象里，因为java中的对象就存在内存里，而无需要自己去操作内存。比如说我查到所有数据，把每一条数据存入List集合里，然后集合就是存于内存当中。)，以后第N次查询时，就不再访问数据库，而是从内存中取数，即从集合里取数。这样的方式，第一次查询会把所有数据查到存入内存，速度巨慢，但以后再查就从内存中取数，要比从数据库快，因为java是服务器端的代码，在服务器上运行，那服务器可以在北京，数据库可以在上海，我们从北京访问上海的数据库，得到一条数据，距离远，是有网络开销的，而从服务器的内存里取数，是本地的内存，距离近，由距离所决定。
• 假分页的特点是，第一次查询巨慢，再次查询就快，但是比较耗内存，内存中要存很多数据。它只适合数量很小的小项目，小到数据量，可能就几百条，甚至就几十条数据。那么这个项目太小了，项目也就不值钱，卖的话也就值个一两万这么一个水平，就这个项目开发完成以后，它所赚的利润，都养不活你一个程序员，一般在工作中很少接触到这样的项目，所以基本上是不会用到的。

真分页

• 在每次查询时，都是获取一一定范围的数据，每次查询速度都一样，它不会很慢，也不是特别快，也不耗内存。这种真分页的方式，它适合任意的项目，所以工作时一定是用这种方式。那么真分页就需要写分页sql，在ORACLE的分页SQL中需要传入两个条件，查询的起始行和终止行，这两个条件是用户点击页面上的分页按钮得到，比如上一页，下一页，或者某一页时，这个页数与条件之间，肯定还有一个换算关系，通过这个关系，将页数转换成条件传给服务器。
• 假设在浏览器端已经打开了一个网页，页面上显示了一些数据，数据的下方有页码，比如1，2，3，用户点击这个页码，就会向服务器发出一个请求。让服务器执行翻页功能，在服务器端，我们给这个从浏览器得到的页面声明一个变量page存储，那么这个页码对于服务器来说，就是已知条件。除了页码，这其中还有一个必要的条件，每页显是多少条数据，声明一个变量size来存储，这个不是由用户点击得到的，是由需求规定好的。服务器通过这两个条件，计算出指定的数据的范围，再利用JDBC访问数据库，返回给浏览器这个范围的数据。
• 软件开发时，一开始应该是有需求去调研，至于每一页显示多少条数据，这是由需求调研而来，问这个用户他们想一页显示多少条数据，有的用户说，我们公司特别有钱，所以显示器都特别大，你多显示点，每页显示30条，有的用户说，我们公司这个比较紧张，显示器比较小，那每页显示5条就够了，就这样，所以这个由需求调研而来。假设需求规定每页显示5条数据， 那么size=5，这是一个前提，由需求规定：
  
  然后所要查询的数据库的这张表里有8条数据，现在我要查询第二页的数据，用户点了第2页page=2：
  
  由size和page这两个已知条件，就可以知道第二页显示的数据是第678，3条数据，第一页显然是从1到5条数据。那就有了一个新的问题，某一页的数据，它是从哪一行到哪一行，这得有一个换算规则，依据个人习惯，算法也不尽相同，参考算法如下：
  

3.代码分析与实现

下面通过模拟一个具体的案例演示真分页：分页查询员工

代码分析：

假设需求规定了，每页显示10行数据，size=10，再假设用户点了第3页，page=3，现在要做的就是，根据这两个查询条件，查询第3页的10条数据，首先写好JDBC连接数据库的代码结构，创建连接，归还连接等。然后

1. 写分页SQL：

String sql = "select * from (select e.*, rownum r from (select * from emps order by empno) e) where r between ? and ?";
这个sql做了两个子查询，但这个其实是比较简单的sql，因为在实际工作时，没有比这更简单的sql了，工作当中的大部分的业务都比较复杂，几乎就很少有单表查询的时候，往往都是几张表关联，只要一关联，这个sql的代码量肯定就比这个大，有的业务稍微复杂一点，那都是10几张表，甚至更多的表关联，有些时候，那个业务比较复杂，关联的表太多，可能我们写一个sql，像写一篇作文一样，几百行，可壮观了。所以写有层次的sql时，最好内层缩进一下，把它写的有层次感，一旦遇到了问题，便于代码的阅读，修改和维护。

2. 发送SQL&参数赋值&执行SQL：

分页sql写好以后，这个sql当中有两个条件不能写死，要写成问号，分别对应着起始行和终止行，由size和page参数计算得出，用来指定查询数据的范围。有了sql，还需要创建PreparedStatement对象，将sql发送给数据库，并为sql中的问号参数赋值，然后执行这个sql，最终遍历结果集就把数据得到了：

PreparedStatement ps = conn.prepareStatement(sql);
ps.setInt(1, (page)size+1);
ps.setInt(2, pagesize);
ResultSet rs = ps.executeQuery();

代码实现：

/**
 * 分页查询员工
 */
@Test
public void test() {
	//假设需求规定了每页显示10行
	int size = 10;
	//假设用户点了第3页
	int page = 3;
	
	Connection conn = null;
	try {
		conn = DBUtil.getConnection();
		String sql = "select * from (select e.*, rownum r from (select * from emps order by empno) e) where r between ? and ?";
		PreparedStatement ps = conn.prepareStatement(sql);
		//起始行
		ps.setInt(1, (page)*size+1);
		//终止行
		ps.setInt(2, page*size);
		ResultSet rs = ps.executeQuery();
		while(rs.next()) {
			System.out.println(rs.getInt("empno"));
			System.out.println(rs.getString("ename"));
		}
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		DBUtil.close(conn);
	}
}

4.验证

观察控制台的数据是否是10条数据。

5.总结

参考文献(References)

文中如有侵权行为，请联系me。。。。。。。。。。。。。
文中的错误，理解不到位的地方在所难免，也请指教！在成长过程中，也将继续不断完善，不作为专业文章。不喜勿喷。