MyBatis ORM映射 处理关联关系-多表连接
Author：Exchanges
Version：9.0.2
目录
一、ORM映射【重点】
1.1 MyBatis自动ORM失效
MyBatis只能自动维护库表”列名“与”属性名“相同时的一一对应关系，二者不同时，无法自动ORM。
| 自动ORM失效 |
|---|
 |
1.2 方案一：列的别名
在SQL中使用 as 为查询字段添加列别名，以匹配属性名。
<mapper namespace="com.qf.mybatis.part2.orm.ManagerDao">
<select id="selectManagerByIdAndPwd" resultType="com.qf.mybatis.part2.orm.Manager">
SELECT mgr_id AS id , mgr_name AS username , mgr_pwd AS password
FROM t_managers
WHERE mgr_id = #{id} AND mgr_pwd = #{pwd}
</select>
</mapper>
1.3 方案二：结果映射（ResultMap - 查询结果的封装规则）
通过< resultMap id="" type="" >映射，匹配列名与属性名。
<mapper namespace="com.qf.mybatis.part2.orm.ManagerDao">
<!--定义resultMap标签-->
<resultMap id="managerResultMap" type="com.qf.mybatis.part2.orm.Manager">
<!--关联主键与列名-->
<id property="id" column="mgr_id" />
<!--关联属性与列名-->
<result property="username" column="mgr_name" />
<result property="password" column="mgr_pwd" />
</resultMap>
<!--使用resultMap作为ORM映射依据-->
<select id="selectAllManagers" resultMap="managerResultMap">
SELECT mgr_id , mgr_name , mgr_pwd
FROM t_managers
</select>
</mapper>
二、MyBatis处理关联关系-多表连接【重点】
实体间的关系：关联关系（拥有 has、属于 belong）
OneToOne：一对一关系（Passenger --- Passport）：一个旅客只有一个护照，一个护照只对应一个旅客
OneToMany：一对多关系（Department --- Employee）：一个部门有多个员工，一个员工只属于一个部门
ManyToMany：多对多关系（Student --- Subject）：一个学生学习多门课程，一个课程被多个学生学习
| Table建立外键关系 |
|---|
 |
| Entity添加关系属性 |
|---|
 |
| Mapper中将属性与列名对应 |
|---|
 |
