关键字: struts spring hibernate 分页
分页有2种办法:
1、 从数据库中取得记录,在内存中再划分,如果遇到记录数很大的时候效率很成问题,此法不可取。
2、 采用hibernate的物理分页,每次只是取一页。从客户端传进来的是第几页和每页多少条记录,要首先查询符合记录的总记录数,
再根据总记录数和当前页,每页记录数可以算出要取的是数据库中的第几条记录。2次查询不可避免了。
单元测试如下:
java 代码
- public
final
void
testFindAllRole() {
- logger.debug("testFindAllRole"
);
- long
l1=System.currentTimeMillis();
- String myaction="roleList.do"
;
- String curPageNO = "1"
;
- CriteriaQuery cq=new
CriteriaQuery(Role.class
,curPageNO,myaction);
- cq.setPageSize(10
);
- PageSupport ps=BeanFactory.getInstance().getRightDelegate().findAllRole(cq, state);
- long
l2=System.currentTimeMillis();
- AppUtils.printCollection(ps.getResultList());
- System.out.println("一共用时为 : "
+(l2-l1));
- }
CriteriaQuery类是对hibernate QBC查询方法的封装,需要的参数是当前操作的实体类,myaction,curPageNO,pageSize,
以便构造出相应的上下翻页的工具条。
在delegate中只是将值向下传:
java 代码
- public
PageSupport findAllRole(CriteriaQuery cq, IState state) {
- if
(DelegateUtil.isNullParam(cq, "CriteriaQuery"
, state)) {
- return
null
;
- }
- Request req = new
Request();
- req.setServiceName(ServiceConsts.FindAllRoleProcessor);
- req.setValue("CriteriaQuery"
, cq);
- try
{
- Response resp = getDelegate().execute(req);
- DelegateUtil.setIState(state, resp);
- return
(PageSupport) (resp.getValue("PageSupport"
));
- } catch
(Exception e) {
- DelegateUtil.handleException(e, "findAllRole"
, state);
- }
- return
new
PageSupport();
- }
在业务逻辑模块command中:
java 代码
- public class FindAllRole extends Command
- {
- private RoleDao dao;
- public void setDao(RoleDao dao) {
- this .dao = dao;
- }
- public void execute(Map params, Map response) throws Exception
- {
- response.put("PageSupport" , (PageSupport)dao.find((CriteriaQuery) params.get("CriteriaQuery" ),true ));
- }
- ……
- }
java 代码
- public
PageSupport find(final
CriteriaQuery cq,final
boolean
isOffset) {
- return
(PageSupport)getHibernateTemplate().execute(
- new
HibernateCallback() {
- public
Object doInHibernate(Session session) throws
HibernateException, SQLException {
- Criteria criteria = cq.getDetachedCriteria().getExecutableCriteria(session);
- //得到总行数
- int
allCounts = ((Integer) criteria.setProjection(Projections.rowCount()).uniqueResult()).intValue();
- criteria.setProjection(null
);//还原
- int
curPageNO = PagerUtil.getCurPageNO(cq.getCurPage());// 当前页
- int
offset = PagerUtil.getOffset(allCounts, curPageNO, cq.getPageSize());
- String toolBar = PagerUtil.getBar(cq.getMyaction(),allCounts,curPageNO,cq.getPageSize());
- if
(isOffset){
- criteria.setFirstResult(offset);
- criteria.setMaxResults(cq.getPageSize());
- }
- return
new
PageSupport(criteria.list(),toolBar,offset,curPageNO);
- }
- }
- ,true
);
- }
在后台的整个操作中非常简练,只需要将带上参数的CriteriaQuery传到dao就可以实现。
CriteriaQuery封装了hibernate的DetachedCriteria。
java 代码
- public class CriteriaQuery implements Serializable{
- public CriteriaQuery(){
- super ();
- }
- private static final long serialVersionUID = -1464383406564081554L;
- // 初始化list
- private String curPage=null ;
- private int pageSize=10 ;
- private String myaction;
- private String myform;
- private CriterionList criterionList;
- private DetachedCriteria detachedCriteria;
- public CriteriaQuery(Class c) {
- this .criterionList = new CriterionList();
- this .detachedCriteria=DetachedCriteria.forClass(c);
- }
- public CriteriaQuery(Class c,String curPage,String myaction,String myform) {
- this .myaction=myaction;
- this .myform=myform;
- this .curPage=curPage;
- this .criterionList = new CriterionList();
- this .detachedCriteria=DetachedCriteria.forClass(c);
- }
- public CriteriaQuery(Class c,String curPage,String myaction) {
- this .myaction=myaction;
- this .curPage=curPage;
- this .criterionList = new CriterionList();
- this .detachedCriteria=DetachedCriteria.forClass(c);
- }
- public CriterionList getCriterionList() {
- return criterionList;
- }
- public void setCriterionList(CriterionList criterionList) {
- this .criterionList = criterionList;
- }
- public String getCurPage() {
- return curPage;
- }
- public void setCurPage(String curPage) {
- this .curPage = curPage;
- }
- public DetachedCriteria getDetachedCriteria() {
- return detachedCriteria;
- }
- public void setDetachedCriteria(DetachedCriteria detachedCriteria) {
- this .detachedCriteria = detachedCriteria;
- }
- public void add(Criterion c) {
- detachedCriteria.add(c);
- }
- public void add() {
- for (int i=0 ;i
- add(getCriterionList().getParas(i));
- }
- }
- public void createAlias(String name){
- detachedCriteria.createCriteria(name);
- }
- public void createAlias(String name,String value){
- detachedCriteria.createCriteria(name,value);
- }
- public Criterion and(CriteriaQuery query, int source, int dest) {
- return Restrictions.and(query.getCriterionList().getParas(source),query.getCriterionList().getParas(dest));
- }
- public Criterion and(Criterion c,CriteriaQuery query, int source) {
- return Restrictions.and(c, query.getCriterionList().getParas(source));
- }
- public Criterion and(Criterion c1, Criterion c2) {
- return Restrictions.and(c1,c2);
- }
- public Criterion or(CriteriaQuery query, int source, int dest) {
- return Restrictions.or(query.getCriterionList().getParas(source),query.getCriterionList().getParas(dest));
- }
- public Criterion or(Criterion c,CriteriaQuery query, int source) {
- return Restrictions.or(c, query.getCriterionList().getParas(source));
- }
- public void or(Criterion c1, Criterion c2) {
- this .detachedCriteria.add(Restrictions.or(c1,c2));
- }
- public void addOrder(String ordername,String ordervalue) {
- // 写入order查询条件
- if ("asc" .equals(ordername)) {
- detachedCriteria.addOrder(Order.asc(ordervalue));
- } else {
- detachedCriteria.addOrder(Order.desc(ordervalue));
- }
- }
- public void eq(String keyname,Object keyvalue){
- criterionList.addPara(Restrictions.eq(keyname, keyvalue));
- }
- public void notEq(String keyname,Object keyvalue){
- criterionList.addPara(Restrictions.or(Restrictions.gt(keyname, keyvalue),Restrictions.lt(keyname, keyvalue)));
- }
- public void like(String keyname,Object keyvalue){
- criterionList.addPara(Restrictions.like(keyname, keyvalue));
- }
- public void gt(String keyname,Object keyvalue){
- criterionList.addPara(Restrictions.gt(keyname, keyvalue));
- }
- public void lt(String keyname,Object keyvalue){
- criterionList.addPara(Restrictions.lt(keyname, keyvalue));
- }
- public void le(String keyname,Object keyvalue){
- criterionList.addPara(Restrictions.le(keyname, keyvalue));
- }
- public void ge(String keyname,Object keyvalue){
- criterionList.addPara(Restrictions.ge(keyname, keyvalue));
- }
- public void ilike(String keyname,Object[] keyvalue){
- criterionList.addPara(Restrictions.in(keyname, keyvalue));
- }
- public void between(String keyname,Object keyvalue1,Object keyvalue2) {
- Criterion c=null ;// 写入between查询条件
- if (!AppUtils.isBlank(keyvalue1) && !AppUtils.isBlank(keyvalue2)) {
- c=Restrictions.between(keyname, keyvalue1, keyvalue2);
- } else if (!AppUtils.isBlank(keyvalue1)) {
- c=Restrictions.ge(keyname, keyvalue1);
- } else if (!AppUtils.isBlank(keyvalue2)) {
- c=Restrictions.le(keyname, keyvalue2);
- }
- criterionList.add(c);
- }
- public int getPageSize() {
- return pageSize;
- }
- public void setPageSize(int pageSize) {
- this .pageSize = pageSize;
- }
- public String getMyaction() {
- return myaction;
- }
- public void setMyaction(String myaction) {
- this .myaction = myaction;
- }
- public String getMyform() {
- return myform;
- }
- public void setMyform(String myform) {
- this .myform = myform;
- }
- }
另外需要一些辅助类的支持:
例如PageSupport 封装了返回的数据和工具条。
java 代码
- public
class
PageSupport implements
Serializable{
- private
int
curPageNO;
- private
int
offset;
- private
String toolBar;
- private
List resultList=null
;
- public
PageSupport() {
- }
- public
PageSupport(List resultList,String toolBar,int
offset,int
curPageNO) {
- this
.curPageNO=curPageNO;
- this
.offset=offset;
- this
.toolBar=toolBar;
- this
.resultList=resultList;
- }
- public
List getResultList() {
- return
resultList;
- }
- public
void
setResultList(List resultList) {
- this
.resultList = resultList;
- }
- public
String getToolBar() {
- return
toolBar;
- }
CriterionList封装了Hibernate的参数:
java 代码
- /**
- * 封装了ArrayList,装的是Criterion
- */
- public class CriterionList extends ArrayList {
- public final Criterion getParas(final int index) {
- return (Criterion) super .get(index);
- }
- public final void addPara(final int index, final Criterion p) {
- super .add(index, p);
- }
- public final void addPara(final Criterion p) {
- super .add(p);
- }
- public final int indexofPara(final Criterion p) {
- return super .indexOf(p);
- }
- public final void removePara(final int index) {
- super .remove(index);
- }
- }
PagerUtil用于将客户端的页码和每页多少个记录算出应该取数据库中的记录的位置,再由hibernate构造出一个三层的sql语句。
java 代码
- public class PagerUtil {
- /**
- *
- * @param allCounts 总记录数
- * @param curPageNO
- * @param pageSize 每页显示的记录数目
- * @return
- */
- public static String getBar(String action,String form,int allCounts,int curPageNO, int pageSize){
- Pager pager = null ;
- try {
- if (curPageNO<1 ) curPageNO=1 ;
- if (curPageNO> (int ) Math.ceil((double ) allCounts / pageSize))
- curPageNO =(int ) Math.ceil((double ) allCounts / pageSize);
- //得到offset
- int offset = (curPageNO-1 )*pageSize;//从第几条开始取值
- //生成工具条
- pager=new Pager(allCounts,offset,pageSize);
- pager.setCurPageNO(curPageNO);//设置当前的页码
- } catch (Exception e) {
- System.out.println("生成工具条出错!" );
- }
- return pager.getToolBar(action,form);
- }
- public static String getBar(String url,int allCounts,int curPageNO, int pageSize){
- Pager pager = null ;
- try {
- if (curPageNO<1 ) curPageNO=1 ;
- if (curPageNO> (int ) Math.ceil((double ) allCounts / pageSize))
- curPageNO =(int ) Math.ceil((double ) allCounts / pageSize);
- //得到offset
- int offset = (curPageNO-1 )*pageSize;//从第几条开始取值
- //生成工具条
- pager=new Pager(allCounts,offset,pageSize);
- pager.setCurPageNO(curPageNO);//设置当前的页码
- } catch (Exception e) {
- System.out.println("生成工具条出错!" );
- }
- return pager.getToolBar(url);
- }
- public static int getOffset(int rowCounts,int curPageNO, int pageSize){
- int offset=0 ;
- try {
- //得到第几页
- if (curPageNO<1 ) curPageNO=1 ;
- if (curPageNO> (int ) Math.ceil((double ) rowCounts / pageSize))
- curPageNO =(int ) Math.ceil((double ) rowCounts / pageSize);
- //得到offset
- offset = (curPageNO-1 )*pageSize;
- } catch (Exception e) {
- System.out.println("getOffset出错!" );
- }
- return offset;
- }
- public static int getCurPageNO(String curPage){
- int curPageNO;
- if (curPage==null ||"" .equals(curPage.trim())) {
- curPageNO=1 ;//第一次处于第一页
- } else {
- try {
- curPageNO = Integer.parseInt(curPage);//得到当前页
- } catch (Exception e) {
- curPageNO=1 ;
- }
- }
- return curPageNO;
- }
- }
在struts 的Action 中:
java 代码
- private
ActionForward findAllRole(ActionMapping mapping, ActionForm form, HttpServletRequest request,
- HttpServletResponse response) {
- String curPageNO = request.getParameter("curPageNO" );
- String myaction;
- String search=request.getParameter("search" );
- //String myform="forms[0]";
- if (search==null ){
- search="" ;
- myaction="findAllRole.do" ;
- }else {
- myaction="findAllRole.do?search=" +search;
- }
- removeRequestAttribute(mapping,request);
- try {
- //Qbc查找方式
- CriteriaQuery cq=new CriteriaQuery(Role.class ,curPageNO,myaction);
- cq.setPageSize(Constants.PAGE_SIZE);
- if (!AppUtils.isBlank(search)){
- cq.like("name" ,"%" +search+"%" );
- cq.add();
- }
- IState state=new StateImpl();
- PageSupport ps= (PageSupport)getBiz().findAllRole(cq, state);
- request.setAttribute("search" ,search);
- request.setAttribute("curPageNO" ,new Integer(ps.getCurPageNO()));
- request.setAttribute("offset" , new Integer(ps.getOffset()+1 ));
- request.setAttribute("list" , ps.getResultList());
- if (!AppUtils.isBlank(ps.getResultList()))
- request.setAttribute("toolBar" , ps.getToolBar());
- }catch (Exception e){
- return handleException(mapping, request, e, "RoleAction's findAllRole" );
- }
- return mapping.findForward("view" );
- }
采用QBC 对单表的分页很简单,只需要改变参数,并且在action 和jsp 之间传递。如果要分页的内容涉及到几张表,
可以将业务逻辑封装为一个视图,并且做好视图相应的实体类,便可以象操纵单表一样。
1、 另外可以使用HQL 来实现分页,在dao 中:
java 代码
- public PageSupport find(final HqlQuery hqlQuery,final boolean needParameter)
- {
- //logger.debug("find PageSupport ,HQL is: "+hqlQuery.getQueryString());
- return (PageSupport)getHibernateTemplate().execute(
- new HibernateCallback() {
- public Object doInHibernate(Session session) throws HibernateException, SQLException {
- Query query=session.createQuery(hqlQuery.getQueryString());
- if (needParameter)
- query.setParameters(hqlQuery.getParam(), hqlQuery.getTypes());
- int allCounts=query.list().size();
- int curPageNO = PagerUtil.getCurPageNO(hqlQuery.getCurPage());
- int offset = PagerUtil.getOffset(allCounts, curPageNO, hqlQuery.getPageSize());
- String toolBar = PagerUtil.getBar(hqlQuery.getMyaction(),allCounts,curPageNO,hqlQuery.getPageSize());
- query.setFirstResult(offset);
- query.setMaxResults(hqlQuery.getPageSize());
- return new PageSupport(query.list(),toolBar,offset,curPageNO);
- }
- }
- ,true );
- }
HqlQuery
代码如下:
java 代码
- public class HqlQuery implements Serializable{
- private String curPage=null ;
- private int pageSize=10 ;
- private String myaction;
- private String myform;
- private String queryString;
- private Object[] param;
- private Type[] types;
- public HqlQuery(String queryString, Object[] param) {
- this .queryString = queryString;
- this .param = param;
- }
- public HqlQuery(String myaction) {
- this .myaction = myaction;
- }
- public Object[] getParam() {
- return param;
- }
- public HqlQuery(String myaction, String queryString, Object[] param,Type[] types) {
- this .myaction = myaction;
- this .queryString = queryString;
- this .param = param;
- this .types=types;
- }
- public void setParam(Object[] param) {
- this .param = param;
- }
- public String getCurPage() {
- return curPage;
- }
- public void setCurPage(String curPage) {
- this .curPage = curPage;
- }
- public String getMyaction() {
- return myaction;
- }
- public void setMyaction(String myaction) {
- this .myaction = myaction;
- }
- public String getMyform() {
- return myform;
- }
- public void setMyform(String myform) {
- this .myform = myform;
- }
- public int getPageSize() {
- return pageSize;
- }
- public void setPageSize(int pageSize) {
- this .pageSize = pageSize;
- }
- public String getQueryString() {
- return queryString;
- }
- public void setQueryString(String queryString) {
- this .queryString = queryString;
- }
- public Type[] getTypes() {
- return types;
- }
- public void setTypes(Type[] types) {
- this .types = types;
- }
- }
如此的单元测试:
java 代码
- public final void testfindOtherFunctionByHql() {
- logger.debug("testfindFunctionByRole" );
- long l1=System.currentTimeMillis();
- String queryString="from Function where id=?" ;
- HqlQuery hqlQuery=new HqlQuery("myaction" );
- hqlQuery.setCurPage("1" );
- //hqlQuery.setPageSize(10);
- PageSupport functions=BeanFactory.getInstance().getRightDelegate().findOtherFunctionByHql(hqlQuery,"1" , state);
- long l2=System.currentTimeMillis();
- AppUtils.printCollection(functions.getResultList());
- List list=functions.getResultList();
- for (int i=0 ;i
- Function f=(Function)list.get(i);
- System.out.println("getId = " +f.getId());
- }
- System.out.println("一共用时为 : " +(l2-l1));
- }
总结:将分页的功能封装起来,每次请求构造参数不同的CriteriaQuery 和HqlQuery ,后台的操作都是重复不变的,
变得是查询的条件。如使用CriteriaQuery 可以这样构造条件,Or 或者and 的条件可以用面向对象的方式来构造:
java 代码
- public final void findAllRole() {
- logger.debug("findAllRole" );
- long l1=System.currentTimeMillis();
- String curPage = "2" ;
- String myaction="xxx.do?" ; //Struts action的名字,看工具条的提示
- String myform="forms[0]" ; //html 里面相应的form的名字
- CriteriaQuery cq=new CriteriaQuery(Role.class ,curPage,myaction,myform); //Role.class为指定的类
- cq.setPageSize(2 ); //不填默认为10
- cq.setCurPage(curPage); //当前页,默认是第一页
- cq.addOrder("asc" ,"id" ); //根据id来排序,顺序是asc,或者是desc
- cq.like("name" , "ROLE_SUPERVISOR" ); //条件0是name='test',其余大于小于like调用相应的API
- cq.eq("enabled" , "1" );//1 //条件1
- cq.eq("roleType" , "ROLE_SUPERVISOR" );//条件2
- //cq.add(); //条件1&&条件2
- cq.add(cq.or(cq.and(cq, 0 , 1 ), cq, 2 ));
- //cq.add(); //增加条件,如果没有条件之间的关系则可以直接add()不带参数。
- PageSupport ps=BeanFactory.getInstance().getRightDelegate().findAllRole(cq,state);
- long l2=System.currentTimeMillis();
- System.out.println("list size: " +ps.getResultList().size());
- System.out.println("一共用时为 : " +(l2-l1));
- for (int i=0 ;i
- Role role=(Role)ps.getResultList().get(i);
- System.out.println("id = " + role.getId());
- //print your code here
- System.out.println("-----------------" );
- }
- AppUtils.printCollection(ps.getResultList());
- System.out.println(ps.getToolBar());
- }
看cq.add(cq.or(cq.and(cq, 0, 1), cq, 2)) ,这句是为CriteriaQuery 增加条件。意思是条件(0 and 1 ) or 2 。以下是日志打出来的结果:<o:p></o:p>
java 代码
- DEBUG [14 :50 :53 ] (POJODelegate.java:execute:82 ) - POJODelegate executing
- INFO [14
:50
:53
] (SequenceProcessor.java:doActivities:23
) - SequenceProcessor 流程开始
- <-- FindAllRoleProcessor
- INFO [14 :50 :53 ] (SequenceProcessor.java:doActivities:32 ) - 活动 : FindAllRole
- Hibernate: select count(*) as y0_ from t_role this_ where ((this_.name like ? and this_.enabled=?)
- or this_.role_type=?) order by this_.id asc
- //首先查出总的记录数
- Hibernate: select this_.id as id0_, this_.name as name3_0_, this_.role_type as role3_3_0_,
- this_.enabled as enabled3_0_, this_.note as note3_0_ from t_role this_ where ((this_.name like ?
- and this_.enabled=?) or this_.role_type=?) order by this_.id asc limit ?
- INFO [14 :50 :53 ] (SequenceProcessor.java:doActivities:69 ) - SequenceProcessor 流程结束 -->
- list size: 1
- 一共用时为 : 531
- id = 1
- -----------------
- 首页 上一页 下一页 尾页 共1 条记录 转到"location='xxx.do?&curPageNO='+this.options[this.selectedIndex].value" >1 ' selected>第1 页
来自:http://onecan.javaeye.com/blog/93396 good