3、映射文件
3.1 映射文件-增删改查
映射文件指导着MyBatis如何进行数据库增删改查,有着非常重要的意义;
- cache –命名空间的二级缓存配置
- cache-ref – 其他命名空间缓存配置的引用。
- resultMap – 自定义结果集映射
- parameterMap – 已废弃!老式风格的参数映射
- sql –抽取可重用语句块。
- insert – 映射插入语句
- update – 映射更新语句
- delete – 映射删除语句
- select – 映射查询语句
CRUD初体验
public interface EmployeeMapper {
public Employee getEmpById(Integer id);
public Long addEmp(Employee employee);
public boolean updateEmp(Employee employee);
public void deleteEmpById(Integer id);
}
<mapper namespace="club.coderhome.c03.mapper.dao.EmployeeMapper">
<select id="getEmpById" resultType="club.coderhome.c01.helloworld.bean.Employee">
select * from employee where id = #{id}
</select>
<!-- public void addEmp(Employee employee); -->
<insert id="addEmp" parameterType="club.coderhome.c01.helloworld.bean.Employee"
useGeneratedKeys="true" keyProperty="id" >
insert into employee(last_name,email,gender)
values(#{lastName},#{email},#{gender})
</insert>
<!-- public void updateEmp(Employee employee); -->
<update id="updateEmp">
update employee
set last_name=#{lastName},email=#{email},gender=#{gender}
where id=#{id}
</update>
<!-- public void deleteEmpById(Integer id); -->
<delete id="deleteEmpById">
delete from employee where id=#{id}
</delete>
</mapper>
public class MapperTest {
@Test
public void testCrud() throws IOException {
SqlSessionFactory ssf = Tools.getSqlSessionFactory("c03/mybatis-config.xml");
SqlSession session = ssf.openSession();
try {
EmployeeMapper mapper = session.getMapper(EmployeeMapper.class);
Employee newEmployee = new Employee(null,"kuang","kuang@163.com","1");
//增
Long count = mapper.addEmp(newEmployee);
//查
System.out.println("After creating : " + mapper.getEmpById(newEmployee.getId()));
//改
newEmployee.setGender("0");
mapper.updateEmp(newEmployee);
//查
System.out.println("After updating : " + mapper.getEmpById(newEmployee.getId()));
//删
mapper.deleteEmpById(newEmployee.getId());
System.out.println("After deleting : " + mapper.getEmpById(newEmployee.getId()));
session.commit();
} finally {
session.close();
}
}
}
另外
- mybatis允许增删改直接定义以下类型返回值
- Integer、Long、Boolean、void
- 我们需要手动提交数据
- sqlSessionFactory.openSession();===》手动提交
- sqlSessionFactory.openSession(true);===》自动提交
3.2 .映射文件-insert-获取自增主键的值
- parameterType:参数类型,可以省略,
- 获取自增主键的值:
- mysql支持自增主键,自增主键值的获取,mybatis也是利用statement.getGenreatedKeys();
- useGeneratedKeys=“true”;使用自增主键获取主键值策略
- keyProperty;指定对应的主键属性,也就是mybatis获取到主键值以后,将这个值封装给javaBean的哪个属性
<!-- public void addEmp(Employee employee); -->
<insert id="addEmp" parameterType="club.coderhome.c01.helloworld.bean.Employee"
useGeneratedKeys="true" keyProperty="id" >
insert into employee(last_name,email,gender)
values(#{lastName},#{email},#{gender})
</insert>
3.3 .映射文件-insert-Oracle使用序列生成主键演示
- Oracle不支持自增;Oracle使用序列来模拟自增;
- 每次插入的数据的主键是从序列中拿到的值;如何获取到这个值;
#从序列获取新主键值
select employee_seq.nextval from dual;
3.4 映射文件-insert-获取非自增主键的值-selectKey
<insert id="addEmp" databaseId="oracle">
<!--
keyProperty:查出的主键值封装给javaBean的哪个属性
order="BEFORE":当前sql在插入sql之前运行
AFTER:当前sql在插入sql之后运行
resultType:查出的数据的返回值类型
BEFORE运行顺序:
先运行selectKey查询id的sql;查出id值封装给javaBean的id属性
在运行插入的sql;就可以取出id属性对应的值
AFTER运行顺序:
先运行插入的sql(从序列中取出新值作为id);
再运行selectKey查询id的sql;
-->
<selectKey keyProperty="id" order="BEFORE" resultType="Integer">
<!-- 编写查询主键的sql语句 -->
<!-- BEFORE-->
select EMPLOYEES_SEQ.nextval from dual
<!-- AFTER:
select EMPLOYEES_SEQ.currval from dual -->
</selectKey>
<!-- 插入时的主键是从序列中拿到的 -->
<!-- BEFORE:-->
insert into employees(EMPLOYEE_ID,LAST_NAME,EMAIL)
values(#{id},#{lastName},#{email<!-- ,jdbcType=NULL -->})
<!-- AFTER:
insert into employees(EMPLOYEE_ID,LAST_NAME,EMAIL)
values(employees_seq.nextval,#{lastName},#{email}) -->
</insert>
selectKey 元素的属性
属性 | 描述 |
---|---|
keyProperty | selectKey 语句结果应该被设置到的目标属性。如果生成列不止一个,可以用逗号分隔多个属性名称。 |
keyColumn | 返回结果集中生成列属性的列名。如果生成列不止一个,可以用逗号分隔多个属性名称。 |
resultType | 结果的类型。通常 MyBatis 可以推断出来,但是为了更加准确,写上也不会有什么问题。MyBatis 允许将任何简单类型用作主键的类型,包括字符串。如果生成列不止一个,则可以使用包含期望属性的 Object 或 Map。 |
order | 可以设置为 BEFORE 或 AFTER。如果设置为 BEFORE,那么它首先会生成主键,设置 keyProperty 再执行插入语句。如果设置为 AFTER,那么先执行插入语句,然后是 selectKey 中的语句 - 这和 Oracle 数据库的行为相似,在插入语句内部可能有嵌入索引调用。 |
statementType | 和前面一样,MyBatis 支持 STATEMENT,PREPARED 和 CALLABLE 类型的映射语句,分别代表 Statement, PreparedStatement 和 CallableStatement 类型。 |
3.5 映射文件-参数处理-单个参数&多个参数&命名参数
- 单个参数:mybatis不会做特殊处理,
#{参数名/任意名}
:取出参数值。
- 多个参数:mybatis会做特殊处理。
- 通常操作:
- 方法:public Employee getEmpByIdAndLastName(Integer id,String lastName);
- 取值:
#{id}
,#{lastName}
- 上述操作会抛出异常:
org.apache.ibatis.binding.BindingException: Parameter 'id' not found. Available parameters are [1, 0, param1, param2]
- 多个参数会被封装成 一个map,
- key:param1…paramN,或者参数的索引也可以
- value:传入的参数值
#{}
就是从map中获取指定的key的值;
- 通常操作:
- 【命名参数】:明确指定封装参数时map的key;@Param(“id”)
- 多个参数会被封装成 一个map,
- key:使用@Param注解指定的值
- value:参数值
#{指定的key}
取出对应的参数值
- 多个参数会被封装成 一个map,
@Test
public void testParameters() throws IOException {
SqlSessionFactory ssf = Tools.getSqlSessionFactory("c03/mybatis-config.xml");
SqlSession session = ssf.openSession();
try {
EmployeeMapper mapper = session.getMapper(EmployeeMapper.class);
//1.
//单个参数:mybatis不会做特殊处理
System.out.println(mapper.getEmpById2(1));
//2.
//多个参数,未作处理 ,mapper直用#{id},#{lastName}会抛异常
try {
System.out.println(mapper.getEmpByIdAndLastName(1, "jallen"));
//org.apache.ibatis.exceptions.PersistenceException:
//### Error querying database. Cause: org.apache.ibatis.binding.BindingException: Parameter 'id' not found. Available parameters are [0, 1, param1, param2]
//### Cause: org.apache.ibatis.binding.BindingException: Parameter 'id' not found. Available parameters are [0, 1, param1, param2]
}catch(PersistenceException ex) {
System.err.println(ex);
}
//多个参数会被封装成 一个map
//key:param1...paramN,或者参数的索引0, 1..也可以(这种方法的可读性较差)
//value:传入的参数值
System.out.println(mapper.getEmpByIdAndLastName2(1, "jallen"));
System.out.println(mapper.getEmpByIdAndLastName3(1, "jallen"));
//3.
//【命名参数】:明确指定封装参数时map的key;@Param("id")
System.out.println(mapper.getEmpByIdAndLastName4(1, "jallen"));
session.commit();
} finally {
session.close();
}
}
<mapper namespace="club.coderhome.c03.mapper.dao.EmployeeMapper">
<!-- 多个参数,不能直写id或lastName,否则抛出 org.apache.ibatis.binding.BindingException: Parameter 'id' not found. Available parameters are [1, 0, param1, param2]-->
<select id="getEmpByIdAndLastName" resultType="club.coderhome.c01.helloworld.bean.Employee">
select * from tbl_employee where id = #{id} and last_name=#{lastName}
</select>
<select id="getEmpByIdAndLastName2" resultType="club.coderhome.c01.helloworld.bean.Employee">
select * from employee where id = #{0} and last_name=#{1}
</select>
<select id="getEmpByIdAndLastName3" resultType="club.coderhome.c01.helloworld.bean.Employee">
select * from employee where id = #{param1} and last_name=#{param2}
</select>
<select id="getEmpByIdAndLastName4" resultType="club.coderhome.c01.helloworld.bean.Employee">
select * from employee where id = #{id} and last_name=#{lastName}
</select>
...
public interface EmployeeMapper {
public Employee getEmpByIdAndLastName(Integer id, String name);
public Employee getEmpByIdAndLastName2(Integer id, String name);
public Employee getEmpByIdAndLastName3(Integer id, String name);
public Employee getEmpByIdAndLastName4(@Param("id")Integer id,//
@Param("lastName")String name);
...
3.6 映射文件-参数处理-POJO&Map&TO
- POJO:如果多个参数正好是我们业务逻辑的数据模型,我们就可以直接传入pojo;
#{属性名}
:取出传入的pojo的属性值
- Map:如果多个参数不是业务模型中的数据,没有对应的pojo,不经常使用,为了方便,我们也可以传入map
#{key}
:取出map中对应的值
- TO:如果多个参数不是业务模型中的数据,但是经常要使用,推荐来编写一个TO(Transfer Object)数据传输对象,如:
Page{
int index;
int size;
}
@Test
public void testParameters() throws IOException {
SqlSessionFactory ssf = Tools.getSqlSessionFactory("c03/mybatis-config.xml");
SqlSession session = ssf.openSession();
try {
EmployeeMapper mapper = session.getMapper(EmployeeMapper.class);
...
//4.
//传入map
Map<String, Object> map = new HashMap<>();
map.put("id", 1);
map.put("lastName", "jallen");
System.out.println("4. " + mapper.getEmpByMap(map));
session.commit();
} finally {
session.close();
}
}
<mapper namespace="club.coderhome.c03.mapper.dao.EmployeeMapper">
<!-- map 作参输入 -->
<select id="getEmpByMap" resultType="club.coderhome.c01.helloworld.bean.Employee">
select * from employee where id = #{id} and last_name=#{lastName}
</select>
...
public interface EmployeeMapper {
public Employee getEmpByMap(Map<String, Object> map);
...
3.7 映射文件-参数处理-参数封装扩展思考
思考========
-
public Employee getEmp(@Param("id")Integer id,String lastName);
- 取值:id==>#{id/param1} lastName==>#{param2}
-
public Employee getEmp(Integer id,@Param("e")Employee emp);
- 取值:id==>#{param1} lastName===>#{param2.lastName/e.lastName}
-
特别注意:如果是Collection(List、Set)类型或者是数组,
-
也会特殊处理。也是把传入的list或者数组封装在map中。
-
key:Collection(collection),如果是List还可以使用这个key(list)
-
public Employee getEmpById(List<Integer> ids);
- 取值:取出第一个id的值: #{list[0]}
-
3.8 源码分析-参数处理-参数封装map的过程
结合源码,mybatis怎么处理参数
- (@Param(“id”)Integer id,@Param(“lastName”)String lastName);
- ParamNameResolver解析参数封装map的;
- names:{0=id, 1=lastName};构造器的时候就确定好了
确定流程:
1.获取每个标了param注解的参数的@Param的值:id,lastName; 赋值给name;
2.每次解析一个参数给map中保存信息:(key:参数索引,value:name的值)
name的值:
标注了param注解:注解的值
没有标注:
1.全局配置:useActualParamName(jdk1.8):name=参数名
2.name=map.size();相当于当前元素的索引
{0=id, 1=lastName,2=2}
args【1,“Tom”,‘hello’】:
public Object getNamedParams(Object[] args) {
final int paramCount = names.size();
//1、参数为null直接返回
if (args == null || paramCount == 0) {
return null;
//2、如果只有一个元素,并且没有Param注解;args[0]:单个参数直接返回
} else if (!hasParamAnnotation && paramCount == 1) {
return args[names.firstKey()];
//3、多个元素或者有Param标注
} else {
final Map<String, Object> param = new ParamMap<Object>();
int i = 0;
//4、遍历names集合;{0=id, 1=lastName,2=2}
for (Map.Entry<Integer, String> entry : names.entrySet()) {
//names集合的value作为key; names集合的key又作为取值的参考args[0]:args【1,"Tom"】:
//eg:{id=args[0]:1,lastName=args[1]:Tom,2=args[2]}
param.put(entry.getValue(), args[entry.getKey()]);
// add generic param names (param1, param2, ...)param
//额外的将每一个参数也保存到map中,使用新的key:param1...paramN
//效果:有Param注解可以#{指定的key},或者#{param1}
final String genericParamName = GENERIC_NAME_PREFIX + String.valueOf(i + 1);
// ensure not to overwrite parameter named with @Param
if (!names.containsValue(genericParamName)) {
param.put(genericParamName, args[entry.getKey()]);
}
i++;
}
return param;
}
}
}
总结:参数多时会封装map,为了不混乱,我们可以使用@Param来指定封装时使用的key;#{key}就可以取出map中的值;
3.9 映射文件-参数处理-#与$取值区别
#{}
和${}
都可以获取map中的值或者pojo对象属性的值;
select * from tbl_employee where id=${id} and last_name=#{lastName}
#Preparing:
select * from tbl_employee where id=2 and last_name=?
区别:
#{}
: 是以预编译的形式,将参数设置到sql语句中;PreparedStatement;防止sql注入${}
: 取出的值直接拼装在sql语句中;会有安全问题;
大多情况下,我们去参数的值都应该去使用#{}
。
原生jdbc不支持占位符的地方我们就可以使用${}
进行取值,比如分表、排序。。。;按照年份分表拆分
select * from ${year}_salary where xxx;
select * from tbl_employee order by ${f_name} ${order}
3.10 映射文件-参数处理-#取值时指定参数相关规则
#{}
:更丰富的用法:
规定参数的一些规则:
- javaType、
- jdbcType、
- mode(存储过程)、
- numericScale、
- resultMap、
- typeHandler、
- jdbcTypeName、
- expression(未来准备支持的功能);
例如:jdbcType
jdbcType通常需要在某种特定的条件下被设置:
- 在我们数据为null的时候,有些数据库可能不能识别mybatis对null的默认处理。比如Oracle DB(报错);
- JdbcType OTHER:无效的类型;因为mybatis对所有的null都映射的是原生Jdbc的OTHER类型,Oracle DB不能正确处理;
由于全局配置中:jdbcTypeForNull=OTHER,Oracle DB不支持,两种解决方法:
- 在mapper文件中写
#{email,jdbcType=NULL}
; - 在全局配置文件
<setting name="jdbcTypeForNull" value="NULL"/>
3.11 映射文件-select-返回List
<mapper namespace="club.coderhome.c03.mapper.dao.EmployeeMapper">
<!-- public List<Employee> getEmpsByLastNameLike(String lastName); -->
<!--resultType:如果返回的是一个集合,要写集合中元素的类型 -->
<select id="getEmpsByLastNameLike" resultType="club.coderhome.c01.helloworld.bean.Employee">
select * from employee where last_name like #{lastName}
</select>
...
public interface EmployeeMapper {
return Collection
public List<Employee> getEmpsByLastNameLike(String str);
...
@Test
public void testList() throws IOException {
SqlSessionFactory ssf = Tools.getSqlSessionFactory("c03/mybatis-config.xml");
SqlSession session = ssf.openSession();
try {
EmployeeMapper em = session.getMapper(EmployeeMapper.class);
List<Employee> result = em.getEmpsByLastNameLike("%a%");
System.out.println(result);
session.commit();
} finally {
session.close();
}
}
3.12 映射文件-select-记录封装map
public interface EmployeeMapper {
//多条记录封装一个map:Map<Integer,Employee>:键是这条记录的主键,值是记录封装后的javaBean
//@MapKey:告诉mybatis封装这个map的时候使用哪个属性作为map的key
@MapKey("lastName")
public Map<String, Employee> getEmpByLastNameLikeReturnMap(String lastName);
//返回一条记录的map;key就是列名,值就是对应的值
public Map<String, Object> getEmpByIdReturnMap(Integer id);
...
<mapper namespace="club.coderhome.c03.mapper.dao.EmployeeMapper">
<!--public Map<Integer, Employee> getEmpByLastNameLikeReturnMap(String lastName); -->
<select id="getEmpByLastNameLikeReturnMap" resultType="club.coderhome.c01.helloworld.bean.Employee">
select * from employee where last_name like #{lastName}
</select>
<!--public Map<String, Object> getEmpByIdReturnMap(Integer id); -->
<select id="getEmpByIdReturnMap" resultType="map">
select * from employee where id=#{id}
</select>
...
@Test
public void testMap() throws IOException {
SqlSessionFactory ssf = Tools.getSqlSessionFactory("c03/mybatis-config.xml");
SqlSession session = ssf.openSession();
try {
EmployeeMapper em = session.getMapper(EmployeeMapper.class);
Map<String, Object> result = em.getEmpByIdReturnMap(1);
System.out.println(result);
System.out.println("---");
Map<String, Employee> result2 = em.getEmpByLastNameLikeReturnMap("%a%");
System.out.println(result2);
session.commit();
} finally {
session.close();
}
}
3.13 映射文件-select-resultMap-自定义结果映射规则
public interface EmployeeMapper {
//自定义结果映射规则
public Employee getEmpByIdWithResultMap(Integer id);
...
<mapper namespace="club.coderhome.c03.mapper.dao.EmployeeMapper">
<!--自定义某个javaBean的封装规则
type:自定义规则的Java类型
id:唯一id方便引用
-->
<resultMap type="club.coderhome.c01.helloworld.bean.Employee" id="MySimpleEmp">
<!--指定主键列的封装规则
id定义主键会底层有优化;
column:指定哪一列
property:指定对应的javaBean属性
-->
<id column="id" property="id"/>
<!-- 定义普通列封装规则 -->
<result column="last_name" property="lastName"/>
<!-- 其他不指定的列会自动封装:我们只要写resultMap就把全部的映射规则都写上。 -->
<result column="email" property="email"/>
<result column="gender" property="gender"/>
</resultMap>
<!-- resultMap:自定义结果集映射规则; -->
<!-- public Employee getEmpById(Integer id); -->
<select id="getEmpByIdWithResultMap" resultMap="MySimpleEmp">
select * from employee where id=#{id}
</select>
@Test
public void testResultMap() throws IOException {
SqlSessionFactory ssf = Tools.getSqlSessionFactory("c03/mybatis-config.xml");
SqlSession session = ssf.openSession();
try {
EmployeeMapper em = session.getMapper(EmployeeMapper.class);
System.out.println(em.getEmpByIdWithResultMap(1));
session.commit();
} finally {
session.close();
}
}
3.14 映射文件-select-resultMap-关联查询-环境搭建
public class Department {
private Integer id;
private String departmentName;
private List<Employee> emps;
//getter and setter and toString()
public class Employee {
...
private Department department;
...
public Employee() {}
//add department's getter and setter
创建数据库表
CREATE TABLE department(
id int(11) primary key auto_increment,
department_name varchar(255)
);
ALTER TABLE employee ADD COLUMN department_id int(11);
ALTER TABLE employee ADD CONSTRAINT fk_employee_department
FOREIGN KEY(department_id) REFERENCES department(id);
INSERT INTO department(department_name) values ('开发部');
INSERT INTO department(department_name) values ('测试部');
3.15 映射文件-select-resultMap-关联查询-级联属性封装结果
public interface EmployeeMapper {
//联合查询:级联属性封装结果集
public Employee getEmpAndDept(Integer id);
...
<mapper namespace="club.coderhome.c03.mapper.dao.EmployeeMapper">
<!-- 联合查询:级联属性封装结果集
-->
<resultMap type="club.coderhome.c01.helloworld.bean.Employee" id="MyDifEmp">
<id column="id" property="id"/>
<result column="last_name" property="lastName"/>
<result column="gender" property="gender"/>
<result column="department_id" property="department.id"/>
<result column="department_name" property="department.departmentName"/>
</resultMap>
<!-- public Employee getEmpAndDept(Integer id);-->
<select id="getEmpAndDept" resultMap="MyDifEmp">
SELECT
e.id id,e.last_name last_name,e.gender gender,
e.department_id department_id, d.department_name department_name
FROM employee e, department d
WHERE e.department_id=d.id AND e.id=#{id}
</select>
...
@Test
public void testResultMapAssociation() throws IOException {
SqlSessionFactory ssf = Tools.getSqlSessionFactory("c03/mybatis-config.xml");
SqlSession session = ssf.openSession();
try {
EmployeeMapper em = session.getMapper(EmployeeMapper.class);
System.out.println(em.getEmpAndDept(1));
session.commit();
} finally {
session.close();
}
}
3.16 映射文件-select-resultMap-关联查询-association定义关联对象封装规则
public interface EmployeeMapper {
//联合查询:级联属性封装结果集
public Employee getEmpAndDept2(Integer id);
<mapper namespace="club.coderhome.c03.mapper.dao.EmployeeMapper">
<!-- association可以指定联合的javaBean对象 -->
<resultMap type="club.coderhome.c01.helloworld.bean.Employee" id="MyDifEmp2">
<id column="id" property="id"/>
<result column="last_name" property="lastName"/>
<result column="gender" property="gender"/>
<!-- association可以指定联合的javaBean对象
property="dept":指定哪个属性是联合的对象
javaType:指定这个属性对象的类型[不能省略]
-->
<association property="department" javaType="club.coderhome.c01.helloworld.bean.Department">
<id column="department_id" property="id"/>
<result column="department_name" property="departmentName"/>
</association>
</resultMap>
<!-- public Employee getEmpAndDept2(Integer id);-->
<select id="getEmpAndDept2" resultMap="MyDifEmp2">
SELECT
e.id id,e.last_name last_name,e.gender gender,
e.department_id department_id, d.department_name department_name
FROM employee e, department d
WHERE e.department_id=d.id AND e.id=#{id}
</select>
@Test
public void testResultMapAssociation2() throws IOException {
SqlSessionFactory ssf = Tools.getSqlSessionFactory("c03/mybatis-config.xml");
SqlSession session = ssf.openSession();
try {
EmployeeMapper em = session.getMapper(EmployeeMapper.class);
System.out.println(em.getEmpAndDept2(1));
System.out.println(em.getEmpAndDept2(1).getDepartment());
session.commit();
} finally {
session.close();
}
}
3.17 映射文件-select-resultMap-关联查询-association分步查询
public interface DepartmentMapper {
public Department getDeptById(Integer id);
}
<mapper namespace="club.coderhome.c03.mapper.dao.DepartmentMapper">
<!--public Department getDeptById(Integer id); -->
<select id="getDeptById" resultType="club.coderhome.c01.helloworld.bean.Department">
select id,department_name departmentName from department where id=#{id}
</select>
</mapper>
...
<mappers>
<mapper resource="c03/EmployeeMapper.xml" />
<mapper resource="c03/DepartmentMapper.xml" />
</mappers>
</configuration>
public interface EmployeeMapper {
//association分步查询
public Employee getEmpByIdStep(Integer id);
...
<mapper namespace="club.coderhome.c03.mapper.dao.EmployeeMapper">
<!-- association分步查询 -->
<!-- 使用association进行分步查询:
1、先按照员工id查询员工信息
2、根据查询员工信息中的department_id值去部门表查出部门信息
3、部门设置到员工中;
-->
<!-- id last_name email gender d_id -->
<resultMap type="club.coderhome.c01.helloworld.bean.Employee" id="MyEmpByStep">
<id column="id" property="id"/>
<result column="last_name" property="lastName"/>
<result column="email" property="email"/>
<result column="gender" property="gender"/>
<!-- association定义关联对象的封装规则
select:表明当前属性是调用select指定的方法查出的结果
column:指定将哪一列的值传给这个方法
流程:使用select指定的方法(传入column指定的这列参数的值)查出对象,并封装给property指定的属性
-->
<association property="department"
select="club.coderhome.c03.mapper.dao.DepartmentMapper.getDeptById"
column="department_id">
</association>
</resultMap>
<!-- public Employee getEmpByIdStep(Integer id);-->
<select id="getEmpByIdStep" resultMap="MyEmpByStep">
select * from employee where id=#{id}
</select>
@Test
public void testResultMapAssociation3() throws IOException {
SqlSessionFactory ssf = Tools.getSqlSessionFactory("c03/mybatis-config.xml");
SqlSession session = ssf.openSession();
try {
EmployeeMapper em = session.getMapper(EmployeeMapper.class);
System.out.println(em.getEmpByIdStep(1));
System.out.println(em.getEmpByIdStep(1).getDepartment());
session.commit();
} finally {
session.close();
}
}
3.18 映射文件-select-resultMap-关联查询-分步查询&延迟加载
我们每次查询Employee对象的时候,都将一起查询出来。部门信息在我们使用的时候再去查询;分段查询的基础之上加上两个配置:
在全局配置文件中配置,实现懒加载
<configuration>
...
<settings>
...
<!--显示的指定每个我们需要更改的配置的值,即使他是默认的。防止版本更新带来的问题 -->
<setting name="lazyLoadingEnabled" value="true"/>
<setting name="aggressiveLazyLoading" value="false"/>
</settings>
Setting | Description | Valid Values | Default |
---|---|---|---|
lazyLoadingEnabled | Globally enables or disables lazy loading. When enabled, all relations will be lazily loaded. This value can be superseded for an specific relation by using the fetchType attribute on it. | true/false | false |
aggressiveLazyLoading | When enabled, an object with lazy loaded properties will be loaded entirely upon a call to any of the lazy properties. Otherwise, each property is loaded on demand. | true/false | true |
aggressive
英 [əˈɡresɪv] 美 [əˈɡresɪv]
adj.
好斗的;挑衅的;侵略的;富于攻击性的;气势汹汹的;声势浩大的;志在必得的
PS. 个人认为aggressiveLazyLoading 可防止懒加载对象链情况出现。TODO: 待验证
3.19 映射文件-select-resultMap-关联查询-collection定义关联集合封装规则
public interface DepartmentMapper {
public Department getDeptByIdPlus(Integer id);
...
<mapper namespace="club.coderhome.c03.mapper.dao.DepartmentMapper">
<!--
public class Department {
private Integer id;
private String departmentName;
private List<Employee> emps;
did dept_name || eid last_name email gender
-->
<!--嵌套结果集的方式,使用collection标签定义关联的集合类型的属性封装规则 -->
<resultMap type="club.coderhome.c01.helloworld.bean.Department" id="MyDept">
<id column="did" property="id"/>
<result column="department_name" property="departmentName"/>
<!--
collection定义关联集合类型的属性的封装规则
ofType:指定集合里面元素的类型
-->
<collection property="emps" ofType="club.coderhome.c01.helloworld.bean.Employee">
<!-- 定义这个集合中元素的封装规则 -->
<id column="eid" property="id"/>
<result column="last_name" property="lastName"/>
<result column="email" property="email"/>
<result column="gender" property="gender"/>
</collection>
</resultMap>
<!-- public Department getDeptByIdPlus(Integer id); -->
<select id="getDeptByIdPlus" resultMap="MyDept">
SELECT d.id did,d.department_name department_name,
e.id eid,e.last_name last_name,
e.email email,e.gender gender
FROM department d LEFT JOIN employee e ON d.id=e.department_id
WHERE d.id=#{id}
</select>
...
public class DepartmentTest {
@Test
public void testGetDeptByIdPlus() throws IOException {
SqlSessionFactory ssf = Tools.getSqlSessionFactory("c03/mybatis-config.xml");
SqlSession session = ssf.openSession();
try {
DepartmentMapper dm = session.getMapper(DepartmentMapper.class);
Department department = dm.getDeptByIdPlus(1);
System.out.println(department);
System.out.println(department.getEmps());
session.commit();
} finally {
session.close();
}
}
}
3.20 映射文件-select-resultMap-关联查询-collection分步查询&延迟加载
public interface EmployeeMapper {
public Employee getEmpsByDeptId(Integer id);
...
<mapper namespace="club.coderhome.c03.mapper.dao.EmployeeMapper">
<!-- public Employee getEmpsByDeptId(Integer departmentId);-->
<select id="getEmpsByDeptId" resultType="club.coderhome.c01.helloworld.bean.Employee">
select * from employee where department_id=#{department_id}
</select>
public interface DepartmentMapper {
public Department getDeptByIdStep(Integer id);
...
<mapper namespace="club.coderhome.c03.mapper.dao.DepartmentMapper">
<!-- collection:分段查询 -->
<resultMap type="club.coderhome.c01.helloworld.bean.Department" id="MyDeptStep">
<id column="id" property="id"/>
<id column="department_name" property="departmentName"/>
<collection property="emps"
select="club.coderhome.c03.mapper.dao.EmployeeMapper.getEmpsByDeptId"
column="id"></collection>
</resultMap>
<!-- public Department getDeptByIdStep(Integer id); -->
<select id="getDeptByIdStep" resultMap="MyDeptStep">
select id,department_name from department where id=#{id}
</select>
public class DepartmentMapperTest {
@Test
public void testGetDeptByIdStep() throws IOException {
SqlSessionFactory ssf = Tools.getSqlSessionFactory("c03/mybatis-config.xml");
SqlSession session = ssf.openSession();
try {
DepartmentMapper dm = session.getMapper(DepartmentMapper.class);
Department department = dm.getDeptByIdStep(1);
System.out.println(department);
System.out.println(department.getEmps());
session.commit();
} finally {
session.close();
}
}
...
3.21 映射文件-select-resultMap-分步查询传递多列值&fetchType
扩展:
- 多列的值传递过去:
- 将多列的值封装map传递;
column="{key1=column1,key2=column2}"
- 将多列的值封装map传递;
- fetchType=“lazy”:表示使用延迟加载;
- lazy:延迟
- eager:立即
<resultMap type="club.coderhome.c01.helloworld.bean.Department" id="MyDeptStep">
<id column="id" property="id"/>
<id column="department_name" property="departmentName"/>
<collection property="emps"
select="club.coderhome.c03.mapper.dao.EmployeeMapper.getEmpsByDeptId"
column="{deptId=id}" fetchType="lazy"></collection>
</resultMap>
<!-- public Department getDeptByIdStep(Integer id); -->
<select id="getDeptByIdStep" resultMap="MyDeptStep">
select id,department_name from department where id=#{id}
</select>
3.22 映射文件-select-resultMap-discriminator鉴别器
public interface EmployeeMapper {
//带有鉴别器的
public List<Employee> getEmpsWithDiscriminator();
...
<mapper namespace="club.coderhome.c03.mapper.dao.EmployeeMapper">
<!-- =======================鉴别器============================ -->
<!-- <discriminator javaType=""></discriminator>
鉴别器:mybatis可以使用discriminator判断某列的值,然后根据某列的值改变封装行为
封装Employee:
如果查出的是女生:就把部门信息查询出来,否则不查询;
如果是男生,把last_name这一列的值赋值给email;
-->
<resultMap type="club.coderhome.c01.helloworld.bean.Employee" id="MyEmpDis">
<id column="id" property="id"/>
<result column="last_name" property="lastName"/>
<result column="email" property="email"/>
<result column="gender" property="gender"/>
<!--
column:指定判定的列名
javaType:列值对应的java类型 -->
<discriminator javaType="string" column="gender">
<!--女生 resultType:指定封装的结果类型;不能缺少。/resultMap-->
<case value="0" resultType="club.coderhome.c01.helloworld.bean.Employee">
<association property="department"
select="club.coderhome.c03.mapper.dao.DepartmentMapper.getDeptById"
column="department_id" fetchType="eager" >
</association>
</case>
<!--男生 ;如果是男生,把last_name这一列的值赋值给email; -->
<case value="1" resultType="club.coderhome.c01.helloworld.bean.Employee">
<id column="id" property="id"/>
<result column="last_name" property="lastName"/>
<result column="last_name" property="email"/>
<result column="gender" property="gender"/>
</case>
</discriminator>
</resultMap>
<!-- public Employee getEmpByIdStep(Integer id);-->
<select id="getEmpsWithDiscriminator" resultMap="MyEmpDis">
select * from employee limit 10
</select>
@Test
public void testGetEmpsWithDiscriminator() throws IOException {
SqlSessionFactory ssf = Tools.getSqlSessionFactory("c03/mybatis-config.xml");
SqlSession session = ssf.openSession();
try {
EmployeeMapper em = session.getMapper(EmployeeMapper.class);
System.out.println(em.getEmpsWithDiscriminator());
session.commit();
} finally {
session.close();
}
}