mybatis分页

mybatis分页查询

1、分页类定义
    package com.techen.tap.vo;

import java.io.Serializable;
import java.util.Map;

import lombok.Getter;
import lombok.Setter;

/**
 * 分页查询参数
 * @author sunmingzhi
 * @date 2019年11月22日 下午7:23:57  
 * @Modify
 */
@Getter
@Setter
public class PageQuery implements Serializable {
    private static final long serialVersionUID = 1L;
    // 当前页码
    private int currentPage;
    // 每页条数
    private int pageSize;
    // 排序字段
    private String sidx;
    // 顺序
    private String sord;

    /**
     * 构造函数
     * @Title:Query
     * @Description:
     * @param params
     */
    public PageQuery(Map<String, Object> params) {
        this.currentPage = Integer.parseInt(params.get("currentPage").toString());
        this.pageSize = Integer.parseInt(params.get("pageSize").toString());
        this.sidx = params.get("sidx").toString();
        this.sord = params.get("sord").toString();
    }
}

2、分页查询结果
package com.techen.tap.vo;

import java.io.Serializable;
import java.util.List;

import lombok.Data;

/**
 * @description:分页查询结果
 * @author: zs
 * @date: 2019年8月17日上午9:52:48
 * @modify:
 */
@Data
public class Page implements Serializable {
    private static final long serialVersionUID = 1L;
    // 总记录数
    private int totalCount;
    // 每页记录数
    private int pageSize;
    // 总页数
    private int totalPage;
    // 当前页数
    private int currentPage;
    // 列表数据
    private List<?> dataList;

    public Page(List<?> dataList, int totalCount, int pageSize, int currentPage) {
        this.dataList = dataList;
        this.totalCount = totalCount;
        this.pageSize = pageSize;
        this.currentPage = currentPage;
        this.totalPage = (int) Math.ceil((double) totalCount / pageSize);
    }
}


3、分页逻辑
@GetMapping(value = "/meterlist")
public ResponseResult<Object> meterlist(@RequestParam(required = false) Map<String, Object> paraMap) {
    PageQuery query = new PageQuery(paraMap);
    //拼接查询语句---sql语句(拼接前台选择的查询条件)
    /**
    String sqlWhere = this.spellListSql(paraMap);
    sqlWhere += " and c_meter.run_status_code<>'9'";
    **/
    paraMap.put("whereSql", sqlWhere);
    //查询符合条件的总数量
    int totalCount = service.count(paraMap);
    List<Map<String, Object>> tempList = new ArrayList<Map<String, Object>>();
    if (totalCount > 0) {
       //进行分页查询---查询结果是List
       tempList = service.queryMapForPage(sqlWhere, query.getCurrentPage(), query.getPageSize(),                                                                           query.getSidx(),query.getSord());
       service.setDispFields(tempList);
    }
    //组装分页查询到的分页类
    Page page = new Page(tempList, totalCount, query.getPageSize(), query.getCurrentPage());
    //返回分页信息
    return ResponseResult.ok(page);
}

4、分页sql
  <select id="queryMapForPage" resultType="com.techen.tap.base.BasicLowerMap" parameterType="map">
        <![CDATA[
            SELECT * FROM file_upload_details 
            WHERE ${whereSql}
            order by ${sidx} ${sord},id asc 
            <!-- 查询pagesize条语句，从begincount开始 -->
            limit #{pagesize} offset #{begincount}  
        ]]> 
  </select>
    
//注意  
public List<Map<String, Object>> queryMapForPage(String whereSql, int curPageIndex, int pageSize, String                                                                                        sidx, String sord) {
    int beginCount = curPageIndex * pageSize - pageSize;
    Map<String, Object> paraMap = new HashMap<String, Object>();
    paraMap.put("begincount", beginCount);
    paraMap.put("pagesize", pageSize);
    paraMap.put("whereSql", whereSql);
    paraMap.put("sidx", sidx);
    paraMap.put("sord", sord);
    return this.getSqlSessionTemplate().selectList(entityClass.getName() + ".queryMapForPage" , paraMap);
}
  

5、拼接sql
protected String convertSql(Map<String, Object> paraMap) {
        StringBufferProxy sql = new StringBufferProxy();
        sql.appendSingle(" 1=1 ");
        if (paraMap.containsKey("file_name") && !StringUtils.isEmpty(paraMap.get("file_name"))) {
            String value = paraMap.get("file_name").toString();
            if (!StringUtil.isEmpty(value)) {
                sql.appendSingle(" and file_name like '%{0}%' ", value);
            }
        }
        if (paraMap.containsKey("data_date") && !StringUtils.isEmpty(paraMap.get("data_date"))) {
            String value = paraMap.get("data_date").toString();
            String getLastDayOfMonth=DateUtil.getLastDayOfMonth(value);
            String date1=value+" 00:00:00";
            String date2=getLastDayOfMonth+" 00:00:00";
            if (!StringUtil.isEmpty(value)) {
                sql.appendSingle(" and createon between '{0}' and '{1}'", date1, date2);
            }
        }
        if (paraMap.containsKey("createuser") && !StringUtils.isEmpty(paraMap.get("createuser"))) {
            String createuser = paraMap.get("createuser").toString();
            List<CboUser> cboUsers = cboUserService.queryByWhere(" cbo_user.name like '%" + createuser + "%'");
            Long[] idArray = new Long[cboUsers.size()];
            for (int i = 0; i < cboUsers.size(); i++) {
                idArray[i] = cboUsers.get(i).getId();
            }
            String substring = Arrays.toString(idArray);
            substring = substring.substring(1, substring.length() - 1);
            sql.appendSingle(" and createuser  in (" + substring + ")");
        }
        if (paraMap.containsKey("upload_status")) {
            String value = paraMap.get("upload_status").toString();
            if (!StringUtil.isEmpty(value)) {
                sql.appendSingle(" and upload_status = '{0}'", value);
            }
        }
        return sql.toString();
    }