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('teacher1'); INSERT INTO teacher(t_name) VALUES('teacher2'); INSERT INTO class(c_name, teacher_id) VALUES('class_a', 1); INSERT INTO class(c_name, teacher_id) VALUES('class_b', 2);
定义实体类：
1.Teacher类，Teacher类时teacher表对应的实体类。
package com.fpc.Entity; public class Teacher { private int t_id; private String t_name; public int getT_id() { return t_id; } public void setT_id(int t_id) { this.t_id = t_id; } public String getT_name() { return t_name; } public void setT_name(String t_name) { this.t_name = t_name; } @Override public String toString() { return "Teacher [t_id:" + t_id + ", t_name:" + t_name + "]"; } }
2.Classes类，Classes类是class表对应的实体类
package com.fpc.Entity; public class Classes { private int c_id; private String c_name; private Teacher teacher; public int getC_id() { return c_id; } public void setC_id(int c_id) { this.c_id = c_id; } public String getC_name() { return c_name; } public void setC_name(String c_name) { this.c_name = c_name; } public Teacher getTeacher() { return teacher; } public void setTeacher(Teacher teacher) { this.teacher = teacher; } @Override public String toString() { return "Class [t_id:" + c_id + ", t_name:" + c_name + ",teacher:"+ teacher + "]"; } }
3.定义sql映射文件classMapper.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.fpc.Mapping.classMapper"> <resultMap type="com.fpc.Entity.Classes" id="ClassResultMap"> <id property="c_id" column="t_id"/> <id property="c_name" column="c_name"/> <association property="teacher" column="teacher_id" javaType="com.fpc.Entity.Teacher"> <id property="t_id" column="t_id"/> <id property="t_name" column="t_name"/> </association> </resultMap> <select id="getClass" resultMap="ClassResultMap"> select * from class c ,teacher t where c_id=#{id} and c.teacher_id=t.t_id </select> </mapper>
在conf.xm文件中注册classMapper.xml文件：
<mapper resource="com/fpc/Mapping/classMapper.xml"/>
4.编写单元测试代码
String statement1 = "com.fpc.Mapping.classMapper.getClass"; Classes class1 = session.selectOne(statement1,2); System.out.println(class1);
运行结果：
MyBatis一对一关联查询总结
MyBatis中使用association标签来解决一对一的关联查询，association标签可用的属性如下：
- property：对象属性的名称
- javaType：对象属性的类型
- column：所对应的外键字段名称
- select：使用另一个查询封装的结果
一对多关联
根据classId查询对应的班级信息，包括学生，老师
1.创建表和数据：基于上面一对一关联查询演示中，已经创建了班级表和教师表，因此这里只要再创建一张学生表就可以了：
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("student_a",1); INSERT INTO student(s_name,class_id) VALUES("student_b",1); INSERT INTO student(s_name,class_id) VALUES("student_c",2); INSERT INTO student(s_name,class_id) VALUES("student_d",2);
创建的结果：
2.定义实体类Student：
package com.fpc.Entity; public class Student { //定义属性，和student表中的字段对应 private int id; //id ===> s_id; private String name; // name ===> s_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 + "]"; } }
2.修改Classes类，添加一个List<Student>属性，使用一个List<Student>集合属性表示班级拥有的学生，如下：
package com.fpc.Entity; import java.util.List; public class Classes { private int c_id; private String c_name; private Teacher teacher; private List<Student> students; public int getC_id() { return c_id; } public void setC_id(int c_id) { this.c_id = c_id; } public String getC_name() { return c_name; } public void setC_name(String c_name) { this.c_name = c_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; } @Override public String toString() { return "Class [t_id:" + c_id + ", t_name:" + c_name + ",teacher:"+ teacher + ", students = " + students +"]"; } }
3.修改sql映射文件classMapper.xml
添加如下的SQL映射信息：
<?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.fpc.Mapping.classMapper"> <resultMap type="com.fpc.Entity.Classes" id="ClassResultMap"> <id property="c_id" column="t_id"/> <id property="c_name" column="c_name"/> <association property="teacher" column="teacher_id" javaType="com.fpc.Entity.Teacher"> <id property="t_id" column="t_id"/> <id property="t_name" column="t_name"/> </association> <!-- ofType指定students集合中的对象类型 --> <collection property="students" ofType="com.fpc.Entity.Student"> <id property="id" column="s_id"/> <id property="name" column="s_name"/> </collection> </resultMap> <select id="getClass" resultMap="ClassResultMap"> select * from class c ,teacher t ,student s where c_id=#{id} and c.teacher_id=t.t_id and s.class_id=c.c_id </select> </mapper>
4.编写单元测试代码：
String resource = "conf.xml"; // InputStream is = Test1.class.getClassLoader().getResourceAsStream(resource); Reader reader = Resources.getResourceAsReader(resource); SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader); String statement = "com.fpc.Mapping.userMapper.getUser"; SqlSession session = sessionFactory.openSession(); String statement1 = "com.fpc.Mapping.classMapper.getClass"; Classes class1 = session.selectOne(statement1,1); System.out.println(class1);
运行结果：
MyBatis一对多关联查询总结
Mybatis中使用collection标签来解决一对多的关联查询，ofType属性指定集合中元素的对象类型
