mybatis 查询分页如何一次查询同时获得总条数

 1、最终返回值类型

public class ListEntity<T> {
    private int count;
    private List<T> list;

    public ListEntity() {
    }

    public ListEntity(Integer count) {
        this.count = count;
    }

    public ListEntity(List<T> list) {
        this.count = list != null ? list.size() : 0;
        this.list = list;
    }

    public ListEntity(int count, List<T> list) {
        this.count = count;
        this.list = list;
    }

    public int getCount() {
        return count;
    }

    public void setCount(int count) {
        this.count = count;
    }

    public List<T> getList() {
        return list;
    }

    public void setList(List<T> list) {
        this.list = list;
    }
}

 

2、参数实体

public class DataRuleRequest extends PageParam {


    /**
     * 条件name
     */
    private String name;

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}


public class PageParam {

    /**
     * 页码
     */
    private Integer pageNumber = 1;

    /**
     * 每页条数
     */
    private Integer pageSize = 10;

    public Integer getPageSize() {
        return pageSize;
    }

    public void setPageSize(Integer pageSize) {
        this.pageSize = pageSize;
    }

    public Integer getPageNumber() {
        return pageNumber;
    }

    public void setPageNumber(Integer pageNumber) {
        this.pageNumber = pageNumber;
    }

    public Integer getPageStartIndex() {
        return (this.pageNumber - 1) * this.pageSize;
    }
}

 

3、mybatis 查询接口

public interface DataRuleReportDaoMapper {
  
    ListEntity selectByName(@Param("request") DataRuleRequest dataRuleRequest,@Param("duCode") List<String> duCode);
}

4、mybatis 查询SQL

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.xx.xxx.dao.DataRuleReportDaoMapper" >
  <resultMap id="BaseResultMap" type="com.xx.xx.model.ListEntity" >
    <constructor >
      <idArg column="cnt" jdbcType="INTEGER" javaType="java.lang.Integer" />
    </constructor>
    <collection property="list" ofType="com.xx.xx.model.map.DataRuleReportDao" javaType="ArrayList">
      <constructor >
        <idArg column="drr_id" jdbcType="BIGINT" javaType="java.lang.Long" />
        <arg column="drr_reportname" jdbcType="VARCHAR" javaType="java.lang.String" />
        <arg column="drr_code" jdbcType="VARCHAR" javaType="java.lang.String" />
        <arg column="drr_verifyid" jdbcType="VARCHAR" javaType="java.lang.String" />
        <arg column="drr_creator" jdbcType="VARCHAR" javaType="java.lang.String" />
        <arg column="create_time" jdbcType="VARCHAR" javaType="java.lang.String" />
        <arg column="update_time" jdbcType="VARCHAR" javaType="java.lang.String" />
        <arg column="drr_type" jdbcType="INTEGER" javaType="java.lang.Integer"  />
      </constructor>
    </collection>
  </resultMap>

  <resultMap type="java.lang.Long" id="queryListCount" autoMapping="true"/>
  <sql id="Base_Column_List" >
    drr_id, drr_reportname, drr_charts,drr_colors, drr_conditions, drr_isvalid, drr_redis_time,
    drr_code, drr_verifyid, drr_creator, create_time, update_time,drr_type
  </sql>
    <sql id="filterParam">
        <where>
            drr_isvalid = 1
            <if test="request.name != null">
                and drr_reportname like concat('%',#{request.name,jdbcType=VARCHAR},'%')
            </if>
          <if test="duCode != null and duCode.size>0">
            and drr_code in
            <foreach collection="duCode" item="item" open="(" separator="," close=")">
              #{item,jdbcType=VARCHAR}
            </foreach>
          </if>
        </where>
    </sql>

  <select id="selectByName" resultMap="BaseResultMap">
    select cnt,
    <include refid="Base_Column_List" />
    from datarule_report,(SELECT count(*) as cnt from datarule_report <include refid="filterParam"/>) a
    <include refid="filterParam"/>
    order by drr_id desc ,drr_id limit  #{request.pageStartIndex,jdbcType=INTEGER},#{request.pageSize,jdbcType=INTEGER}

  </select>

</mapper>

返回值结果