1、手动处理映射关系
//实体类
public class Emp implements Serializable {
private Integer empno;
private String name;
private String job;
private Integer mgr;
private Date hiredate;
private Double sal;
private Double comm;
private Integer deptno;
}
<mapper namespace="com.msb.mapper.EmpMapper">
<!--手动处理数据库查询字段和封装实体类属性之间的映射关系
1 主键一般使用id属性
2 当属性名和查询出的数据表字段名相同 可以不写映射关系
-->
<resultMap id="empMap" type="emp">
<!--<id property="empno" column="empno"></id>-->
<result property="name" column="ename"></result>
<!--<result property="job" column="job"></result>
<result property="sal" column="sal"></result>
<result property="hiredate" column="hiredate"></result>
<result property="mgr" column="mgr"></result>
<result property="comm" column="comm"></result>
<result property="deptno" column="deptno"></result>-->
</resultMap>
<select id="findByEmpno" resultMap="empMap" >
select * from emp where empno =#{empno}
</select>
</mapper>
2、一对一关联查询
(1)实体类
@AllArgsConstructor
@NoArgsConstructor
@Data
public class Emp implements Serializable {
private Integer empno;
private String ename;
private String job;
private Integer mgr;
private Date hiredate;
private Double sal;
private Double comm;
private Integer deptno;
// 组合一个Dept对象作为自己的属性
private Dept dept;
}
(2)接口
public interface EmpMapper {
/**
* 根据员工编号查询员工的所有信息并携带所在的部门信息
* @param empno 要查询的员工编号
* @return Emp对象,组合了Dept对象作为属性,对部门信息进行存储
*/
Emp findEmpJoinDeptByEmpno(int empno);
}
(3)映射文件
<?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.msb.mapper.EmpMapper">
<!--Emp findEmpJoinDeptByEmpno(int empno);-->
<resultMap id="empJoinDept" type="emp">
<!--设置emp本身的八个属性的映射关系-->
<id property="empno" column="empno"></id>
<result property="ename" column="ename"></result>
<result property="job" column="job"></result>
<result property="sal" column="sal"></result>
<result property="hiredate" column="hiredate"></result>
<result property="mgr" column="mgr"></result>
<result property="comm" column="comm"></result>
<result property="deptno" column="deptno"></result>
<!--
association 处理一对一
封装一对一信息关系的标签
property emp类的属性名
javaType 用哪个类的对象给属性赋值
-->
<association property="dept" javaType="dept">
<id column="deptno" property="deptno"></id>
<result column="dname" property="dname"></result>
<result column="loc" property="loc"></result>
</association>
</resultMap>
<select id="findEmpJoinDeptByEmpno" resultMap="empJoinDept" >
select * from
emp e
left join dept d
on e.deptno =d.deptno
where empno = #{empno}
</select>
</mapper>
(4)测试代码
@Test
public void testOneToOne() throws ParseException {
EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
Emp emp = mapper.findEmpJoinDeptByEmpno(7499);
System.out.println(emp);
}
3、一对多关联查询
(1)实体类
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Dept implements Serializable {
private Integer deptno;
private String dname;
private String loc;
// 组合一个Emp的List集合作为属性
private List<Emp> empList;
}
(2)接口
public interface DeptMapper {
/**
* 根据部门编号查询部门信息及该部分的所有员工信息
* @param deptno 要查询的部门编号
* @return Dept对象,内部组合了一个Emp的List属性用于封装部门的所有员工信息
*/
Dept findDeptJoinEmpsByDeptno(int deptno);
}
(3)映射文件
<?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.msb.mapper.DeptMapper">
<!--Dept findDeptJoinEmpsByDeptno(int deptno);-->
<resultMap id="deptJoinEmps" type="dept">
<id column="deptno" property="deptno"></id>
<result column="dname" property="dname"></result>
<result column="loc" property="loc"></result>
<!--处理一对多关系的标签-->
<collection property="empList" ofType="emp" >
<!--设置emp本身的八个属性的映射关系-->
<id property="empno" column="empno"></id>
<result property="ename" column="ename"></result>
<result property="job" column="job"></result>
<result property="sal" column="sal"></result>
<result property="hiredate" column="hiredate"></result>
<result property="mgr" column="mgr"></result>
<result property="comm" column="comm"></result>
<result property="deptno" column="deptno"></result>
</collection>
</resultMap>
<select id="findDeptJoinEmpsByDeptno" resultMap="deptJoinEmps">
select * from dept d left join emp e on d.deptno =e.deptno where d.deptno =#{deptno}
</select>
</mapper>
(4)测试代码
@Test
public void testOneToMany() throws ParseException {
DeptMapper mapper = sqlSession.getMapper(DeptMapper.class);
Dept dept = mapper.findDeptJoinEmpsByDeptno(20);
System.out.println(dept);
System.out.println("---------");
List<Emp> empList = dept.getEmpList();
empList.forEach(System.out::println);
}
4、多对多关联查询
(1)实体类
@NoArgsConstructor
@AllArgsConstructor
@Data
public class Project implements Serializable {
private Integer pid;
private String pname;
private Integer money;
// 组合一个ProjectRecord对象集合作为属性
private List<ProjectRecord> projectRecords;
}
@Data
@AllArgsConstructor
@NoArgsConstructor
public class ProjectRecord implements Serializable {
private Integer empno;
private Integer pid;
// 组合一个Emp对象作为属性
private Emp emp;
}
(2)接口
public interface ProjectMapper {
/**
* 根据项目编号查询一个项目信息及参与该项目的所有员工信息
* @param pid 项目编号
* @return 所有信息封装的Project对象
*/
Project findProjectJoinEmpsByPid(int pid);
}
(3)映射文件
<?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.msb.mapper.ProjectMapper">
<!--Project findProjectJoinEmpsByPid(int pid);-->
<resultMap id="projectJoinEmps" type="project">
<id column="pid" property="pid"></id>
<result column="pname" property="pname"></result>
<result column="money" property="money"></result>
<!--一对多 集合属性 collection-->
<collection property="projectRecords" ofType="projectRecord">
<id column="empno" property="empno"></id>
<id column="pid" property="pid"></id>
<!--一对一 -->
<association property="emp" javaType="emp">
<id property="empno" column="empno"></id>
<result property="ename" column="ename"></result>
<result property="job" column="job"></result>
<result property="sal" column="sal"></result>
<result property="hiredate" column="hiredate"></result>
<result property="mgr" column="mgr"></result>
<result property="comm" column="comm"></result>
<result property="deptno" column="deptno"></result>
</association>
</collection>
</resultMap>
<select id="findProjectJoinEmpsByPid" resultMap="projectJoinEmps">
select * from
project p
left join projectrecord pr
on p.pid = pr.pid
left join emp e
on e.empno = pr.empno
where p.pid= #{pid}
</select>
</mapper>
(4)测试代码
@Test
public void testManyToMany() throws ParseException {
ProjectMapper mapper = sqlSession.getMapper(ProjectMapper.class);
Project project = mapper.findProjectJoinEmpsByPid(2);
System.out.println(project.getPid());
System.out.println(project.getPname());
System.out.println(project.getMoney());
List<ProjectRecord> projectRecords = project.getProjectRecords();
for (ProjectRecord projectRecord : projectRecords) {
Emp emp = projectRecord.getEmp();
System.out.println(emp);
}
}