JDBC中的分页查询

工具:Myeclipse(Eclipse):
知识点:JDBC中的分页
jar包:
commons-collections4-4.0.jar
commons-dbcp-1.4.jar
commons-pool-1.6.jar
Oracle 11g 11.2.0.1.0 JDBC_ojdbc6.jar

代码都是基于自己写的连接池技术,,已经分享过了
url:http://blog.youkuaiyun.com/coder_hello_world/article/details/78515974


JDBC中常常使用分页技术对数据读中的数据进行处理,
就像在网页在搜索某样东西,不会全部给你看,但是会形成分页

分页算法:
start=(page-1)*pageSize-1

end = page*pageSzie;

package com.gaosheng.day07;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Scanner;

import com.gaosheng.day02.JDBCPool1;

/*
 *分页机制 
 * @author 89155
 *
 */
public class PageDemo {
	@SuppressWarnings("all")
	public static void main(String[] args) {
	Scanner scanner = new Scanner(System.in);
	
	//输入表名
	System.out.println("输入查询的表名");
	String tableName = scanner.nextLine().trim();
	
	//输入按照什么列名排序
	System.out.println("输入按照什么列名排序");
	String colName = scanner.nextLine().trim();
	
	//每页中显示的数据
	System.out.println("显示每页中的数据量:");
	int pageSize = Integer.parseInt(scanner.nextLine());
	
	//显示第几页
	System.out.println("显示第几页:");
	int page = Integer.parseInt(scanner.nextLine());
	
	try{
		Connection connection 
		= JDBCPool1.getConnection();
		
		/*	
		 *排序:SELECT * FROM EMP ORDER BY ENAME;
		 *编号:
		 *范围:
		 */
		//查出数据量
		
		String pageSql = "SELECT * FROM("
				+ "SELECT ROWNUM RW,E.* FROM "
				+ "(SELECT * FROM "
				+tableName+" "
				+ "ORDER BY "+colName+") E) "
					+ "WHERE RW BETWEEN ? AND ?";
	/*	String pageSql = 
				"SELECT * FROM("
				+ "SELECT ROWNUM RW,E.* FROM "
				+ "(SELECT * FROM "
				+tableName+" "
				+ "ORDER BY "+colName+") E) "
				+ "WHERE RW >=? AND RW <=?";*/
		System.out.println(pageSql);
		
		PreparedStatement pstate = connection.prepareStatement(pageSql);
	//设置数量的数量
			int strat = (page - 1)*pageSize+1;
			int end = page*pageSize;
			pstate.setInt(1, strat);
			pstate.setInt(2, end);
			
			ResultSet rs = pstate.executeQuery();
			while(rs.next()){
			int sal = rs.getInt("SAL");
			String ename = rs.getString("ENAME");
			System.out.println(sal+":"+ename);
			}
	}catch(Exception e){
		e.printStackTrace();
	}finally{
		JDBCPool1.closeConnection();
	}
		
}
}