mybatis关联查询之一对多查询
一对多,是最常见的一种设计。就是 A 表的一条记录,对应 B 表的多条记录,且 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>
编写数据库表对应的实体。
对于department表,对应实体如下:注意增加一个包含了Employee集合。
package com.yefengyu.mybatis.entity; import java.util.List; public class Department { private Integer id; private String deptName; private List<Employee> employees; 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; } public List<Employee> getEmployees() { return employees; } public void setEmployees(List<Employee> employees) { this.employees = employees; } @Override public String toString() { return "Department{" + "id=" + id + ", deptName='" + deptName + '\'' + ", employees=" + employees + '}'; } }
对于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.Department; public interface DepartmentMapper { public Department getDeptById(Integer id); }
根据部门ID查询部门信息和对应的所有员工信息。
编写mapper映射文件(本节重点)
collection嵌套结果集方法:
<?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"> <resultMap id="dept" type="com.yefengyu.mybatis.entity.Department"> <id column="d_id" property="id"/> <result column="dept_name" property="deptName"/> <!-- collection定义关联集合类型的属性的封装规则 ofType:指定集合里面元素的类型 --> <collection property="employees" ofType="com.yefengyu.mybatis.entity.Employee" javaType="java.util.ArrayList"> <id column="id" property="id"/> <result column="last_name" property="lastName"/> </collection> </resultMap> <select id="getDeptById" resultMap="dept"> select e.id id, e.last_name last_name, e.dept_id dept_id, d.id d_id,d.dept_name dept_name from department d left join employee e on e.dept_id = d.id where d.id = #{id} </select> </mapper>
新建一个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/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 { DepartmentMapper mapper = sqlSession.getMapper(DepartmentMapper.class); Department dept = mapper.getDeptById(1); System.out.println(dept); } finally { sqlSession.close(); } }
结果如下:
Created connection 1938056729. Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@73846619] ==> Preparing: select e.id id, e.last_name last_name, e.dept_id dept_id, d.id d_id,d.dept_name dept_name from department d left join employee e on e.dept_id = d.id where d.id = ? ==> Parameters: 1(Integer) <== Columns: id, last_name, dept_id, d_id, dept_name <== Row: 1, Tom, 1, 1, CIA <== Row: 3, Neo, 1, 1, CIA <== Total: 2 Department{id=1, deptName='CIA', employees=[Employee{id=1, lastName='Tom', department=null}, Employee{id=3, lastName='Neo', department=null}]} Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@73846619] Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@73846619]
上面查询虽然可以查询出数据,但是数据过多则会有性能问题,因此好的做法是分步查询。
分步查询
1、新增查询员工的接口,特别注意是根据部门id来查询
package com.yefengyu.mybatis.mapper; import com.yefengyu.mybatis.entity.Employee; public interface EmployeeMapper { Employee getEmpByDeptId(Integer deptId); }
2、编写对应的mapper映射文件
<?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"> <select id="getEmployee" resultType="com.yefengyu.mybatis.entity.Employee"> select * from employee where dept_id = #{id} </select> </mapper>
3、编写查询部门的接口
package com.yefengyu.mybatis.mapper; import com.yefengyu.mybatis.entity.Department; public interface DepartmentMapper { public Department getDeptById(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.DepartmentMapper"> <resultMap id="dept" type="com.yefengyu.mybatis.entity.Department"> <id column="id" property="id"/> <result column="dept_name" property="deptName"/> <!-- 扩展:多列的值传递过去: 将多列的值封装map传递:column="{key1=column1,key2=column2}" fetchType="lazy":表示使用延迟加载; - lazy:延迟 - eager:立即 --> <collection property="employees" select="com.yefengyu.mybatis.mapper.EmployeeMapper.getEmployee" column="id" fetchType="lazy"> </collection> </resultMap> <select id="getDeptById" resultMap="dept"> select id ,dept_name from department where id = #{id} </select> </mapper>
5、测试结果
Created connection 1694556038. Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@6500df86] ==> Preparing: select id ,dept_name from department where id = ? ==> Parameters: 1(Integer) <== Columns: id, dept_name <== Row: 1, CIA <== Total: 1 ==> Preparing: select * from employee where dept_id = ? ==> Parameters: 1(Integer) <== Columns: id, last_name, dept_id <== Row: 1, Tom, 1 <== Row: 3, Neo, 1 <== Total: 2 Department{id=1, deptName='CIA', employees=[Employee{id=1, lastName='Tom', department=null}, Employee{id=3, lastName='Neo', department=null}]} Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@6500df86] Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@6500df86]
如果测试代码的打印改为:
System.out.println(dept.getDeptName());
那么结果如下,不会查询员工信息。
Created connection 1694556038. Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@6500df86] ==> Preparing: select id ,dept_name from department where id = ? ==> Parameters: 1(Integer) <== Columns: id, dept_name <== Row: 1, CIA <== Total: 1 CIA Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@6500df86] Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@6500df86] Returned connection 1694556038 to pool.
全局配置与局部配置
1、全局配置
lazyLoadingEnabled:延迟加载的全局开关。当开启时,所有关联对象都会延迟加载。 特定关联关系中可通过设置 fetchType 属性来覆盖该项的开关状态。
默认值:false
aggressiveLazyLoading:当开启时,任何方法的调用都会加载该对象的所有属性。 否则,每个属性会按需加载(参考 lazyLoadTriggerMethods)。
默认值:false (在 3.4.1 及之前的版本默认值为 true),现在新版本可以不用关注此设置。
<setting name="lazyLoadingEnabled" value="true"/> <setting name="aggressiveLazyLoading" value="false"/>
2、局部配置fetchType
<association property="" fetchType="eager"></association> <collection property="" fetchType="lazy"></collection>
- lazy:延迟
- eager:立即
3、区别(查询部门信息,不查看员工信息时)
全局 | 局部 | 是否延迟 |
不开启 | 不开启 | 否 |
不开启 | lazy | 是 |
不开启 | eager | 否 |
开启 | 不开启 | 是 |
开启 | lazy | 是 |
开启 | eager | 否 |