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

最新推荐文章于 2023-06-15 20:03:33 发布

明德致远

最新推荐文章于 2023-06-15 20:03:33 发布

阅读量1.2k

点赞数

分类专栏： mybatis学习文章标签： Mybatis学习-关联查询（员工和部门一对一的关系查询associa

本文链接：https://blog.csdn.net/weixin_41865602/article/details/89318622

版权

mybatis学习专栏收录该内容

5 篇文章 0 订阅

订阅专栏

mybatis-config.xml文件

<?xml version="1.0" encoding="UTF-8" ?>

<!-- 
	2、settings包含很多重要的设置项
	setting:用来设置每一个设置项
		name：设置项名
		value：设置项取值
 -->
 
<settings>
	<!-- <setting name="mapUnderscoreToCamelCase" value="true"/> -->
	<setting name="jdbcTypeForNull" value="NULL"/>
	
	<!--显示的指定每个我们需要更改的配置的值，即使他是默认的。防止版本更新带来的问题  -->
	<setting name="lazyLoadingEnabled" value="true"/>
	<setting name="aggressiveLazyLoading" value="false"/>
</settings>


<!-- 3、typeAliases：别名处理器：可以为我们的java类型起别名 
		别名不区分大小写
-->
<typeAliases>
	<!-- 1、typeAlias:为某个java类型起别名
			type:指定要起别名的类型全类名;默认别名就是类名小写；employee
			alias:指定新的别名
	 -->
	<!-- <typeAlias type="com.atguigu.mybatis.bean.Employee" alias="emp"/> -->
	
	<!-- 2、package:为某个包下的所有类批量起别名 
			name：指定包名（为当前包以及下面所有的后代包的每一个类都起一个默认别名（类名小写），）
	-->
	<package name="com.ldp.mybatis.bean"/>
	
	<!-- 3、批量起别名的情况下，使用@Alias注解为某个类型指定新的别名 -->
</typeAliases>
	
<!-- 
	4、environments：环境们，mybatis可以配置多种环境 ,default指定使用某种环境。可以达到快速切换环境。
		environment：配置一个具体的环境信息；必须有两个标签；id代表当前环境的唯一标识
			transactionManager：事务管理器；
				type：事务管理器的类型;JDBC(JdbcTransactionFactory)|MANAGED(ManagedTransactionFactory)
					自定义事务管理器：实现TransactionFactory接口.type指定为全类名
			
			dataSource：数据源;
				type:数据源类型;UNPOOLED(UnpooledDataSourceFactory)
							|POOLED(PooledDataSourceFactory)
							|JNDI(JndiDataSourceFactory)
				自定义数据源：实现DataSourceFactory接口，type是全类名
	 -->
	 
<environments default="dev_mysql">
	<environment id="dev_mysql">
		<transactionManager type="JDBC"></transactionManager>
		<dataSource type="POOLED">
			<property name="driver" value="${jdbc.driver}" />
			<property name="url" value="${jdbc.url}" />
			<property name="username" value="${jdbc.username}" />
			<property name="password" value="${jdbc.password}" />
		</dataSource>
	</environment>
	
	<environment id="dev_oracle">
		<transactionManager type="JDBC" />
		<dataSource type="POOLED">
			<property name="driver" value="${orcl.driver}" />
			<property name="url" value="${orcl.url}" />
			<property name="username" value="${orcl.username}" />
			<property name="password" value="${orcl.password}" />
		</dataSource>
	</environment>
</environments>


<!-- 5、databaseIdProvider：支持多数据库厂商的；
	 type="DB_VENDOR"：VendorDatabaseIdProvider
	 	作用就是得到数据库厂商的标识(驱动getDatabaseProductName())，mybatis就能根据数据库厂商标识来执行不同的sql;
	 	MySQL，Oracle，SQL Server,xxxx
  -->
<databaseIdProvider type="DB_VENDOR">
	<!-- 为不同的数据库厂商起别名 -->
	<property name="MySQL" value="mysql"/>
	<property name="Oracle" value="oracle"/>
	<property name="SQL Server" value="sqlserver"/>
</databaseIdProvider>


