Mybatis探究-----一对一、一对多关联查询
1.一对一关联
声明两个实体类
1)部门类
package com.entity; import java.util.UUID; /** * 部门类 * * @author yyx 2019年9月17日 */ public class Department { /** * 部门ID */ private String deptId; /** * 部门名称 */ private String deptName; public Department() { super(); } public Department(String deptId, String deptName) { super(); this.deptId = deptId; this.deptName = deptName; } public String getDeptId() { return deptId; } public void setDeptId(String deptId) { this.deptId = deptId; } public String getDeptName() { return deptName; } public void setDeptName(String deptName) { this.deptName = deptName; } @Override public String toString() { return "Department [deptId=" + deptId + ", deptName=" + deptName + "]"; } public static void main(String[] args) { String str = UUID.randomUUID().toString(); System.out.println(str); } }
雇员类
package com.entity; import java.util.UUID; /** * 雇员类 * * @author yyx 2019年9月17日 */ public class Employee { /** * 雇员ID */ private String empId; /** * 雇员名称 */ private String empName; /** * 部门 */ private Department empDept; public Employee() { super(); } public Employee(String empId, String empName, Department empDept) { super(); this.empId = empId; this.empName = empName; this.empDept = empDept; } public String getEmpId() { return empId; } public void setEmpId(String empId) { this.empId = empId; } public String getEmpName() { return empName; } public void setEmpName(String empName) { this.empName = empName; } public Department getEmpDept() { return empDept; } public void setEmpDept(Department empDept) { this.empDept = empDept; } @Override public String toString() { return "Employee [empId=" + empId + ", empName=" + empName + ", empDept=" + empDept + "]"; } public static void main(String[] args) { String str = UUID.randomUUID().toString(); System.out.println(str); } }
1.1 级联属性查询
EmployeeMapper.xml配置
<resultMap type="com.entity.Employee" id="EmployeeResult"> <id column="empId" property="empId" /> <result column="empName" property="empName" /> <result column="deptId" property="empDept.deptId" /> <result column="deptName" property="empDept.deptName" /> </resultMap> <select id="getEmpAndDept" resultMap="EmployeeResult"> select e.empId,e.empName,d.deptId,d.deptName from t_emp e,t_dept d where e.deptId=d.deptId </select>
1.2 单步查询
EmployeeMapper.xml配置
<resultMap type="com.entity.Employee" id="EmployeeResult"> <id column="empId" property="empId" /> <result column="empName" property="empName" /> <!-- association可以指定联合的javaBean对象 property="dept":指定哪个属性是联合的对象 javaType:指定这个属性对象的类型[不能省略] --> <association property="empDept" javaType="com.entity.Department"> <id column="deptId" property="deptId" /> <result column="deptName" property="deptName" /> </association> </resultMap> <select id="getEmpAndDept" resultMap="EmployeeResult"> select e.empId,e.empName,d.deptId,d.deptName from t_emp e,t_dept d where e.deptId=d.deptId </select>
1.3 分步查询
EmployeeMapper.xml配置
<resultMap type="com.entity.Employee" id="EmployeeResult"> <id column="empId" property="empId" /> <result column="empName" property="empName" /> <!-- association定义关联对象的封装规则 select:表明当前属性是调用select指定的方法查出的结果 column:指定将哪一列的值传给这个方法 流程:使用select指定的方法(传入column指定的这列参数的值)查出对象,并封装给property指定的属性 --> <association property="empDept" select="com.entity.DepartmentMapper.getDeptById" column="deptId"> </association> </resultMap> <select id="getEmpAndDept" resultMap="EmployeeResult"> select * from t_emp </select>
DepartmentMapper.xml配置
<select id="getDeptById" resultType="com.entity.Department"> select deptId,deptName from t_dept where deptId=#{deptId} </select>
2.一对多关联
声明两个实体类
部门类
package com.entity; import java.util.List; import java.util.UUID; /** * 部门类 * * @author yyx 2019年9月17日 */ public class Department { /** * 部门ID */ private String deptId; /** * 部门名称 */ private String deptName; private List<Employee> deptEmps; public Department() { super(); } public Department(String deptId, String deptName) { super(); this.deptId = deptId; this.deptName = deptName; } public String getDeptId() { return deptId; } public void setDeptId(String deptId) { this.deptId = deptId; } public String getDeptName() { return deptName; } public void setDeptName(String deptName) { this.deptName = deptName; } @Override public String toString() { return "Department [deptId=" + deptId + ", deptName=" + deptName + ", deptEmps=" + deptEmps+ "]"; } public static void main(String[] args) { String str = UUID.randomUUID().toString(); System.out.println(str); } }
雇员类
package com.entity; import java.util.UUID; /** * 雇员类 * * @author yyx 2019年9月17日 */ public class Employee { /** * 雇员ID */ private String empId; /** * 雇员名称 */ private String empName; public Employee() { super(); } public Employee(String empId, String empName) { super(); this.empId = empId; this.empName = empName; } public String getEmpId() { return empId; } public void setEmpId(String empId) { this.empId = empId; } public String getEmpName() { return empName; } public void setEmpName(String empName) { this.empName = empName; } @Override public String toString() { return "Employee [empId=" + empId + ", empName=" + empName + "]"; } public static void main(String[] args) { String str = UUID.randomUUID().toString(); System.out.println(str); } }
2.1 单步查询
DepartmentMapper.xml配置
<resultMap type="com.entity.Department" id="DepartmentResult"> <id column="deptId" property="deptId" /> <result column="deptName" property="deptName" /> <collection property="deptEmps" ofType="com.entity.Employee"> <id column="empId" property="empId" /> <result column="empName" property="empName" /> </collection> </resultMap> <select id="getDeptAndEmps" resultMap="DepartmentResult"> select d.deptId,d.deptName,e.empId,e.empName from t_dept d left join t_emp e on d.deptId=e.deptId </select>
2.2 分步查询
DepartmentMapper.xml配置
<resultMap type="com.entity.Department" id="DepartmentResult"> <id column="deptId" property="deptId" /> <id column="deptName" property="deptName" /> <!-- 扩展:多列的值传递过去: 将多列的值封装map传递; column="{key1=column1,key2=column2}" fetchType="lazy":表示使用延迟加载; - lazy:延迟 - eager:立即 --> <collection property="deptEmps" select="com.dao.EmployeeMapper.getEmps" column="deptId" fetchType="lazy"></collection> </resultMap> <select id="getDeptAndEmps" resultMap="DepartmentResult"> select * from t_dept </select>
EmployeeMapper.xml配置
<resultMap type="com.entity.Employee" id="EmployeeResult"> <id column="empId" property="empId" /> <result column="empName" property="empName" /> </resultMap> <select id="getEmps" resultMap="EmployeeResult"> select * from t_emp where deptId=#{deptId} </select>
一对一、多对一数据库语句
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
/* SQLyog Ultimate v12.09 (64 bit) MySQL - 5.7.9-log : Database - db_mybatis_associate ********************************************************************* */ /*!40101 SET NAMES utf8 */; /*!40101 SET SQL_MODE=''*/; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; CREATE DATABASE /*!32312 IF NOT EXISTS*/`db_mybatis_associate` /*!40100 DEFAULT CHARACTER SET utf8 */; USE `db_mybatis_associate`; /*Table structure for table `t_dept` */ DROP TABLE IF EXISTS `t_dept`; CREATE TABLE `t_dept` ( `deptId` varchar(50) NOT NULL, `deptName` varchar(20) NOT NULL, PRIMARY KEY (`deptId`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*Data for the table `t_dept` */ insert into `t_dept`(`deptId`,`deptName`) values ('6cf35a30-3b09-432b-bae6-14ab8cb6c964','销售部'),('7135aeab-f50e-4db2-b0ea-6cd007223203','生产部'); /*Table structure for table `t_emp` */ DROP TABLE IF EXISTS `t_emp`; CREATE TABLE `t_emp` ( `empId` varchar(50) NOT NULL, `empName` varchar(20) NOT NULL, `deptId` varchar(50) NOT NULL, PRIMARY KEY (`empId`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*Data for the table `t_emp` */ insert into `t_emp`(`empId`,`empName`,`deptId`) values ('b14af873-ff15-45d3-ab37-8a2718d8fb35','杜甫','6cf35a30-3b09-432b-bae6-14ab8cb6c964'),('cebdaba7-bca3-4812-a52f-e9b0d38a13e4','李斯','6cf35a30-3b09-432b-bae6-14ab8cb6c964'); /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;