SQL_CALC_FOUND_ROWS的使用

需求

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

   基本操作是两条SQL：

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

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

现在通过一条SQL足矣

低配版本

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

高配版本

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

MyBatis中的使用

<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">selectSQL_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>

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

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