各种数据库分页

sqlserver分页

辅助类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92

package com.utils;

import java.util.Collections;
import java.util.List;

/**
* 分页辅助类
* @author ZhaoYihao
*
*/
public class Page {

/**
* 当前页码
*/
private int pageNum;
/**
* 开始条数
*/
private int startNum;
/**
* 结束条数
*/
private int endNum;
/**
* 每页条数
*/
private int recordPerPage = 10;

/**
* 总页数
*/
private int pageCount;

/**
* 查询数据列表
*/
private List records = Collections.emptyList();


public Page() {}

public Page(int pageNum) {
init(pageNum);
}

private void init(int pageNum2) {
this.startNum = ((pageNum2-1)*recordPerPage)+1;
this.endNum = startNum + recordPerPage;
}

public int getPageNum() {
return pageNum;
}
public void setPageNum(int pageNum) {
this.pageNum = pageNum;
}
public int getStartNum() {
return startNum;
}
public void setStartNum(int startNum) {
this.startNum = startNum;
}
public int getEndNum() {
return endNum;
}
public void setEndNum(int endNum) {
this.endNum = endNum;
}
public int getRecordPerPage() {
return recordPerPage;
}
public void setRecordPerPage(int recordPerPage) {
this.recordPerPage = recordPerPage;
}
public int getPageCount() {
return pageCount;
}
public void setPageCount(int pageCount) {
this.pageCount = pageCount;
}

public List getRecords() {
return records;
}

public void setRecords(List records) {
this.records = records;
}

}

需要从前台传过来当前所在页面。后台初始化Page对象,将当前页面set进实体类,自动初始化其他信息。

sqlserver

1
2
3
4
5

SELECT * FROM ( SELECT row_number() over(order by create_time) rownum,A.* FROM (
select * from bdip_chat_point where tree_id = '624' and user_name like '%杨%'
)A
)B where rownum between 1 AND 2;

oracle

方案一:

1
2
3
4

select u_name,u_sal
from (select users.*,rownum rn from users where rownum <= end)
where rn > start

方案二:

1
2
3
4

select u_name,u_sal
from (select users.*,rownum rn from users)
where rn between start and end

方案一效率更高!!!

mysql

使用limit 完成分页