MyBatis-映射文件06-多表联合查询

MyBatis-映射文件06-多表联合查询

使用标签中的标签和标签来处理多表联合查询时查询得到的字段值与JavaBean成员变量的对应问题,即多表联合查询时的封装规则

---

使用到的实体类的定义: //Employee.java package bean; public class Employee { private Integer id; private String lastName; private String email; private String gender; private Department dept; //部门 //省略getter和setter方法 //无参构造器 public Employee() { super(); } //有参构造器 public Employee(Integer id, String lastName, String email, String gender) { super(); this.id = id; this.lastName = lastName; this.email = email; this.gender = gender; } @Override public String toString() { return "Employee [id=" + id + ", lastName=" + lastName + ", email=" + email + ", gender=" + gender + "]"; } } //Department package bean; import java.util.List; public class Department { private Integer id; private String departmentName; private List<Employee> emps; //该部门下的所有员工 //省略getter和setter方法 @Override public String toString() { return "Department [id=" + id + ", departmentName=" + departmentName + "]"; } }

全局配置文件:mybatis-config.xml

<?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> <environments default="development"> <!-- 配置MySQL数据库的连接信息 --> <environment id="development"> <transactionManager type="JDBC" /> <dataSource type="POOLED"> <property name="driver" value="com.mysql.jdbc.Driver" /> <property name="url" value="jdbc:mysql://localhost:3306/mybatis" /> <property name="username" value="root" /> <property name="password" value="root" /> </dataSource> </environment> </environments> <mappers> <!-- 批量注册 --> <package name="dao" /> </mappers> </configuration>

---

需求1:查询员工(Employee)的同时查出员工对应的部门(Department),一个员工有一个与之对应的部门(多表联合查询)-------使用标签

解决方案1:使用级联属性封装成resultMap

EmployeeMapper.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="dao.EmployeeMapper"> <!-- 联合查询：使用级联属性封装成resultMap --> <resultMap type="bean.Employee" id="MyEmpAndDept"> <id column="id" property="id"/> <result column="last_name" property="lastName"/> <result column="gender" property="gender"/> <result column="did" property="dept.id"/> <result column="dept_name" property="dept.departmentName"/> </resultMap> <!--接口中的方法定义:public Employee getEmpAndDept(Integer id);--> <select id="getEmpAndDept" resultMap="MyEmpAndDept"> <!-- select * from tab_employee e,tbl_dept d where e.d_id = d.id and e.id = #{id} --> select e.id id,e.last_name last_name,e.gender gender,e.d_id d_id,d.id did,d.dept_name dept_name from tbl_employee e, tbl_dept d where e.d_id = d.id and e.id = #{id} </select> </mapper>

测试方法:

