目录
1、resultMap处理字段和属性的映射关系(字段和属性名不一致)
1.2、解决办法二:在核心配置文件的 settings 标签设置全局配置
2.4.2、区别二:若需要执行第二个SQL语句,执行顺序不一样
0、准备工作
创建新模块 MyBatis_demo3
创建两张数据表并添加一些数据
1、resultMap处理字段和属性的映射关系(字段和属性名不一致)
若字段名和属性名不一致,则查询的结果中,属性名和字段名不一致的属性值为 null
1.1、解决办法一:在 SQL 语句中起别名
① 在 EmpMapper 添加方法
/**
* 查询所有员工信息
*/
List<Emp> getAllEmp();
② 在 EmpMapper.xml 添加 SQL 语句
<!-- List<Emp> getAllEmp() -->
<select id="getAllEmp" resultType="Emp">
select eid, emp_name empName, age, sex, email from t_emp
</select>
③ 测试方法
@Test
public void testGetAllEmp(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
List<Emp> allEmp = mapper.getAllEmp();
System.out.println(allEmp);
}
④ 输出结果
DEBUG 03-17 00:06:30,494 ==> Preparing: select eid, emp_name empName, age, sex, did email from t_emp (BaseJdbcLogger.java:137)
DEBUG 03-17 00:06:30,534 ==> Parameters: (BaseJdbcLogger.java:137)
DEBUG 03-17 00:06:30,558 <== Total: 5 (BaseJdbcLogger.java:137)
[Emp{eid=1, empName='张三', age=33, sex='男', email='1'}, Emp{eid=2, empName='李四', age=55, sex='男', email='2'}, Emp{eid=3, empName='王五', age=16, sex='女', email='3'}, Emp{eid=4, empName='赵六', age=37, sex='男', email='1'}, Emp{eid=5, empName='田七', age=54, sex='女', email='null'}]
1.2、解决办法二:在核心配置文件的 settings 标签设置全局配置
① 在核心配置文件 mybatis-config.xml 中的 settings 标签进行设置,注意 settings 标签要在 properties 标签之后
<!-- 设置MyBatis的全局配置 -->
<settings>
<!-- mapUnderscoreToCamelCase:将下划线 _ 自动映射为驼峰,例如emp_name映射为empName -->
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
② SQL 语句取消设置别名
<!-- List<Emp> getAllEmp() -->
<select id="getAllEmp" resultType="Emp">
<!-- select eid, emp_name empName, age, sex, email from t_emp -->
select * from t_emp
</select>
③ 运行,输出结果:
DEBUG 03-17 20:27:41,506 ==> Preparing: select * from t_emp (BaseJdbcLogger.java:137)
DEBUG 03-17 20:27:41,533 ==> Parameters: (BaseJdbcLogger.java:137)
DEBUG 03-17 20:27:41,556 <== Total: 5 (BaseJdbcLogger.java:137)
[Emp{eid=1, empName='张三', age=33, sex='男', email='zhangsan@qq.com'}, Emp{eid=2, empName='李四', age=55, sex='男', email='lisi@qq.com'}, Emp{eid=3, empName='王五', age=16, sex='女', email='wangwu@qq.com'}, Emp{eid=4, empName='赵六', age=37, sex='男', email='zhaoliu@qq.com'}, Emp{eid=5, empName='田七', age=54, sex='女', email='null'}]
1.3、解决办法三:使用 resultMap
① 在 EmpMapper.xml 设置 resultMap 并更改 SQL 语句,并将 mybatis-config.xml 的 settings 标签注释掉
<!--
resultMap:设置自定义映射关系
id:唯一标识,不能重复
type:设置映射关系中的实体类类型
-->
<resultMap id="empResultMap" type="Emp">
<!--
id 设置主键的映射关系
result 设置普通字段的映射关系
properties:设置映射关系中的属性名,必须是type所设置的实体类类型中的属性名
column:设置映射关系中的字段名,必须是sql语句查询出的字段名
-->
<id property="eid" column="eid"></id>
<result property="empName" column="emp_name"></result>
<result property="age" column="age"></result>
<result property="sex" column="sex"></result>
<result property="email" column="email"></result>
</resultMap>
<select id="getAllEmp" resultMap="empResultMap">
select * from t_emp
</select>
② 测试,输出结果:
DEBUG 03-17 20:48:10,966 ==> Preparing: select * from t_emp (BaseJdbcLogger.java:137)
DEBUG 03-17 20:48:10,998 ==> Parameters: (BaseJdbcLogger.java:137)
DEBUG 03-17 20:48:11,018 <== Total: 5 (BaseJdbcLogger.java:137)
[Emp{eid=1, empName='张三', age=33, sex='男', email='zhangsan@qq.com'}, Emp{eid=2, empName='李四', age=55, sex='男', email='lisi@qq.com'}, Emp{eid=3, empName='王五', age=16, sex='女', email='wangwu@qq.com'}, Emp{eid=4, empName='赵六', age=37, sex='男', email='zhaoliu@qq.com'}, Emp{eid=5, empName='田七', age=54, sex='女', email='null'}]
2、多对一映射处理
首先在 Emp 类添加 private Dept dept; 属性
2.1、方法一:使用 resultMap 级联属性赋值
① 在 EmpMapper 接口添加方法
/**
* 查询员工及其所对应的部门信息
*/
Emp getEmpAndDept(@Param("eid") Integer eid);
② 在 EmpMapper.xml 添加 SQL 语句
<!-- Emp getEmpAndDept(@Param("eid") Integer eid) -->
<resultMap id="empAndDeptResultMapOne" type="Emp">
<id property="eid" column="eid"></id>
<result property="empName" column="emp_name"></result>
<result property="age" column="age"></result>
<result property="sex" column="sex"></result>
<result property="email" column="email"></result>
<result property="dept.did" column="did"></result>
<result property="dept.deptName" column="dept_name"></result>
</resultMap>
<select id="getEmpAndDept" resultMap="empAndDeptResultMapOne">
select * from t_emp left join t_dept on t_emp.did = t_dept.did where t_emp.eid = #{eid}
</select>
③ 测试,输出结果
DEBUG 03-17 21:58:25,724 ==> Preparing: select * from t_emp left join t_dept on t_emp.did = t_dept.did where t_emp.eid = ? (BaseJdbcLogger.java:137)
DEBUG 03-17 21:58:25,751 ==> Parameters: 3(Integer) (BaseJdbcLogger.java:137)
DEBUG 03-17 21:58:25,773 <== Total: 1 (BaseJdbcLogger.java:137)
Emp{eid=3, empName='王五', age=16, sex='女', email='wangwu@qq.com', dept=Dept{did=3, deptName='C'}}
2.2、方式二:使用 association
① EmpMapper.xml 的 SQL 语句
<resultMap id="empAndDeptResultMapTwo" type="Emp">
<id property="eid" column="eid"></id>
<result property="empName" column="emp_name"></result>
<result property="age" column="age"></result>
<result property="sex" column="sex"></result>
<result property="email" column="email"></result>
<!--
association:处理多对一的映射关系
property:需要处理多对一的映射关系的属性名
javaType:property对应的类型
-->
<association property="dept" javaType="Dept">
<id property="did" column="did"></id>
<result property="deptName" column="dept_name"></result>
</association>
</resultMap>
<select id="getEmpAndDept" resultMap="empAndDeptResultMapTwo">
select * from t_emp left join t_dept on t_emp.did = t_dept.did where t_emp.eid = #{eid}
</select>
② 运行测试,输出结果:
DEBUG 03-17 22:20:11,445 ==> Preparing: select * from t_emp left join t_dept on t_emp.did = t_dept.did where t_emp.eid = ? (BaseJdbcLogger.java:137)
DEBUG 03-17 22:20:11,483 ==> Parameters: 3(Integer) (BaseJdbcLogger.java:137)
DEBUG 03-17 22:20:11,504 <== Total: 1 (BaseJdbcLogger.java:137)
Emp{eid=3, empName='王五', age=16, sex='女', email='wangwu@qq.com', dept=Dept{did=3, deptName='C'}}
2.3、方式三:分布查询与 association
① 在 EmpMapper 接口添加分步查询的第一步
/**
* 通过分布查询查询员工及其对应的部门信息
* 分布查询第一步:查询员工信息
*/
Emp getEmpAndDeptByStepOne(@Param("eid") Integer eid);
② 在 EmpMapper.xml 添加 SQL 语句及 resultMap
<!-- Emp getEmpAndDeptByStepOne(@Param("eid") Integer eid) -->
<resultMap id="empAndDeptByStepResultMap" type="Emp">
<id property="eid" column="eid"></id>
<result property="empName" column="emp_name"></result>
<result property="age" column="age"></result>
<result property="sex" column="sex"></result>
<result property="email" column="email"></result>
<!--
select:设置分步查询的SQL的唯一标识,即 namespace.SQLId 或 mapper接口的全类名.方法名
column:设置分步查询的条件
-->
<association property="dept" select="com.zyj.mybatis.mapper.DeptMapper.getEmpAndDeptByStepTwo" column="did"></association>
</resultMap>
<select id="getEmpAndDeptByStepOne" resultMap="empAndDeptByStepResultMap">
select * from t_emp where eid = #{eid}
</select>
③ 在 DeptMapper 接口添加分步查询的第二步
/**
* 通过分布查询查询员工及其对应的部门信息
* 分布查询第二步:通过did查询员工所对应的部门信息
*/
Dept getEmpAndDeptByStepTwo(@Param("did") Integer did);
④ 在 DeptMapper.xml 添加 SQL 语句
<!-- Dept getEmpAndDeptByStepTwo(@Param("did") Integer did) -->
<select id="getEmpAndDeptByStepTwo" resultType="Dept">
select * from t_dept where did = #{did}
</select>
⑤ 测试方法:
@Test
public void testGetEmpAndDeptByStep(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
Emp empAndDeptByStepOne = mapper.getEmpAndDeptByStepOne(3);
System.out.println(empAndDeptByStepOne);
}
⑥ 输出结果:
DEBUG 03-17 22:42:20,636 ==> Preparing: select * from t_emp where eid = ? (BaseJdbcLogger.java:137)
DEBUG 03-17 22:42:20,669 ==> Parameters: 3(Integer) (BaseJdbcLogger.java:137)
DEBUG 03-17 22:42:20,689 ====> Preparing: select * from t_dept where did = ? (BaseJdbcLogger.java:137)
DEBUG 03-17 22:42:20,690 ====> Parameters: 3(Integer) (BaseJdbcLogger.java:137)
DEBUG 03-17 22:42:20,692 <==== Total: 1 (BaseJdbcLogger.java:137)
DEBUG 03-17 22:42:20,693 <== Total: 1 (BaseJdbcLogger.java:137)
Emp{eid=3, empName='王五', age=16, sex='女', email='wangwu@qq.com', dept=Dept{did=3, deptName='C'}}
2.4、分步查询的优点:延迟加载
分步查询的优点:可以实现延迟加载,但是必须在核心配置文件中设置全局配置信息(在 settings 标签中进行设置):
-
lazyLoadingEnabled:延迟加载的全局开关。当开启时,所有关联对象都会延迟加载。这里需要设置为 true
-
aggressiveLazyLoading:当开启时,任何方法的调用都会加载该对象的所有属性。 否则,每个属性会按需加载。这里需要设置为 false(mybatis 版本小于等于3.4.1时,默认为 true,其他版本默认为 false,所以高版本可以不用设置)
此时就可以实现按需加载,获取的数据是什么,就只会执行相应的sql。此时可通过association和collection中的fetchType属性设置当前的分步查询是否使用延迟加载,fetchType="lazy(延迟加载)|eager(立即加载)"
2.4.1、区别一:按需加载
① 在没有开启延迟加载时,运行以下方法:
@Test
public void testLazyLoading(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
Emp empAndDeptByStepOne = mapper.getEmpAndDeptByStepOne(3);
System.out.println(empAndDeptByStepOne.getEmpName());
}
输出结果如下:
DEBUG 03-17 22:57:45,143 ==> Preparing: select * from t_emp where eid = ? (BaseJdbcLogger.java:137)
DEBUG 03-17 22:57:45,169 ==> Parameters: 3(Integer) (BaseJdbcLogger.java:137)
DEBUG 03-17 22:57:45,187 ====> Preparing: select * from t_dept where did = ? (BaseJdbcLogger.java:137)
DEBUG 03-17 22:57:45,187 ====> Parameters: 3(Integer) (BaseJdbcLogger.java:137)
DEBUG 03-17 22:57:45,190 <==== Total: 1 (BaseJdbcLogger.java:137)
DEBUG 03-17 22:57:45,191 <== Total: 1 (BaseJdbcLogger.java:137)
王五
可以看到执行了两个 SQL 语句
② 在 mybatis-config.xml 开启延迟加载
<!-- 设置MyBatis的全局配置 -->
<settings>
<!-- mapUnderscoreToCamelCase:将下划线 _ 自动映射为驼峰,例如emp_name映射为empName -->
<setting name="mapUnderscoreToCamelCase" value="true"/>
<!-- 开启延迟加载 -->
<setting name="lazyLoadingEnabled" value="true"/>
</settings>
然后再次运行上面的方法,输出结果如下
DEBUG 03-17 22:59:01,553 ==> Preparing: select * from t_emp where eid = ? (BaseJdbcLogger.java:137)
DEBUG 03-17 22:59:01,588 ==> Parameters: 3(Integer) (BaseJdbcLogger.java:137)
DEBUG 03-17 22:59:01,664 <== Total: 1 (BaseJdbcLogger.java:137)
王五
可以看到只执行了一个 SQL 语句
2.4.2、区别二:若需要执行第二个SQL语句,执行顺序不一样
若没有开启延迟加载,所有SQL语句是一起执行的
若开启了延迟加载,SQL 语句不是一起执行
① 若没有开启延迟加载,执行以下方法
@Test
public void testLazyLoading(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
Emp empAndDeptByStepOne = mapper.getEmpAndDeptByStepOne(3);
System.out.println(empAndDeptByStepOne.getEmpName());
System.out.println("++++++++++++++++++++++++++++++++++++++");
System.out.println(empAndDeptByStepOne.getDept());
}
输出结果如下:
DEBUG 03-17 23:05:50,835 ==> Preparing: select * from t_emp where eid = ? (BaseJdbcLogger.java:137)
DEBUG 03-17 23:05:50,887 ==> Parameters: 3(Integer) (BaseJdbcLogger.java:137)
DEBUG 03-17 23:05:50,920 ====> Preparing: select * from t_dept where did = ? (BaseJdbcLogger.java:137)
DEBUG 03-17 23:05:50,920 ====> Parameters: 3(Integer) (BaseJdbcLogger.java:137)
DEBUG 03-17 23:05:50,929 <==== Total: 1 (BaseJdbcLogger.java:137)
DEBUG 03-17 23:05:50,930 <== Total: 1 (BaseJdbcLogger.java:137)
王五
++++++++++++++++++++++++++++++++++++++
Dept{did=3, deptName='C'}
可以看到两条SQL语句是一起执行的
② 开启延迟加载,再次执行上面的方法,输出结果如下:
DEBUG 03-17 23:06:53,715 ==> Preparing: select * from t_emp where eid = ? (BaseJdbcLogger.java:137)
DEBUG 03-17 23:06:53,767 ==> Parameters: 3(Integer) (BaseJdbcLogger.java:137)
DEBUG 03-17 23:06:53,886 <== Total: 1 (BaseJdbcLogger.java:137)
王五
++++++++++++++++++++++++++++++++++++++
DEBUG 03-17 23:06:53,887 ==> Preparing: select * from t_dept where did = ? (BaseJdbcLogger.java:137)
DEBUG 03-17 23:06:53,888 ==> Parameters: 3(Integer) (BaseJdbcLogger.java:137)
DEBUG 03-17 23:06:53,890 <== Total: 1 (BaseJdbcLogger.java:137)
Dept{did=3, deptName='C'}
可以看到两条SQL语句不是同时执行
2.4.3、开启全局的延迟加载后设置立即加载效果
① 在分步查询的 association 标签设置 fetchType
<!-- Emp getEmpAndDeptByStepOne(@Param("eid") Integer eid) -->
<resultMap id="empAndDeptByStepResultMap" type="Emp">
<id property="eid" column="eid"></id>
<result property="empName" column="emp_name"></result>
<result property="age" column="age"></result>
<result property="sex" column="sex"></result>
<result property="email" column="email"></result>
<!--
select:设置分步查询的SQL的唯一标识,即 namespace.SQLId 或 mapper接口的全类名.方法名
column:设置分步查询的条件
fetchType:当开启了全局的延迟加载后,可通过此属性手动控制延迟加载的效果,若没有开启全局的延迟加载,不管是什么属性都是立即加载
fetchType="lazy" 延迟加载
fetchType="eager" 立即加载
-->
<association property="dept"
select="com.zyj.mybatis.mapper.DeptMapper.getEmpAndDeptByStepTwo"
column="did"
fetchType="eager"
></association>
</resultMap>
<select id="getEmpAndDeptByStepOne" resultMap="empAndDeptByStepResultMap">
select * from t_emp where eid = #{eid}
</select>
② 在开启了全局的延迟加载后,执行以下方法
@Test
public void testLazyLoading(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
Emp empAndDeptByStepOne = mapper.getEmpAndDeptByStepOne(3);
System.out.println(empAndDeptByStepOne.getEmpName());
System.out.println("++++++++++++++++++++++++++++++++++++++");
System.out.println(empAndDeptByStepOne.getDept());
}
③ 输出结果:
DEBUG 03-17 23:20:51,917 ==> Preparing: select * from t_emp where eid = ? (BaseJdbcLogger.java:137)
DEBUG 03-17 23:20:51,943 ==> Parameters: 3(Integer) (BaseJdbcLogger.java:137)
DEBUG 03-17 23:20:51,962 ====> Preparing: select * from t_dept where did = ? (BaseJdbcLogger.java:137)
DEBUG 03-17 23:20:51,963 ====> Parameters: 3(Integer) (BaseJdbcLogger.java:137)
DEBUG 03-17 23:20:51,965 <==== Total: 1 (BaseJdbcLogger.java:137)
DEBUG 03-17 23:20:51,965 <== Total: 1 (BaseJdbcLogger.java:137)
王五
++++++++++++++++++++++++++++++++++++++
Dept{did=3, deptName='C'}
进程已结束,退出代码为 0
可以看到SQL语句一起执行了
3、一对多映射处理
在 Dept 类中添加属性 private List<Emp> emps;
3.1、方法一:使用 collection
① 在 DeptMapper 接口添加方法
/**
* 获取部门及部门中所有的员工信息
*/
Dept getDeptAndEmp(@Param("did") Integer did);
② 在 DeptMapper.xml 添加 SQL 语句和 resultMap
<!-- Dept getDeptAndEmp(@Param("did") Integer did) -->
<resultMap id="deptAndEmpResultMap" type="Dept">
<id property="did" column="did"></id>
<result property="deptName" column="dept_name"></result>
<!--
collection:处理一对多映射关系
ofType:标识该属性所对应的集合中存储数据的的类型
-->
<collection property="emps" ofType="emp">
<id property="eid" column="eid"></id>
<result property="empName" column="emp_name"></result>
<result property="age" column="age"></result>
<result property="sex" column="sex"></result>
<result property="email" column="email"></result>
</collection>
</resultMap>
<select id="getDeptAndEmp" resultMap="deptAndEmpResultMap">
select * from t_dept left join t_emp on t_emp.did = t_dept.did where t_dept.did = #{did}
</select>
③ 测试方法
@Test
public void testDeptAndEmp(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
DeptMapper mapper = sqlSession.getMapper(DeptMapper.class);
Dept deptAndEmp = mapper.getDeptAndEmp(2);
System.out.println(deptAndEmp);
}
④ 输出结果
DEBUG 03-17 23:47:14,270 ==> Preparing: select * from t_dept left join t_emp on t_emp.did = t_dept.did where t_dept.did = ? (BaseJdbcLogger.java:137)
DEBUG 03-17 23:47:14,301 ==> Parameters: 2(Integer) (BaseJdbcLogger.java:137)
DEBUG 03-17 23:47:14,325 <== Total: 1 (BaseJdbcLogger.java:137)
Dept{did=2, deptName='B', emps=[Emp{eid=2, empName='李四', age=55, sex='男', email='lisi@qq.com', dept=null}]}
3.2、方法二:分步查询
① 在 DeptMapper 添加分步查询的第一步
/**
* 通过分步查询查询部门及部门中所有员工的信息
* 分步查询第一步:查询部门信息
*/
Dept getDeptAndEmpByStepOne(@Param("did") Integer did);
② 在 DeptMapper.xml 添加SQL语句及 resultMap
<!-- Dept getDeptAndEmpByStepOne(@Param("did") Integer did) -->
<resultMap id="deptAndEmpByStepResultMap" type="Dept">
<id property="did" column="did"></id>
<result property="deptName" column="dept_name"></result>
<collection property="emps"
select="com.zyj.mybatis.mapper.EmpMapper.getDeptAndEmpByStepTwo"
column="did"></collection>
</resultMap>
<select id="getDeptAndEmpByStepOne" resultMap="deptAndEmpByStepResultMap">
select * from t_dept where did = #{did}
</select>
③ 在 EmpMapper 添加分步查询的第二步
/**
* 通过分步查询查询部门及部门中所有员工的信息
* 分步查询第二步:根据did查询员工信息
*/
List<Emp> getDeptAndEmpByStepTwo(@Param("did") Integer did);
④ 在 EmpMapper.xml 添加SQL语句
<!-- List<Emp> getDeptAndEmpByStepTwo(@Param("did") Integer did) -->
<select id="getDeptAndEmpByStepTwo" resultType="Emp">
select * from t_emp where did = #{did}
</select>
⑤ 测试方法
@Test
public void testDeptAndEmpByStep(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
DeptMapper mapper = sqlSession.getMapper(DeptMapper.class);
Dept deptAndEmpByStepOne = mapper.getDeptAndEmpByStepOne(1);
System.out.println(deptAndEmpByStepOne.getDeptName());
System.out.println("++++++++++++++++++++++++++++++++++++++++++++++++++++");
System.out.println(deptAndEmpByStepOne);
}
⑥ 输出结果
DEBUG 03-18 00:01:11,633 ==> Preparing: select * from t_dept where did = ? (BaseJdbcLogger.java:137)
DEBUG 03-18 00:01:11,661 ==> Parameters: 1(Integer) (BaseJdbcLogger.java:137)
DEBUG 03-18 00:01:11,735 <== Total: 1 (BaseJdbcLogger.java:137)
A
++++++++++++++++++++++++++++++++++++++++++++++++++++
DEBUG 03-18 00:01:11,736 ==> Preparing: select * from t_emp where did = ? (BaseJdbcLogger.java:137)
DEBUG 03-18 00:01:11,736 ==> Parameters: 1(Integer) (BaseJdbcLogger.java:137)
DEBUG 03-18 00:01:11,738 <== Total: 2 (BaseJdbcLogger.java:137)
Dept{did=1, deptName='A', emps=[Emp{eid=1, empName='张三', age=33, sex='男', email='zhangsan@qq.com', dept=null}, Emp{eid=4, empName='赵六', age=37, sex='男', email='zhaoliu@qq.com', dept=null}]}