-
问题
由于嵌套结果方式会导致结果集被折叠,因此分页查询的结果在折叠后总数会减少
<resultMap id="baseResultMap" type="com.jia.Student"> <id column="id" property="id"/> <result column="name" property="name"/> <result column="gender" property="gender"/> <collection property="bookList" ofType="com.jia.Book"> <result column="book_id" property="id"/> <result column="student_id" property="studentId"/> <result column="amount" property="amount"/> </collection> </resultMap> <select id="getStudent" resultMap="baseResultMap"> SELECT Student.id, Student.`name`, Student.gender, book.id book_id, book.student_id, book.amount FROM student left join book on book.student_id=student.id WHERE student.is_delete=0 limit #{pageNumber} ,#{pageSize} </select>
-
解决方法
使用collection里的select定义查询,条件是上层student的id
执行过程是,先执行getStudent的查询,将学生全都查询出来并进行分页,这种情况下分页是没问题的,然后再去查询每个学生对应的多条书本信息,走collection里的select查询,使用的条件是第一次查询结果中的主键id,然后他会自己查询book信息封装到每个对应的student中,完成查询。
<resultMap id="baseResultMap" type="com.jia.Student"> <id column="id" property="id"/> <result column="name" property="name"/> <result column="gender" property="gender"/> <collection property="bookList" ofType="com.jia.Book" select="selectBook" column="id"> <result column="book_id" property="id"/> <result column="student_id" property="studentId"/> <result column="amount" property="amount"/> </collection> </resultMap> <select id="getStudent" resultMap="baseResultMap"> SELECT Student.id, Student.`name`, Student.gender FROM student WHERE student.is_delete=0 limit #{pageNumber} ,#{pageSize} </select> <select id="selectBook" resultType="com.jia.Book"> select * from book where book.student_id=#{id} </select>