<!-- 将我们写好的sql映射文件（EmployeeMapper.xml）一定要注册到全局配置文件（mybatis-config.xml）中 -->
<!-- 6、mappers：将sql映射注册到全局配置中 -->
<mappers>
	<!-- 
		mapper:注册一个sql映射 
			注册配置文件
			resource：引用类路径下的sql映射文件
				mybatis/mapper/EmployeeMapper.xml
			url：引用网路路径或者磁盘路径下的sql映射文件
				file:///var/mappers/AuthorMapper.xml
			注册接口
			class：引用（注册）接口，
				1、有sql映射文件，映射文件名必须和接口同名，并且放在与接口同一目录下；
				2、没有sql映射文件，所有的sql都是利用注解写在接口上;
				推荐：
					比较重要的，复杂的Dao接口我们来写sql映射文件
					不重要，简单的Dao接口为了开发快速可以使用注解；
	-->
	<!-- <mapper resource="mybatis/mapper/EmployeeMapper.xml"/> -->
	<!-- <mapper class="com.atguigu.mybatis.dao.EmployeeMapperAnnotation"/> -->
	<!-- 批量注册： -->
	<package name="com.ldp.mybatis.dao"/>
</mappers>

员工实体类Employee package com.ldp.mybatis.bean;

import java.util.Date;

public class Employee {

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;

public Employee() {
}

public Employee(Integer id, String lastname, String email, Date birth, Date createtime, String emp_image,
		Department department) {
	super();
	this.id = id;
	this.lastname = lastname;
	this.email = email;
	this.birth = birth;
	this.createtime = createtime;
	this.emp_image = emp_image;
	this.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 String getEmail() {
	return email;
}

public void setEmail(String email) {
	this.email = email;
}

public Date getBirth() {
	return birth;
}

public void setBirth(Date birth) {
	this.birth = birth;
}

public Date getCreatetime() {
	return createtime;
}

public void setCreatetime(Date createtime) {
	this.createtime = createtime;
}

public String getEmp_image() {
	return emp_image;
}

public void setEmp_image(String emp_image) {
	this.emp_image = emp_image;
}

public Department getDepartment() {
	return department;
}

public void setDepartment(Department department) {
	this.department = department;
}

@Override
public String toString() {
	return "Employee [id=" + id + ", lastname=" + lastname + ", email=" + email + ", birth=" + birth
			+ ", createtime=" + createtime + ", emp_image=" + emp_image + ", department=" + department + "]";
}

}

部门的实体类department
package com.ldp.mybatis.bean;

public class Department {

private Integer id;				//部门编号	
private String departmentName;	//部门名称

public Integer getId() {
	return id;
}

public void setId(Integer id) {
	this.id = id;
}

public String getDepartmentName() {
	return departmentName;
}

public void setDepartmentName(String departmentName) {
	this.departmentName = departmentName;
}

@Override
public String toString() {
	return "Department [id=" + id + ", departmentName=" + departmentName
			+ "]";
}

}
员工和部门是一对一的关联关系：
从resultmap引入开始，一步一步的联合查询

<resultMap type="com.ldp.mybatis.bean.Employee" id="MyEmp">
<!-- 设置主键  
	column：对应的是数据库中的列
	property:对应的是对象中的属性
private Integer id;
// 不能被修改
private String lastname;
private String email;
// 从前端传入的是 String 类型, 所以需要注意转换
private Date birth;
// 不能被修改
private Date createtime;
//添加头像
private String emp_image;
// 单向 n-1 的关联关系
private Integer department_id;
-->
	<id column="id" 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"/>
	<result column="department_id" property="department.id"/>
</resultMap>

<select id="reEmployee" parameterType="int" resultMap="MyEmp">
	select * from employee where id=#{id}
</select>

<!-- 查询员工信息的时候的查询部门信息
	联合查询有关记录
 -->
<resultMap type="com.ldp.mybatis.bean.Employee" id="MyEmp1">
<!-- 设置主键字段 -->
	<id column="id" property="id"/>
	<!-- 普通字段 -->
	<result column="lastname" property="lastname"/>
	<result column="email" property="email"/>
	<result column="department_id" property="department.id"/>
	<result column="departmentName" property="department.departmentName"/>
</resultMap>
<!--//联合查询指定相关字段返回
Employee getMyemp2(Integer id); -->
<select id="getMyemp2" parameterType="int" resultMap="MyEmp1">
	SELECT 
	  e.`id`,
	  e.`lastname`,
	  e.`email`,
	  e.`birth`,
	  e.`department_id`,
	  d.`departmentName`
	FROM
	  `employee` e LEFT JOIN `department` d
	  ON e.`department_id`=d.`id` WHERE e.`id`=1
</select>

<!-- 一对一的查询 -->
<resultMap type="com.ldp.mybatis.bean.Employee" id="emp_dept">
	<!-- 主键 -->
	<id column="id" 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"/>
	<result column="department_id" property="department.id"/>
	<!-- 
		association的用于一对一查询
		javaType：属性类别
	 -->
	<association  property="department" javaType="com.ldp.mybatis.bean.Department">
		<!-- id：主键声明 表示 -->
		<id column="department_id" property="id"></id>
		<result column="departmentName" property="departmentName"/>
	</association>
</resultMap>
<!-- 
//查询出员工对应的部门信息
List<Employee> getEmpAndDept();
 -->
 <select id="getEmpAndDept" resultMap="emp_dept">
 	SELECT 
	  e.`id`,
	  e.`lastname`,
	  e.`email`,
	  e.`birth`,
	  e.`createtime`,
	  e.`emp_image`,
	  e.`department_id`,
	  d.`departmentName`
	FROM
	   `employee` e LEFT JOIN `department` d
	   ON e.`department_id`=d.`id` 
 </select>
 
<!--  	//分步查询数据
Employee getEmpAndDeptSept(Integer id); -->
<resultMap type="com.ldp.mybatis.bean.Employee" id="emp_dept_sept">
	<!-- 主键 -->
	<id column="id" 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"/>
	<result column="department_id" property="department.id"/>
	<!-- association进行分步查询 
		department关联的属性
		select：表明当前属性是select指定的方法查出的结果
		column:传输的参数
		
