JAVA_JDBC面向对象分页(初步设计二之oracle)

现在加入oracle和sql2005的分页代码实现；

sql2005测试数据脚本：

USE master
go
IF DB_ID('paginationdb') IS NOT NULL
DROP DATABASE paginationdb
GO

----建库
CREATE DATABASE paginationdb
GO
----建表
USE paginationdb
IF EXISTS (SELECT  name FROM sysobjects WHERE [name]='student_')
DROP TABLE student_
GO
----创建用户信息表
CREATE TABLE student_
(
	id_ INT NOT NULL IDENTITY (1,1) PRIMARY KEY,
	name_ varchar(50),
	sex_ varchar(50),
	age_ int,
	grade_ varchar(50),
	class_ varchar(50)
)
GO
--插入测试数据
INSERT INTO student_(name_,sex_,age_,grade_,class_)
SELECT 'student1'	,'男',	11	,'一年级'	,'二班' UNION
SELECT 'student2'	,'男',	12	,'一年级'	,'二班' UNION
SELECT 'student3'	,'男',	13	,'一年级'	,'二班' UNION
SELECT 'student4'	,'女',	14	,'一年级'	,'二班' UNION
SELECT 'student5'	,'女',	15	,'一年级'	,'二班' UNION
SELECT 'student6'	,'男',	16	,'二年级'	,'一班' UNION
SELECT 'student7'	,'男',	17	,'二年级'	,'一班' UNION
SELECT 'student8'	,'女',	18	,'一年级'	,'一班' UNION
SELECT 'story1'	,'男',	19	,'一年级'	,'二班' UNION
SELECT 'story2'	,'女',	11	,'一年级'	,'一班' UNION
SELECT 'story3'	,'男',	12	,'二年级'	,'一班' UNION
SELECT 'story4'	,'女',	13	,'一年级'	,'二班' UNION
SELECT 'story5'	,'男',	14	,'一年级'	,'一班' UNION
SELECT 'story6'	,'男',	15	,'一年级'	,'一班' UNION
SELECT 'story7'	,'女',	16	,'一年级'	,'二班' UNION
SELECT 'story8'	,'男',	17	,'一年级'	,'二班' UNION
SELECT 'story9'	,'男',	18	,'一年级'	,'二班' UNION
SELECT 'story8'	,'男',	15	,'一年级'	,'二班' UNION
SELECT 'story8'	,'男',	16	,'一年级'	,'二班' UNION
SELECT 'story10'	,'保密',	16	,'一年级'	,'二班' 
GO

oracle测试数据：