package test; import java.io.IOException; import java.io.InputStream; 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 bean.Employee; import dao.EmployeeMapper; public class MyBatisTest { // 获取SqlSessionfactory对象的方法 public SqlSessionFactory getSqlSessionFactory() throws IOException { String resource = "mybatis-config.xml"; InputStream inputStream = Resources.getResourceAsStream(resource); return new SqlSessionFactoryBuilder().build(inputStream); } @Test public void test() throws IOException { SqlSessionFactory sqlSessionFactory = getSqlSessionFactory(); SqlSession openSession = sqlSessionFactory.openSession(); try { EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class); Employee empAndDept = mapper.getEmpAndDept(1); System.out.println(empAndDept); System.out.println(empAndDept.getDept().getDepartmentName()); } finally { openSession.close(); } } }

输出结果:

DEBUG 10-31 21:33:50,291 ==> Preparing: select e.id id,e.last_name last_name,e.gender gender,e.d_id d_id,d.id did,d.dept_name dept_name from tbl_employee e, tbl_dept d where e.d_id = d.id and e.id = ? (BaseJdbcLogger.java:145) DEBUG 10-31 21:33:50,329 ==> Parameters: 1(Integer) (BaseJdbcLogger.java:145) DEBUG 10-31 21:33:50,358 <== Total: 1 (BaseJdbcLogger.java:145) Employee [id=1, lastName=Admin, email=null, gender=1] 开发部

解决方案2:使用association定义单个对象的封装规则

EmployeeMapper.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="dao.EmployeeMapper"> <!-- 使用association定义单个对象的封装规则 --> <resultMap type="bean.Employee" id="MyEmpAndDept"> <id column="id" property="id"/> <result column="last_name" property="lastName"/> <result column="gender" property="gender"/> <!-- association可以指定联合的javabean对象 property:指定哪个属性是联合的对象 javaType：指定联合的对象的类型[不能省略] --> <association property="dept" javaType="bean.Department"> <id column="did" property="id"/> <result column="dept_name" property="departmentName"/> </association> </resultMap> <select id="getEmpAndDept" resultMap="MyEmpAndDept"> <!-- select * from tab_employee e,tbl_dept d where e.d_id = d.id and e.id = #{id} --> select e.id id,e.last_name last_name,e.gender gender,e.d_id d_id,d.id did,d.dept_name dept_name from tbl_employee e, tbl_dept d where e.d_id = d.id and e.id = #{id} </select> </mapper>

测试方法和输出结果同方案1

解决方案3:使用association进行分步查询

EmployeeMapper.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="dao.EmployeeMapper"> <!-- 使用association进行分步查询 1.先根据员工id查出员工信息 2.根据查询到的员工信息中的d_id值去部门表查询出部门信息 3.部门设置到员工信息中 流程：使用select指定的方法（传入column指定的这列参数的值）查出对象并封装给property属性 分布查询的好处： 1.可以整合已有的查询方法 2.可以使用延迟加载 --> <resultMap type="bean.Employee" id="MyEmpByStep"> <id column="id" property="id"/> <result column="last_name" property="lastName"/> <result column="email" property="email"/> <result column="gender" property="gender"/> <!-- 使用association定义关联对象的封装规则 --> <!-- 这里的select的值为DepartmentMapper.xml中的命名空间值+select的id值，表示当前属性是调用select指定的方法查出的结果 --> <!-- column:指定将哪一列（数据表的列名/别名）的值传给这个select指定的方法 --> <association property="dept" select="dao.DepartmentMapper.getDeptById" column="d_id"> </association> </resultMap> <!-- 对应的EmployeeMapper.java接口中的方法定义:public Employee getEmpByIdStep(Integer id); --> <select id="getEmpByIdStep" resultMap="MyEmpByStep"> select * from tbl_employee where id = #{id} </select> </mapper>

DepartmentMapper.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="dao.DepartmentMapper"> <!-- 对应的DepartmentMapper.java接口中的方法定义:public Department getDeptById(Integer id); --> <select id="getDeptById" resultType="bean.Department"> select id,dept_name departmentName from tbl_dept where id = #{id} </select> </mapper>

测试方法:

//获取mapper的方法同方案1中的测试方法 Employee empByIdStep = mapper.getEmpByIdStep(1); System.out.println(empByIdStep);

此时不需要获得该员工的部门信息,输出结果如下:

DEBUG 10-31 21:44:19,478 ==> Preparing: select * from tbl_employee where id = ? (BaseJdbcLogger.java:145) DEBUG 10-31 21:44:19,509 ==> Parameters: 1(Integer) (BaseJdbcLogger.java:145) DEBUG 10-31 21:44:19,550 <== Total: 1 (BaseJdbcLogger.java:145) Employee [id=1, lastName=Admin, email=Admin, gender=1]

可以看到只执行了查询员工信息的sql语句,而在association标签中调用的查询该员工部门的sql语句并没有执行

如果在测试方法中加上:

System.out.println(empByIdStep.getDept().getDepartmentName());

则此时的输出结果为:

DEBUG 10-31 21:56:37,888 ==> Preparing: select * from tbl_employee where id = ? (BaseJdbcLogger.java:145) DEBUG 10-31 21:56:37,930 ==> Parameters: 1(Integer) (BaseJdbcLogger.java:145) DEBUG 10-31 21:56:37,949 ====> Preparing: select id,dept_name departmentName from tbl_dept where id = ? (BaseJdbcLogger.java:145) DEBUG 10-31 21:56:37,949 ====> Parameters: 1(Integer) (BaseJdbcLogger.java:145) DEBUG 10-31 21:56:37,949 <==== Total: 1 (BaseJdbcLogger.java:145) DEBUG 10-31 21:56:37,949 <== Total: 1 (BaseJdbcLogger.java:145) Employee [id=1, lastName=Admin, email=jerry.com, gender=1] 开发部

从上面的输出结果可以看出,实际上是发送了两次sql语句,也就是说只有当需要association中关联的查询的结果时,才会调用该查询

---

需求2:查询部门的时候将部门对应的所有员工信息查询出来–标签

解决方案1:嵌套结果集的方式：使用collection标签定义关联的集合类型的属性封装规则

DepartmentMapper.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="dao.DepartmentMapper"> <!-- 使用collection标签定义关联的集合类型的属性封装规则 --> <resultMap type="bean.Department" id="myDept"> <id column="did" property="id"/> <result column="dept_name" property="departmentName"/> <!-- collection定义关联的集合类型的封装规则 property：该javabean里面定义的集合的名称 ofType：指定集合里面元素的类型 --> <collection property="emps" ofType="bean.Employee"> <!-- 在Collection中定义这个集合中的元素的封装规则 --> <id column="eid" property="id"/> <result column="last_name" property="lastName"/> <result column="email" property="email"/> <result column="gender" property="gender"/> </collection> </resultMap> <select id="getDeptByIdPlus" resultMap="myDept"> select d.id did,d.dept_name dept_name,e.id eid,e.last_name last_name,e.email email,e.gender gender from tbl_dept d left join tbl_employee e on d.id = e.d_id where d.id = #{id} </select> </mapper>

测试方法:

//获取openSession的方法同需求1的方案1的测试方法 DepartmentMapper mapper = openSession.getMapper(DepartmentMapper.class); Department deptByIdPlus = mapper.getDeptByIdPlus(1); System.out.println(deptByIdPlus); System.out.println(deptByIdPlus.getEmps());

输出结果:

DEBUG 10-31 22:07:08,712 ==> Preparing: select d.id did,d.dept_name dept_name,e.id eid,e.last_name last_name,e.email email,e.gender gender from tbl_dept d left join tbl_employee e on d.id = e.d_id where d.id = ? (BaseJdbcLogger.java:145) DEBUG 10-31 22:07:08,771 ==> Parameters: 1(Integer) (BaseJdbcLogger.java:145) DEBUG 10-31 22:07:08,806 <== Total: 8 (BaseJdbcLogger.java:145) Department [id=1, departmentName=开发部] [Employee [id=1, lastName=Admin, email=jerry.com, gender=1], Employee [id=4, lastName=tina, email=tina.com, gender=0], Employee [id=46, lastName=Lucky, email=Lucky.com, gender=1], Employee [id=47, lastName=Helen, email=helen.com, gender=1], Employee [id=48, lastName=Elieen, email=Eileen.com, gender=1], Employee [id=49, lastName=Lucky, email=Lucky.com, gender=1], Employee [id=50, lastName=Helen, email=helen.com, gender=1], Employee [id=51, lastName=Elieen, email=Eileen.com, gender=1]]

解决方案2:分步查询,先查询出该id对应的部门的信息,再从员工表中查询出部门为该id的员工信息

DepartmentMapper.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="dao.DepartmentMapper"> <!-- 分步查询： 1.select * from tbl_dept where id = #{id} 2.select * from tbl_dept where d_id =#{id} --> <resultMap type="bean.Department" id="myDeptStep"> <id column="id" property="id"/> <result column="dept_name" property="departmentName"/> <!-- property：对应的javabean中的集合的变量名 select:调用的那个sql语句所在的sql映射文件中的命名空间+select的id值 column：要传给所调用的那个select的参数值 fetchType="lazy":默认为lazy，表示使用延迟加载， lazy：延迟加载 eager：立即加载 即使在全局配置文件中开启了全局延迟加载，也可以通过改变fetchType的值使该查询为立即加载 --> <collection property="emps" select="dao.EmployeeMapper.getEmpByDeptId" column="deptId=id" fetchType="eager"> </collection> <!-- column:传递多列的值 将多列的值封装成map传递 column="{key1=column1,key2=column2}" --> </resultMap> <!-- 1.查询部门信息：public Department getDeptByIdStep(Integer id); --> <select id="getDeptByIdStep" resultMap="myDeptStep"> select id,dept_name departmentName from tbl_dept where id = #{id} </select> </mapper>

EmployeeMapper.xml

<!-- 2.根据部门id查询该部门下的所有员工的分布查询需要的方法:public List<Employee> getEmpByDeptId(Integer id); --> <select id="getEmpByDeptId" resultType="bean.Employee"> select * from tbl_employee where d_id = #{deptId} </select>

测试方法:

//获取openSession的方法同需求1的方案1的测试方法 DepartmentMapper mapper = openSession.getMapper(DepartmentMapper.class); Department deptByIdStep = mapper.getDeptByIdStep(1); System.out.println(deptByIdStep); //System.out.println(deptByIdStep.getEmps());

输出结果:

DEBUG 10-31 22:44:34,040 ==> Preparing: select id,dept_name departmentName from tbl_dept where id = ? (BaseJdbcLogger.java:145) DEBUG 10-31 22:44:34,076 ==> Parameters: 1(Integer) (BaseJdbcLogger.java:145) DEBUG 10-31 22:44:34,172 <== Total: 1 (BaseJdbcLogger.java:145) DEBUG 10-31 22:44:34,173 ==> Preparing: select * from tbl_employee where d_id = ? (BaseJdbcLogger.java:145) DEBUG 10-31 22:44:34,174 ==> Parameters: 1(Integer) (BaseJdbcLogger.java:145) DEBUG 10-31 22:44:34,177 <== Total: 8 (BaseJdbcLogger.java:145) 开发部

这里之所以也执行了查询该部门下员工信息的sql,是因为在xml映射文件中,我们设置了fetchType=“eager”,也就是无论全局配置文件中是否开启了懒加载,在这里都会采用立即加载的策略