表一对多关联（[尚硅谷]_张晓飞_Mybatis 学习笔记四）

 

CREATE TABLE teacher(t_id INT PRIMARY KEY AUTO_INCREMENT, t_name VARCHAR(20));CREATE TABLE class(c_id INT PRIMARY KEY AUTO_INCREMENT, c_name VARCHAR(20), teacher_id INT);ALTER TABLE class ADD CONSTRAINT fk_teacher_id FOREIGN KEY (teacher_id) REFERENCES teacher(t_id);INSERT INTO teacher(t_name) VALUES('LS1');INSERT INTO teacher(t_name) VALUES('LS2');INSERT INTO class(c_name, teacher_id) VALUES('bj_a', 1);INSERT INTO class(c_name, teacher_id) VALUES('bj_b', 2);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);INSERT INTO student(s_name, class_id) VALUES('xs_B', 1);INSERT INTO student(s_name, class_id) VALUES('xs_C', 1);INSERT INTO student(s_name, class_id) VALUES('xs_D', 2);INSERT INTO student(s_name, class_id) VALUES('xs_E', 2);INSERT INTO student(s_name, class_id) VALUES('xs_F', 2);

 

package com.hb.bean;import java.util.List;public class Classes {private int id;private String name;private Teacher teacher;private List<Student> students;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;}@Overridepublic String toString() {return "Classes [id=" + id + ", name=" + name + ", teacher=" + teacher + ", students=" + students + "]";}}

 

package com.hb.bean;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;}@Overridepublic String toString() {return "Student [id=" + id + ", name=" + name + "]";}}

 

package com.hb.bean;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;}@Overridepublic String toString() {return "Teacher [id=" + id + ", name=" + name + "]";}}

 

<?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.hb.bean.classesMapper"><!-- 这里namespace必须是“包名 + xml文件名”，不然要运行的时候要报错 “is not known to the MapperRegistry” --><!-- 方式一: 嵌套结果: 使用嵌套结果映射来处理重复的联合结果的子集 SELECT * FROM class c, teacher t,student s WHERE c.teacher_id=t.t_id AND c.C_id=s.class_id AND c.c_id=1 --><select id="getClass3" parameterType="int" resultMap="ClassResultMap3">select * from class c, teacher t,student s where c.teacher_id=t.t_id andc.C_id=s.class_id and c.c_id=#{id}</select><resultMap type="com.hb.bean.Classes" id="ClassResultMap3"><id property="id" column="c_id" /><result property="name" column="c_name" /><association property="teacher" column="teacher_id"javaType="com.hb.bean.Teacher"><id property="id" column="t_id" /><result property="name" column="t_name" /></association><!-- ofType指定students集合中的对象类型 --><collection property="students" ofType="com.hb.bean.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="getClass4" parameterType="int" resultMap="ClassResultMap4">select * from class where c_id=#{id}</select><resultMap type="com.hb.bean.Classes" id="ClassResultMap4"><id property="id" column="c_id" /><result property="name" column="c_name" /><association property="teacher" column="teacher_id"javaType="com.hb.bean.Teacher" select="getTeacher2"></association><collection property="students" ofType="com.hb.bean.Student" column="c_id"select="getStudent"></collection></resultMap><select id="getTeacher2" parameterType="int" resultType="com.hb.bean.Teacher">SELECT t_id id, t_name name FROM teacher WHERE t_id=#{id}</select><select id="getStudent" parameterType="int" resultType="com.hb.bean.Student">SELECT s_id id, s_name name FROM student WHERE class_id=#{id}</select></mapper>

 

package com.hb.test3;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;import com.hb.bean.Classes;public class Test3 {public static SqlSessionFactory sqlSessionFactory;public static SqlSession sqlSession;@Beforepublic void init(){//"/"表示是在src根目录下String source = "config.xml";InputStream inputStream = Test3.class.getClassLoader().getResourceAsStream(source);try {sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);//sqlSession = sqlSessionFactory.openSession();//是否自动提交，默认不是自动提交的sqlSession = sqlSessionFactory.openSession(true);} catch (Exception e) {e.printStackTrace();}}@Testpublic void selectById(){String statment = "com.hb.bean.classesMapper.getClass3";Classes classes = sqlSession.selectOne(statment, 1);System.out.println(classes);}@Testpublic void selectById2(){String statment = "com.hb.bean.classesMapper.getClass4";Classes classes = sqlSession.selectOne(statment, 1);System.out.println(classes);}}

 

<?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><settings><!-- changes from the defaults for testing --><setting name="cacheEnabled" value="false" /><setting name="useGeneratedKeys" value="true" /><setting name="defaultExecutorType" value="REUSE" /></settings><environments default="development"><environment id="development"><transactionManager type="jdbc" /><dataSource type="POOLED"><!-- <property name="driver" value="oracle.jdbc.driver.OracleDriver"/> <property name="url" value="jdbc:oracle:thin:@192.168.56.201:1521:system"/> --><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="123456" /></dataSource></environment></environments><mappers><mapper resource="com/hb/bean/classesMapper.xml" /></mappers></configuration>