Mybatis学习-关联查询（部门和员工多对一的关系查询collection）

以下是有关collection多对一的使用（以部门和员工为例）

部门的实体类

private Integer id;
//部门编号
private String departmentName;	//部门名称
private List<Employee> employees;//部门和员工是多对一的关系
..

员工的实体类

private Integer id;
// 不能被修改
private String lastname;
private String email;
// 从前端传入的是 String 类型, 所以需要注意转换
private Date birth;
// 不能被修改
private Date createtime;
//添加头像
private String emp_image;
// 单向 n-1 的关联关系
private Department department;
..

DepartmentMapper.xml文件 嵌套结果集的形式

<!--//根据di查询出部门信息
Department getDeptByidPlus(Integer id);
-->
<resultMap type="com.ldp.mybatis.bean.Department" id="dept_emp">
<!-- 定义主键 -->
<id column="id" property="id"/>
<!-- 普通字段 -->
<result column="departmentName" property="departmentName"/>
<!-- 下面是关联查询
collection：多对一使用该标签
property="employees"在部门pojo中属性名名称
ofType:代表集合中的对象
-->
<collection property="employees" ofType="com.ldp.mybatis.bean.Employee">
<!-- 主键 -->
<id column="eid" property="id"/>
<!-- 普通字段 -->
<result column="lastname" property="lastname"/>
<result column="email" property="email"/>
<result column="birth" property="birth"/>
<result column="createtime" property="createtime"/>
<result column="emp_image" property="emp_image"/>
<!-- 查询出来的是 department_id 所以属性中的也需要department.id点一下-->
<result column="department_id" property="department.id"/>
</collection>
</resultMap>
<select id="getDeptByidPlus" parameterType="int" resultMap="dept_emp">
SELECT
d.`id`,
d.`departmentName`,
e.`id` eid,
e.`birth`,
e.`createtime`,
e.`department_id`,
e.`email`,
e.`emp_image`,
e.`lastname`
FROM
`department` d LEFT JOIN employee e
ON d.`id`=e.`department_id`
WHERE d.`id`=#{id};
</select>

/**
* 测试多对一
* @throws IOException
*/

@Test
public void test01() throws IOException {
//首先获得SqlSessionFactory对象
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
//获得sqlSession
SqlSession sqlSession = sqlSessionFactory.openSession(true);
try {
DepartmentMapper departmentMapper = sqlSession.getMapper(DepartmentMapper.class);
//进行查询
Department department = departmentMapper.getDeptByidPlus(1);
System.out.println(department);
List<Employee> list=department.getEmployees();
for (Employee employee : list) {
System.out.println(employee);
}
} finally {
sqlSession.close();
}
}

测试的结果

DEBUG 04-15 19:38:54,444 ==>
Preparing: SELECT d.`id`, d.`departmentName`, e.`id` eid, e.`birth`, e.`createtime`, e.`department_id`, e.`email`, e.`emp_image`, e.`lastname` FROM `department` d LEFT JOIN employee e ON d.`id`=e.`department_id` WHERE d.`id`=?;
(BaseJdbcLogger.java:145)
DEBUG 04-15 19:38:54,542 ==> Parameters: 1(Integer)
(BaseJdbcLogger.java:145)
DEBUG 04-15 19:38:54,592 <==
Total: 4
(BaseJdbcLogger.java:145)
Department [id=1, departmentName=行政部]
Employee [id=7, lastname=刘东平7, email=1846882398@qq.com, birth=Wed Mar 27 00:00:00 CST 2019, createtime=Wed Mar 27 20:01:59 CST 2019, emp_image=C:/upload/14/2/f0312f6e638248058eaebae3f4d8952f.jpg, department=Department [id=1, departmentName=null]]
Employee [id=201, lastname=杨峰, email=110@qq.com, birth=Mon Apr 15 11:44:26 CST 2019, createtime=Mon Apr 15 11:34:26 CST 2019, emp_image=null, department=Department [id=1, departmentName=null]]
Employee [id=202, lastname=杨峰, email=110@qq.com, birth=Mon Apr 15 14:40:48 CST 2019, createtime=Mon Apr 15 14:40:48 CST 2019, emp_image=null, department=Department [id=1, departmentName=null]]
Employee [id=203, lastname=杨峰, email=110@qq.com, birth=Mon Apr 15 14:42:17 CST 2019, createtime=Mon Apr 15 14:42:17 CST 2019, emp_image=null, department=Department [id=1, departmentName=null]]

利用collection分步查询

com.ldp.mybatis.dao.EmployeeMapper 下面定义一个根据部门id查询员工信息的配置信息

<select id="getBydeptId" parameterType="int" resultType="com.ldp.mybatis.bean.Employee">
select * from employee where department_id=#{department_id};
</select>

在com.ldp.mybatis.dao.DepartmentMapper.xml 下面的定义信息

