这篇文章主要讲解内容:
1. 中resultMap属性的使用
2.一对一关联查询
3.一对一的延迟加载
说明性的文字都以注解的方式说明,不再单独列出。
1.数据准备
create table DEPT
(
DEPTNO int(2) not null,
DNAME varchar(14),
LOC varchar(13)
)
;
alter table DEPT
add constraint PK_DEPT primary key (DEPTNO);
create table EMP
(
EMPNO int(4) not null,
ENAME varchar(10),
JOB varchar(9),
MGR int(4),
HIREDATE date,
SAL int(7 ),
COMM int(7 ),
DEPTNO int(2)
)
;
alter table EMP
add constraint PK_EMP primary key (EMPNO);
alter table EMP
add constraint FK_DEPTNO foreign key (DEPTNO)
references DEPT (DEPTNO);
insert into DEPT (DEPTNO, DNAME, LOC)
values (10, 'ACCOUNTING', 'NEW YORK');
insert into DEPT (DEPTNO, DNAME, LOC)
values (20, 'RESEARCH', 'DALLAS');
insert into DEPT (DEPTNO, DNAME, LOC)
values (30, 'SALES', 'CHICAGO');
insert into DEPT (DEPTNO, DNAME, LOC)
values (40, 'OPERATIONS', 'BOSTON');
commit;
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7369, 'SMITH', 'CLERK', 7902, str_to_date('17-12-1980', '%d-%m-%Y'), 800, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7499, 'ALLEN', 'SALESMAN', 7698, str_to_date('20-02-1981', '%d-%m-%Y'), 1600, 300, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7521, 'WARD', 'SALESMAN', 7698, str_to_date('22-02-1981', '%d-%m-%Y'), 1250, 500, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7566, 'JONES', 'MANAGER', 7839, str_to_date('02-04-1981', '%d-%m-%Y'), 2975, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7654, 'MARTIN', 'SALESMAN', 7698, str_to_date('28-09-1981', '%d-%m-%Y'), 1250, 1400, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7698, 'BLAKE', 'MANAGER', 7839, str_to_date('01-05-1981', '%d-%m-%Y'), 2850, null, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7782, 'CLARK', 'MANAGER', 7839, str_to_date('09-06-1981', '%d-%m-%Y'), 2450, null, 10);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7788, 'SCOTT', 'ANALYST', 7566, str_to_date('19-04-1987', '%d-%m-%Y'), 3000, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7839, 'KING', 'PRESIDENT', null, str_to_date('17-11-1981', '%d-%m-%Y'), 5000, null, 10);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7844, 'TURNER', 'SALESMAN', 7698, str_to_date('08-09-1981', '%d-%m-%Y'), 1500, 0, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7876, 'ADAMS', 'CLERK', 7788, str_to_date('23-05-1987', '%d-%m-%Y'), 1100, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7900, 'JAMES', 'CLERK', 7698, str_to_date('03-12-1981', '%d-%m-%Y'), 950, null, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7902, 'FORD', 'ANALYST', 7566, str_to_date('03-12-1981', '%d-%m-%Y'), 3000, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7934, 'MILLER', 'CLERK', 7782, str_to_date('23-01-1982', '%d-%m-%Y'), 1300, null, 10);
commit;
create table salgrade (grade int(4),losal int(4),hisal int(4));
insert into salgrade (grade,losal,hisal) values (1,700,1200);
insert into salgrade (grade,losal,hisal) values (2,1201,1400);
insert into salgrade (grade,losal,hisal) values (3,1401,2000);
insert into salgrade (grade,losal,hisal) values (4,2001,3000);
insert into salgrade (grade,losal,hisal) values (5,3001,9999);
2. 数据库配置文件db.properties
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mydb
jdbc.username=root
jdbc.password=admin
3. POJO(Emp.java和Dept.java)
Emp.java
package com.qwy.bean;
import java.util.Date;
/**
* @author qwy
* @create 2021-04-08 21:18
**/
public class Emp {
private Integer empno;
private String ename;
private String job;
private Integer mgr;
private Date hiredate;
private float sal;
private float comm;
private Integer deptno;
/*添加部门信息*/
private Dept dept;
public Emp() {
}
public Emp(Integer empno, String ename, String job, Integer mgr, Date hiredate, float sal, float comm, Integer deptno) {
this.empno = empno;
this.ename = ename;
this.job = job;
this.mgr = mgr;
this.hiredate = hiredate;
this.sal = sal;
this.comm = comm;
this.deptno = deptno;
}
public Dept getDept() {
return dept;
}
public void setDept(Dept dept) {
this.dept = dept;
}
public Integer getEmpno() {
return empno;
}
public void setEmpno(Integer empno) {
this.empno = empno;
}
public String getEname() {
return ename;
}
public void setEname(String ename) {
this.ename = ename;
}
public String getJob() {
return job;
}
public void setJob(String job) {
this.job = job;
}
public Integer getMgr() {
return mgr;
}
public void setMgr(Integer mgr) {
this.mgr = mgr;
}
public Date getHiredate() {
return hiredate;
}
public void setHiredate(Date hiredate) {
this.hiredate = hiredate;
}
public float getSal() {
return sal;
}
public void setSal(float sal) {
this.sal = sal;
}
public float getComm() {
return comm;
}
public void setComm(float comm) {
this.comm = comm;
}
public Integer getDeptno() {
return deptno;
}
public void setDeptno(Integer deptno) {
this.deptno = deptno;
}
@Override
public String toString() {
return "Emp{" +
"empno=" + empno +
", ename='" + ename + '\'' +
", job='" + job + '\'' +
", mgr=" + mgr +
", hiredate=" + hiredate +
", sal=" + sal +
", comm=" + comm +
", deptno=" + deptno +
'}';
}
}
Dept.java
package com.qwy.bean;
/**
* @author qwy
* @create 2021-04-08 21:25
**/
public class Dept {
private Integer deptno;
private String dname;
private String loc;
public Dept() {
}
public Dept(Integer deptno, String dname, String loc) {
this.deptno = deptno;
this.dname = dname;
this.loc = loc;
}
public Integer getDeptno() {
return deptno;
}
public void setDeptno(Integer deptno) {
this.deptno = deptno;
}
public String getDname() {
return dname;
}
public void setDname(String dname) {
this.dname = dname;
}
public String getLoc() {
return loc;
}
public void setLoc(String loc) {
this.loc = loc;
}
@Override
public String toString() {
return "Dept{" +
"deptno=" + deptno +
", dname='" + dname + '\'' +
", loc='" + loc + '\'' +
'}';
}
}
4. 接口EmpMapper.java
package com.qwy.mapper;
import com.qwy.bean.Emp;
/**
* @author qwy
* @create 2021-04-08 21:28
**/
public interface EmpMapper {
/*
* 查询员工信息,并获取员工对应的部门信息*/
public Emp getEmpAndDeptByEmpno(Integer empno);
}
5. 全局配置文件mybatis-config.xml
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!--导入资源文件(数据库的配置信息)-->
<properties resource="db.properties"></properties>
<settings>
<setting name="cacheEnabled" value="true"/>
<setting name="lazyLoadingEnabled" value="true"/>
<setting name="multipleResultSetsEnabled" value="true"/> <setting name="useColumnLabel" value="true"/>
<setting name="useGeneratedKeys" value="false"/>
<setting name="autoMappingBehavior" value="PARTIAL"/>
<setting name="autoMappingUnknownColumnBehavior" value="WARNING"/>
<setting name="defaultExecutorType" value="SIMPLE"/>
<setting name="defaultStatementTimeout" value="25"/>
<setting name="defaultFetchSize" value="100"/>
<setting name="safeRowBoundsEnabled" value="false"/>
<setting name="mapUnderscoreToCamelCase" value="false"/> <setting name="localCacheScope" value="SESSION"/>
<setting name="jdbcTypeForNull" value="OTHER"/>
<setting name="lazyLoadTriggerMethods" value="equals,clone,hashCode,toString"/>
</settings>
<!--配置环境-->
<environments default="development">
<environment id="development">
<!--配置事务管理器-->
<transactionManager type="JDBC"/>
<!--配置数据源-->
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<!--加载映射文件信息-->
<package name="com.qwy.mapper"/>
</mappers>
</configuration>
6. 日志log4j.properties
log4j.rootLogger=DEBUG,stdout
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
1og4j.appender.stdout.layout.ConversionPattern=%5p [%t]-%m%n
7. 映射文件EmpMapper.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.qwy.mapper.EmpMapper">
<!--
resultMap:
id: 命名空间下的唯一标识,供其他地方引用
type:指定返回的类型
-->
<resultMap id="empAndDeptMap" type="com.qwy.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:指定对象中的属性
javaType:指定该属性的类型
-->
<association property="dept" javaType="com.qwy.bean.Dept">
<!--指定主键列-->
<id property="deptno" column="dno"></id>
<!--普通字段的映射-->
<result property="dname" column="dname"></result>
<result property="loc" column="loc"></result>
</association>
</resultMap>
<!--public Emp getEmpAndDeptByEmpno(Integer empno);
注意:这里既要查询员工信息,又要查询员工对应的部门信息(一对一的关系)
默认情况下resultType只有查询出的字段名和POJO中的属性名一致才能将查询的信息封装到POJO中,
故这里不能简单使用resultType来封装查询的数据,且这里Emp中有deptno,Dept中也有deptno
解决方案:
1.如果两个表中的字段名或POJO的属性名不一致可以使用别名方式解决(此处不能使用)
2.使用resultMap替代resultType,此处使用此方式
-->
<select id="getEmpAndDeptByEmpno" resultMap="empAndDeptMap" parameterType="int">
select e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,e.deptno,d.deptno dno,d.dname,d.loc
from emp e ,dept d
where e.deptno= d.deptno and empno=#{empno}
</select>
</mapper>
8. 测试类
package com.qwy.test;
import com.qwy.bean.Dept;
import com.qwy.bean.Emp;
import com.qwy.mapper.EmpMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
/**
* @author qwy
* @create 2021-04-08 21:39
**/
public class TestMybatis {
private SqlSessionFactory sqlSessionFactory;
@Before
public void getSqlSessionFactory() throws IOException {
String resource = "mybatis-config.xml";
//加载核心配置文件
InputStream inputStream = Resources.getResourceAsStream(resource);
//创建SqlSessionFactory工厂
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
}
@Test
public void testGetEmpAndDeptByEmpno(){
SqlSession sqlSession = sqlSessionFactory.openSession();
try{
EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);
Emp emp = empMapper.getEmpAndDeptByEmpno(7788);
System.out.println("emp = " + emp);
//获取对应的部门信息
/* Dept dept = emp.getDept();
System.out.println("dept = " + dept);*/
}finally {
sqlSession.close();
}
}
}
输出:
SQL:select e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,e.deptno,d.deptno dno,d.dname,d.loc from emp e ,dept d where e.deptno= d.deptno and empno=?
从运行结果来看:我们可以得到员工信息,也能得到员工对应的部门信息,但是我们无论我们是否需要部门信息,这条语句都会执行,这就增加了数据库的负担。
以下使用懒加载方式实现一对一映射:
9. 编写DeptMapper.java接口
package com.qwy.mapper;
import com.qwy.bean.Dept;
/**
* @author qwy
* @create 2021-04-08 22:18
**/
public interface DeptMapper {
public Dept getDeptByDeptno(Integer deptno);
}
10. 编写接口对应的映射文件 DeptMapper.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.qwy.mapper.DeptMapper">
<!--public Dept getDeptByDeptno(Integer deptno);-->
<select id="getDeptByDeptno" parameterType="int" resultType="com.qwy.bean.Dept">
select deptno,dname,loc from dept where deptno=#{deptno}
</select>
</mapper>
11. 在全局文件中配置懒加载项
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!--导入资源文件(数据库的配置信息)-->
<properties resource="db.properties"></properties>
<settings>
<setting name="cacheEnabled" value="true"/>
<!--当设置为‘true’的时候,懒加载的对象可能被任何懒属性全部加载。否则,每个属性都按需加载。-->
<setting name="aggressiveLazyLoading" value="false"/>
<!--全局性设置懒加载。如果设为‘false’,则所有相关联的都会被初始化加载。-->
<setting name="lazyLoadingEnabled" value="true"/>
<setting name="multipleResultSetsEnabled" value="true"/>
<setting name="useColumnLabel" value="true"/>
<setting name="useGeneratedKeys" value="false"/>
<setting name="autoMappingBehavior" value="PARTIAL"/>
<setting name="autoMappingUnknownColumnBehavior" value="WARNING"/>
<setting name="defaultExecutorType" value="SIMPLE"/>
<setting name="defaultStatementTimeout" value="25"/>
<setting name="defaultFetchSize" value="100"/>
<setting name="safeRowBoundsEnabled" value="false"/>
<setting name="mapUnderscoreToCamelCase" value="false"/>
<setting name="localCacheScope" value="SESSION"/>
<setting name="jdbcTypeForNull" value="OTHER"/>
<setting name="lazyLoadTriggerMethods" value=""/>
<!--equals,clone,hashCode,toString 会影响懒加载-->
<!-- <setting name="lazyLoadTriggerMethods" value="equals,clone,hashCode,toString"/>-->
</settings>
<!--配置环境-->
<environments default="development">
<environment id="development">
<!--配置事务管理器-->
<transactionManager type="JDBC"/>
<!--配置数据源-->
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<!--加载映射文件信息-->
<package name="com.qwy.mapper"/>
</mappers>
</configuration>
对懒加载有影响的三项:
<!--equals,clone,hashCode,toString 会影响懒加载-->
<!-- <setting name="lazyLoadTriggerMethods" value="equals,clone,hashCode,toString"/>-->
<!--当设置为‘true’的时候,懒加载的对象可能被任何懒属性全部加载。否则,每个属性都按需加载。-->
<setting name="aggressiveLazyLoading" value="false"/>
<!--全局性设置懒加载。如果设为‘false’,则所有相关联的都会被初始化加载。-->
<setting name="lazyLoadingEnabled" value="true"/>
12. EmpMapper.java接口新添方法
package com.qwy.mapper;
import com.qwy.bean.Emp;
/**
* @author qwy
* @create 2021-04-08 21:28
**/
public interface EmpMapper {
/*
* 查询员工信息,并获取员工对应的部门信息*/
public Emp getEmpAndDeptByEmpno(Integer empno);
public Emp getEmpAndDeptByEmpno2(Integer empno);
}
13. 修改EmpMapper.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.qwy.mapper.EmpMapper">
<!--
resultMap:
id: 命名空间下的唯一标识,供其他地方引用
type:指定返回的类型
-->
<resultMap id="empAndDeptMap" type="com.qwy.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:指定对象中的属性
javaType:指定该属性的类型
-->
<association property="dept" javaType="com.qwy.bean.Dept">
<!--指定主键列-->
<id property="deptno" column="dno"></id>
<!--普通字段的映射-->
<result property="dname" column="dname"></result>
<result property="loc" column="loc"></result>
</association>
</resultMap>
<!--public Emp getEmpAndDeptByEmpno(Integer empno);
注意:这里既要查询员工信息,又要查询员工对应的部门信息(一对一的关系)
默认情况下resultType只有查询出的字段名和POJO中的属性名一致才能将查询的信息封装到POJO中,
故这里不能简单使用resultType来封装查询的数据,且这里Emp中有deptno,Dept中也有deptno
解决方案:
1.如果两个表中的字段名或POJO的属性名不一致可以使用别名方式解决(此处不能使用)
2.使用resultMap替代resultType,此处使用此方式
-->
<select id="getEmpAndDeptByEmpno" resultMap="empAndDeptMap" parameterType="int">
select e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,e.deptno,d.deptno dno,d.dname,d.loc
from emp e ,dept d
where e.deptno= d.deptno and empno=#{empno}
</select>
<!--
public Emp getEmpAndDeptByEmpno2(Integer empno);
-->
<resultMap id="empAndDept2" type="com.qwy.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>
<!--
配置一对一关系:
property:指定关联属性名
javaType:指定关联属性的类型
column:指定关联的列
fetchType:设置懒加载(按需加载)
select:引用其他查询,如果是其他命名空间下的需要指定命名空间
-->
<association property="dept" javaType="com.qwy.bean.Dept" column="deptno"
fetchType="lazy" select="com.qwy.mapper.DeptMapper.getDeptByDeptno" >
</association>
</resultMap>
<select id="getEmpAndDeptByEmpno2" parameterType="int" resultMap="empAndDept2">
select e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,e.deptno from emp e where empno=#{empno}
</select>
</mapper>
14. 修改测试类
package com.qwy.test;
import com.qwy.bean.Dept;
import com.qwy.bean.Emp;
import com.qwy.mapper.EmpMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
/**
* @author qwy
* @create 2021-04-08 21:39
**/
public class TestMybatis {
private SqlSessionFactory sqlSessionFactory;
@Before
public void getSqlSessionFactory() throws IOException {
String resource = "mybatis-config.xml";
//加载核心配置文件
InputStream inputStream = Resources.getResourceAsStream(resource);
//创建SqlSessionFactory工厂
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
}
@Test
public void testGetEmpAndDeptByEmpno(){
SqlSession sqlSession = sqlSessionFactory.openSession();
try{
EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);
Emp emp = empMapper.getEmpAndDeptByEmpno(7788);
System.out.println("emp = " + emp);
//获取对应的部门信息
/* Dept dept = emp.getDept();
System.out.println("dept = " + dept);*/
}finally {
sqlSession.close();
}
}
@Test
public void testGetEmpAndDeptByEmpno2(){
SqlSession sqlSession = sqlSessionFactory.openSession();
try{
EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);
//执行select e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,e.deptno from emp e where empno=?
Emp emp = empMapper.getEmpAndDeptByEmpno2(7788);
System.out.println("emp = " + emp);
//获取对应的部门信息,在配置中使用了懒加载,只有使用时才会查询
// select deptno,dname,loc from dept where deptno=?
/* Dept dept = emp.getDept();
System.out.println("dept = " + dept);*/
}finally {
sqlSession.close();
}
}
}
每一点点付出,都值得尊重源码下载