JDBC中的分页查询

package day03;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.Scanner;import day01.DBUtil2;/** * 分页查询 */public class PageDemo {public static void main(String[] args) {Scanner scan = new Scanner(System.in);System.out.println("请输入要查看的表名:");String tableName = scan.nextLine().trim();System.out.println("请输入排序的列名:");String colName = scan.nextLine().trim();System.out.println("请输入一页显示的条数:");int pageSizes = Integer.parseInt(scan.nextLine().trim());System.out.println("请输入查看的页数:");int page = Integer.parseInt(scan.nextLine().trim());try {Connection conn = DBUtil2.getConnection();/** * SELECT * FROM (  SELECT ROWNUM RW,t.* FROM      (SELECT * FROM EMP ORDER BY SAL) t   ) WHERE rw BETWEEN ? AND ? */String sql = "SELECT * FROM ( "+ "SELECT ROWNUM rw,t.* FROM "+ "(SELECT * FROM "+tableName+" ORDER BY "+colName+") t "+ ") WHERE rw BETWEEN ? AND ?";PreparedStatement ps = conn.prepareStatement(sql);int start = pageSizes*(page-1)+1;int end = pageSizes*page;ps.setInt(1, start);ps.setInt(2, end);ResultSet rs = ps.executeQuery();while(rs.next()){int rw  = rs.getInt(1);//rs.getInt("rw")也可以int empno = rs.getInt("empno");//getInt(2);String ename = rs.getString("ename");//getInt(3);System.out.println(rw+","+empno+","+ename);}} catch (SQLException e) {e.printStackTrace();} finally {DBUtil2.closeConnection();}}}

测试结果如下：

请输入要查看的表名:emp请输入排序的列名:empno请输入一页显示的条数:5请输入查看的页数:26,7698,BLAKE7,7782,CLARK8,7788,SCOTT9,7839,KING10,7844,TURNER

方法二：思路更加严谨

实体类

package homework;import java.io.Serializable;import java.sql.Date;public class Emp implements Serializable{private static final long serialVersionUID = 1L;private Integer empno;private String ename;private String job;private Integer mgr;private Date hiredate;private Double sal;private Double comm;private Integer deptno;public Emp() {}public Integer getEmpno() {return empno;}public void setEmpno(Integer empno) {this.empno = empno;}public String getEname() {return ename;}public void setEname(String ename) {this.ename = ename;}public String getJob() {return job;}public void setJob(String job) {this.job = job;}public Integer getMgr() {return mgr;}public void setMgr(Integer mgr) {this.mgr = mgr;}public Date getHiredate() {return hiredate;}public void setHiredate(Date hiredate) {this.hiredate = hiredate;}public Double getSal() {return sal;}public void setSal(Double sal) {this.sal = sal;}public Double getComm() {return comm;}public void setComm(Double comm) {this.comm = comm;}public Integer getDeptno() {return deptno;}public void setDeptno(Integer deptno) {this.deptno = deptno;}@Overridepublic String toString() {return "Emp [empno=" + empno + ", ename=" + ename + ", job=" + job + ", mgr=" + mgr + ", hiredate=" + hiredate+ ", sal=" + sal + ", comm=" + comm + ", deptno=" + deptno + "]";}}

DAO

package homework;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;import day01.DBUtil2;public class PageDAO {/** *  * @param page  第几页 * @param pageSizes  每页显示多少条数据 * @return List<Emp> */public List<Emp> findPage(int page,int pageSizes){Connection conn = null;List<Emp> list = null;try {conn = DBUtil2.getConnection();conn.setAutoCommit(false);//设置为手动提交String sql1 = "SELECT COUNT(*) FROM emp";String sql2 = "SELECT * FROM ("+ "SELECT ROWNUM rw,e.* FROM "+ "(SELECT * FROM emp ORDER BY empno) e"+ ") WHERE rw BETWEEN ? AND ?";PreparedStatement ps = conn.prepareStatement(sql1);ResultSet rs = ps.executeQuery();if(!rs.next()){throw new RuntimeException("访问数据库失败(查询总数据)");}int count = rs.getInt(1);//该表一共有count条数据int mod = count % pageSizes;//求余数int n = count/pageSizes;int amountPage = (mod==0)? n:(n+1) ;//判断输入页码是否符合要求if(page < 1){//查看第一页page = 1;}if(page > amountPage){page = amountPage;}//实现分页查询ps = conn.prepareStatement(sql2);int start = (page-1)*pageSizes+1;int end = page*pageSizes;ps.setInt(1, start);ps.setInt(2, end);rs = ps.executeQuery();list = new ArrayList<Emp>();while(rs.next()){Emp e = new Emp();e.setEmpno(rs.getInt("empno"));e.setEname(rs.getString("ename"));e.setJob(rs.getString("job"));e.setMgr(rs.getInt("mgr"));e.setHiredate(rs.getDate("hiredate"));e.setSal(rs.getDouble("sal"));e.setComm(rs.getDouble("comm"));e.setDeptno(rs.getInt("deptno"));list.add(e);}} catch (SQLException e) {e.printStackTrace();try {conn.rollback();} catch (SQLException e1) {e1.printStackTrace();}} finally {DBUtil2.closeConnection();}return list;}}

测试方法：

package homework;import java.util.List;public class TestPage {public static void main(String[] args) {PageDAO dao = new PageDAO();List<Emp> list = dao.findPage(-5, 4);for(Emp e : list){System.out.println(e);}}}

测试结果如下：

Emp [empno=4, ename=tom, job=manager, mgr=7839, hiredate=2017-04-01, sal=5000.0, comm=300.0, deptno=30]Emp [empno=5, ename=marry, job=clerk, mgr=4, hiredate=2017-04-02, sal=3000.0, comm=0.0, deptno=30]Emp [empno=6, ename=tery, job=salesman, mgr=4, hiredate=2017-04-03, sal=2500.0, comm=200.0, deptno=30]Emp [empno=7, ename=jim, job=salesman, mgr=4, hiredate=2017-04-04, sal=2500.0, comm=200.0, deptno=30]

显示的为第一页的内容