		association还可以使用缓存加载（懒加载）：
		显示的指定每个我们需要更改的配置的值，即使他是默认的。防止版本更新带来的问题 	
		<setting name="lazyLoadingEnabled" value="true"/>
		<setting name="aggressiveLazyLoading" value="false"/>
	-->
	<association property="department" select="com.ldp.mybatis.dao.DepartmentMapper.getDeptByid" 
	column="department_id">
	</association>
	
</resultMap>
<select id="getEmpAndDeptSept" parameterType="int" resultMap="emp_dept_sept">
	select * from employee where id=#{id}
</select>

departmentMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>

<!-- //根据di查询出部门信息
Department getDeptByid(Integer id); -->
<select id="getDeptByid" parameterType="int" resultType="department">
	select * from department where id=#{id}	
</select>

测试代码缓存加载的作用： association还可以使用缓存加载（懒加载）

	前提配置：显示的指定每个我们需要更改的配置的值，即使他是默认的。防止版本更新带来的问题 	
	<setting name="lazyLoadingEnabled" value="true"/>
	<setting name="aggressiveLazyLoading" value="false"/>
	
@Test
	public void testResultMap() throws IOException {
		//获得sql
		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
		SqlSession sqlSession = sqlSessionFactory.openSession(true);
		//获取接口代理类
		try {
			EmployeeMapper employeeMapper = sqlSession.getMapper(EmployeeMapper.class);
			/*Employee reEmployee = employeeMapper.reEmployee(1);
			System.out.println(reEmployee);*/
			//分步查询
			Employee empAndDeptSept = employeeMapper.getEmpAndDeptSept(1);
			//测试懒加载，不需要查询部门信息，那么就不会进行查询
			System.out.println(empAndDeptSept.getLastname());
		} finally {
			sqlSession.close();
		}
	}

测试结果

DEBUG 04-15 18:57:03,450 ==> Preparing: select * from employee where id=? (BaseJdbcLogger.java:145)
DEBUG 04-15 18:57:03,536 > Parameters: 1(Integer) (BaseJdbcLogger.java:145)
DEBUG 04-15 18:57:03,819 < Total: 1 (BaseJdbcLogger.java:145)
刘东平

如果需要部门信息

//获取接口代理类

		try {
			EmployeeMapper employeeMapper = sqlSession.getMapper(EmployeeMapper.class);
			/*Employee reEmployee = employeeMapper.reEmployee(1);
			System.out.println(reEmployee);*/
			//分步查询
			Employee empAndDeptSept = employeeMapper.getEmpAndDeptSept(1);
			//测试懒加载，不需要查询部门信息，那么就不会进行查询
			//System.out.println(empAndDeptSept.getLastname());
			//如果需要部门信息
			System.out.println(empAndDeptSept.getDepartment());
		} finally {
			sqlSession.close();
		}

测试结果
DEBUG 04-15 19:09:34,357 ==> Preparing: select * from employee where id=? (BaseJdbcLogger.java:145)
DEBUG 04-15 19:09:34,449 > Parameters: 1(Integer) (BaseJdbcLogger.java:145)
DEBUG 04-15 19:09:34,676 < Total: 1 (BaseJdbcLogger.java:145)
DEBUG 04-15 19:09:34,678 ==> Preparing: select * from department where id=? (BaseJdbcLogger.java:145)
DEBUG 04-15 19:09:34,679 > Parameters: 888(Integer) (BaseJdbcLogger.java:145)
DEBUG 04-15 19:09:34,682 < Total: 1 (BaseJdbcLogger.java:145)
Department [id=888, departmentName=董事长]