映射文件的select
查询多条记录
返回记录封装list
之前我们都是查询一条记录的,现在需要查询多条记录,比如返回的类型是list集合,那么需要如何写返回值类型呢?实际返回值类型还是写list里面的单个值的类型
public List<Emp> getEmpByNames(String ename);
<select id="getEmpByNames" resultType="bean.Emp">
select * from Emp where ename like #{ename};
</select>
List<Emp> empByNames = empMapper.getEmpByNames("%e%");
for (Emp emp : empByNames)
{
System.out.println(emp);
}
返回记录封装Map
//多条记录封装一个map:Map<Integer, Emp>
//key是这条记录的主键,值是记录封装后的JavaBean
//这个@MapKey("eid")注解是告诉mybatis封装map的时候使用的是哪一个属性作为key
@MapKey("eid")
public Map<Integer, Emp> getEmpByEnameReturnMap(String ename);
//返回一条记录的map对象,key是列名,值是对应的值
public Map<String, Object> getEmpByIdReturnMap(Integer eid);
<!-- resultType还是写map里面value的对应的数据类型 -->
<select id="getEmpByEnameReturnMap" resultType="bean.Emp">
select * from emp where ename like #{ename}
</select>
<!-- resultType写map,因为mybatis已经封装好了 -->
<select id="getEmpByIdReturnMap" resultType="map">
select * from emp where eid=#{eid}
</select>
Map<String, Object> empByIdReturnMap = empMapper.getEmpByIdReturnMap(7);
System.out.println(empByIdReturnMap);
System.out.println(empByIdReturnMap.values());
System.out.println("===========");
Map<Integer, Emp> empByEnameReturnMap = empMapper.getEmpByEnameReturnMap("%e%");
System.out.println(empByEnameReturnMap);
resultMap自定义结果映射
全局setting设置 autoMappingBehavior默认是PARTIAL，开启自动映射 的功能。
唯一的要求是列名和javaBean属性名一致
如果autoMappingBehavior设置为null则会取消自动映射
数据库字段命名规范，POJO属性符合驼峰命名法，
如 A_COLUMNaColumn，
我们可以开启自动驼峰命名规则映射功能，mapUnderscoreToCamelCase=true。
现在的问题就是数据的字段命名和JavaBean的命名对不上,
所以就使用resultMap,自定义结果映射
public Emp getEmpById(Integer id);
<!-- 自定义JavaBean的封装规则
type:自定义规则的Java类型
id:唯一标识,提高引用的
-->
<resultMap type="bean.Emp" id="myemp">
<!--
id指定定义主键列的封装规则
id定义主键底层会有优化的
column是指定哪一列
property:指定对应的JavaBean属性
-->
<id column="id" property="eid"/>
<!-- <result/>是定义普通列的封装规则 -->
<result column="name" property="ename"/>
<!-- 其他不指定的列会自动封装,但是最好就是我们只要写resultMap就把全部的映射规则都写上 -->
<result column="email" property="email"/>
<result column="gender" property="gender"/>
</resultMap>
<select id="getEmpById" resultMap="myemp" >
select eid id,ename name,email,gender from emp where eid= #{eid}
</select>
<!-- 这样会发现eid和ename封装不了的,因为id和name不对应JavaBean的属性 -->
<!--
<select id="getEmpById" resultType="emp" >
select eid id,ename name,email,gender from emp where eid= #{eid}
</select>
-->
Emp empById = empMapper.getEmpById(7);
System.out.println(empById);
结果:
Emp [eid=7, ename=jane, email=jane.com, gender=男]
联合查询:级联属性封装结果集
现在是查出Emp的同时又想查询出对应的部门
先在数据做好准备,同时增加Dept类的代码
create table dept
( did int(11) primary key auto_increment,
dname varcahr(255)
)
alter table emp add column did int(11);
alter table emp add constraint fk_e_d
foreign key(did) references dept(did)
public Emp getEmpAndDept(Integer eid);
<!--
联合查询:级联属性封装结果集
-->
<resultMap type="bean.Emp" id="comemp">
<id column="eid" property="eid"/>
<result column="ename" property="ename"/>
<result column="email" property="email"/>
<result column="gender" property="gender"/>
<result column="dname" property="dept.dname"/>
<result column="did" property="dept.did"/>
</resultMap>
<!-- public Emp getEmpAndDept(Integer eid); -->
<select id="getEmpAndDept" resultMap="comemp">
select e.eid eid,e.ename ename,e.email email,
e.gender gender,e.did did,d.dname dname
from emp e,dept d where e.did=d.did and eid=#{eid};
</select>
<!-- 这样定义,部门的属性是没有结果的
<select id="getEmpAndDept" resultType="emp">
select *
from emp e,dept d where e.did=d.did and eid=#{eid};
</select>
-->
Emp empAndDept = empMapper.getEmpAndDept(7);
System.out.println(empAndDept);
结果:
Emp [eid=7, ename=jane, email=jane.com, gender=男, dept=Dept [did=2, dname=销售部]]
association定义关联对象封装规则
<!-- 使用association定义关联的单个对象的封装规则 -->
<resultMap type="bean.Emp" id="comemp2">
<id column="eid" property="eid"/>
<result column="ename" property="ename"/>
<result column="email" property="email"/>
<result column="gender" property="gender"/>
<!-- association可以指定联合的JavaBean对象
property="dept":指定那个属性是联合对象
javaType="bean.Dept":指定这个属性的联合对象的类型(不能省略)
-->
<association property="dept" javaType="bean.Dept">
<id column="did" property="did"/>
<result column="dname" property="dname"/>
</association>
</resultMap>
使用association进行分布查询
使用association进行分布查询:所谓的发布查询就是:
- 先按照员工的eid查询员工的信息
- 根据查到的员工信息中的did值去部门表查询出部门信息
- 将部门的信息设置到员工的信息当中
public Emp getEmpByIdStep(Integer eid);
<!--
使用association进行分布查询:所谓的发布查询就是:
1.先按照员工的eid查询员工的信息
2.根据查到的员工信息中的did值去部门表查询出部门信息
3.将部门的信息设置到员工的信息当中
-->
<resultMap type="bean.Emp" id="empbystep">
<id column="eid" property="eid"/>
<result column="ename" property="ename"/>
<result column="email" property="email"/>
<result column="gender" property="gender"/>
<!-- association定义关联对象的封装规则
select:表明当前属性是调用select指定的方法查出的结果
column:指定将哪一列的值传给这个方法
流程:使用select指定的方法(参数是column指定的列的值)查出对象
并将这个对象封装给property指定的属性
-->
<association property="dept"
select="bean.DeptMapper.getDeptById" column="did">
</association>
</resultMap>
<!-- public Emp getEmpByIdStep(Integer eid); -->
<select id="getEmpByIdStep" resultMap="empbystep">
select * from emp where eid=#{eid}
</select>
package bean;
public interface DeptMapper
{
public Dept getDeptById(Integer did);
}
<?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="bean.DeptMapper">
<!-- public Dept getDeptById(Integer did); -->
<select id="getDeptById" resultType="bean.Dept">
select * from dept where did=#{did};
</select>
</mapper>
Emp empByIdStep = empMapper.getEmpByIdStep(7);
System.out.println(empByIdStep);
结果:
Sat May 16 19:08:53 CST 2020 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
DEBUG 05-16 19:08:54,084 ==> Preparing: select * from emp where eid=? (BaseJdbcLogger.java:145)
DEBUG 05-16 19:08:54,104 ==> Parameters: 7(Integer) (BaseJdbcLogger.java:145)
DEBUG 05-16 19:08:54,124 ====> Preparing: select * from dept where did=?; (BaseJdbcLogger.java:145)
DEBUG 05-16 19:08:54,124 ====> Parameters: 2(Integer) (BaseJdbcLogger.java:145)
DEBUG 05-16 19:08:54,126 <==== Total: 1 (BaseJdbcLogger.java:145)
DEBUG 05-16 19:08:54,127 <== Total: 1 (BaseJdbcLogger.java:145)
Emp [eid=7, ename=jane, email=jane.com, gender=男, dept=Dept [did=2, dname=销售部]]
使用association分布查询可以延迟加载
association可以实现延迟加载,只要在全局配置里面进行配置两个参数就行
<!--
显示指定每个我们需要更改的配置的值,即使是默认的
因为版本更新的时候,默认的值可能会更新
lazyLoadingEnabled:懒加载开启,默认是false
aggressiveLazyLoading:立即加载,就是会加载每一个属性,就算你不需要也进行加载
-->
<setting name="lazyLoadingEnabled" value="true"/>
<setting name="aggressiveLazyLoading" value="false"/>
Emp empByIdStep = empMapper.getEmpByIdStep(7);
System.out.println(empByIdStep.getEname());
结果由之前发送两条sql语句变成发送一条sql语句
Sat May 16 19:29:10 CST 2020 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
DEBUG 05-16 19:29:11,179 ==> Preparing: select * from emp where eid=? (BaseJdbcLogger.java:145)
DEBUG 05-16 19:29:11,200 ==> Parameters: 7(Integer) (BaseJdbcLogger.java:145)
DEBUG 05-16 19:29:11,252 <== Total: 1 (BaseJdbcLogger.java:145)
jane
collection定义关联集合封装规则
现在查询部门的时候将部门对应的所有员工信息也查询出来
public Dept getDeptByIdPlus(Integer did);
<!-- 现在查询部门的时候将部门对应的所有员工信息也查询出来 -->
<resultMap type="bean.Dept" id="mydept">
<id column="did" property="did"/>
<id column="dname" property="dname"/>
<!--
collection定义关联集合类型的属性的封装规则
ofType:指定集合里面的元素的类型
-->
<collection property="list" ofType="bean.Emp">
<id column="eid" property="eid"/>
<result column="ename" property="ename"/>
<result column="email" property="email"/>
<result column="gender" property="gender"/>
</collection>
</resultMap>
<select id="getDeptByIdPlus" resultMap="mydept">
select d.did did,d.dname dname,
e.eid eid,e.ename ename,e.email email,e.gender gender
from dept d
left join emp e
on d.did=e.did where d.did=2
</select>
DeptMapper mapper = openSession.getMapper(DeptMapper.class);
Dept deptByIdPlus = mapper.getDeptByIdPlus(2);
System.out.println(deptByIdPlus);
System.out.println(deptByIdPlus.getList());
结果
DEBUG 05-17 00:30:13,319 ==> Preparing: select d.did did,d.dname dname, e.eid eid,e.ename ename,e.email email,e.gender gender from dept d left join emp e on d.did=e.did where d.did=2 (BaseJdbcLogger.java:145)
DEBUG 05-17 00:30:13,347 ==> Parameters: (BaseJdbcLogger.java:145)
DEBUG 05-17 00:30:13,368 <== Total: 2 (BaseJdbcLogger.java:145)
Dept [did=2, dname=销售部]
[Emp [eid=7, ename=jane, email=jane.com, gender=男, dept=null], Emp [eid=8, ename=jane, email=jane.com, gender=男, dept=null]]
collection关联集合封装延迟和加载
public Dept getDeptByIdStep(Integer id);
<!-- public Dept getDeptByIdStep(Integer id); -->
<resultMap type="bean.Dept" id="mydeptstep">
<id column="did" property="did"/>
<id column="dname" property="dname"/>
<collection property="list" select="bean.EmpMapper.getEmpByDeptId" column="did"></collection>
</resultMap>
<select id="getDeptByIdStep" resultMap="mydeptstep">
select * from dept where did=#{did}
</select>
Dept deptByIdStep = mapper.getDeptByIdStep(2);
System.out.println(deptByIdStep.getDname());
System.out.println(deptByIdStep.getList());
分步查询传递多列值,fetchType
如果分步查询需要传递多个列值过去,
需要将多个列值封装成map对象进行传递
比如:column="{key1=column1,key2=column2}"
上面的column="did"改成column="{did=did}""
我们可能在全局变量里面设置了延迟执行还是立即执行
其实我们还可以控制每一个的association,collection关联集合是立即执行还是延迟执行
在关联集合里面的fetchType属性进行设置,
取值有两个
- lazy:延迟
- eager:立即
<collection fetchType="lazy" property="list" select="bean.EmpMapper.getEmpByDeptId" column="{did=did}"></collection>
discriminator(鉴别器)
public Emp getEmpByIdStep2(Integer eid);
<!--
discriminator:鉴别器
mybatis可以使用discriminator判断某列的值
然后根据这列的值改变封装行为
比如:
如果查出的员工是女生,就把部门的信息查出来
如果是男生,就将email封装成名字
<discriminator javaType="string" column="gender">
column:指定判断的列名
javaType:列值对应的Java类型
<case value="女" resultType="bean.Emp">
这里的resultType不能少,要么是resultType,要么resultMap
它们都是指定封装的结果类型,就是封装到那个类的属性里面
-->
<resultMap type="bean.Emp" id="empbystep2">
<id column="eid" property="eid"/>
<result column="ename" property="ename"/>
<result column="email" property="email"/>
<result column="gender" property="gender"/>
<discriminator javaType="string" column="gender">
<case value="男" resultType="bean.Emp">
<id column="eid" property="eid"/>
<result column="ename" property="ename"/>
<result column="ename" property="email"/>
<result column="gender" property="gender"/>
</case>
<case value="女" resultType="bean.Emp">
<association property="dept"
select="bean.DeptMapper.getDeptById" column="did">
</association>
</case>
</discriminator>
</resultMap>
<!-- public Emp getEmpByIdStep(Integer eid); -->
<select id="getEmpByIdStep2" resultMap="empbystep2">
select * from emp where eid=#{eid}
</select>
Emp empByIdStep2 = empMapper.getEmpByIdStep2(9);
System.out.println(empByIdStep2);
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