//创建学生表
CREATE TABLE STUDENT_
(
	ID_ INT PRIMARY KEY,
	NAME_ VARCHAR2(50),
	SEX_ VARCHAR2(50),
	AGE_ INT,
	GRADE_ VARCHAR2(50),
	CLASS_ VARCHAR2(50)
)
//为学生表创建序列
CREATE SEQUENCE SEQ_STUDENT_ID;
//插入测试数据
INSERT INTO STUDENT_ VALUES (SEQ_STUDENT_ID.NEXTVAL,'student1','男',11,'一年级','二班');
INSERT INTO STUDENT_ VALUES (SEQ_STUDENT_ID.NEXTVAL,'student2','男',12,'一年级','二班');
INSERT INTO STUDENT_ VALUES (SEQ_STUDENT_ID.NEXTVAL,'student3','男',13,'一年级','二班');
INSERT INTO STUDENT_ VALUES (SEQ_STUDENT_ID.NEXTVAL,'student4','女',14,'一年级','二班');
INSERT INTO STUDENT_ VALUES (SEQ_STUDENT_ID.NEXTVAL,'student5','女',15,'一年级','二班');
INSERT INTO STUDENT_ VALUES (SEQ_STUDENT_ID.NEXTVAL,'student6','男',16,'二年级','一班');
INSERT INTO STUDENT_ VALUES (SEQ_STUDENT_ID.NEXTVAL,'student7','男',17,'二年级','一班');
INSERT INTO STUDENT_ VALUES (SEQ_STUDENT_ID.NEXTVAL,'student8','女',18,'一年级','一班');
INSERT INTO STUDENT_ VALUES (SEQ_STUDENT_ID.NEXTVAL,'story1','男',19,'一年级','二班');
INSERT INTO STUDENT_ VALUES (SEQ_STUDENT_ID.NEXTVAL,'story2','女',11,'一年级','一班');
INSERT INTO STUDENT_ VALUES (SEQ_STUDENT_ID.NEXTVAL,'story3','男',12,'二年级','一班');
INSERT INTO STUDENT_ VALUES (SEQ_STUDENT_ID.NEXTVAL,'story4','女',13,'一年级','二班');
INSERT INTO STUDENT_ VALUES (SEQ_STUDENT_ID.NEXTVAL,'story5','男',14,'一年级','一班');
INSERT INTO STUDENT_ VALUES (SEQ_STUDENT_ID.NEXTVAL,'story6','男',15,'一年级','一班');
INSERT INTO STUDENT_ VALUES (SEQ_STUDENT_ID.NEXTVAL,'story7','女',16,'一年级','二班');
INSERT INTO STUDENT_ VALUES (SEQ_STUDENT_ID.NEXTVAL,'story8','男',17,'一年级','二班');
INSERT INTO STUDENT_ VALUES (SEQ_STUDENT_ID.NEXTVAL,'story9','男',18,'一年级','二班');
INSERT INTO STUDENT_ VALUES (SEQ_STUDENT_ID.NEXTVAL,'story8','男',15,'一年级','二班');
INSERT INTO STUDENT_ VALUES (SEQ_STUDENT_ID.NEXTVAL,'story8','男',16,'一年级','二班');
INSERT INTO STUDENT_ VALUES (SEQ_STUDENT_ID.NEXTVAL,'story10','保密',16,'一年级','二班');
COMMIT;

  

 

重构StudentManager类：

package org.forever.manager;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import oracle.jdbc.driver.OracleDriver;

import org.forever.entities.Condition;
import org.forever.entities.Operation;
import org.forever.entities.Order;
import org.forever.entities.OrderType;
import org.forever.entities.PageInfo;
import org.forever.entities.Student;

import com.mysql.jdbc.Driver;

public class StudentManager {
	
