重温MyBatis（四、Mapper的关联映射之三：多对多关联映射）

1、AdminInfoMapper.xml的文件：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd"
        >

<mapper namespace="com.springmvc.mapper.AdminInfoMapper">

    <!--    根据用户编号查询信息-使用子查询-->
    <select id="findAdminInfoBySfid" parameterType="int" resultType="AdminInfo">
        select * from admin_info ai where id in (
            select aiid from connect_ai_sf where sfid = #{id}
        )
    </select>

    <!--    根据编号查询信息-->
    <select id="findAdminInfoById" parameterType="int" resultMap="AdminInfoMap">
        select * from admin_info where id = #{id}
    </select>
    <resultMap id="AdminInfoMap" type="AdminInfo">
        <id property="id" column="id"/>
        <result property="name" column="name"/>

        <!--    多对多关联-->
        <collection property="systemFuncsList"
                    ofType="SystemFuncs"
                    column="id"
                    select="com.springmvc.mapper.SystemFuncsMapper.findSystemFuncsByAiid"
        ></collection>
    </resultMap>




</mapper>

2、SystemFuncsMapper.xml的文件：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd"
        >

<mapper namespace="com.springmvc.mapper.SystemFuncsMapper">

    <!--    根据用户编号查询信息-->
<!--    <select id="findSystemFuncsById" parameterType="int" resultType="SystemFuncs">-->
<!--        select * from system_funcs sf where id = #{id}-->
<!--    </select>-->

    <!--    根据用户编号查询信息-使用子查询-->
    <select id="findSystemFuncsByAiid" parameterType="int" resultType="SystemFuncs">
        select * from system_funcs sf where id in (
            select sfid from connect_ai_sf where aiid = #{id}
        )
    </select>


    <!--    根据编号查询信息-->
    <select id="findSystemFuncsById" parameterType="int" resultMap="SystemFuncsMap">
        select * from system_funcs where id = #{id}
    </select>
    <resultMap id="SystemFuncsMap" type="SystemFuncs">
        <id property="id" column="id"/>
        <result property="name" column="name"/>

        <!--    多对多关联-->
        <collection property="adminInfoList"
                    ofType="AdminInfo"
                    column="id"
                    select="com.springmvc.mapper.AdminInfoMapper.findAdminInfoBySfid"
        ></collection>
    </resultMap>


</mapper>