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
请输入查看的页数:
2
6,7698,BLAKE
7,7782,CLARK
8,7788,SCOTT
9,7839,KING
10,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;
	}
	@Override
	public 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]

显示的为第一页的内容