	//基于oracle10g数据库
	public void doPagerOracle(PageInfo pageInfo){
		
		String sqlPage =
				" SELECT * FROM "+
				" (SELECT ROWNUM  NUM,@TABLE_NAME.* FROM @TABLE_NAME WHERE (1=1) AND (ROWNUM<=(@CURRENT_PAGE*@PAGE_SIZE)) @CONDITIONS @ORDERS ) "+
				" WHERE NUM>(@CURRENT_PAGE-1)*@PAGE_SIZE "
				;
		String sqlCount = " SELECT COUNT(*) FROM @TABLE_NAME WHERE 1=1 @CONDITIONS ";

		//处理条件
		List<Object> params = new ArrayList<Object>();
		String conditions = processCondition(pageInfo, params);
		//处理排序
		String orderList = processOrder(pageInfo);
		//获取完整分页语句
		String sqlp=sqlPage.replaceAll("@CONDITIONS",conditions)
						   .replaceAll("@ORDERS", orderList)
						   .replaceAll("@TABLE_NAME", pageInfo.getTableName())
						   .replaceAll("@PAGE_SIZE", pageInfo.getPageSize()+"")
						   .replaceAll("@CURRENT_PAGE", pageInfo.getCurrentPage()+"");
		//获取完整的统计语句
		String sqlc = sqlCount.replaceAll("@CONDITIONS", conditions)
							  .replaceAll("@TABLE_NAME", pageInfo.getTableName());
		
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		try {
			Class.forName(OracleDriver.class.getName());
			connection = DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.101:1521:ORCL", "scott", "story");
			statement = connection.prepareStatement(sqlc);
			for (int i = 0; i < params.size(); i++) {
				statement.setObject(i+1, params.get(i));
			}
			resultSet = statement.executeQuery();
			resultSet.next();
			int totalItems = resultSet.getInt(1);
			//设置总条数
			pageInfo.setTotalItems(totalItems);
			//设置总页数
			pageInfo.setTotalPage(
					totalItems%pageInfo.getPageSize()==0?
							totalItems/pageInfo.getPageSize():
								totalItems/pageInfo.getPageSize()+1
			);
			//设置结果集
			statement = connection.prepareStatement(sqlp);
			for (int i = 0; i < params.size(); i++) {
				statement.setObject(i+1, params.get(i));
			}
			resultSet = statement.executeQuery();
			List<Student> students = new ArrayList<Student>();
			while(resultSet.next()){
				Student student = new Student();
				student.setId(resultSet.getInt("ID_"));
				student.setName(resultSet.getString("NAME_"));
				student.setSex(resultSet.getString("SEX_"));
				student.setAge(resultSet.getInt("AGE_"));
				student.setGrade(resultSet.getString("GRADE_"));
				student.setStuClass(resultSet.getString("CLASS_"));
				students.add(student);
			}
			
			pageInfo.setResult(students);
			
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		}finally{
			if(connection!=null){
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
					throw new RuntimeException(e);
				}
			}
		}
	}
	
	//基于sql2005数据库
	public void doPagerSql2k5(PageInfo pageInfo){
		
		StringBuffer sqlPage = new StringBuffer(
				" SELECT TOP(@PAGE_SIZE) * FROM @TABLE_NAME WHERE 1=1 AND @PKEY NOT IN "+
				" (SELECT TOP((@CURRENT_PAGE-1)*@PAGE_SIZE) @PKEY FROM @TABLE_NAME WHERE 1=1 @CONDITIONS @ORDERS) "+
				" @CONDITIONS @ORDERS "
		);
		
		StringBuffer sqlCount = new StringBuffer(
				" SELECT COUNT(*) FROM @TABLE_NAME WHERE 1=1 @CONDITIONS "
		);
		
		//处理条件
		List<Object> params = new ArrayList<Object>();
		String conditions = processCondition(pageInfo, params);
		//处理排序
		String orderList = processOrder(pageInfo);
		//获取完整分页语句
		String sqlp=sqlPage.toString()
					.replaceAll("@CONDITIONS", conditions)
					.replaceAll("@ORDERS", orderList)
					.replaceAll("@TABLE_NAME", pageInfo.getTableName())
					.replaceAll("@PKEY", pageInfo.getPkey())
					.replaceAll("@PAGE_SIZE", pageInfo.getPageSize()+"")
					.replaceAll("@CURRENT_PAGE", pageInfo.getCurrentPage()+"");
		//获取完整统计语句
		String sqlc=sqlCount.toString()
					.replaceAll("@CONDITIONS", conditions)
					.replaceAll("@TABLE_NAME", pageInfo.getTableName());
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		
		try {
			Class.forName(net.sourceforge.jtds.jdbc.Driver.class.getName());
			connection = DriverManager.getConnection("jdbc:jtds:sqlserver://localhost:1151/paginationdb", "sa", "123456");
			statement = connection.prepareStatement(sqlc);
			for (int i = 0; i < params.size(); i++) {
				statement.setObject(i+1, params.get(i));
			}
			resultSet = statement.executeQuery();
			resultSet.next();
			int totalItems = resultSet.getInt(1);
			//设置总条数
			pageInfo.setTotalItems(totalItems);
			//设置总页数
			pageInfo.setTotalPage(
					totalItems%pageInfo.getPageSize()==0?
							totalItems/pageInfo.getPageSize():
								totalItems/pageInfo.getPageSize()+1
			);
			//设置结果集
			statement = connection.prepareStatement(sqlp);
			//因为涉及两个conditions，所以参数是2倍
			for (int i = 0; i < params.size(); i++) {
				statement.setObject(i+1, params.get(i));
			}
			for (int i = params.size(); i < params.size()*2; i++) {
				statement.setObject(i+1, params.get(i-params.size()));
			}

			resultSet = statement.executeQuery();
			List<Student> students = new ArrayList<Student>();
			while(resultSet.next()){
				Student student = new Student();
				student.setId(resultSet.getInt("id_"));
				student.setName(resultSet.getString("name_"));
				student.setSex(resultSet.getString("sex_"));
				student.setAge(resultSet.getInt("age_"));
				student.setGrade(resultSet.getString("grade_"));
				student.setStuClass(resultSet.getString("class_"));
				students.add(student);
			}
			
			pageInfo.setResult(students);
			
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		}finally{
			if(connection!=null){
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
					throw new RuntimeException(e);
				}
			}
		}
			
	}
	
	//基于mysql数据库
	public void doPagerMysql(PageInfo pageInfo){
		
		StringBuffer sqlPage = new StringBuffer("SELECT * FROM @TABLE_NAME WHERE 1=1 @CONDITIONS @ORDERS LIMIT @FIRST_INDEX,@PAGE_SIZE ");
		StringBuffer sqlCount = new StringBuffer("SELECT COUNT(*) FROM @TABLE_NAME WHERE 1=1 @CONDITIONS ") ;
		
		//处理条件
		List<Object> params = new ArrayList<Object>();
		String conditions = processCondition(pageInfo, params);
		
		//处理排序
		String orderList = processOrder(pageInfo);

		//获取完整分页语句
		String sqlp=sqlPage.toString()
					.replaceAll("@CONDITIONS", conditions)
					.replaceAll("@ORDERS", orderList)
					.replaceAll("@TABLE_NAME", pageInfo.getTableName())
					.replaceAll("@PKEY", pageInfo.getPkey())
					.replaceAll("@FIRST_INDEX", (pageInfo.getCurrentPage()-1)*pageInfo.getPageSize()+"")
					.replaceAll("@PAGE_SIZE", pageInfo.getPageSize()+"");
		
		//获取完整统计语句
		String sqlc = sqlCount.toString()
					.replaceAll("@CONDITIONS", conditions)
					.replaceAll("@TABLE_NAME", pageInfo.getTableName());
		
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		
		try {
			Class.forName(Driver.class.getName());
			connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/paginationdb", "root", "root");
			statement = connection.prepareStatement(sqlc);
			for (int i = 0; i < params.size(); i++) {
				statement.setObject(i+1, params.get(i));
			}
			resultSet = statement.executeQuery();
			resultSet.next();
			int totalItems = resultSet.getInt(1);
			//设置总条数
			pageInfo.setTotalItems(totalItems);
			//设置总页数
			pageInfo.setTotalPage(
					totalItems%pageInfo.getPageSize()==0?
							totalItems/pageInfo.getPageSize():
								totalItems/pageInfo.getPageSize()+1
			);
			//设置结果集
			statement = connection.prepareStatement(sqlp);
			for (int i = 0; i < params.size(); i++) {
				statement.setObject(i+1, params.get(i));
			}
			resultSet = statement.executeQuery();
			List<Student> students = new ArrayList<Student>();
			while(resultSet.next()){
				Student student = new Student();
				student.setId(resultSet.getInt("id_"));
				student.setName(resultSet.getString("name_"));
				student.setSex(resultSet.getString("sex_"));
				student.setAge(resultSet.getInt("age_"));
				student.setGrade(resultSet.getString("grade_"));
				student.setStuClass(resultSet.getString("class_"));
				students.add(student);
			}
			
			pageInfo.setResult(students);
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(connection!=null){
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
	
	//处理条件
	public String processCondition(PageInfo pageInfo,List<Object> params){
		StringBuffer condition = new StringBuffer();
		Condition[]conditions = pageInfo.getConditions();
		if(conditions!=null && conditions.length>0){
			for (Condition item : conditions) {
				
				switch (item.getOperation()) {
				case EQ:
					condition.append(" AND (" + item.getPropertyName()+" =?) ");
					break;
				case LIKE:
					condition.append(" AND (" + item.getPropertyName()+" LIKE ?) ");
					break;
				case BETWEEN:
					condition.append(" AND (" + item.getPropertyName() + " BETWEEN ? AND ?) ");
					break;
				case IN:
					condition.append(" AND (" + item.getPropertyName() + " IN (");
					for (int i = 0; i < ((Object[])item.getPropertyValue()).length; i++) {
						condition.append("?,");
					}
					condition.replace(condition.length()-1, condition.length(), "");
					condition.append(")) ");
					//.....
				default:
					break;
				}
				
				if(item.getOperation() == Operation.LIKE){
					params.add("%"+item.getPropertyValue()+"%");
				}else if(item.getOperation() == Operation.BETWEEN){
					params.add(((Object[])item.getPropertyValue())[0]);
					params.add(((Object[])item.getPropertyValue())[1]);
				}else if(item.getOperation() == Operation.IN){
					for (Object object : (Object[])item.getPropertyValue()) {
						params.add(object);
					}
				}else{
					params.add(item.getPropertyValue());
				}
				
				
			}
		}
		return condition.toString();
	}
	
	//处理排序
	public String processOrder(PageInfo pageInfo){
		Order[]orders = pageInfo.getOrders();
		StringBuffer orderList = new StringBuffer(" ORDER BY ");
		if(orders!=null && orders.length>0){
			for (Order item : orders) {
				switch (item.getOrderType()) {
				case ASC:
					orderList.append(item.getPropertyName()+" ASC ,");
					break;
						
				case DESC:
					orderList.append(item.getPropertyName()+" DESC ,");
					break;
				}
			}
			orderList.replace(orderList.length()-1, orderList.length(), "");
		}else{
			orderList.append(" @PKEY ASC ");
		}
		return orderList.toString();
	}
	
	
	public static void main(String[] args) {
		StudentManager studentManager = new StudentManager();
		PageInfo pageInfo = new PageInfo();
		pageInfo.setTableName("student_");//指定查询表名
		pageInfo.setPkey("id_");//指定该表的主键名
		pageInfo.setPageSize(2);//每页显示2条
		pageInfo.setCurrentPage(2);//查看第二页
		
		//实体暂时没有和数据库进行映射，所以条件和排序里面的属性名暂时写成数据库的属性名
		
		/*例子：查询一年级二班，名字中包含stor的，年龄在14到18的，性别是男和女的学生
		 *并且按照姓名升序排序，名字相同的按照年龄的逆序排序。 
		 */
		pageInfo.setConditions(
				new Condition[]{
					new Condition("grade_", "一年级", Operation.EQ),	
					new Condition("class_", "二班", Operation.EQ),
					new Condition("name_","stor",Operation.LIKE),
					new Condition("age_", new Object[]{14,18}, Operation.BETWEEN),
					new Condition("sex_",new Object[]{"男","女"},Operation.IN)
				}
		);
		
		pageInfo.setOrders(
				new Order[]{
					new Order("name_", OrderType.ASC),
					new Order("age_",OrderType.DESC)
				}
		);
//		System.out.println("************mysql分页*****************");
//		studentManager.doPagerMysql(pageInfo);
//		System.out.println("totalPage:"+pageInfo.getTotalPage());
//		System.out.println("totalItems:" + pageInfo.getTotalItems());
//		for (Object item : pageInfo.getResult()) {
//			System.out.println("***************");
//			System.out.println(((Student)item).getId());
//			System.out.println(((Student)item).getName());
//			System.out.println(((Student)item).getSex());
//		}
		
//		System.out.println("**************sql2005分页*****************");
//		studentManager.doPagerSql2k5(pageInfo);
//		System.out.println("totalPage:"+pageInfo.getTotalPage());
//		System.out.println("totalItems:" + pageInfo.getTotalItems());
//		for (Object item : pageInfo.getResult()) {
//			System.out.println("***************");
//			System.out.println(((Student)item).getId());
//			System.out.println(((Student)item).getName());
//			System.out.println(((Student)item).getSex());
//		}
		System.out.println("**************ORACLE分页*****************");
		studentManager.doPagerOracle(pageInfo);
		System.out.println("totalPage:"+pageInfo.getTotalPage());
		System.out.println("totalItems:" + pageInfo.getTotalItems());
		for (Object item : pageInfo.getResult()) {
			System.out.println("***************");
			System.out.println(((Student)item).getId());
			System.out.println(((Student)item).getName());
			System.out.println(((Student)item).getSex());
		}
		
		
		
	}
	
}