一对多关联关系

一对多关联关系

 

<?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.hous.day5.classMapper">
	<!--
	方式一: 嵌套结果: 使用嵌套结果映射来处理重复的联合结果的子集
	SELECT * FROM class,student WHERE class.c_id=student.class_id AND class.c_id=#{id}
	SELECT * FROM class,student,teacher WHERE class.c_id=student.class_id AND class.teacher_id=teacher.t_id  AND class.c_id=1
	注意：映射关系要按照顺序，和javabean的属性对应
	-->
	<select id="getClass" parameterType="int" resultMap="classResultMap">
		SELECT * FROM class,student,teacher WHERE class.c_id=student.class_id 
			AND class.teacher_id=teacher.t_id  AND class.c_id=#{id}
	</select>
	<resultMap type="com.hous.day5.Classes" id="classResultMap">
		<id property="id" column="c_id"/>
		<result property="name" column="c_name"/>
		<association property="teacher" javaType="com.hous.day5.Teacher">
			<id property="id" column="t_id"/>
			<result property="name" column="t_name"/>
		</association>
		<collection property="students" ofType="com.hous.day5.Student">
			<id property="id" column="s_id"/>
			<result property="name" column="s_name"/>
		</collection>
	</resultMap>
	
	<!--
	方式二：嵌套查询：通过执行另外一个 SQL 映射语句来返回预期的复杂类型
	SELECT * FROM class WHERE c_id=1;
	SELECT * FROM teacher WHERE t_id=1 //1 是上一个查询得到的 teacher_id 的值
	SELECT * FROM student WHERE class_id=1 //1 是第一个查询得到的 c_id 字段的值
	-->
	<select id="getClass2" parameterType="int" resultMap="classResultMap2">
		SELECT * FROM class WHERE c_id=#{id}
	</select>
	<select id="getTeacher" parameterType="int" resultType="com.hous.day5.Teacher">
		SELECT t_id id, t_name name FROM teacher WHERE t_id=#{id}
	</select>
	<select id="getStudents" parameterType="int" resultType="com.hous.day5.Student">
		SELECT s_id id, s_name name FROM student WHERE class_id=1
	</select>
	<resultMap type="com.hous.day5.Classes" id="classResultMap2">
		<id property="id" column="c_id"/>
		<result property="name" column="c_name"/>
		<association property="teacher" column="teacher_id" select="getTeacher">
			<id property="id" column="t_id"/>
			<result property="name" column="t_name"/>
		</association>
		<collection property="students" column="c_id" select="getStudents">
			<id property="id" column="s_id"/>
			<result property="name" column="s_name"/>
		</collection>
	</resultMap>
	
</mapper>

 

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
	
	<environments default="development">
		<environment id="development">
			<transactionManager type="JDBC"/>
			<dataSource type="POOLED">
				<property name="driver" value="com.mysql.jdbc.Driver"/>
				<property name="url" value="jdbc:mysql://localhost:3306/mybatis"/>
				<property name="username" value="root" />
				<property name="password" value="root" />
			</dataSource>
		</environment>
	</environments>
	
	<mappers>
		<mapper resource="com/hous/day5/classMapper.xml" />
	</mappers>
</configuration>

 

package com.hous.day5;

import static org.junit.Assert.*;

import java.io.InputStream;

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;

public class MyTest {
	
	private SqlSessionFactory factory = null;
	
	@Before
	public void setUp() throws Exception {
		String resource = "day5/config.xml";
		InputStream config = MyTest.class.getClassLoader().getResourceAsStream(resource);
		factory = new SqlSessionFactoryBuilder().build(config);
	}

	@Test
	public void testGetClass() {
		SqlSession session = factory.openSession();
		String statement = "com.hous.day5.classMapper.getClass2";
		Classes classes = session.selectOne(statement, 1);
		session.commit();
		session.close();
		System.out.println("查询结果：" + classes);
	}

}

 

package com.hous.day5;

import java.util.List;

public class Classes {
	private int id;
	private String name;
	private Teacher teacher;
	private List<Student> students;

	public Classes() {
		super();
	}

	public Classes(int id, String name, Teacher teacher) {
		super();
		this.id = id;
		this.name = name;
		this.teacher = teacher;
	}

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public Teacher getTeacher() {
		return teacher;
	}

	public void setTeacher(Teacher teacher) {
		this.teacher = teacher;
	}
	
	public List<Student> getStudents() {
		return students;
	}

	public void setStudents(List<Student> students) {
		this.students = students;
	}

	/* (non-Javadoc)
	 * @see java.lang.Object#toString()
	 */
	@Override
	public String toString() {
		return "Classes [id=" + id + ", name=" + name + ", teacher=" + teacher + ", students=" + students + "]";
	}

}

 

package com.hous.day5;

public class Student {
	private int id;
	private String name;

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	@Override
	public String toString() {
		return "Student [id=" + id + ", name=" + name + "]";
	}

}

 

package com.hous.day5;

public class Teacher {
	private int id;
	private String name;

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	@Override
	public String toString() {
		return "Teacher [id=" + id + ", name=" + name + "]";
	}

}

 

create table student(
	s_id int primary key auto_increment,
	s_name varchar(20),
	class_id int
);
insert into student(s_name, class_id) values('xs_A',1),('xs_B',1),('xs_C',1),('xs_D',2),('xs_E',2),('xs_F',2);