2.1 OneToOne
1.导入依赖，拷贝之前的db.properties，log4j.properties，mybatis-config.xml以及Druid连接池配置类等...并稍作适当修改
<dependencies>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.9</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.29</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.2.9</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.24</version>
</dependency>
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13.2</version>
<scope>test</scope>
</dependency>
</dependencies>
2.创建旅客表以及护照表
-- 旅客表
CREATE TABLE t_passenger(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50),
sex VARCHAR(1),
birthday DATE
)DEFAULT CHARSET =utf8;
INSERT INTO t_passenger VALUES(1,'zhansan','f','2020-11-11');
INSERT INTO t_passenger VALUES(2,'lucy','m','2020-12-12');
-- 护照表
CREATE TABLE t_passport(
id INT PRIMARY KEY AUTO_INCREMENT,
nationality VARCHAR(50),
expire DATE,
passenger_id INT UNIQUE
)DEFAULT CHARSET =utf8;
INSERT INTO t_passport VALUES(10001,'China','2030-11-11',1);
INSERT INTO t_passport VALUES(10002,'America','2030-12-12',2);
3.创建对应的实体类
package com.qf.pojo;
import lombok.Data;
import java.util.Date;
//旅客表
@Data
public class Passenger {
private Integer id;
private String name;
private String sex;
private Date birthday;
private Passport passport;//存储对应的护照信息，关系属性
}
---------------------------------------------------------
package com.qf.pojo;
import lombok.Data;
import java.util.Date;
//护照表
@Data
public class Passport {
private Integer id;
private String nationality;
private Date expire;
}
4.创建Dao
package com.qf.dao;
import com.qf.pojo.Passenger;
public interface PassengerDao {
//通过旅客id查询旅客信息以及护照信息（关联查询）
public Passenger findById(Integer id);
}
5.创建PassengerDao.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.qf.dao.PassengerDao">
<resultMap id="passengerMap" type="Passenger">
<id column="id" property="id"></id>
<result column="name" property="name"></result>
<result column="sex" property="sex"></result>
<result column="birthday" property="birthday"></result>
<!-- 描述 passportId nationality expire 和 passport 映射规则 -->
<association property="passport" javaType="Passport">
<id column="passportId" property="id"></id>
<result column="nationality" property="nationality"></result>
<result column="expire" property="expire"/>
</association>
</resultMap>
<!-- 查询旅客及其护照，由于两个表都有名为id的列，需要在查询时区分，否则相同列名的值会被覆盖 -->
<select id="findById" resultMap="passengerMap">
SELECT p1.id,p1.name,p1.sex,p1.birthday,
p2.id passportId,p2.nationality,p2.expire
FROM t_passenger p1 INNER JOIN t_passport p2
ON p1.id = p2.passenger_id
WHERE p1.id=#{id};
</select>
</mapper>
6.测试
package com.qf.demo;
import com.qf.dao.PassengerDao;
import com.qf.pojo.Passenger;
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.Test;
import java.io.InputStream;
public class MybatisTest {
@Test
public void testFindById()throws Exception{
//准备环境
InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
//-------------------------------------------------------------
PassengerDao passengerDao = sqlSession.getMapper(PassengerDao.class);
Passenger passenger = passengerDao.findById(1);
System.out.println(passenger);
//-------------------------------------------------------------
sqlSession.close();
inputStream.close();
}
}
2.2 OneToMany
1.创建表
CREATE TABLE t_department(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(50),
location VARCHAR(100)
)DEFAULT CHARSET =utf8;
INSERT INTO t_department VALUES(1,"教学部","北京"),(2,"研发部","上海");
CREATE TABLE t_employee(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(50),
salary DOUBLE,
dept_id INT
)DEFAULT CHARSET =utf8;
INSERT INTO t_employee VALUES(1,"jack",1000.5,1);
INSERT INTO t_employee VALUES(2,"rose",2000.5,1);
INSERT INTO t_employee VALUES(3,"张三",3000.5,2);
INSERT INTO t_employee VALUES(4,"李四",4000.5,2);
2.创建实体类
package com.qf.pojo;
import lombok.Data;
@Data
public class Employee {
private Integer id;
private String name;
private Double salary;
// 员工从属的部门信息
private Department department;
}
-----------------------------------------
package com.qf.pojo;
import lombok.Data;
import java.util.List;
@Data
public class Department {
private Integer id;
private String name;
private String location;
// 存储部门中所有员工信息
private List<Employee> employees;
}
3.创建Dao
package com.qf.dao;
import com.qf.pojo.Employee;
import org.apache.ibatis.annotations.Param;
public interface EmployeeDao {
// 查询员工信息 并且 查到对应的部门信息
Employee findEmployeeById(@Param("id") Integer id);
}
---------------------------------------------------------
package com.qf.dao;
import com.qf.pojo.Department;
import org.apache.ibatis.annotations.Param;
public interface DepartmentDao {
// 查询部门，及其所有员工信息
Department findDepartmentById(@Param("id") Integer id);
}
4.创建EmployeeDao.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.qf.dao.EmployeeDao">
<resultMap id="empMap" type="Employee">
<id column="id" property="id"></id>
<result column="name" property="name"></result>
<result column="salary" property="salary"></result>
<association property="department" javaType="Department">
<id column="deptId" property="id"></id>
<result column="deptName" property="name"></result>
<result column="location" property="location"></result>
</association>
</resultMap>
<select id="findEmployeeById" resultMap="empMap">
select e.id,e.name,e.salary,
d.id deptId ,d.name deptName,d.location
from t_employee e join t_department d
on d.id = e.dept_id
where e.id=#{id}
</select>
</mapper>
5.创建DepartmentDao.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.qf.dao.DepartmentDao">
<resultMap id="deptMap" type="Department">
<id column="id" property="id"></id>
<result column="name" property="name"></result>
<result column="location" property="location"></result>
<collection property="employees" ofType="Employee">
<id column="emp_id" property="id"></id>
<result column="emp_name" property="name"></result>
<result column="salary" property="salary"></result>
</collection>
</resultMap>
<select id="findDepartmentById" resultMap="deptMap">
select d.id ,d.name,d.location,
e.id emp_id,e.name emp_name,e.salary
from t_department d join t_employee e
on d.id = e.dept_id
where d.id=#{id}
</select>
</mapper>
6.测试
package com.qf.demo;
import com.qf.dao.DepartmentDao;
import com.qf.dao.EmployeeDao;
import com.qf.pojo.Department;
import com.qf.pojo.Employee;
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.Test;
import java.io.InputStream;
public class MybatisTest {
@Test
public void findEmployeeById()throws Exception{
//准备环境
InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
//-------------------------------------------------------------
EmployeeDao employeeDao = sqlSession.getMapper(EmployeeDao.class);
Employee employee = employeeDao.findEmployeeById(1);
System.out.println(employee);
//-------------------------------------------------------------
sqlSession.close();
inputStream.close();
}
@Test
public void findDeptById()throws Exception{
//准备环境
InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
//-------------------------------------------------------------
DepartmentDao departmentDao = sqlSession.getMapper(DepartmentDao.class);
Department department = departmentDao.findDepartmentById(1);
System.out.println(department);
//-------------------------------------------------------------
sqlSession.close();
inputStream.close();
}
}
2.3 ManyToMany
| 建立第三张关系表 |
|---|
 |
