论坛首页 → Java企业应用论坛 →

JAVA_JDBC面向对象分页(初步设计一之mysql)

浏览 2618 次
锁定老帖子 主题：JAVA_JDBC面向对象分页(初步设计一之mysql)
精华帖 (0) :: 良好帖 (0) :: 新手帖 (0) :: 隐藏帖 (0)
作者 正文
  • AbstractForever
  • 等级: 初级会员
  • 性别:
  • 文章: 11
  • 积分: 60
  • 来自: 成都
   发表时间：2009-11-26   最后修改：2010-03-03

看了很多网上的分页写法，有兴趣的可以直接去看看hibernate的源码，里面的写法还是值得参考，先理清思路：

1.要想分页，就要获取分页所需信息，这里用一个类PageInfo表示：

哪个对象，真正面向对象分页就要配置实体和数据库的映射关系，通过解析配置文件加上java的反射技术就ok了。

有时间会慢慢补充完整。

以下是各种数据库的分页语句：

以@开头的都是自定义变量，到后面都会被替换成具体的内容，这样写看起清晰点(个人见解)，初步设计如下：
@PAGE_SIZE表示每页显示多少条
@CURRENT_PAGE 当前要查看的页数
@TABLE_NAME 表名
@PKEY 主键
@criterions 条件集合
@orders 排序集合

sqk2k5分页语句:

 

分页结果集语句：
SELECT TOP(@PAGE_SIZE) * FROM @TABLE_NAME WHERE @PKEY NOT IN 
(SELECT TOP(@PAGE_SIZE*(@CURRENT_PAGE-1)) @PKEY FROM @TABLE_NAME WHERE 1=1 @CONDITIONS @ORDERS) @CONDITIONS @ORDERS

统计语句：
SELECT COUNT(*) FROM @TABLE_NAME WHERE 1=1 @CONDITIONS

 

mysql分页语句：

分页结果集语句：
SELECT * FROM @TABLE_NAME WHERE 1=1 @CONDITIONS @ORDERS LIMIT @FIRST_INDEX,@PAGE_SIZE 
统计语句：
SELECT COUNT(*) FROM @TABLE_NAME WHERE 1=1 @CONDITIONS

  

 oracle分页语句：

 

 以下以mysql做实验,太细节的东西就不必追究了，主要是核心思想的实现：

测试准备：

1.数据库paginationdb，表student_;

