带查询条件的分页实例

1、配置bean文件 2、建立DBUtilsTemplate类 public class DBUtilsTemplate {    private DataSource dataSource;    private QueryRunner queryRunner;    private static final Log LOG = LogFactory.getLog(DBUtilsTemplate.class);      public void setDataSource(DataSource dataSource) {        this.dataSource = dataSource;     }      public  QueryRunnergetQueryRunner() {        queryRunner = new QueryRunner(dataSource);        return queryRunner;     }       } 3、在BasicDao中实现通用的查询方法 // queryWhere="where name=? and age=?"          publicQueryResult<T> pageQuery(int startindex,int pagesize,StringqueryWhere,Object queryParams[]){                                       QueryResult<T>queryResult = new QueryResult<T>();                    try{                             List<T>list = null;                             Longtotalrecord = null;                                                         QueryRunnerqueryRunner = db.getQueryRunner();------------>这里实例化new QueryRunner(datasource)的那个类DBUtilsTemplate，是交给spring中的bean打理了，只需在该类中注册DBUtilsTemplate：db就可以了，就是在这样子：                                                           //Stringsql = "select * from customer where limit "                             if(startindex<0|| pagesize<0){                                      //不想分页                                      if(queryWhere==null){                                                //所有数据                                                Stringsql = "select * from "+ getCurClass().getSimpleName();                                                list= (List<T>) queryRunner.query(sql, new BeanListHandler(getCurClass()));                                                sql= "select count(*) from " + getCurClass().getSimpleName();                                                totalrecord= (Long) queryRunner.query(sql, new ScalarHandler());                                      }else{                                                //获取带条件的数据                                                                                    Stringsql = "select * from " + getCurClass().getSimpleName() + queryWhere;                                                list= (List<T>) queryRunner.query(sql, queryParams, newBeanListHandler(getCurClass()));                                                sql= "select count(*) from " + getCurClass().getSimpleName() +queryWhere;                                                totalrecord= (Long) queryRunner.query(sql, queryParams, new ScalarHandler());                                      }                             }else{                                      //想分页                                      if(queryWhere==null){                                                //获取无条件分页（所有数据的分页数据）                                    System.out.println("想分页--------不带查询条件不带查询条件不带查询条件不带查询条件");                                                Stringsql = "select * from "  +getCurClass().getSimpleName() +" limit ?,?";                                                Objectparams[] = {startindex,pagesize};                                                list= (List<T>) queryRunner.query(sql, params, newBeanListHandler(getCurClass()));                                                sql= "select count(*) from " + getCurClass().getSimpleName();                                               totalrecord= (Long) queryRunner.query(sql, new ScalarHandler());                                      }else{                                                //有条件，并且分页  // queryWhere="where name=? and age=?"                                                Stringsql = "select * from " + getCurClass().getSimpleName() + queryWhere +" limit ?,?";                                                Objectparams[] = new Object[queryParams.length+2];                                                for(inti=0;i<queryParams.length;i++){                                                         params[i]= queryParams[i];                                                }                                        System.out.println("11111111111111111111111111"+startindex+"...."+pagesize);                                                params[params.length-2]= startindex;                                                params[params.length-1]= pagesize;                                                                                               list= (List<T>) queryRunner.query(sql, params, newBeanListHandler(getCurClass()));                                                sql= "select count(*) from " + getCurClass().getSimpleName() +queryWhere;                                                totalrecord= (Long) queryRunner.query(sql, queryParams, new ScalarHandler());                                      }                             }                             queryResult.setList(list);                             queryResult.setTotalrecord(totalrecord.intValue());                             returnqueryResult;                    }catch(Exception e) {                             thrownew RuntimeException(e);                    }                             } 4、创建将查询条件封装成Bean的类                              /*  * To change this license header, chooseLicense Headers in Project Properties.  * To change this template file, choose Tools |Templates  * and open the template in the editor.  */   packagecom.scau.paper.model;   import java.util.ArrayList; importjava.util.Calendar; importjava.util.List;   /**  *  * @author didi  */ public classGroupsQueryFormBean {     private String TTeacherByMasterid;     private String TTeacherByScretid;     private String TProjectsetting;     private String groupno;       public String getTTeacherByMasterid() {         return TTeacherByMasterid;     }       public void setTTeacherByMasterid(StringTTeacherByMasterid) {         this.TTeacherByMasterid =TTeacherByMasterid;     }       public String getTTeacherByScretid() {         return TTeacherByScretid;     }       public void setTTeacherByScretid(StringTTeacherByScretid) {         this.TTeacherByScretid =TTeacherByScretid;     }       public String getTProjectsetting() {         return TProjectsetting;     }       public void setTProjectsetting(StringTProjectsetting) {         this.TProjectsetting = TProjectsetting;     }     public String getGroupno() {         return groupno;     }       public void setGroupno(String groupno) {         this.groupno = groupno;     }         public WhereAndParam buildSqlWhere(){                                                                           StringBuffersb = new StringBuffer();                                      List list =new ArrayList();                                      sb.append("where 1=1 ");                                                                           if(this.TTeacherByMasterid!=null&& !this.TTeacherByMasterid.trim().equals("")){                                                sb.append("andtgroup.TTeacherByMasterid.workno like ? ");                                                list.add("%"+ TTeacherByMasterid + "%");                                      }                                      if(this.TTeacherByScretid!=null&& !this.TTeacherByScretid.trim().equals("")){                                                sb.append("andtgroup.TTeacherByScretid.workno like ? ");                                                list.add("%"+ TTeacherByScretid + "%");                                      }                                      if(this.TProjectsetting!=null&& !this.TProjectsetting.trim().equals("")){                                                sb.append("andtgroup.TProjectsetting.year like ? ");                                                list.add("%"+TProjectsetting + "%");                                      }                                      if(this.groupno!=null&& !this.groupno.trim().equals("")){                                                sb.append("andtgroup.groupno like ? ");                                                list.add("%"+groupno + "%");                                      }                                        Stringwhere = sb.toString();   // where 1=1 andname=? and cellphone=?                                      Objectparams[] = list.toArray();                                                                           WhereAndParamwp = new WhereAndParam();                                      wp.setParams(params);                                      wp.setWhere(where);             System.out.println("在GroupsQueryFormBean中，组装出来的条件查询语句为----" + wp.getWhere());                                      return wp;                              }                                                           } 5、写查询结果页面 ！！！！！！ 6、组装SQL语句查询--à脱离HQL-àDBUtils多表查询 ==================================================出现问题============================================================= BeanListHandler与BeanHandler <c:forEach items="${list}" var="customer"> <a href="javascript:void(0)" οnclick="del(${customer.id})">删除</a> import java.util.UUID;-----------自动生成id --------------------------------------- 导入commons-beanutils-1.8.0.jar和commons-logging 将注册的用户信息包装到RegisterFormBean类里 ===========================分页================================ 1、建立好三个类PageInfo、QueryResult、PageBean -= =============================查询分页=============================== //请求参数被封装在CustomerQueryFormBean CustomerQueryFormBean customerQueryFormBean = WebUtil.request2Bean(request, CustomerQueryFormBean.class); CustomerQueryFormBean用于封装查询用户的属性--------------组装好的查询语句（带查询参数） //查询数据库的一条完整查询语句 CustomerQueryFormBean.WhereAndParam wap = customerQueryFormBean.buildSqlWhere(); //包装好分页参数 PageInfo pageInfo = WebUtil.request2Bean(request, PageInfo.class); //分页既待条件查询 PageBean pageBean = customerService.pageQueryByWhere(wap,pageInfo); 【  public PageBean pageQueryByWhere(CustomerQueryFormBean.WhereAndParam wap, PageInfo pageInfo) {         QueryResult queryResult = customerDao.pageQuery(pageInfo.getStartindex(), pageInfo.getPagesize(), wap.getWhere(), wap.getParams());         PageBean pageBean = new PageBean();         pageBean.setCurrentpage(pageInfo.getCurrentpage());         pageBean.setList(queryResult.getList());         pageBean.setPagesize(pageInfo.getPagesize());         pageBean.setTotalrecord(queryResult.getTotalrecord());         return pageBean;     } ====================================================================================== //有条件，并且分页  //  queryWhere="where name=? and age=?"      String sql = "select * from customer " + queryWhere + " limit ?,?";      Object params[] = new Object[queryParams.length+2];      for(int i=0;i<queryParams.length;i++){       params[i] = queryParams[i];      }                                         System.out.println("11111111111111111111111111"+startindex+"...."+pagesize);      params[params.length-2] = startindex;      params[params.length-1] = pagesize;            list = (List) queryRunner.query(sql, params, new BeanListHandler(Customer.class));      sql = "select count(*) from customer " + queryWhere;      totalrecord = (Long) queryRunner.query(sql, queryParams, new ScalarHandler());      】 ---------------------------------------条件的查询分页实现------------------------------------------- 1、只是分页显示页面 jsp请求页面分页参数---->分页信息Bean----->调用查询方法（分页信息） 2、又要分页又要查询的显示信息 jsp页面查询信息通过隐藏域的形式传查询参数给control，还有页面参数信息 ------------->查询信息封装成查询表单Bean----------->将查询表单bean中的查询参数组装成查询的SQL语句 ------------->页面封装成bean------->带着页面bean和查询表单bean去查询信息返回页面 ====================================================================================== ------------------------------DBUtil多表查询--------------------------------------------- select a.* from t_group a left join t_teacher b on a.masterid=b.id where (b.workno=?) ----------------------------------要继续解决的问题------------------------------------------- 为什么要双击才可以？ 功能对应不同的SQL语句还是还能继续封装？ 设置：当页面大小等于总记录数和当前页为最后一页时，下一页的链接不能点击