需求
经常会有这么一种情况，让你根据条件分页查询学生的信息，最后还要总条数，
基本操作是两条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"> 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>
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);
