MyBatis(4),关联映射
关联关系
一对一:例如一个人对应一张身份证
一对多:例如一个班级有多个学生
多对多:例如老师和学生的关系,一个老师对应多个学生,一个学生对应多个老师
一对一
准备实体类
使用的是EMP表和DEPT表
Emp.java
package com.yangqihang.bean;
import java.util.Date;
public class Emp{
private Integer empno;
private String ename;
private String job;
private Integer mgr;
private Date hiredate;
private Double sal;
private Double comm;
private Dept dept;
//省略Getter和Setter
//省略toString
Dept.java
package com.yangqihang.bean;
public class Dept {
private Integer deptno;
private String dname;
private String loc;
//省略Getter和Setter
//省略toString
嵌套结果方式
需要SQL语句进行关联查询
EmpDao.java
package com.yangqihang.dao;
import com.yangqihang.bean.Emp;
public interface EmpDao {
Emp selectByEmpno(Integer empno);
}
EmpDao.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.yangqihang.dao.EmpDao">
<resultMap id="EmpDeptResultMap" type="com.yangqihang.bean.Emp">
<id property="empno" column="empno"></id>
<result property="ename" column="ename"></result>
<result property="job" column="job"></result>
<result property="mgr" column="mgr"></result>
<result property="hiredate" column="hiredate"></result>
<result property="sal" column="sal"></result>
<result property="comm" column="comm"></result>
<association property="dept" javaType="com.yangqihang.bean.Dept">
<id property="deptno" column="deptno"></id>
<id property="dname" column="dname"></id>
<id property="loc" column="loc"></id>
</association>
</resultMap>
<select id="selectByEmpno" resultMap="EmpDeptResultMap">
select e.*,d.dname,d.loc from emp e left join dept d on e.deptno = d.deptno where e.empno = #{empno}
</select>
</mapper>
嵌套查询方式
EmpDao.java同上
DeptDao.java
package com.yangqihang.dao;
import com.yangqihang.bean.Dept;
public interface DeptDao {
Dept selectByDeptno(Integer deptno);
}
EmpDao.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.yangqihang.dao.EmpDao">
<resultMap id="EmpDeptResultMap" type="com.yangqihang.bean.Emp">
<id property="empno" column="empno"></id>
<result property="ename" column="ename"></result>
<result property="job" column="job"></result>
<result property="mgr" column="mgr"></result>
<result property="hiredate" column="hiredate"></result>
<result property="sal" column="sal"></result>
<result property="comm" column="comm"></result>
<association property="dept" column="deptno" select="com.yangqihang.dao.DeptDao.selectByDeptno"></association>
</resultMap>
<select id="selectByEmpno" resultMap="EmpDeptResultMap">
select e.*,d.dname,d.loc from emp e left join dept d on e.deptno = d.deptno where e.empno = #{empno}
</select>
</mapper>
association标签中的column属性表示指定传入select方法的值是哪个列下的数据
DeptDao.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.yangqihang.dao.DeptDao">
<select id="selectByDeptno" resultType="com.yangqihang.bean.Dept">
select * from dept where deptno = #{deptno}
</select>
</mapper>
一对多
准备实体类
Emp.java
package com.yangqihang.bean;
import java.util.Date;
public class Emp{
private Integer empno;
private String ename;
private String job;
private Integer mgr;
private Date hiredate;
private Double sal;
private Double comm;
private Integer deptno;
//省略Getter和Setter
//省略toString
Dept.java
package com.yangqihang.bean;
import java.util.List;
public class Dept {
private Integer deptno;
private String dname;
private String loc;
private List<Emp> empList;
//省略Getter和Setter
//省略toString
嵌套结果方式
DeptDao.java
package com.yangqihang.dao;
import com.yangqihang.bean.Dept;
public interface DeptDao {
Dept selectByDeptno(Integer deptno);
}
DeptDao.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.yangqihang.dao.DeptDao">
<resultMap id="DeptEmpResultMap" type="com.yangqihang.bean.Dept">
<id property="deptno" column="deptno"></id>
<result property="dname" column="dname"></result>
<result property="loc" column="loc"></result>
<collection property="empList" ofType="com.yangqihang.bean.Emp">
<id property="empno" column="empno"></id>
<result property="ename" column="ename"></result>
<result property="job" column="job"></result>
<result property="mgr" column="mgr"></result>
<result property="hiredate" column="hiredate"></result>
<result property="sal" column="sal"></result>
<result property="comm" column="comm"></result>
<result property="deptno" column="deptno"></result>
</collection>
</resultMap>
<select id="selectByDeptno" resultMap="DeptEmpResultMap">
select e.*,d.dname,d.loc from dept d left join emp e on d.deptno = e.deptno where d.deptno =#{deptno}
</select>
</mapper>
嵌套查询方式
DeptDao.java同上
EmpDao.java
package com.yangqihang.dao;
import com.yangqihang.bean.Emp;
import java.util.List;
public interface EmpDao {
List<Emp> selectByDeptno(Integer deptno);
}
DeptDao.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.yangqihang.dao.DeptDao">
<resultMap id="DeptEmpResultMap" type="com.yangqihang.bean.Dept">
<id property="deptno" column="deptno"></id>
<result property="dname" column="dname"></result>
<result property="loc" column="loc"></result>
<collection property="empList" column="deptno" select="com.yangqihang.dao.EmpDao.selectByDeptno"></collection>
</resultMap>
<select id="selectByDeptno" resultMap="DeptEmpResultMap">
select * from dept where deptno = #{deptno}
</select>
</mapper>
EmpDao.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.yangqihang.dao.EmpDao">
<select id="selectByDeptno" resultType="com.yangqihang.bean.Emp">
select * from emp where deptno = #{deptno}
</select>
</mapper>
多对多
多对多与一对多实现类似,不过会增加中间表
延迟查询
当我们在进行表关联的时候,有可能在查询结果的时候不需要关联对象的属性值,那么此时可以通过延迟加载来实现功能,例如有时候只需要查询Dept表的dname属性,而不需要再查询emp表中的数据,可以使用延迟查询
//只需要输出dname
String dname = dao.selectByDeptno(10).getDname();
System.out.println(dname);
单独设置
可以在关联查询中设置fetchType属性为lazy
<collection property="empList" column="deptno" select="com.yangqihang.dao.EmpDao.selectByDeptno" fetchType="lazy"></collection>
或者在全局配置中开启延时加载
<settings>
<setting name="lazyLoadingEnabled" value="true"/>
</settings>
当全局设置开启的时候,但是希望一个sql查询不适用延时策略可以设置fetchType属性为eager