带查询条件的分页实例

来源：互联网发布：snmp trap 端口编辑：程序博客网时间：2024/05/19 11:19

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)" onclick="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语句还是还能继续封装？
设置：当页面大小等于总记录数和当前页为最后一页时，下一页的链接不能点击

0 0