看了很多网上的分页写法,有兴趣的可以直接去看看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());
}
}
}