数据库分页Java实现
MySQL分页
主要是MySQL数据库内置LIMIT函数
注意添加mysql的JAR包mysql-connector-java-5.1.13-bin.jar
select * from table LIMIT 5,10; // 检索记录行 6-15
在中小数据量的情况下，这样的SQL足够用了，唯一需要注意的问题就是确保使用了索引。随着数据量的增加，页数会越来越多，查看后几页的SQL就可能类似：
select * from content order by id desc limit 10000, 10
一言以蔽之，就是越往后分页，LIMIT语句的偏移量就会越大，速度也会明显变慢。
此时，我们可以通过2种方式：
一，子查询的分页方式来提高分页效率
select * from 'content' where id <= (select id from 'content' order by id desc LIMIT ".($page-1)*$pagesize.", 1) order by id desc LIMIT $pagesize;
为什么会这样呢？因为子查询是在索引上完成的，而普通的查询时在数据文件上完成的，通常来说，索引文件要比数据文件小得多，所以操作起来也会更有效率。
经过飘易的实测，使用子查询的分页方式的效率比纯LIMIT提高了14-20倍！
二，JOIN分页方式
select * from 'content' as t1 join (select id from 'content' order by id desc LIMIT ".($page-1)*$pagesize.", 1) as t2 where t1.id <= t2.id order by t1.id desc LIMIT $pagesize;
经过我的测试，join分页和子查询分页的效率基本在一个等级上，消耗的时间也基本一致。
MySQL分页Java实现
<%@ page language="java" import="java.util.*,java.sql.*" pageEncoding="gbk"%> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <title>MySQL分页</title> </head> <body> <table> <tr> <td>用户名</td> <td>所在城市</td> <td>职业</td> </tr> <% //加载驱动 Class.forName("com.mysql.jdbc.Driver"); //得到连接 Connection ct = DriverManager.getConnection( "jdbc:mysql://localhost:3306/tour", "root", "root"); Statement sm = ct.createStatement(); //接收pageNow参数 String s_pageNow = request.getParameter("pageNow"); int pageNow = 1; if (s_pageNow != null) { pageNow = Integer.parseInt(s_pageNow); } //查询总页数 int pageCount = 0;//当前为第几页 int pageSize = 3;//每页显示几条记录 int rowCount = 0;//总共有多少条记录 ResultSet rs = sm.executeQuery("select count(*) from users"); if (rs.next()) { rowCount = rs.getInt(1); if (rowCount % pageSize == 0) { pageCount = rowCount / pageSize; } else { pageCount = rowCount / pageSize + 1; } } rs = sm.executeQuery("select * from users limit "+ ((pageNow - 1) * pageSize + 1) +","+pageNow* pageSize+""); while (rs.next()) { out.println("<tr>"); out.println("<td>" + rs.getString(2) + "</td>"); out.println("<td>" + rs.getString(5) + "</td>"); out.println("<td>" + rs.getString(6) + "</td>"); out.println("</tr>"); } out.println("<tr>"); //打印总页数 for (int i = 1; i < pageCount; i++) { out.println("<a href=index.jsp?pageNow=" + i + ">" + "第" + i + "页" + "</a>"); } out.println("</tr>"); rs.close(); sm.close(); ct.close(); %> </table> </body> </html>
Oracle分页
主要是3层的嵌套查询
注意添加Oracle的JAR 包classes12.jar
经典的SQL语句(用ROWNUM实现)
select * from (select a1.*,rownum rn from (select ename,sal from emp order by sal) a1 where rownum<=10) where rn >=6;
Oracle分页java实现
<%@ page language="java" import="java.util.*,java.sql.*" pageEncoding="gbk"%> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <title>Oracle分页</title> </head> <body> <table> <tr> <td>用户名</td> <td>职业</td> <td>薪水</td> </tr> <% //加载驱动 Class.forName("oracle.jdbc.driver.OracleDriver"); //得到连接 Connection ct = DriverManager.getConnection( "jdbc:oracle:thin:@127.0.0.1:1521:ORCL", "SCOTT", "scott"); Statement sm = ct.createStatement(); //接收pageNow参数 String s_pageNow = request.getParameter("pageNow"); int pageNow = 1; if (s_pageNow != null) { pageNow = Integer.parseInt(s_pageNow); } //查询总页数 int pageCount = 0;//当前为第几页 int pageSize = 3;//每页显示几条记录 int rowCount = 0;//总共有多少条记录 ResultSet rs = sm.executeQuery("select count(*) from emp"); if (rs.next()) { rowCount = rs.getInt(1); if (rowCount % pageSize == 0) { pageCount = rowCount / pageSize; } else { pageCount = rowCount / pageSize + 1; } } rs = sm.executeQuery("select * from (select a1.*,rownum rn from (select * from emp ) a1 where rownum<=" + pageNow * pageSize + ") where rn >=" + ((pageNow - 1) * pageSize + 1) + ""); while (rs.next()) { out.println("<tr>"); out.println("<td>" + rs.getString(2) + "</td>"); out.println("<td>" + rs.getString(3) + "</td>"); out.println("<td>" + rs.getString(6) + "</td>"); out.println("</tr>"); } out.println("<tr>"); //打印总页数 for (int i = 1; i < pageCount; i++) { out.println("<a href=index.jsp?pageNow=" + i + ">" + "第" + i + "页" + "</a>"); } out.println("</tr>"); rs.close(); sm.close(); ct.close(); %> </table> </body> </html>