1.创建表
CREATE TABLE t_student(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(50),
sex VARCHAR(1)
)DEFAULT CHARSET = utf8;
INSERT INTO t_student VALUES(1,'jack','m');
INSERT INTO t_student VALUES(2,'rose','f');
CREATE TABLE t_subject(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(50),
grade INT
)DEFAULT CHARSET = utf8;
INSERT INTO t_subject VALUES(1001,'JavaSE',1);
INSERT INTO t_subject VALUES(1002,'JavaEE',2);
CREATE TABLE t_stu_sub(
student_id INT,
subject_id INT
)DEFAULT CHARSET = utf8;
INSERT INTO t_stu_sub VALUES(1,1001);
INSERT INTO t_stu_sub VALUES(1,1002);
INSERT INTO t_stu_sub VALUES(2,1001);
INSERT INTO t_stu_sub VALUES(2,1002);
2.创建实体类
package com.qf.pojo;
import lombok.Data;
import java.util.List;
@Data
public class Student {
private Integer id;
private String name;
private String sex;
private List<Subject> subjects;
}
-----------------------------------------
package com.qf.pojo;
import lombok.Data;
import java.util.List;
@Data
public class Subject {
private Integer id;
private String name;
private Integer grade;
private List<Student> students;
}
3.创建Dao
package com.qf.dao;
public interface StudentDao {
}
---------------------------------------------
package com.qf.dao;
import com.qf.pojo.Subject;
import org.apache.ibatis.annotations.Param;
public interface SubjectDao {
public Subject findSubjectById(@Param("id") Integer id);
}
4.创建StudentDao.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.qf.dao.StudentDao">
</mapper>
5.创建SubjectDao.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.qf.dao.SubjectDao">
<resultMap id="subjectMap" type="Subject">
<id column="id" property="id"></id>
<result column="name" property="name"></result>
<result column="grade" property="grade"></result>
<collection property="students" ofType="Student">
<id column="stu_id" property="id"></id>
<result column="stu_name" property="name"></result>
<result column="sex" property="sex"></result>
</collection>
</resultMap>
<select id="findSubjectById" resultMap="subjectMap">
SELECT t_subject.id,t_subject.name,t_subject.grade,
t_student.id stu_id,t_student.name stu_name,t_student.sex
FROM t_subject JOIN t_stu_sub
ON t_subject.id = t_stu_sub.subject_id
JOIN t_student
ON t_stu_sub.student_id = t_student.id
WHERE t_subject.id=#{id}
</select>
</mapper>
6.测试
package com.qf.demo;
import com.qf.dao.SubjectDao;
import com.qf.pojo.Subject;
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.Test;
import java.io.InputStream;
public class MybatisTest {
@Test
public void findById()throws Exception{
//准备环境
InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
//-------------------------------------------------------------
SubjectDao subjectDao= sqlSession.getMapper(SubjectDao.class);
Subject subject = subjectDao.findSubjectById(1001);
System.out.println(subject);
//-------------------------------------------------------------
sqlSession.close();
inputStream.close();
}
}
2.4 关系总结
一的一方，添加集合；多的一方，添加对象。（主要指一对多关系中）
双方均可建立关系属性，建立关系属性后，对应的Mapper文件中需使用< ResultMap >完成多表映射。
持有对象关系属性，使用< association property="dept" javaType="department" >
持有集合关系属性，使用< collection property="emps" ofType="employee" >
