主要思路:从前端获取当前页offset 和 当前页的条数pagesize ,然后使用分页查询的方式,进行数据库查询,完成分页。首先,分页类:
public class SystemContext {
private static ThreadLocal offset = new ThreadLocal();
private static ThreadLocal pagesize = new ThreadLocal();
public static void setOffset(int _offset){
offset.set(_offset);
}
public static int getOffset(){
Integer _offset = (Integer)offset.get();
if(_offset == null){
return 0;
}
return _offset;
}
public static void removeOffset(){
offset.remove();
}
public static void setPagesize(int _pagesize){
pagesize.set(_pagesize);
}
public static int getPagesize(){
Integer _pagesize = (Integer)pagesize.get();
if(_pagesize == null){
return Integer.MAX_VALUE;
}
return _pagesize;
}
public static void removePagesize(){
pagesize.remove();
}
}
定义两个参数,使用线程同步锁,以保证本次查询来自同一个线程。
2,接着去拦截器,将这两个参数拦截。具体框架,具体配置拦截器,如果不清楚,请先学习自己框架的拦截器怎么配置。拦截方法如下:
private void pager(Invocation inv){
SystemContext.setOffset(getOffset(inv.getController().getRequest()) < 0 ? 0 : getOffset(inv.getController().getRequest()));
SystemContext.setPagesize(getPagesize(inv.getController().getRequest()) < 0 ? 0 : getPagesize(inv.getController().getRequest()));
}
protected int getOffset(HttpServletRequest request){
int offset = 1;
// 希望从request中获得offset参数
try {
offset = Integer.parseInt(request.getParameter("offset"));
} catch (Exception ignore) {
}
return offset;
}
protected int getPagesize(HttpServletRequest request){
int pagesize = 100;
try {
pagesize = Integer.parseInt(request.getParameter("pagesize"));
} catch (Exception ignore) {
}
return pagesize;
}
3、接着去写一个通用的分页查询方法(其主要通过拼SQL的方式去查询,先找到你要的数据库方式分页方式,再组装即可SQL即可,我这里举例了SQLserver数据库的分页方式,用了低效的查询方式,可以有更高效率的 row number ...over方法,当时才发现2000版本不支持这个方法,所以改用了该低效的方法):
public PagerVo findPage(String sql, String orderCol) {
/**
select select_col from (select top 10 select_col from (select top 20 select_col from terminal t22 order by pkid desc) as t order by pkid asc) as t1 order by pkid desc
*/
//先补上分页的信息
String sqlTmp = sql.toLowerCase();
//把字段拿出来
String tmp1 = sqlTmp.substring(0, sqlTmp.indexOf("from"));
String temp2 = sqlTmp.substring(sqlTmp.indexOf("from"));
//tmp1 = tmp1.trim();
tmp1 =tmp1.replace("select", "");
String orderCon = "SELECT "
+ " * "
+ " FROM ( "
+ "SELECT TOP "
+ SystemContext.getPagesize()
+ " "
+ " * "
+ " "
+ " FROM (SELECT TOP "
+ (SystemContext.getOffset()*SystemContext.getPagesize())
+" 0 as m , "
+ tmp1
+" "
+ temp2
+ " ORDER BY "
+ orderCol
+ " DESC) AS T ORDER BY t.m "
+ " ASC) AS T1 ORDER BY t1.m"
+ " DESC";
String totalSql = " select count(1) as total_num from ( "+sql+" ) t_total__ ";
List totalCount = Db.query(totalSql);//Jfinal中的方法,hibernate等框架不同
List datas = Db.query(orderCon);
PagerVo pv = new PagerVo();
pv.setTotal((totalCount == null || totalCount.size() <= 0) ? 0 : ((Integer)totalCount.get(0)) );
pv.setDatas(datas);
return pv;
}
4,在DAO中写好SQL后,直接调用分页这个分页方法,返回总数和数据。