SQL_CALC_FOUND_ROWS的使用

需求

   经常会有这么一种情况，让你根据条件分页查询学生的信息，最后还要总条数，

   基本操作是两条SQL：

   (1)select * from student where age  = 18 limit 10,10 ;

   (2) select count(*) from student where age  = 18

现在通过一条SQL足矣

低配版本

代码语言：javascript复制

select  * from student WHERE id < 1000 LIMIT 10,10 ;
select  count(id) from student WHERE id < 1000 ;

高配版本

代码语言：javascript复制

select SQL_CALC_FOUND_ROWS 
    *
from student 
WHERE id < 1000
LIMIT 10,10;
SELECT FOUND_ROWS() as total_count;

MyBatis中的使用

代码语言：javascript复制

<resultMap id="BaseResultMap" type="com.Student">
        <id column="id" jdbcType="BIGINT" property="id"/>
        <result column="name" jdbcType="VARCHAR" property="name"/> 
    </resultMap>

    <resultMap id="ExtCountResultMap" type="Integer">
        <result column="total_count" jdbcType="INTEGER" javaType="Integer"/>
    </resultMap>


    <select id="getStudentInfo2" resultMap="BaseResultMap,ExtCountResultMap">
        select
        SQL_CALC_FOUND_ROWS
        * from student where id in
        <foreach collection="ids" item="id" index="i" open="(" close=")" separator=",">
            #{id}
        </foreach>
        <if test="limit != null">
            <if test="offset != null">
                limit ${offset}, ${limit}
            </if>
        </if>
        ;SELECT FOUND_ROWS() as total_count;


    </select>

代码语言：javascript复制

public interface TestExtDao {


  public List<?> getStudentInfo2(
      @Param("ids") List<Integer> ids,
      @Param("offset") Integer offset,
      @Param("limit") Integer limit);
}

代码语言：javascript复制

List<?> result = testExtDao.getStudentInfo2(ids, offset, limit);
    List<Student> extResults = (List<Student>) result.get(0);
    Integer count = ((List<Integer>) result.get(1)).get(0);

sql limit rows select 分页

0 人点赞