<!-- //collection进分步查询出数据
Department getDeptBySept(Integer id); -->
<resultMap type="com.ldp.mybatis.bean.Department" id="dept_emp_sept">
<!-- 定义主键 -->
<id column="id" property="id"/>
<!-- 普通字段 -->
<result column="departmentName" property="departmentName"/>
<!-- 下面是关联查询
collection：多对一使用该标签
property="employees"在部门pojo中属性名名称
ofType:代表集合中的对象
-->
<collection property="employees" select="com.ldp.mybatis.dao.EmployeeMapper.getBydeptId" column="id"></collection>
</resultMap>
<!-- 定义的查询方法 -->
<select id="getDeptBySept" parameterType="int" resultMap="dept_emp_sept">
select * from department where id=#{id}
</select>

/**
* 测试多对一(利用分步查询的方式)
* @throws IOException
*/

@Test
public void test02() throws IOException {
//首先获得SqlSessionFactory对象
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
//获得sqlSession
SqlSession sqlSession = sqlSessionFactory.openSession(true);
try {
DepartmentMapper departmentMapper = sqlSession.getMapper(DepartmentMapper.class);
//进行查询
Department department = departmentMapper.getDeptBySept(1);
System.out.println(department);
List<Employee> list=department.getEmployees();
for (Employee employee : list) {
System.out.println(employee);
}
} finally {
sqlSession.close();
}
}

查询结果:会有两条SQL语句

DEBUG 04-15 20:02:35,029 ==>
Preparing: select * from department where id=?
(BaseJdbcLogger.java:145)
DEBUG 04-15 20:02:35,119 ==> Parameters: 1(Integer)
(BaseJdbcLogger.java:145)
DEBUG 04-15 20:02:35,441 <==
Total: 1
(BaseJdbcLogger.java:145)
DEBUG 04-15 20:02:35,444 ==>
Preparing: select * from employee where department_id=?;
(BaseJdbcLogger.java:145)
DEBUG 04-15 20:02:35,446 ==> Parameters: 1(Integer)
(BaseJdbcLogger.java:145)
DEBUG 04-15 20:02:35,456 <==
Total: 4
(BaseJdbcLogger.java:145)
Department [id=1, departmentName=行政部]
Employee [id=7, lastname=刘东平7, email=1846882398@qq.com, birth=Wed Mar 27 00:00:00 CST 2019, createtime=Wed Mar 27 20:01:59 CST 2019, emp_image=C:/upload/14/2/f0312f6e638248058eaebae3f4d8952f.jpg, department=null]
Employee [id=201, lastname=杨峰, email=110@qq.com, birth=Mon Apr 15 11:44:26 CST 2019, createtime=Mon Apr 15 11:34:26 CST 2019, emp_image=null, department=null]
Employee [id=202, lastname=杨峰, email=110@qq.com, birth=Mon Apr 15 14:40:48 CST 2019, createtime=Mon Apr 15 14:40:48 CST 2019, emp_image=null, department=null]
Employee [id=203, lastname=杨峰, email=110@qq.com, birth=Mon Apr 15 14:42:17 CST 2019, createtime=Mon Apr 15 14:42:17 CST 2019, emp_image=null, department=null]

collection也有缓存加载（也称按需加载）
前提条件需要在配置文件中配置以下信息

<settings>
<setting name="mapUnderscoreToCamelCase" value="true"/>
<setting name="jdbcTypeForNull" value="NULL"/>
<!--显示的指定每个我们需要更改的配置的值，即使他是默认的。防止版本更新带来的问题
-->
<setting name="lazyLoadingEnabled" value="true"/>
<setting name="aggressiveLazyLoading" value="false"/>
</settings>

将测试代码修改为

DepartmentMapper departmentMapper = sqlSession.getMapper(DepartmentMapper.class);
//进行查询
Department department = departmentMapper.getDeptBySept(1);
System.out.println(department.getDepartmentName());

查询的结果只有一条SQL语句

DEBUG 04-15 20:08:02,050 ==>
Preparing: select * from department where id=?
(BaseJdbcLogger.java:145)
DEBUG 04-15 20:08:02,168 ==> Parameters: 1(Integer)
(BaseJdbcLogger.java:145)
DEBUG 04-15 20:08:02,414 <==
Total: 1
(BaseJdbcLogger.java:145)
行政部

扩展知识

1.在进行分步查询的时候：如果column需要传输多列的值的情况如何处理？
将多列值封装成map传输过去

column{key1=“id”,key=“lasename”}

2.配置文件之后默认是进行延迟加载的，那么是否可以手动修改懒加载状态？

fetchType=“lazy” 表示使用延迟加载
lazy:表示延迟加载
eager：表示即使加载

最后

以上就是故意水杯为你收集整理的Mybatis学习-关联查询（部门和员工多对一的关系查询collection）的全部内容，希望文章能够帮你解决Mybatis学习-关联查询（部门和员工多对一的关系查询collection）所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。