Mybatis关联查询一对多和多对一

清疚 2021-09-16 10:22 412阅读 0赞

*  **一对多的第一种写法**

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

Student表：

![20171221105823926][]

Teacher表：

![20171221105900227][]

*  创建实体bean

> Teacher.java:

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

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;
    	} 
    
    }

*  下面重点来了：配置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>

*  测试类：
 *      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接受即可。不过这样就不太符合面向对象的理念了。

[20171221105823926]: /images/20210725/7a85455ce9c6495ba2031d1061554fe1.png
[20171221105900227]: /images/20210725/56c8f88ea75241afa97cffced9c67446.png