mybatis关联查询之一对一查询
一对一也就是 A 表的一条记录对应 B 表的一条记录,下面的测试数据中,从employee 表来看,一个员工对应一个部门,是一对一关系,如果从部门角度来看,则是一对多的关系,一个部门对应多个员工,本节主要研究一对一的关系。
数据表建立
新建数据表department,有两个字段,插入两条数据如下:
id | dept_name |
1 | CIA |
2 | FSB |
新建数据表employee,有三个字段,其中dept_id是外键,关联department表的主键id。插入数据如下:
id | last_name | dept_id |
1 | Tom | 1 |
2 | Jerry | 2 |
3 | Neo | 1 |
4 | Cypher | 2 |
新建maven工程,添加依赖,主要是mybatis和mysql
<dependencies> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>3.4.6</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.47</version> </dependency> </dependencies>
3、编写数据库表对应的实体。
对于department表,对应实体如下:
package com.yefengyu.mybatis.entity; public class Department { private Integer id; private String deptName; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getDeptName() { return deptName; } public void setDeptName(String deptName) { this.deptName = deptName; } @Override public String toString() { return "Department{" + "id=" + id + ", deptName='" + deptName + '\'' + '}'; } }
对于employee表,实体如下,注意在Employee实体中把外键直接变成对于Department对象的引用。
package com.yefengyu.mybatis.entity; public class Employee { private Integer id; private String lastName; private Department department; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getLastName() { return lastName; } public void setLastName(String lastName) { this.lastName = lastName; } public Department getDepartment() { return department; } public void setDepartment(Department department) { this.department = department; } @Override public String toString() { return "Employee{" + "id=" + id + ", lastName='" + lastName + '\'' + ", department=" + department + '}'; } }
编写mapper接口
package com.yefengyu.mybatis.mapper; import com.yefengyu.mybatis.entity.Employee; public interface EmployeeMapper { Employee getEmployee(Integer id); }
根据员工ID查询员工信息和部门信息
编写mapper映射文件(本节重点)
一对一查询mapper文件有三种编写方式,分别是:
1、级联属性法
<?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.yefengyu.mybatis.mapper.EmployeeMapper"> <resultMap id="employee" type="com.yefengyu.mybatis.entity.Employee"> <id column="id" property="id"/> <result column="last_name" property="lastName"/> <result column="d_id" property="department.id"/> <result column="dept_name" property="department.deptName"/> </resultMap> <select id="getEmployee" resultMap="employee"> select e.id id, e.last_name last_name, e.dept_id dept_id, d.id d_id,d.dept_name dept_name from employee e, department d where e.id = #{id} and e.dept_id = d.id </select> </mapper>
2、association嵌套结果集
<?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.yefengyu.mybatis.mapper.EmployeeMapper"> <!-- 使用association定义关联的单个对象的封装规则; --> <resultMap id="employee" type="com.yefengyu.mybatis.entity.Employee"> <id column="id" property="id"/> <result column="last_name" property="lastName"/> <!-- association可以指定联合的javaBean对象 property="department":指定哪个属性是联合的对象 javaType:指定这个属性对象的类型[不能省略] --> <association property="department" javaType="com.yefengyu.mybatis.entity.Department"> <id column="d_id" property="id"/> <result column="dept_name" property="deptName"/> </association> </resultMap> <select id="getEmployee" resultMap="employee"> select e.id id, e.last_name last_name, e.dept_id dept_id, d.id d_id,d.dept_name dept_name from employee e, department d where e.id = #{id} and e.dept_id = d.id </select> </mapper>
3、association分步查询法
1)首先需要创建一个关于department的接口
package com.yefengyu.mybatis.mapper; import com.yefengyu.mybatis.entity.Department; public interface DepartmentMapper { public Department getDeptById(Integer id); }
2)其次编写该接口的映射文件
<?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.yefengyu.mybatis.mapper.DepartmentMapper"> <select id="getDeptById
" resultType="com.yefengyu.mybatis.entity.Department"> select id,dept_name from department where id = #{id} </select> </mapper>
3)现在定义一个查询employee的接口(级联属性法和association嵌套结果集测试时已经建立该接口)
package com.yefengyu.mybatis.mapper; import com.yefengyu.mybatis.entity.Employee; public interface EmployeeMapper { Employee getEmployee(Integer id); }
4)编写该接口对应的映射文件
<?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.yefengyu.mybatis.mapper.EmployeeMapper"> <resultMap id="employee" type="com.yefengyu.mybatis.entity.Employee"> <id column="id" property="id"/> <result column="last_name" property="lastName"/> <!-- association定义关联对象的封装规则 select:表明当前属性是调用select指定的方法查出的结果 column:指定将哪一列的值传给这个方法 流程:使用select指定的方法(传入column指定的这列参数的值)查出对象,并封装给property指定的属性 --> <association property="department" select="com.yefengyu.mybatis.mapper.DepartmentMapper.getDeptById" column="dept_id"> </association> </resultMap> <select id="getEmployee" resultMap="employee"> select * from employee where id = #{id} </select> </mapper>
延迟加载:
每次查询Employee对象的时候,都将部门信息一起查询出来。但是想要部门信息在我们使用的时候再去查询,如果不需要就不查询,怎么实现?分步查询的基础上加两个配置:
<!--显示的指定每个我们需要更改的配置的值,即使他是默认的。防止版本更新带来的问题 --> <setting name="lazyLoadingEnabled" value="true"/> <setting name="aggressiveLazyLoading" value="false"/>
Mybatis仅支持association关联对象和collection关联集合对象的延迟加载,association指的就是一对一,collection指的就是一对多查询。
新建一个mybatis全局配置文件,详细信息见官网
<?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> <setting name="mapUnderscoreToCamelCase" value="true"/> <setting name="logImpl" value="STDOUT_LOGGING" /> </settings> <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://127.0.0.1:3306/mybatis?characterEncoding=utf8&allowMultiQueries=true"/> <property name="username" value="root"/> <property name="password" value="123456"/> </dataSource> </environment> </environments> <mappers> <mapper resource="mapper/EmployeeMapper.xml"/> <mapper resource="mapper/DepartmentMapper.xml"/> </mappers> </configuration>
测试
通用测试代码:
public static void main(String[] args) throws IOException { InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream); SqlSession sqlSession = sqlSessionFactory.openSession(); try { EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class); Employee employee = mapper.getEmployee(1); System.out.println(employee); } finally { sqlSession.close(); } }
准对于延迟加载的特殊测试,对于上面测试代码,如果只打印员工名
System.out.println(employee.getLastName());
那么不会查询部门表
Created connection 1776957250. Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@69ea3742] ==> Preparing: select * from employee where id = ? ==> Parameters: 1(Integer) <== Columns: id, last_name, dept_id <== Row: 1, tom, 1 <== Total: 1 tom Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@69ea3742] Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@69ea3742] Returned connection 1776957250 to pool.
如果打印部门相关信息,例如通用测试的代码(由于实体都重写了toString方法,因此打印employee的时候也打印了department,所以也是使用了部门信息),此时的日志为:
Created connection 1776957250. Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@69ea3742] ==> Preparing: select * from employee where id = ? ==> Parameters: 1(Integer) <== Columns: id, last_name, dept_id <== Row: 1, tom, 1 <== Total: 1 ==> Preparing: select id,dept_name from department where id = ? ==> Parameters: 1(Integer) <== Columns: id, dept_name <== Row: 1, A <== Total: 1 Employee{id=1, lastName='tom', department=Department{id=1, deptName='A'}} Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@69ea3742] Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@69ea3742] Returned connection 1776957250 to pool.