Mybatis学习笔记-一对一，一对多，多对多关联查询

我会带着你远行 2022-10-22 06:28 206阅读 0赞

## 一、一对一关联查询 ##

**1、数据库数据**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk4MTMxNQ_size_16_color_FFFFFF_t_70]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk4MTMxNQ_size_16_color_FFFFFF_t_70 1]

**2、实体类**

public class Teacher {
        
        String tid;
        String tname;
        Class classes;
    
        public String getTid() {
        
            return tid;
        }
    
        public void setTid(String tid) {
        
            this.tid = tid;
        }
    
        public String getTname() {
        
            return tname;
        }
    
        public void setTname(String tname) {
        
            this.tname = tname;
        }
    
        public Class getClasses() {
        
            return classes;
        }
    
        @Override
        public String toString() {
        
            return "Teacher{" +
                    "tid='" + tid + '\'' +
                    ", tname='" + tname + '\'' +
                    ", classes=" + classes +
                    '}';
        }
    
        public void setClasses(Class classes) {
        
            this.classes = classes;
        }
    }

public class Class {
        
        String cid;
        String cname;
        int StudentNum;
    
        public String getCid() {
        
            return cid;
        }
    
        public void setCid(String cid) {
        
            this.cid = cid;
        }
    
        public String getCname() {
        
            return cname;
        }
    
        public void setCname(String cname) {
        
            this.cname = cname;
        }
    
        public int getStudentNum() {
        
            return StudentNum;
        }
    
        public void setStudentNum(int studentNum) {
        
            StudentNum = studentNum;
        }
    
        @Override
        public String toString() {
        
            return "Class{" +
                    "cid='" + cid + '\'' +
                    ", cname='" + cname + '\'' +
                    ", StudentNum=" + StudentNum +
                    '}';
        }
    }

**3、Dao接口相应方法：** 根据班级查询教师信息

public List<Teacher> selectClass(String cid);

**4、xml文件select语句**

<resultMap id="TeacherToClass" type="com.atguigu.mybatis.Entry.Teacher">
            <id column="tid" property="tid"></id>
            <result column="tname" property="tname"></result>
            <association property="classes" javaType="com.atguigu.mybatis.Entry.Class">
                <id property="cid" column="cid"></id>
                <result property="cname" column="cname"></result>
                <result property="studentNum" column="studentNum"></result>
            </association>
        </resultMap>
    
        <select id="selectClass" resultMap="TeacherToClass">
            select tid,tname,class.cid,cname,studentNum
            from teacher,class
            where teacher.cid=class.cid and class.cid=#{cid}
        </select>

**5、测试语句**

@Test
        public void selectClass(){
        
            SqlSession sqlSession= MybatisUtil.getSqlSession();
            List<Teacher> teacher=sqlSession.selectList("com.atguigu.mybatis.Dao.TeacherDao.selectClass","1");
            System.out.println(teacher);
        }

## 二、一对多关联查询 ##

public class Student2 {
        
        private int id;
        private String name;
        private int tid;
    }

public class Teacher2 {
        
        private int id;
        private String name;
        private List<Student2> students;
    }

<select id="getTeacher2" resultMap="teacherStu2">
            select * from teacher where id = #{id}
        </select>
        <resultMap id="teacherStu2" type="com.me.domain.Teacher2" >
            <result property="id" column="id"/>
            <result property="name" column="name"/>
            <collection property="students" javaType="ArrayList" ofType="com.me.domain.Student2" select="getStudentByTid" column="id"/>
        </resultMap>
    
    
        <select id="getStudentByTid" resultType="com.me.domain.Student2">
            select * from student where tid = #{id}
        </select>

public void getTeacher2(){
        
            SqlSession sqlSession = MyBatisUtils.getSqlSession();
            TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
            Teacher2 teacher = mapper.getTeacher2(1);
            System.out.println(teacher.toString());
            sqlSession.close();
        }

\*\*

## 三、多对多关联查询 ##

**1、数据库**  
![在这里插入图片描述][20210404193011909.png]  
![在这里插入图片描述][20210404193019757.png]  
![在这里插入图片描述][20210404193027738.png]  
**2、实体类**

import java.util.List;
    public class Classes {
        
    	private Integer cid;
    	private String cname;
    	private List<Teacher> teachers;
    	public Integer getCid() {
        
    		return cid;
    	}
    	public void setCid(Integer cid) {
        
    		this.cid = cid;
    	}
    	public String getCname() {
        
    		return cname;
    	}
    	public void setCname(String cname) {
        
    		this.cname = cname;
    	}
    	public List<Teacher> getTeachers() {
        
    		return teachers;
    	}
    	public void setTeachers(List<Teacher> teachers) {
        
    		this.teachers = teachers;
    	}
    	@Override
    	public String toString() {
        
    		return "Classes [cid=" + cid + ", cname=" + cname + ", teachers=" + teachers + "]";
    	}
    }

import java.util.List;
    public class Teacher {
        
    	private Integer tid;
    	private String tname;
    	private List<Classes> classes;
    	public Integer getTid() {
        
    		return tid;
    	}
    	public void setTid(Integer tid) {
        
    		this.tid = tid;
    	}
    	public String getTname() {
        
    		return tname;
    	}
    	public void setTname(String tname) {
        
    		this.tname = tname;
    	}
    	public List<Classes> getClasses() {
        
    		return classes;
    	}
    	public void setClasses(List<Classes> classes) {
        
    		this.classes = classes;
    	}
    	@Override
    	public String toString() {
        
    		return "Teacher [tid=" + tid + ", tname=" + tname + "]";
    	}
    }

**3、xml文件映射**

<resultMap type="com.po.Classes" id="ClassesWithTeacherResult">
    		<id property="cid" column="cid"/>
    		<result property="cname" column="cname"/>
    		
    		<collection property="teachers" ofType="com.po.Teacher">
    			<id property="tid" column="tid"/>
    			<result property="tname" column="tname"/>
    		</collection>
    	</resultMap>
    	<select id="findClassesWithTeacher" parameterType="Integer" resultMap="ClassesWithTeacherResult">
    		select * from t_classes c,t_teacher t,t_classessTeacher ct
    		where ct.cid=c.cid
    		and ct.tid=t.tid
    		and c.cid=#{cid}
    	</select>

**多对多关联查询参考链接：** https://blog.csdn.net/dwenxue/article/details/82108178

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk4MTMxNQ_size_16_color_FFFFFF_t_70]: /images/20221022/9de8417f11fc43539b8f55e5cc954694.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk4MTMxNQ_size_16_color_FFFFFF_t_70 1]: /images/20221022/57ddaff1237d41d095cd78590e19b6fa.png
[20210404193011909.png]: /images/20221022/5427bf8fa3734ec6b8b43c66bc11991b.png
[20210404193019757.png]: /images/20221022/90432fdf3b194bb7b9bc8d865b03c929.png
[20210404193027738.png]: /images/20221022/f29bcd4e79d240958d941b1761861915.png