MyBatis(3)
一、 ResultMap:自定义结果映射规则
在xml映射文件中,除了可以使用resultType
定义返回值类型外,还可以使用resultMap
自定义结果集映射规则
EmployeeMapper接口
public Employee getEmpById(Integer id);
xml映射文件
<select id="getEmpById" resultMap="MyEmp">
select * from mybatis.tbl_employee where id=#{id}
</select>
resultMap定义
<resultMap id="MyEmp" type="mybatis_3_mapper.com.Lemon.mybatis.bean.Employee">
<id column="id" property="id"/>
<result column="last_name" property="lastName"/>
<result column="email" property="email"/>
<result column="gender" property="gender"/>
</resultMap>
(1)id
:主键的封装规则
(2)result
:普通列的封装规则
(3)column
:指定是表中的哪一列
(4)property
:指定该列所对应的javaBean对象的哪一个属性
(5)注意到这里定义了映射规则,所以就算不开启mapUnderscoreToCamelCase
驼峰命名规则,也可以识别到对应的属性
(6)映射规则:数据表中列名column <=> java对象的属性名property
二、association的关联查询
要求查询Employee的同时查询员工所在的部门
Employee.java
private Integer id;
private String lastName;
private String gender;
private String email;
private Department dept;
Department.java
private Integer id;
private String departmentName;
private List<Employee> emps;
查询结果:id last_name gender d_id did dept_name
法一:级联属性
由于在Emp中定义了Departrment属性,因此可以使用dept.id
和dept.departmentName
查询员工所在的部门
EmployeeMapper接口
public Employee getEmpAndDept(Integer id);
xml映射文件
<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
FROM mybatis.tbl_employee e, mybatis.tbl_dept d
WHERE e.d_id=d.id AND e.id=#{id}
</select>
resultMap定义
<resultMap id="MyDifEmp" type="mybatis_3_mapper.com.Lemon.mybatis.bean.Employee">
<id column="id" property="id"/>
<result column="last_name" property="lastName"/>
<result column="gender" property="gender"/>
<!-- TODO 法一:级联属性 -->
<result column="did" property="dept.id"/>
<result column="dept_name" property="dept.departmentName"/>
</resultMap>
法二:association标签
association
标签:用于在ResultMap
中嵌套定义单个对象的封装规则
property
属性:指定外层对象的哪个属性是要关联(嵌套)的对象
javaType
属性:指定该关联对象的类型(全类名) 不能省略
xml映射文件
<select id="getEmpAndDept" 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
FROM mybatis.tbl_employee e, mybatis.tbl_dept d
WHERE e.d_id=d.id AND e.id=#{id}
</select>
resultMap定义
<resultMap id="MyDifEmp2" type="mybatis_3_mapper.com.Lemon.mybatis.bean.Employee">
<id column="id" property="id"/>
<result column="last_name" property="lastName"/>
<result column="gender" property="gender"/>
<association property="dept" javaType="mybatis_3_mapper.com.Lemon.mybatis.bean.Department">
<id column="did" property="id"/>
<result column="dept_name" property="departmentName"/>
</association>
</resultMap>
主函数
Employee empAndDept = mapper.getEmpAndDept(1);
System.out.println(empAndDept);
System.out.println(empAndDept.getDept());
三、association的分步查询
关联查询可以改成分步查询:
由于Emp和Dept是通过id进行关联的,所以可以根据Emp的id查询到Dept的信息
1. 按照员工id查询员工信息:id last_name gender email d_id
2. 根据查询到的员工信息中的d_id值,到部门表中查出部门信息:id dept_name
3. 将部门信息设置到查询到的员工中
sql语句
SELECT * FROM tbl_employee WHERE id =1;
SELECT * FROM tbl_dept WHERE id=1
association
标签:用于在ResultMap
中嵌套定义单个对象的封装规则
property
属性:指定外层对象的哪个属性是要关联(嵌套)的对象
select
属性:表明当前属性property
是调用select
指定的方法进行查询 NameSpace.id的形式
column
属性:指定将哪一列的值传给这个方法(使得两表关联的列)
EmployeeMapper接口
public Employee getEmpByIdStep(Integer id);
EmployeeMapper.xml
<select id="getEmpByIdStep" resultMap="MyEmpByStep">
select * from mybatis.tbl_employee where id=#{id}
</select>
<resultMap id="MyEmpByStep" type="mybatis_3_mapper.com.Lemon.mybatis.bean.Employee">
<id column="id" property="id"/>
<result column="last_name" property="lastName"/>
<result column="email" property="email"/>
<result column="gender" property="gender"/>
<association property="dept"
select="mybatis_3_mapper.com.Lemon.mybatis.dao.DepartmentMapper.getDeptById"
column="d_id"/>
</resultMap>
DepartmentMapper接口
public Department getDeptById(Integer id);
DepartmentMapper.xml
<mapper namespace="mybatis_3_mapper.com.Lemon.mybatis.dao.DepartmentMapper">
<select id="getDeptById" resultType="mybatis_3_mapper.com.Lemon.mybatis.bean.Department">
select id, dept_name departmentName
from mybatis.tbl_dept
where id = #{id}
</select>
</mapper>
主函数
Employee employee = mapper.getEmpByIdStep(1);
System.out.println(employee);
System.out.println(employee.getDept());
四、分步查询进阶:延迟加载
使用延迟加载可以在用到Dept查询时,才发送查询Dept的sql语句
全局配置文件mybatis-config.xml
<settings>
<setting name="lazyLoadingEnabled" value="true"/>
<setting name="aggressiveLazyLoading" value="false"/>
</settings>
五、collection的关联查询
查询部门的时候,将部门对应的所有员工信息也查询出来
这里由于一个部门可以对应多个员工,所以结果的返回值类型是List
Department.java
private List<Employee> emps;
Department接口
public Department getDeptByIdPlus(Integer id);
Department.xml
<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 mybatis.tbl_dept d
LEFT JOIN mybatis.tbl_employee e
ON d.id = e.d_id
WHERE d.id = 1
</select>
collection
标签:定义List集合类型的属性的封装规则
property
属性:集合属性的名称(Department对象中定义的List类型的Emps)
ofType
属性:集合中元素的类型Employee
<resultMap id="MyDept" type="mybatis_3_mapper.com.Lemon.mybatis.bean.Department">
<id column="did" property="id"/>
<result column="dept_name" property="departmentName"/>
<collection property="emps" ofType="mybatis_3_mapper.com.Lemon.mybatis.bean.Employee">
<!-- 定义这个集合中元素的封装规则 -->
<id column="eid" property="id"/>
<result column="last_name" property="lastName"/>
<result column="email" property="email"/>
<result column="gender" property="gender"/>
</collection>
</resultMap>
主函数
Department department = mapper.getDeptByIdPlus(1);
System.out.println(department);
System.out.println(department.getEmps());
六、collection的分步查询和延迟加载
根据Dept的id查询指定部门id的员工信息
sql语句
SELECT * FROM tbl_dept WHERE id=1
SELECT * FROM tbl_employee WHERE d_id=1
collection
标签:用于在ResultMap
中嵌套定义List类型对象的封装规则
fetchType
属性:设置为lazy表示使用延迟加载(默认)
DepartmentMapper接口
public Department getDeptByIdStep(Integer id);
DepartmentMapper.xml
<select id="getDeptByIdStep" resultMap="MyDeptStep">
select id, dept_name departmentName
from mybatis.tbl_dept
where id = #{id}
</select>
<resultMap id="MyDeptStep" type="mybatis_3_mapper.com.Lemon.mybatis.bean.Department">
<id column="id" property="id"/>
<result column="dept_name" property="departmentName"/>
<collection property="emps"
select="mybatis_3_mapper.com.Lemon.mybatis.dao.EmployeeMapperPlus.getEmpsByDeptId"
column="{deptId=id}"
fetchType="lazy"/>
</resultMap>
EmployeeMapper接口
public List<Employee> getEmpsByDeptId(Integer deptId);
EmployeeMapper.xml
<select id="getEmpsByDeptId" resultType="mybatis_3_mapper.com.Lemon.mybatis.bean.Employee">
select * from mybatis.tbl_employee where d_id=#{deptId}
</select>
主函数
Department deptByIdStep = mapper.getDeptByIdStep(1);
System.out.println(deptByIdStep.getDepartmentName());
System.out.println(deptByIdStep.getEmps());
七、discriminator鉴别器
discriminator
标签:判断某列的值,根据值进行不同的封装规则(类似于switch-case)
javaType
属性:需要判断的列的值对应的类型
column
属性:需要判断的列名
其中case
标签表示一个case,value
属性表示可能的值,resultType
属性表示要封装的结果的类型(不能省略)
假设封装规则如下:
1. 如果查出的是女生,就把部门信息查询出来,否则不查询
2. 如果查出的是男生,就把last_name这一列的值赋值给email
依然使用分步查询的方法
EmployeeMapper接口
public Employee getEmpByIdStep(Integer id);
EmployeeMapper.xml
<select id="getEmpByIdStep" resultMap="MyEmpDis">
select * from mybatis.tbl_employee where id=#{id}
</select>
<resultMap id="MyEmpDis" type="mybatis_3_mapper.com.Lemon.mybatis.bean.Employee">
<id column="id" property="id"/>
<result column="last_name" property="lastName"/>
<result column="email" property="email"/>
<result column="gender" property="gender"/>
<discriminator javaType="string" column="gender">
<!-- 女生 -->
<case value="0" resultType="mybatis_3_mapper.com.Lemon.mybatis.bean.Employee">
<!-- 定义规则:如果查出的是女生,就把部门信息查询出来,否则不查询 -->
<association property="dept"
select="mybatis_3_mapper.com.Lemon.mybatis.dao.DepartmentMapper.getDeptById"
column="d_id"/>
</case>
<!-- 男生 -->
<case value="1" resultType="mybatis_3_mapper.com.Lemon.mybatis.bean.Employee">
<!-- 定义规则:如果查出的是男生,就把last_name这一列的值赋值给email -->
<id column="id" property="id"/>
<result column="last_name" property="lastName"/>
<result column="last_name" property="email"/>
<result column="gender" property="gender"/>
</case>
</discriminator>
</resultMap>
主函数
Employee employee = mapper.getEmpByIdStep(3);
System.out.println(employee);
System.out.println(employee.getDept());