求一个能同时支持derby,DB2,SQLServer三种数据库的数据库分页的方法
[b]问题补充：[/b]
不使用持久层框架
补充:
上面的分页sql语句是sqlserver的.
下面是其它数据库的,比照的写就OK了.
几种数据库分页获得本页数据的写法！
oracle:
SELECT * FROM
(
SELECT A.*, rownum r
FROM
(
SELECT *
FROM Articles
ORDER BY PubTime DESC
) A
WHERE rownum <= PageUpperBound
) B
WHERE r > PageLowerBound;
注释：红色部分为可更换的查询语句PageUpperBound为大树，也就是我们所说的结束的条数目，PageLowerBound为开始的条数目！
DB2:
select * from ( select rownumber() over() as row_next,t.* from (
SELECT *
FROM Articles
ORDER BY PubTime DESC
注释：红色部分可更换你想要的查询语句10更换为开始的条目30更换为结束的条目数字
MYSQL：
sql + " limit " + spage + "," + perpagenum;
注释：sql可替换为你想要的查询语句spage为开始的条目数字，perpagenum为开始往后有多少条
PostgreSQL：
sql + " limit " + perpagenum + " OFFSET " + spage
注释：注意的地方与上面相同
SQLSERVER
分页方案一：(利用Not In和SELECT TOP分页)
语句形式：
SELECT TOP 10 *
FROM TestTable
WHERE (ID NOT IN
(SELECT TOP 20 id
FROM TestTable
ORDER BY id))
ORDER BY ID
SELECT TOP 页大小 *
FROM TestTable
WHERE (ID NOT IN
(SELECT TOP 页大小*页数 id
FROM 表
ORDER BY id))
ORDER BY ID
分页方案二：(利用ID大于多少和SELECT TOP分页）
语句形式：
SELECT TOP 10 *
FROM TestTable
WHERE (ID >
(SELECT MAX(id)
FROM (SELECT TOP 20 id
FROM TestTable
ORDER BY id) AS T))
ORDER BY ID
SELECT TOP 页大小 *
FROM TestTable
WHERE (ID >
(SELECT MAX(id)
FROM (SELECT TOP 页大小*页数 id
FROM 表
ORDER BY id) AS T))
ORDER BY ID
;Hibernate
ibatis,Hibernate
等等ORM都做得不错
自己去看hibernate的源代码，javaeye也发布过一些精简版的分页方法，也是模仿hibernate编写的，具体地址忘了。
在接触持久框架之前写过分页代码，已考虑到跨数据库问题.
基本思路和持久框架相近, 就是将特定数据库的分页SQL语句封装到DAO实现当中.用抽象工厂来隔离持久层和业务层.
下面是代码和使用方式.
[code="java"]
package oes.page;
import java.util.List;
/**
@param 存储数据类型,比如对用户列表分页,或对定单列表分页.
/
public class PageBean {
private int curPage = 1; //当前是第几页 *
private int maxPage; //一共有多少页
private int maxRowCount; //一共有多少行 **
private int rowsPerPage=3; //每页多少行,默认为3行
private List list;
//默认构造方法
public PageBean() {
super();
// TODO 自动生成构造函数存根
}
//当前页，数据总行数
public PageBean(int curPage, int maxRowCount) {
this.curPage = curPage;
this.maxRowCount = maxRowCount;
}
//当前页，数据总行数,每页可显示行数
public PageBean(int curPage, int maxRowCount, int rowsPerPage) {
this(curPage,maxRowCount);
this.rowsPerPage = rowsPerPage;
}
public int getCurPage() {
return curPage;
}
//用于设置当前页码 *
public void setCurPage(int curPage) {
if (curPage <= this.getFirstPage()) {
this.curPage = this.getFirstPage();
} else if (curPage >= this.getLastPage()) {
this.curPage = this.getLastPage();
} else {
this.curPage = curPage;
}
}
public int getMaxPage() {
maxPage = (maxRowCount + rowsPerPage - 1) / rowsPerPage;
return maxPage;
}
public void setMaxPage(int maxPage) {
this.maxPage = maxPage;
}
public int getMaxRowCount() {
return maxRowCount;
}
public void setMaxRowCount(int maxRowCount) {
//如果记录数为0; 给出默认值1.
if (maxRowCount == 0) {
this.maxRowCount = 1;
} else {
this.maxRowCount = maxRowCount;
}
}
//用于设置每页可显示的行数 *
public int getRowsPerPage() {
return rowsPerPage;
}
public void setRowsPerPage(int rowsPerPage) {
this.rowsPerPage = rowsPerPage;
}
public List getList() {
return list;
}
public void setList(List list) {
this.list = list;
}
//返回第一页页码
public int getFirstPage() {
//第一页数据总是返回1
return 1;
}
//返回最后一页页码
public int getLastPage() {
return (this.maxRowCount + this.rowsPerPage - 1 ) / this.rowsPerPage;
}
}
[/code]
[code="java"]
package oes.page;
/**
import java.util.List;
public class AbstractAction {
private AbstractAction(){}
/**
分页逻辑控制的便利使用方法.
*/
public static PageBean getPageBean(String cur,PageDAO dao,int... arrs) {
PageBean<T> pb = new PageBean<T>();
//由数据库中查得当前的总行数
pb.setMaxRowCount(dao.getCount());
int curPage = 1; //传递进来的cur如果不为非负整数则取当前页为第一页.
if (cur != null && cur.matches("\\d+")) {
curPage = Integer.parseInt(cur);
//System.out.println(curPage);
}
//由页面得到当前页码数
pb.setCurPage(curPage);
if (arrs.length != 0) {
pb.setRowsPerPage(arrs[0]);
}
//获取所请求页码的数据
List<T> list = dao.getResult(pb.getCurPage(),pb.getRowsPerPage());
pb.setList(list);
return pb;
}
}
[/code]
DAO层的实现方式
[code="java"]package oes.dao;
import oes.entity.*;
import oes.page.PageDAO;
/**
定义用户持久层接口.
*/
public interface UserDAO extends DAO {
/*
没有分页形式的查询出所有用户列表.
*/
public List selectAll()
/*
import java.util.*;
import java.sql.*;
import oes.page.*;
import oes.dao.UserDAO;
import oes.entity.Role;
import oes.entity.User;
public class UserDAOImpl implements UserDAO {
private DBConnection dbcon = null;
public UserDAOImpl() {
dbcon = new DBConnection();
}
public List selectAll() {
User user = null;
Role role = null;
List<User> list = null;
Map<Integer,User> users = new HashMap<Integer,User>();
Map<Integer,Role> roles = new HashMap<Integer,Role>();
Connection conn = null;
PreparedStatement psmt = null;
ResultSet rs = null;
String sql = "select uid,uname,upassword,urealname,ustate,rid,rname from view_user_role ";
try {
conn = dbcon.getConnection();
psmt = conn.prepareStatement(sql);
rs = psmt.executeQuery();
while (rs != null && rs.next()) {
int uid = rs.getInt(1);
int rid = rs.getInt(6);
if (users.containsKey(uid)) {
user = users.get(uid);
if (roles.containsKey(rid)) {
role = roles.get(rid);
} else {
role = new Role();
role.setRid(rid);
role.setRname(rs.getString(7));
roles.put(rid,role);
}
user.setRole(role);
} else {
user = new User();
user.setUid(uid);
user.setUname(rs.getString(2));
user.setUpassword(rs.getString(3));
user.setUrealname(rs.getString(4));
user.setUstate(rs.getInt(5));
if (roles.containsKey(rid)){
role = roles.get(rid);
} else {
role = new Role();
role.setRid(rid);
role.setRname(rs.getString(7));
roles.put(rid,role);
}
user.setRole(role);
users.put(uid,user);
}
}
list = new ArrayList<User>(users.values());
} catch (SQLException sqle) {
sqle.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
CloseUtil.closeResultSet(rs);
CloseUtil.closePreparedStatement(psmt);
CloseUtil.closeConnection(conn);
}
return list;
}
public PageDAO<User> selectUsers() {
return new PageDAO<User>() {
int count = selectAll().size();
public List<User> getResult(int curPage,int rowsPerPage) {
List<User> list = selectAll();
if (curPage * rowsPerPage >= count || rowsPerPage >= count) {
list = list.subList((curPage - 1) * rowsPerPage,count);
} else if (curPage * rowsPerPage < count) {
list = list.subList((curPage - 1) * rowsPerPage,curPage * rowsPerPage);
}
return list;
}
public int getCount() {
return count;
}
};
}
}
[/code]
[code="java"]package com.dao;
import com.entity.*;
import java.util.*;
import com.util.*;
public interface ReplyDAO extends DAO{
public PageDAO> getReplysByTid(int tid);
}
[/code]
[code="java"]
package com.dao;
import com.entity.Reply;
import com.entity.User;
import com.util.TwoTuple;
import java.sql.*;
import java.util.*;
public class ReplyDAOImpl implements ReplyDAO {
private DBConnection dbcon = null;
public ReplyDAOImpl() {
dbcon = new DBConnection();
}
public PageDAO<TwoTuple<User, Reply>> getReplysByTid(final int tid) {
return new PageDAO<TwoTuple<User, Reply>>() {
public List<TwoTuple<User, Reply>> getResult(int curPage,
int rowPerPage) {
List<TwoTuple<User, Reply>> list = new ArrayList<TwoTuple<User, Reply>>();
TwoTuple<User, Reply> twoTuple = null;
User user = null;
Reply reply = null;
Connection conn = null;
PreparedStatement psmt = null;
ResultSet rs = null;
String sql = "select top " + rowPerPage
+ " * from bbsreply where rid not in (select top "
+ rowPerPage * (curPage - 1)
+ " rid from bbsreply where rtid = ?) and rtid = ?";
UserDAOImpl userDAOImpl = new UserDAOImpl();
Map<Integer, User> map = new HashMap<Integer, User>(); // 对查询User对象进行缓存
try {
conn = dbcon.getConnection();
psmt = conn.prepareStatement(sql);
psmt.setInt(1, tid);
psmt.setInt(2, tid);
rs = psmt.executeQuery();
while (rs != null && rs.next()) {
reply = new Reply();
reply.setRid(rs.getInt(1));
reply.setRtid(rs.getInt(2));
reply.setRsid(rs.getInt(3));
int uid = rs.getInt(4);
reply.setRuid(uid);
reply.setRface(rs.getInt(5));
reply.setRcontents(rs.getString(6));
reply.setRtime(rs.getDate(7) + " " + rs.getTime(7));
reply.setRclickCount(rs.getInt(8));
reply.setIp(rs.getString(9));
reply.setUpdatetime(rs.getDate(10) + " " + rs.getTime(10));
if (!map.containsKey(uid)) {
user = userDAOImpl.select(uid);
map.put(uid, user);
}
twoTuple = new TwoTuple<User, Reply>(map.get(uid),
reply);
list.add(twoTuple);
}
} catch (SQLException sqle) {
sqle.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
CloseUtil.closeResultSet(rs);
CloseUtil.closeStatement(psmt);
CloseUtil.closeConn(conn);
}
return list;
}
public int getCount() {
int count = 0;
Connection conn = null;
PreparedStatement psmt = null;
ResultSet rs = null;
String sql = "select count(*) from bbsreply where rtid = ?";
try {
conn = dbcon.getConnection();
psmt = conn.prepareStatement(sql);
psmt.setInt(1, tid);
rs = psmt.executeQuery();
if (rs != null && rs.next()) {
count = rs.getInt(1);
}
} catch (SQLException sqle) {
sqle.printStackTrace();
} finally {
CloseUtil.closeResultSet(rs);
CloseUtil.closeStatement(psmt);
CloseUtil.closeConn(conn);
}
return count;
}
};
}
}
[/code]
上面两个针对用户类和回贴类进行分页处理,PageDao接口都对匿名内部类实现.
分页一个是对List的分页,另一个是用SQL语句分页.
WEB层控制器的使用代码
[code="java"]
package oes.action.impl.user;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import oes.action.Action;
import oes.dao.*;
import oes.page.*;
import oes.entity.*;
public class SupportUserAction implements Action {
//处理用户维护.
public String process(HttpServletRequest request,
HttpServletResponse response) {
// TODO 自动生成方法存根
Factory factory = Factory.getFactory("oes.dao.impl.SQLFactory");
UserDAO userDAO = factory.getUserDAO();
String cur = request.getParameter("cur");
PageDAO pageDAO = userDAO.selectUsers();
PageBean pageBean = AbstractAction.getPageBean(cur,pageDAO);
request.setAttribute("pb",pageBean);
return "/jsp/User/UserSupport.jsp";
}
}
[/code]
页面使用代码:
[code="java"]
第一页--上一页
/c:if
/c:param
</c:url>
<a href="${link1}">第一页</a>--
<c:url var="link2" value="/control/supportuser">
<c:param name="cur" value="${pb.curPage -1}" />
</c:url>
<a href="${link2}">上一页</a>
</c:if>
--
<c:if test="${pb.curPage==pb.lastPage}">
下一页--最后页
</c:if>
<c:if test="${pb.curPage!=pb.lastPage && pb.maxRowCount != 0}">
<c:url var="link3" value="/control/supportuser">
<c:param name="cur" value="${pb.curPage +1}" />
</c:url>
<a href="${link3}">下一页</a>--
<c:url var="link4" value="/control/supportuser">
<c:param name="cur" value="${pb.lastPage}" />
</c:url>
<a href="${link4}">最后页</a>
</c:if>
转到<select onchange="jumpPage(this)">
<c:forEach var="i" begin="1" end="${pb.lastPage}">
<option value="${i}" <c:if test="${pb.curPage == i}">selected</c:if>>
${i}
</option>
</c:forEach>
</select>页
输入页码:<input type="text" id="jumpBox" size="3" value="${pb.curPage}" />
<input type="button" value="跳转" onclick="jumpPage(document.getElementById('jumpBox'))" />
[/code]
总结:对抽象工厂以实现跨数据库,用范型以实现跨多种类型.