Mybatis多表联合查询（多对多）

た入场券 2024-04-17 21:53 75阅读 0赞

# 1、多对多查询 #

#### 1 准备工作 ####

（1）数据表准备：以老师表和学生表举例（多对多关系需要创建一个中间关系表）

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDc4NDY3Mw_size_16_color_FFFFFF_t_70]  
![在这里插入图片描述][20190901202721656.png]  
![在这里插入图片描述][20190901202733374.png]  
（2）实体类准备  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDc4NDY3Mw_size_16_color_FFFFFF_t_70 1]  
![在这里插入图片描述][2019090120284046.png]

#### 2 额外查询 ####

这里使用额外查询和关联查询都可以。  
可以通过学生表查询老师表，也可以通过老师表查询学生；这里以学生表查询老师表为例。  
（1）在学生表对应的mapper映射文件中，写入代码：

<resultMap id="map1" type="com.ujy.many2one.bean.Student">
            <id property="id" column="s_id"/>
            <result property="name" column="s_name"/>
            <result property="birth" column="s_birth"/>
            <result property="sex" column="s_sex"/>
            <collection property="tea" column="s_id" ofType="Teacher"
                        select="com.ujy.many2one.mapper.ITeaMapper.select1"/>
        </resultMap>
        <select id="select1" resultMap="map1">
            select * from student
            <where>
                <if test="id != null">
                    and s_id = #{id}
                </if>
                <if test="name != null">
                    and s_name = #{name}
                </if>
            </where>
        </select>

（2）在老师表对应的mapper映射文件中，写入代码：

<resultMap id="map1" type="com.ujy.many2one.bean.Teacher">
            <id property="id" column="t_id"/>
            <result property="name" column="t_name"/>
        </resultMap>
        
        <select id="select1" resultMap="map1">
            SELECT * from teacher t,stu_tea st where t.t_id = st.t_id and st.s_id=#{id}
        </select>

（3）测试：

/*多对多查询*/
        @Test
        public void select(){
            StuServiceImp stuServiceImp = new StuServiceImp();
            //查询学号为4的学生，有几位老师
            Student student = new Student(4,null,null,null,null);
            //Student student = new Student();
            List<Student> list = stuServiceImp.select1(student);
            for(Student i:list){
                List<Teacher> li = i.getTea();
                for(Teacher j:li){
                    System.out.println(i.getId()+" "+i.getName()+" "+j.getId()+""+j.getName());
                }
            }
        }

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDc4NDY3Mw_size_16_color_FFFFFF_t_70]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/d22292fb1e694297b0e9cde07f3f76bb.png
[20190901202721656.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/affccb984445456e9c66ee52a246bad9.png
[20190901202733374.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/ab10fbaeecf943b2ad8f6b5da8bee048.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDc4NDY3Mw_size_16_color_FFFFFF_t_70 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/3913721ed0204ab987aea3cdde735757.png
[2019090120284046.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/8def4b2e510044ddac7b5625886e6235.png