文章目录
五、MyBatis对结果集的映射关系
当我们从数据库中拿到数据集的时候,MyBatis如何将结果集封装成一个Java对象映射
1、resultType自动映射
- autoMappingBehavior默认是PARTIAL,开启自动映射的功能。唯一的要求是列名和javaBean属性名一致
- 如果autoMappingBehavior设置为null则会取消自动映射
- 数据库字段命名规范,POJO属性符合驼峰命名法,如A_COLUMNaColumn,我们可以开启自动驼峰命名规则映射功能,mapUnderscoreToCamelCase=true
2、resultMap自定义映射
<1>、自定义resultMap,实现对结果集的映射
<2>、id用于对主键的映射
<3>、result用于对普通列的映射
<!--自定义映射
type:最终结果集封装的类型
<id>:完成主键列的映射
column:指定结果集的列名
property:指定对象的属性名
<result>:完成普通列的映射
-->
<resultMap id="MyEmp" type="mybatis.beans.Employee">
<id column="id" property="id"></id>
<result column="last_name" property="lastName"/>
<result column="email" property="email"/>
<result column="gender" property="gender"/>
</resultMap>
将SqlSessionFactory封装成一个方法,方面后面的使用。
public SqlSessionFactory getSqlSessionfactory() throws Exception{
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
return sqlSessionFactory;
}
测试代码:
@Test
public void testResultMap() throws Exception {
SqlSessionFactory sqlSessionfactory = getSqlSessionfactory();
SqlSession session = sqlSessionfactory.openSession();
try {
EmployeeMapperResultMap mapper = session.getMapper(EmployeeMapperResultMap.class);
Employee employee = mapper.getEmployeeById(1008);
System.out.println(employee);
}finally {
session.close();
}
}
自定义映射也支持级联的方式,代码如下:
<resultMap id="myEmpAndDept" type="mybatis.beans.Employee">
<id column="eid" property="id"></id>
<result column="last_mane" property="lastName"/>
<result column="email" property="email"/>
<result column="gender" property="gender"/>
<!--级联-->
<!--<result column="did" property="department.id"/>-->
<!--<result column="dept_name" property="department.departmentName"/>-->
</resultMap>
<4>、association:复杂的类型关联,许多结果包装成这种类型
<select id="getEmployeeAndDept" resultMap="myEmpAndDept">
select e.id eid, e.last_name, e.email,e.gender, d.id did,d.dept_name from table_employee e, table_dept d where e.d_id = d.id and e.id=#{id}
</select>
<resultMap id="myEmpAndDept" type="mybatis.beans.Employee">
<id column="eid" property="id"></id>
<result column="last_mane" property="lastName"/>
<result column="email" property="email"/>
<result column="gender" property="gender"/>
<!--级联-->
<!--<result column="did" property="department.id"/>-->
<!--<result column="dept_name" property="department.departmentName"/>-->
<!--
association:完成关联、联合属性的映射
property:指定联合属性
javaType:指定联合属性的类型
-->
<association property="department" javaType="mybatis.beans.Department">
<id column="did" property="id"/>
<result column="dept_name" property="departmentName"/>
</association>
</resultMap>
<5>、collection:复杂类型的集合
<select id="getDeptById" resultType="mybatis.beans.Department">
select id,dept_name departmentName from table_dept where id=#{id}
</select>
<select id="getDeptAndEmps" resultMap="myDeptAndEmps">
select d.id did,d.dept_name, e.id eid,e.last_name,e.email,e.gender
from table_dept d left outer join table_employee e on d.id = e.d_id where d.id = #{id}
</select>
<resultMap id="myDeptAndEmps" type="mybatis.beans.Department">
<id column="did" property="id"/>
<result column="dept_name" property="departmentName"/>
<!--
Collection:完成集合类型的联合属性的映射
property:指定联合属性
ofType:指定集合中元素的类型
-->
<collection property="emps" ofType="mybatis.beans.Employee">
<id column="eid" property="id"/>
<result column="last_name" property="lastName"/>
<result column="email" property="email"/>
<result column="gender" property="gender"/>
</collection>
</resultMap>
@Test
public void testResultMapCollcetion() throws Exception {
SqlSessionFactory sqlSessionfactory = getSqlSessionfactory();
SqlSession session = sqlSessionfactory.openSession();
try {
DepartmentMapperResultMap mapper = session.getMapper(DepartmentMapperResultMap.class);
Department department = mapper.getDeptAndEmps(4);
System.out.println(department);
System.out.println(department.getEmps());
}finally {
session.close();
}
}
六、MyBatis动态SQL
1、简介
- 动态 SQL是MyBatis强大特性之一。极大的简化我们拼装SQL的操作
- 动态 SQL 元素和使用 JSTL 或其他类似基于 XML 的文本处理器相似
- MyBatis 采用功能强大的基于 OGNL 的表达式来简化操作
if
choose (when, otherwise)
trim (where, set)
foreach - OGNL( Object Graph Navigation Language )对象图导航语言,这是一种强大的
表达式语言,通过它可以非常方便的来操作对象属性。 类似于我们的EL,SpEL等
访问对象属性 : person.name
调用方法: person.getName()
调用静态属性/方法: @java.lang.Math@PI
@java.util.UUID@randomUUID()
调用构造方法: new com.atguigu.bean.Person(‘admin’).name
运算符: +,-*,/,%
逻辑运算符: in,not in,>,>=,<,<=,==,!=
注意:xml中特殊符号如”,>,<等这些都需要使用转义字符
2、if/where
- if用于完成简单的判断
- Where用于解决SQL语句中where关键字以及条件中第一个and或者是or的问题。
<!--List<Employee> getEmpsByConditionIfWhere(Employee Condition);-->
<select id="getEmpsByConditionIfWhere" resultType="mybatis.beans.Employee">
select id,last_name,email,gender from table_employee
<where> <!--在sql语句中提供WHERE关键字,并且要解决第一个出现的and或者是or-->
<if test="id!=null">
and id = #{id}
</if>
<if test="last_name!=null&&last_name!=""">
and last_name = #{lastName}
</if>
<if test="email!=null and email.trim()!=''">
and email = #{email}
</if>
<if test="gender==0 or gender==1">
and gender = #{gender}
</if>
</where>
</select>
将SqlSessionFactory封装成一个方法,方便后面的使用。
public SqlSessionFactory getSqlSessionfactory() throws Exception{
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
return sqlSessionFactory;
}
@Test
public void testIf() throws Exception {
SqlSessionFactory sqlSessionfactory = getSqlSessionfactory();
SqlSession session = sqlSessionfactory.openSession();
try {
EmployeeMapperDynamicSQL mapper = session.getMapper(EmployeeMapperDynamicSQL.class);
Employee employee = new Employee();
employee.setId(1002);
employee.setLastName("tom");
employee.setEmail("tom@sina.com");
employee.setGender(0);
List<Employee> emps = mapper.getEmpsByConditionIfWhere(employee);
System.out.println(emps);
}finally {
session.close();
}
}
if拼接sql语句的时候有一个问题就是,当前面一个值为空,后面值不为空或者判断正确的时候还是会报错,因为第一个的值被忽略的时候,关键词 and会连接上,从而导致SQL语法出错。这时可以使用where。
3、trim
可以在条件判断完的SQL语句前后,添加或者是去掉指定的字符
<select id="getEmpsByConditionTrim" resultType="mybatis.beans.Employee">
select id,last_name,email,gender from table_employee
<!--
prefix:添加一个前缀
prefixOverrides:覆盖/删除一个前缀
suffix:添加一个后缀
suffixOverrides:覆盖/删除一个前缀
-->
<trim prefix="where" suffixOverrides="and">
<if test="id!=null">
id = #{id} and
</if>
<if test="last_name!=null&&last_name!=""">
last_name = #{lastName} and
</if>
<if test="email!=null and email.trim()!=''">
email = #{email} and
</if>
<if test="gender==0 or gender==1">
gender = #{gender}
</if>
</trim>
</select>
@Test
public void testTrim() throws Exception {
SqlSessionFactory sqlSessionfactory = getSqlSessionfactory();
SqlSession session = sqlSessionfactory.openSession();
try {
EmployeeMapperDynamicSQL mapper = session.getMapper(EmployeeMapperDynamicSQL.class);
Employee employee = new Employee();
employee.setId(1002);
employee.setLastName("tom");
// employee.setEmail("tom@sina.com");
// employee.setGender(0);
List<Employee> emps = mapper.getEmpsByConditionTrim(employee);
System.out.println(emps);
}finally {
session.close();
}
}
4、set
主要用于解决修改操作中可能多出逗号的问题。
<update id="updateEmpByConditionSet">
update table_employee
<set>
<if test="last_name!=null">
last_name = #{lastName},
</if>
<if test="email!=null">
email = #{email},
</if>
<if test="gender==0 or gender==1">
gender = #{gender}
</if>
</set>
</update>
5、choose
主要用于分支判断,类似于Java中的switch case,只会满足所有分支中的一个。
<select id="getEmpsByConditionChoose" resultType="mybatis.beans.Employee">
select id,last_name,email,gender from table_employee where
<choose>
<when test="id!=null">
id = #{id}
</when>
<when test="lastName!=null">
last_name = #{lastName}
</when>
<when test="email!=null">
email = #{email}
</when>
<otherwise>
gender = 1
</otherwise>
</choose>
</select>
6、foreach
<!--
foreach:
collection:指定要迭代的集合
item:当前从集合中迭代出的元素
open:指定一个开始字符
close:指定一个结束字符
separator:元素与元素之间的分隔符
-->
<select id="getEmpsByIds" resultType="mybatis.beans.Employee">
select id, last_name, email, gender from table_employee
where id in
<foreach collection="ids" item="currID" open="(" close=")" separator=",">
#{currID}
</foreach>
</select>
@Test
public void testForeach() throws Exception {
SqlSessionFactory sqlSessionfactory = getSqlSessionfactory();
SqlSession session = sqlSessionfactory.openSession();
try {
EmployeeMapperDynamicSQL mapper = session.getMapper(EmployeeMapperDynamicSQL.class);
List<Integer> ids = new ArrayList<>();
ids.add(1001);
ids.add(1002);
ids.add(1003);
ids.add(1004);
List<Employee> emps = mapper.getEmpsByIds(ids);
System.out.println(emps);
}finally {
session.close();
}
}
7、sql
sql标签是用于抽取可以重用的sql片段,将相同的、使用频繁的SQL片段抽取出来,单独使用,且方便多次引用。
<!--抽取可重用的SQL片段-->
<sql id="selectEmployeeSQL">
select id,last_name,email,gender
</sql>