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

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

sql2005测试数据脚本：

USE mastergoIF DB_ID('paginationdb') IS NOT NULLDROP DATABASE paginationdbGO----建库CREATE DATABASE paginationdbGO----建表USE paginationdbIF 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,'一年级','二班' UNIONSELECT 'student2','男',12,'一年级','二班' UNIONSELECT 'student3','男',13,'一年级','二班' UNIONSELECT 'student4','女',14,'一年级','二班' UNIONSELECT 'student5','女',15,'一年级','二班' UNIONSELECT 'student6','男',16,'二年级','一班' UNIONSELECT 'student7','男',17,'二年级','一班' UNIONSELECT 'student8','女',18,'一年级','一班' UNIONSELECT 'story1','男',19,'一年级','二班' UNIONSELECT 'story2','女',11,'一年级','一班' UNIONSELECT 'story3','男',12,'二年级','一班' UNIONSELECT 'story4','女',13,'一年级','二班' UNIONSELECT 'story5','男',14,'一年级','一班' UNIONSELECT 'story6','男',15,'一年级','一班' UNIONSELECT 'story7','女',16,'一年级','二班' UNIONSELECT 'story8','男',17,'一年级','二班' UNIONSELECT 'story9','男',18,'一年级','二班' UNIONSELECT 'story8','男',15,'一年级','二班' UNIONSELECT 'story8','男',16,'一年级','二班' UNIONSELECT '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());}}}

?