使用mybatis结合pageHelper实现分页查询，但查询多表的时候数据就会比真实的要少，如下面程序。
需求是：tab_question是题目表，tab_option是选项表，查询一道题目下面4个选项，一页有5个题目。
//controller层
@RestController
@RequestMapping("/question")
public class QuestionController extends BaseController {
@Autowired
private QuestionService questionService;
@GetMapping
public PageInfo findAll(){
PageHelper.startPage(1, 5);
List<Question> questions = questionService.findAll();
PageInfo pageInfo = new PageInfo(questions);
return pageInfo;
}
}
mapper配置
<resultMap id="questionMap" type="question">
<id column="qid" property="qid"/>
<result column="question" property="question"/>
<result column="qtype" property="qtype"/>
<collection property="options" ofType="option" >
<id column="oid" property="oid"/>
<result column="optionDec" property="optionDec"/>
<result column="qid" property="qid"/>
</collection>
</resultMap>
<select id="findAll" resultMap="questionMap">
select * from tab_question q left join tab_option o on q.qid = o.qid
</select>
查询结果，一页只有一个题目，而且第二个题目还不全
{
"total": 36,
"list": [
{
"qid": 1,
"question": "这个问题有几个字",
"qtype": 1,
"options": [
{
"oid": 1,
"optionDec": "1",
"qid": 1
},
{
"oid": 2,
"optionDec": "2",
"qid": 1
},
{
"oid": 3,
"optionDec": "3",
"qid": 1
},
{
"oid": 4,
"optionDec": "8",
"qid": 1
}
]
},
{
"qid": 2,
"question": "大明王朝1566有哪些角色",
"qtype": 2,
"options": [
{
"oid": 5,
"optionDec": "嘉靖",
"qid": 2
}
]
}
]
}
原因是：此时的分页是多表查询后得到的记录再进行分页（相当于按照选项进行分页），但实际要查询的是按照题目进行分页。
解决办法：使用子查询
修改mapper配置，在resultMap中嵌套子查询
<!--此处省略了id,result标签-->
<resultMap id="questionMap" type="question">
<collection property="options" ofType="option" column="qid" select="findOptionById"/>
</resultMap>
<!--主查询-->
<select id="findAll" resultMap="questionMap">
select * from tab_question
</select>
<!--子查询-->
<select id="findOptionById" parameterType="int" resultType="option">
select * from tab_option where qid = #{qid}
</select>
这次结果就对了
{
"total": 9,
"list": [
{
"qid": 0,
"question": "这个问题有几个字",
"qtype": 1,
"options": [
{
"oid": 1,
"optionDec": "1",
"qid": 1
},
{
"oid": 2,
"optionDec": "2",
"qid": 1
},
{
"oid": 3,
"optionDec": "3",
"qid": 1
},
{
"oid": 4,
"optionDec": "8",
"qid": 1
}
]
},
{
"qid": 0,
"question": "大明王朝1566有哪些角色",
"qtype": 2,
"options": [
{
"oid": 5,
"optionDec": "嘉靖",
"qid": 2
},
{
"oid": 6,
"optionDec": "海瑞",
"qid": 2
},
{
"oid": 7,
"optionDec": "张居正",
"qid": 2
},
{
"oid": 8,
"optionDec": "徐阶",
"qid": 2
}
]
},
{
"qid": 0,
"question": "海钢锋叫什么",
"qtype": 1,
"options": [
{
"oid": 10,
"optionDec": "海瑞",
"qid": 26
},
{
"oid": 11,
"optionDec": "海笔架",
"qid": 26
},
{
"oid": 12,
"optionDec": "润莲",
"qid": 26
},
{
"oid": 13,
"optionDec": "白圭",
"qid": 26
}
]
},
{
"qid": 0,
"question": "海钢锋叫什么",
"qtype": 1,
"options": [
{
"oid": 14,
"optionDec": "海瑞",
"qid": 28
},
{
"oid": 15,
"optionDec": "海笔架",
"qid": 28
},
{
"oid": 16,
"optionDec": "润莲",
"qid": 28
},
{
"oid": 17,
"optionDec": "白圭",
"qid": 28
}
]
},
{
"qid": 0,
"question": "海钢锋叫什么",
"qtype": 1,
"options": [
{
"oid": 18,
"optionDec": "海瑞",
"qid": 29
},
{
"oid": 19,
"optionDec": "海笔架",
"qid": 29
},
{
"oid": 20,
"optionDec": "润莲",
"qid": 29
},
{
"oid": 21,
"optionDec": "白圭",
"qid": 29
}
]
}
]
}