[Java知识库]mybatis配合pageHelper一对多查询分页问题处理

使用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
                }
            ]
        }
    ]
}