Mybatis关联查询之一对多和多对一XML配置详解

浅浅的花香味﹌ 2021-09-27 10:02 341阅读 0赞

转载至 : [https://blog.csdn.net/qq\_33561055/article/details/78861131][https_blog.csdn.net_qq_33561055_article_details_78861131]

> 平时在开发过程中dao、bean和XML文件都是自动生成的，很少写XML的配置关系，今天记录一下mybatis的关联查询中的多对一和一对多的情况。

*  首先是有两张表(学生表Student和老师Teacher表)，为了更易懂，这里只设置了最简单的几个必要字段。表结构如下图

> Student表：
> 
> ![20171221105823926][]  
> 
> 
> Teacher表：
> 
> ![20171221105900227][]

*  创建实体bean

> Teacher.java:  
>

>     import java.util.List; 
>           
>           /** 
>           * TODO 
>           * @version 创建时间：2017年12月21日 上午9:02:45 
>           */ 
>           public class Teacher { 
>           
>           private Integer id; 
>           private String name; 
>           private String className; 
>           private List<Student> students; 
>           
>           public List<Student> getStudents() { 
>           return students; 
>           } 
>           
>           public void setStudents(List<Student> students) { 
>           this.students = students; 
>           } 
>           
>           public Integer getId() { 
>           return id; 
>           } 
>           
>           public void setId(Integer id) { 
>           this.id = id; 
>           } 
>           
>           public String getName() { 
>           return name; 
>           } 
>           
>           public void setName(String name) { 
>           this.name = name; 
>           } 
>           
>           public String getClassName() { 
>           return className; 
>           } 
>           
>           public void setClassName(String className) { 
>           this.className = className; 
>           } 
>           
>           }
> 
> Sfudent.java
> 
>     /** 
>           * TODO 
>           * 
>           * @author 作者 E-mail:2332999366@qq.com 
>           * @version 创建时间：2017年12月21日 上午9:01:17 
>           */ 
>           public class Student { 
>           
>           private Integer id; 
>           private String name; 
>           private Integer teacherId; 
>           private String className; 
>           private Teacher teacher; 
>           
>           
>           public Teacher getTeacher() { 
>           return teacher; 
>           } 
>           
>           public void setTeacher(Teacher teacher) { 
>           this.teacher = teacher; 
>           } 
>           
>           public Integer getId() { 
>           return id; 
>           } 
>           
>           public void setId(Integer id) { 
>           this.id = id; 
>           } 
>           
>           public String getName() { 
>           return name; 
>           } 
>           
>           public void setName(String name) { 
>           this.name = name; 
>           } 
>           
>           public Integer getTeacherId() { 
>           return teacherId; 
>           } 
>           
>           public void setTeacherId(Integer teacherId) { 
>           this.teacherId = teacherId; 
>           } 
>           
>           public String getClassName() { 
>           return className; 
>           } 
>           
>           public void setClassName(String className) { 
>           this.className = className; 
>           } 
>           
>           @Override 
>           public String toString() { 
>           return "{id:"+ this.id+ ",name:"+ this.name+ ",className:"+ this.className+ ",teacherId:"+ this.teacherId+ "}"; 
>           } 
>           }

*  下面重点来了：配置Mapper.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.tz.mybatis.dao.studentDao"> 
>           
>            
>           <resultMap type="Teacher" id="teacherMap"> 
>           <id column="id" property="id"/> 
>           <result column="name" property="name"/> 
>           <collection property="students" ofType="Student" column="id"> 
>           <id column="sid" property="id"/>  
>           <result column="sname" property="name"/>  
>           <result column="className" property="className"/> 
>           </collection> 
>           </resultMap> 
>           
>           
>            
>           <select id="getTeachers" parameterType="Teacher" resultMap="teacherMap"> 
>           SELECT 
>           t.id, 
>           t.NAME, 
>           t.class_Name, 
>           s.id AS sid, 
>           s. NAME AS sname, 
>           s.class_name as className 
>           FROM 
>           teacher t 
>           LEFT JOIN student s ON t.id = s.teacher_id 
>           </select> 
>           </mapper>
> 
>   
>

*  测试类：

>   
> 
> 
>     package com.tz.test; 
>           
>           import java.io.IOException; 
>           import java.io.InputStream; 
>           import java.util.List; 
>           import org.apache.ibatis.io.Resources; 
>           import org.apache.ibatis.session.SqlSession; 
>           import org.apache.ibatis.session.SqlSessionFactory; 
>           import org.apache.ibatis.session.SqlSessionFactoryBuilder; 
>           import org.junit.Before; 
>           import org.junit.Test; 
>           import com.tz.mybatis.bean.Student; 
>           import com.tz.mybatis.bean.Teacher; 
>           
>           public class TeacherTest { 
>           
>           private SqlSessionFactory sqlSessionFactory; 
>           
>           @Before 
>           public void init() throws IOException { 
>           String resource = "mybatis-config.xml"; 
>           InputStream inputStream = Resources.getResourceAsStream(resource); 
>           sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); 
>           } 
>           
>           @Test 
>           public void getTeachers() { 
>           SqlSession session = sqlSessionFactory.openSession(); 
>           List<Teacher> list = session.selectList( "com.tz.mybatis.dao.studentDao.getTeachers"); 
>           System.out.println(list); 
>           } 
>           
>           }
> 
> 下面给出第二种写法：
> 
>      
>           <resultMap type="Teacher" id="teacherMaps"> 
>           <id column="id" property="id"/> 
>           <result column="name" property="name"/> 
>           <result column="class_name" property="className"/> 
>           <collection property="students" ofType="Student" select="getStudents" column="id"> 
>           </collection> 
>           </resultMap> 
>           
>           
>            
>           <select id="getAllTeacher" parameterType="Teacher" resultMap="teacherMaps"> 
>           SELECT 
>           t.id, 
>           t.NAME, 
>           t.class_name 
>           FROM 
>           teacher t 
>           </select> 
>           
>           <select id="getStudents" parameterType="int" resultType="Student"> 
>           select 
>           s.id, 
>           s. NAME, 
>           s.class_name as className 
>           from student s 
>           where teacher_id = #{id} 
>           </select>
> 
>   
> 测试类：
> 
>     @Test 
>           public void getTeachers2() { 
>           SqlSession session = sqlSessionFactory.openSession(); 
>           List<Teacher> list = session.selectList( "com.tz.mybatis.dao.studentDao.getAllTeacher"); 
>           System.out.println(list); 
>           }
> 
> 查询学生信息(多对一)：
> 
> 首先还是配置文件：
> 
>     <resultMap type="Student" id="studentMap"> 
>           <id column="id" property="id"/> 
>           <result column="name" property="name"/> 
>           <result column="class_name" property="className"/> 
>           <result column="teacher_id" property="teacherId"/> 
>           <association property="teacher" select="getTeacher" column="teacher_id" javaType="Teacher"> 
>            
>           </association> 
>           </resultMap> 
>           
>           
>           <select id="getStudent" resultMap="studentMap"> 
>           SELECT 
>           s.id, 
>           s.name, 
>           s.class_name, 
>           s.teacher_id 
>           FROM 
>           student s 
>           </select> 
>           
>           <select id="getTeacher" resultType="Teacher" parameterType="int"> 
>           SELECT 
>           t.id, 
>           t.name, 
>           t.class_name as className 
>           FROM teacher t 
>           where id = #{teacher_id} 
>           </select>
> 
>   
> 测试类：
> 
>     @Test 
>           public void getStudents() { 
>           SqlSession session = sqlSessionFactory.openSession(); 
>           List<Student> list = session.selectList( "com.tz.mybatis.dao.studentDao.getStudent"); 
>           System.out.println(list); 
>           }
> 
>   
> 最后：当然如果不想配置这么麻烦的信息，可以直接写一个关联查询的SQL语句，返回结果直接由Map接受即可。不过这样就不太符合面向对象的理念了。  
>

[https_blog.csdn.net_qq_33561055_article_details_78861131]: https://blog.csdn.net/qq_33561055/article/details/78861131
[20171221105823926]: /images/20210923/18c1a534acb847ffbf4bfef245940e3b.png
[20171221105900227]: /images/20210923/013c4d0164924781824822b8d1ae78b2.png