笔记要点
出错分析与总结
工程组织
1.定义接口
DepartmentMapper
package com.dao; import com.bean.Department; public interface DepartmentMapper { public Department getDeptById(Integer id); }
EmployeeMapperPlus
package com.dao; import com.bean.*; public interface EmployeeMapperPlus { public Employee getEmpByIdStep(Integer id); //分步查询 public Employee getEmpAndDept(Integer id);//关联查询 public Employee getEmpAndDept2(Integer id); //关联查询 ,使用association }
2.定义XML映射文件
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="com.dao.DepartmentMapper"> <!--public Department getDeptById(Integer id);--> <select id="getDeptById" resultType="com.bean.Department"> select id,dept_name departmentName from tbl_dept where id=#{id} </select> <!-- public class Department { private Integer id; private String departmentName; private List<Employee> emps; public Department getDeptByIdPlus(Integer id); --> <select id="getDeptByIdPlus" resultMap=""> select id,dept_name departmentName from tbl_dept where id=#{id} </select> </mapper>
EmployeeMapperPlus.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="com.dao.EmployeeMapperPlus"> <!--ResultMap ;自定义结果集映射规则; type: 自定义规则的Java类型;id: 唯一的标识,方便引用--> <resultMap id="MySimpleEmp" type="com.bean.Employee"> <!--指定主键列的封装规则,id定义主键,底层会有优化规则; column : 指定结果集的具体的那一列; property:指定的JavaBean对应的属性--> <id column="id" property="id"/> <!--定义普通列的封装规则--> <result column="last_name" property="lastName"/> <!--,其他不指定的列会自动封装;但是, 我们只要写ResultMap,就把剩下的映射全部都写上--> <result column="email" property="email"/> <result column="gender" property="gender"/> </resultMap> <!--public Employee getEmpById(Integer id); 注意进行更改为resultMap--> <select id="getEmpById" resultMap="MySimpleEmp"> select * from tbl_employee where id=#{id} </select> <!--场景1,方法1:使用级联属性的方式 查询Employee的同时查询员工对应的部门Employee.dept=Department.id; 输出员工对应的部门的全部信息: id last_name gender did dept_name --> <resultMap id="MyDifEmp" type="com.bean.Employee"> <!--column : 指定结果集的具体的那一列; property:指定的JavaBean对应的属性--> <id column="id" property="id"/> <result column="last_name" property="lastName"/> <result column="gender" property="gender"/> <result column="email" property="email"/> <result column="did" property="dept.id"/> <result column="dept_name" property="dept.departmentName"/> </resultMap> <!--public Employee getEmpAndDept2(Integer id); //association 定义封装规则!--> <select id="getEmpAndDept" resultMap="MyDifEmp"> 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,email FROM tbl_employee e,tbl_dept d WHERE e.`d_id`=d.`id` AND e.id=#{id}; </select> <!--方法2:-使用association可以指定联合的javaBean的对象 --> <resultMap id="MyDifEmp2" type="com.bean.Employee"> <!--column : 指定结果集的具体的那一列; property:指定的JavaBean对应的属性--> <id column="id" property="id"/> <result column="last_name" property="lastName"/> <result column="gender" property="gender"/> <result column="email" property="email"/> <!--使用association可以指定联合的javaBean的对象;(定义单个对象的封装规则!) property="dept";指定那个属性是联合的对象;javaType="dept";指定那个属性对象的类型;--> <association property="dept" javaType="com.bean.Department"> <id column="did" property="id"/> <result column="dept_name" property="departmentName"/> </association> </resultMap> <!--public Employee getEmpAndDept2(Integer id); //关联查询--> <select id="getEmpAndDept2" resultMap="MyDifEmp2"> 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,email FROM tbl_employee e,tbl_dept d WHERE e.`d_id`=d.`id` AND e.id=#{id}; </select> <!--使用association进行分步查询, 1.先按照员工的id查询员工信息; 2.根据查询员工信息中的d_id值去部门表查出部门信息; 3;,部门设置到员工中 --> <!--id last_name gender email d_id--> <resultMap id="MyEmpByStep" type="com.bean.Employee"> <id column="id" property="id" /> <result column="last_name" property="lastName"/> <!--association 定义关联对象的封装规则 select : 表明当前属性是调用select总置顶的方法查出的结果! 总的流程: 使用select 指定的方法(传入)查出对象,并封装给property --> <association column="d_id" property="dept" select="com.dao.DepartmentMapper.getDeptById"> </association> </resultMap> <!--public Employee getEmpByIdStep(Integer id);--> <select id="getEmpByIdStep" resultMap="MyEmpByStep"> select * from tbl_employee where id=#{id} </select> <!--可以使用延迟加载,(按需加载,或者叫做懒加载) Employee==>dept: 我们可以每次查询Employee对象的时候,都将一起查询出来; 部门信息在我们需要使用的时候再去查询,分段查询的基础之上加两个配置; --> <!-- 场景2: 查询部门的时候将部门对应的所有员工的信息也全部查询出来 --> </mapper>
3.编写测试关联查询的 代码
public SqlSessionFactory getSqlSessionFactory() throws IOException { String resource = "mybatis-config.xml"; InputStream inputStream=Resources.getResourceAsStream(resource); return new SqlSessionFactoryBuilder().build(inputStream); } @Test public void test06() throws Exception { SqlSession openSession = getSqlSessionFactory().openSession(); try { EmployeeMapperPlus mapper = openSession.getMapper(EmployeeMapperPlus.class); System.out.println("---tp_30---多表关联查询,级联属性的封装结果!--------"); Employee employee = mapper.getEmpAndDept(1); System.out.println(employee); System.out.println("---tp_31--多表关联查询,使用association进行连接!--------"); Employee employee2 = mapper.getEmpAndDept2(1); System.out.println(employee2); openSession.commit();//默认是不自动提交数据的,需要我们自己手动提交 } finally { openSession.close(); } }
测试结果
---tp_30---多表关联查询,级联属性的封装结果!-------- DEBUG 12-01 15:13:43,362 ==> 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,email FROM tbl_employee e,tbl_dept d WHERE e.`d_id`=d.`id` AND e.id=?; (BaseJdbcLogger.java:145) DEBUG 12-01 15:13:43,380 ==> Parameters: 1(Integer) (BaseJdbcLogger.java:145) DEBUG 12-01 15:13:43,391 <== Total: 1 (BaseJdbcLogger.java:145) Employee{id=1, lastName='jerry', email='jerry@163.com', gender='1', dept=Department{id=1, departmentName='开发部'}} ---tp_31--多表关联查询,使用association进行连接!-------- DEBUG 12-01 15:13:43,391 ==> 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,email FROM tbl_employee e,tbl_dept d WHERE e.`d_id`=d.`id` AND e.id=?; (BaseJdbcLogger.java:145) DEBUG 12-01 15:13:43,392 ==> Parameters: 1(Integer) (BaseJdbcLogger.java:145) DEBUG 12-01 15:13:43,393 <== Total: 1 (BaseJdbcLogger.java:145) Employee{id=1, lastName='jerry', email='jerry@163.com', gender='1', dept=Department{id=1, departmentName='开发部'}} Process finished with exit code 0
在全局配置文件中 开启延迟加载 (按需加载,或者叫做懒加载)
<settings> <setting name="mapUnderscoreToCamelCase" value="true"/> <setting name="jdbcTypeForNull" value="NULL"/> <!--显示地指定每个我们需要更改的配置的值,及时他是默认的;防止版本替换带来的问题--> <setting name="lazyLoadingEnabled" value="true"/> <setting name="aggressiveLazyLoading" value="false"/> </settings>
代码1 (仍使用上面的环境配置信息, 此代码不调用到Department 数据库 )
@Test public void test07() throws Exception{ SqlSession openSession = getSqlSessionFactory().openSession(); try { EmployeeMapperPlus mapper = openSession.getMapper(EmployeeMapperPlus.class); System.out.println("---tp_32---多表关联查询,使用association进行分布查询-----"); Employee employee = mapper.getEmpByIdStep(1); System.out.println(employee.getEmail()); // System.out.println(employee.getDept()); openSession.commit();//默认是不自动提交数据的,需要我们自己手动提交 }finally { openSession.close(); } }
结果1 (没有进行Dept 上数据库的关联查询)
---tp_32---多表关联查询,使用association进行分布查询----- DEBUG 12-01 15:18:57,230 ==> Preparing: select * from tbl_employee where id=? (BaseJdbcLogger.java:145) DEBUG 12-01 15:18:57,249 ==> Parameters: 1(Integer) (BaseJdbcLogger.java:145) DEBUG 12-01 15:18:57,308 <== Total: 1 (BaseJdbcLogger.java:145) jerry@163.com
代码2 (仍使用上面的环境配置信息, 此代码可以调用到Department 数据库 )
EmployeeMapperPlus mapper = openSession.getMapper(EmployeeMapperPlus.class); System.out.println("---tp_32---多表关联查询,使用association进行分布查询-----"); Employee employee = mapper.getEmpByIdStep(1); // System.out.println(employee.getEmail()); System.out.println(employee.getDept()); openSession.commit();//默认是不自动提交数据的,需要我们自己手动提交
结果2
---tp_32---多表关联查询,使用association进行分布查询----- DEBUG 12-01 15:19:50,044 ==> Preparing: select * from tbl_employee where id=? (BaseJdbcLogger.java:145) DEBUG 12-01 15:19:50,066 ==> Parameters: 1(Integer) (BaseJdbcLogger.java:145) DEBUG 12-01 15:19:50,129 <== Total: 1 (BaseJdbcLogger.java:145) DEBUG 12-01 15:19:50,129 ==> Preparing: select id,dept_name departmentName from tbl_dept where id=? (BaseJdbcLogger.java:145) DEBUG 12-01 15:19:50,130 ==> Parameters: 1(Integer) (BaseJdbcLogger.java:145) DEBUG 12-01 15:19:50,131 <== Total: 1 (BaseJdbcLogger.java:145) Department{id=1, departmentName='开发部'}