2.数据库脚本：

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for student_
-- ----------------------------
DROP TABLE IF EXISTS `student_`;
CREATE TABLE `student_` (
  `id_` int(11) NOT NULL auto_increment,
  `name_` varchar(20) default NULL,
  `sex_` varchar(20) default NULL,
  `age_` int(11) default NULL,
  `grade_` varchar(60) default NULL,
  `class_` varchar(60) default NULL,
  PRIMARY KEY  (`id_`)
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records 
-- ----------------------------
INSERT INTO `student_` VALUES ('1', 'student1', '男', '11', '一年级', '二班');
INSERT INTO `student_` VALUES ('2', 'student2', '男', '12', '一年级', '二班');
INSERT INTO `student_` VALUES ('3', 'student3', '男', '13', '一年级', '二班');
INSERT INTO `student_` VALUES ('4', 'student4', '女', '14', '一年级', '二班');
INSERT INTO `student_` VALUES ('5', 'student5', '女', '15', '一年级', '二班');
INSERT INTO `student_` VALUES ('6', 'student6', '男', '16', '二年级', '一班');
INSERT INTO `student_` VALUES ('7', 'student7', '男', '17', '二年级', '一班');
INSERT INTO `student_` VALUES ('8', 'student8', '女', '18', '一年级', '一班');
INSERT INTO `student_` VALUES ('9', 'story1', '男', '19', '一年级', '二班');
INSERT INTO `student_` VALUES ('10', 'story2', '女', '11', '一年级', '一班');
INSERT INTO `student_` VALUES ('11', 'story3', '男', '12', '二年级', '一班');
INSERT INTO `student_` VALUES ('12', 'story4', '女', '13', '一年级', '二班');
INSERT INTO `student_` VALUES ('13', 'story5', '男', '14', '一年级', '一班');
INSERT INTO `student_` VALUES ('14', 'story6', '男', '15', '一年级', '一班');
INSERT INTO `student_` VALUES ('15', 'story7', '女', '16', '一年级', '二班');
INSERT INTO `student_` VALUES ('16', 'story8', '男', '17', '一年级', '二班');
INSERT INTO `student_` VALUES ('17', 'story9', '男', '18', '一年级', '二班');
INSERT INTO `student_` VALUES ('18', 'story8', '男', '15', '一年级', '二班');
INSERT INTO `student_` VALUES ('19', 'story8', '男', '16', '一年级', '二班');
INSERT INTO `student_` VALUES ('20', 'story10', '保密', '16', '一年级', '二班');

 

3.测试数据

 

以下是分页类（pageInfo）的初步设计：

package org.forever.entities;

import java.util.List;

//分页信息
public class PageInfo {

	private int currentPage = 1;// 当前页
	private int totalPage;// 总页数
	private int totalItems;// 总条数
	private int pageSize = 10;// 每页显示多少条
	private List<?> result;// 结果集合

	private String tableName;// 表名
	private String pkey;// 主键
	private Condition[] conditions;// 条件集合
	private Order[] orders;// 排序集合

	public PageInfo() {

	}

	public int getCurrentPage() {
		return currentPage;
	}

	public void setCurrentPage(int currentPage) {
		this.currentPage = currentPage;
	}

	public int getTotalPage() {
		return totalPage;
	}

	public void setTotalPage(int totalPage) {
		this.totalPage = totalPage;
	}

	public int getTotalItems() {
		return totalItems;
	}

	public void setTotalItems(int totalItems) {
		this.totalItems = totalItems;
	}

	public List<?> getResult() {
		return result;
	}

	public void setResult(List<?> result) {
		this.result = result;
	}

	public String getTableName() {
		return tableName;
	}

	public void setTableName(String tableName) {
		this.tableName = tableName;
	}

	public String getPkey() {
		return pkey;
	}

	public void setPkey(String pkey) {
		this.pkey = pkey;
	}

	public Condition[] getConditions() {
		return conditions;
	}

	public void setConditions(Condition[] conditions) {
		this.conditions = conditions;
	}

	public Order[] getOrders() {
		return orders;
	}

	public void setOrders(Order[] orders) {
		this.orders = orders;
	}

	public int getPageSize() {
		return pageSize;
	}

	public void setPageSize(int pageSize) {
		this.pageSize = pageSize;
	}

}

 操作类型枚举：

package org.forever.entities;
//操作类型
public enum Operation {
	IN,
	EQ,
	GT,
	LT,
	NE,
	GE,
	LE,
	BETWEEN,
	LIKE
	
	
}

 排序类型枚举：

package org.forever.entities;

public enum OrderType {

	ASC,
	DESC
	
}

 条件操作：

package org.forever.entities;

//条件操作
public class Condition {

	private String propertyName;// 属性名
	private Object propertyValue;// 属性值
	private Operation operation;// 操作符号

	public Condition() {
	}

	public String getPropertyName() {
		return propertyName;
	}

	public void setPropertyName(String propertyName) {
		this.propertyName = propertyName;
	}

	public Object getPropertyValue() {
		return propertyValue;
	}

	public void setPropertyValue(Object propertyValue) {
		this.propertyValue = propertyValue;
	}

	public Operation getOperation() {
		return operation;
	}

	public void setOperation(Operation operation) {
		this.operation = operation;
	}

	public Condition(String propertyName, Object propertyValue,
			Operation operation) {
		super();
		this.propertyName = propertyName;
		this.propertyValue = propertyValue;
		this.operation = operation;
	}
	
	

}

 排序操作：

package org.forever.entities;

public class Order {

	private String propertyName;// 属性名
	private OrderType orderType;// 排序类型

	public Order() {

	}

	public Order(String propertyName, OrderType orderType) {
		super();
		this.propertyName = propertyName;
		this.orderType = orderType;
	}

	public String getPropertyName() {
		return propertyName;
	}

	public void setPropertyName(String propertyName) {
		this.propertyName = propertyName;
	}

	public OrderType getOrderType() {
		return orderType;
	}

	public void setOrderType(OrderType orderType) {
		this.orderType = orderType;
	}

}

 student类：

package org.forever.entities;

import java.io.Serializable;

public class Student implements Serializable{
	private static final long serialVersionUID = -1646247293734375803L;
	public Integer id;//主键
	public String name;//名字
	public String sex;//性别
	public String grade;//年级
	public String stuClass;//班级
	public int age;//年龄

	public Student() {

	}

	public Integer getId() {
		return id;
	}

	public void setId(Integer id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getSex() {
		return sex;
	}

	public void setSex(String sex) {
		this.sex = sex;
	}

	public String getGrade() {
		return grade;
	}

	public void setGrade(String grade) {
		this.grade = grade;
	}

	public String getStuClass() {
		return stuClass;
	}

	public void setStuClass(String stuClass) {
		this.stuClass = stuClass;
	}

	public int getAge() {
		return age;
	}

	public void setAge(int age) {
		this.age = age;
	}

	
}

 学生业务：

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 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 {
	
	public void doPager(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 ") ;
		
		//处理条件
		Condition[]conditions = pageInfo.getConditions();
		StringBuffer condition = new StringBuffer();
		List<Object> params = new ArrayList<Object>();
		
		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());
				}
				
				
			}
		}
		//处理排序
		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 ");
		}
		
		String sqlp=sqlPage.toString()
					.replaceAll("@CONDITIONS", condition.toString())
					.replaceAll("@ORDERS", orderList.toString())
					.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", condition.toString())
					.replaceAll("@TABLE_NAME", pageInfo.getTableName())
					.replaceAll("@PKEY", pageInfo.getPkey());
		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 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)
				}
		);
		
		studentManager.doPager(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());
		}
		
		
	}
	
}

 
返回顶楼
         
  • cnyangqi
  • 等级: 初级会员
  • 性别:
  • 文章: 97
  • 积分: 20
  • 来自: 杭州
   发表时间：2010-03-23  
奇怪的是Oracle分页语句怎么没有的。。。
返回顶楼
          回帖地址
0 请登录后投票
  • AbstractForever
  • 等级: 初级会员
  • 性别:
  • 文章: 11
  • 积分: 60
  • 来自: 成都
   发表时间：2010-03-25  
cnyangqi 写道
奇怪的是Oracle分页语句怎么没有的。。。

有哈，在第二篇
返回顶楼
          回帖地址
0 请登录后投票
论坛首页 → Java企业应用版

跳转论坛:
Global site tag (gtag.js) - Google Analytics