一、动态SQL
有时候,静态的 SQL 语句并不能满足应用程序的需求。我们可以根据一些条件,来动态地构建 SQL 语句。
如果你有任何JDBC或类似的框架经验,你就会了解拼接一个有条件的Sql字符串是是多么痛苦,尤其需要确保不要忘记空格或不要忘记列表中的逗号等。MyBatis最强大的特性之一就是它的动态SQL功能,它的动态SQL可以帮我们解决这种痛苦。
MyBatis通过使用
1.<if>
2.<choose>(<when>,<otherwise>)
3.<trim>(<where>,<set>)
4.<foreach>
元素提供了对构造动态SQL语句的高级别支持
1.1)
元素被用来有条件地嵌入SQL片段,如果测试条件被赋值为true,则相应地SQL片段将会被添加到SQL语句中
例如:
我们需要从s_emp表中查询指定条件员工的信息,条件如下:
1.指定title,固定会有的查询条件
2.指定薪资范围
3.指定雇用日期范围
<select id="selectEmpByCondition" resultMap="empResult">
select id,last_name,first_name,title,salary,start_date,
comments,commission_pct
from s_emp
where title = #{param1}
<if test="param2!=null">
and salary >= #{param2}
</if>
<if test="param3!=null">
and salary<![CDATA[<=]]> #{param3}
</if>
<if test="param4!=null">
and start_date >= #{param4}
</if>
<if test="param5!=null">
and start_date <![CDATA[<=]]> #{param5}
</if>
</select>
测试:
SqlSession session = openSession();
DynamicSqlMapper mapper =
session.getMapper(DynamicSqlMapper.class);
List<Employee> list =
mapper.selectEmpByCondition(
"Stock Clerk", 900d,1500D , null, null);
for(Employee e:list){
System.out.println(e);
}
1.2)choose,when 和 otherwise 条件
有时候,查询功能是以查询类别为基础的。首先,用户需要先选择希望通过什么类型作为查询条件类别来进行查询(例如按照员工Title,员工雇用日期,员工工资中的一个),然后根据选择的查询类别,输入相应的参数。在这样的情景中,我们【需要只使用其中一种】查询类别。
MyBatis提供了元素支持此类型的SQL预处理。
<select id="selectByConditionType" resultMap="empResult">
select id,last_name,first_name,title,salary,start_date,
comments,commission_pct
from s_emp
<choose>
<when test="selectBy == 'title'">
where title = #{title}
</when>
<when test="selectBy == 'startDate'">
where start_date >= #{startDate}
</when>
<otherwise>
where salary >= 2000
</otherwise>
</choose>
</select>
测试:
HashMap<String,Object> map = new HashMap<>();
map.put("selectBy", "title");
map.put("title", "Stock Clerk");
List<Employee> list = mapper.selectByConditionType(map);
1.3) where
有时候,所有的查询条件应该是可选的。此时,我们应该使用WHERE子句。并且如果有多个条件,我们需要在条件中添加AND或OR。
MyBatis提供了<where>元素支持这种类型的动态SQL语句。
<select id="selectWhere" resultMap="empResult">
select id,last_name,first_name,title,salary,start_date,
comments,commission_pct
from s_emp
<where>
<if test="title!=null">
and title = #{title}
</if>
<if test="minSalary!=null">
and salary >= #{minSalary}
</if>
<if test="maxSalary!=null">
and salary<![CDATA[<=]]> #{maxSalary}
</if>
<if test="minDate!=null">
and start_date >= #{minDate}
</if>
<if test="maxDate!=null">
and start_date <![CDATA[<=]]> #{maxDate}
</if>
</where>
</select>
where元素只有在其内部标签有返回内容时才会在动态语句上插入WHERE条件语句。并且,如果WHERE子句以AND或者OR打头,则打头的AND或OR将会被移除。
1.4) set标记
元素和元素类似,如果其内部条件判断有任何内容返回时,他会插入SET SQL 片段。
< update id="updateEmployee" parameterType="dynamic.Employee">
update s_emp
<set>
<if test="lastName != null">last_name=#{lastName},</if>
<if test="firstName != null">first_name=#{firstName},</if>
<if test="comments != null">comments=#{comments},</if>
<if test="startDate != null">start_date=#{startDate},</if>
<if test="salary != null">salary=#{salary},</if>
<if test="title != null">title=#{title},</if>
<if test="commission_pct != null">commission_pct=#{commission_pct},</if>
</set>
where id=#{id}
</ update>
这里,如果条件返回了任何文本内容,将会插入set关键字和其文本内容,并且会剔除将末尾的“,”。
1.5) trim 条件
元素和元素类似,但是提供了在添加前缀/后缀或者移除前缀/后缀方面提供更大的灵活性。
<select id="selectTrim" resultMap="empResult">
select id,last_name,first_name,title,salary,start_date,
comments,commission_pct
from s_emp
<trim prefix="where" prefixOverrides="And|OR">
<if test="title!=null">
and title = #{title}
</if>
<if test="minSalary!=null">
or salary >= #{minSalary}
</if>
</trim>
</select>
title!= null:
sql:select id,last_name,first_name,title,salary,start_date,
comments,commission_pct
from s_emp
where title = #{title}
1.6) foreach 循环
另外一个强大的动态SQL语句构造标签即是。它可以迭代遍历一个数组或者列表,构造AND/OR条件或一个IN子句。
假设我们想找到id为 1,3,6的员工的信息,我们可以传递一个id组成的列表给映射语句,然后通过<foreach>遍历此列表构造动态SQL。
<select id="selectForeach" resultMap="empResult">
select id,last_name,first_name,title,salary,start_date,
comments,commission_pct
from s_emp
<where>
<!-- 此处代表传递参数类型为list集合-->
<if test="list!=null">
<foreach collection="list" item="empid">
or id = #{empid}
</foreach>
</if>
</where>
</select>
测试:
SqlSession session = openSession();
DynamicSqlMapper mapper =
session.getMapper(DynamicSqlMapper.class);
List<Integer> ids = new ArrayList<>();
ids.add(1);
ids.add(5);
ids.add(8);
List<Employee> list = mapper.selectForeach(ids);
for(Employee e:list){
System.out.println(e);
}
<!--此处代表传递参数类型是map-->
<select id="selectForeach" resultMap="empResult">
select id,last_name,first_name,title,salary,start_date,
comments,commission_pct
from s_emp
<where>
<!-- 此处代表传递参数类型为map集合-->
<if test="ids!=null">
<foreach collection="ids" item="empid">
or id = #{empid}
</foreach>
</if>
</where>
</select>
测试:
SqlSession session = openSession();
DynamicSqlMapper mapper =
session.getMapper(DynamicSqlMapper.class);
List<Integer> ids = new ArrayList<>();
ids.add(1);
ids.add(5);
ids.add(8);
HashMap<String, Object> o = new HashMap<>();
o.put("ids", ids);
List<Employee> list = mapper.selectForeach(o);
for(Employee e:list){
System.out.println(e);
}
使用foreach标签生成in语句:
<if test="ids!=null">
id in
<foreach collection="ids" item="empid"
open="(" separator="," close=")">
#{empid}
</foreach>
</if>
四、Mapper文件中的其他标记
4.1.sql标记
这个元素用来定义一些可以包含在其他语句中的可重用的sql片段,比如
<sql id="empColumn">
select id,last_name,first_name,title,salary,start_date,
comments,commission_pct
from s_emp
</sql>
如上sql片段可以包含在另外一个语句中,例如:
<select id="selectForeach" resultMap="empResult">
<include refid="empColumn"></include>
<where>
<if test="ids!=null">
id in
<foreach collection="ids" item="empid"
open="(" separator="," close=")">
#{empid}
</foreach>
</if>
</where>
</select>
4.2.include标签用来引入<sql>标记定义的语句
4.3.bind
bind元素可以从OGNL表达式中创建一个变量并将其绑定到上下文。比如:
<select id="selectByLike" resultMap="empResult">
select id,last_name,first_name,title,salary,start_date,
comments,commission_pct
from s_emp
<if test="_parameter!=null">
<bind name="title" value="'%'+_parameter+'%'"/>
where title like #{title}
</if>
</select>
五、 mybatis的一些特殊功能
5.1 多行结果集映射成Map
如果你有一个映射语句返回多行记录,并且你想以HashMap的形式存储记录的值,使用记录列名作为key值,而记录对应值或为value值。我们可以使用sqlSession.selectMap(),如下所示:
<select id="findAllStudents" resultMap="StudentResult">
select * from Students
</select>
Map<Integer, Student> studentMap = sqlSession.selectMap("com.briup.mappers.StudentMapper.findAllStudents", "studId");
这里studentMap将会将studId作为key值,而Student对象作为value值。
@MapKey("id")
Map<Integer,Emp> findByFirstName(String last_name);
如果希望自定义结果返回:
session.select("statement",param,ResultHandler);
5.2 使用RowBounds对结果集进行分页
有时候,我们会需要跟海量的数据打交道,比如一个有数百万条数据级别的表。由于计算机内存的限制我们不可能一次性加载这么多数据,我们可以获取到数据的一部分。特别是在Web应用程序中,分页机制被用来以一页一页的形式展示海量的数据。
MyBatis可以使用RowBounds逐页加载表数据。RowBounds对象可以使用offset和limit参数来构建。参数offset表示开始位置,而limit表示要取的记录的数目。假设如果你想每页加载并显示25条学生的记录,你可以使用如下的代码:
<select id="findAllStudents" resultMap="StudentResult">
select * from Students
</select>
然后,你可以加载如下加载第一页数据(前25条):
int offset =0 , limit =25;
RowBounds rowBounds = new RowBounds(offset, limit);
List<Student> = studentMapper.getStudents(rowBounds);
若要展示第二页,使用offset=25,limit=25;第三页,则为offset=50,limit=25。
但是其实Mybatis的分页功能是很弱的,它是基于内存的分页(查出所有记录再按偏移量和limit取结果),在大数据量的情况下这样的分页基本上是没有用的。(我们 也可以借助于各种类型的数据库自定义逻辑分页,比如oracle使用rownum)。 而且我们可以直接下载mybatis分页相关的插件(就是几个jar包)来用
select id,last_name
from (select id,last_name,rownum rw
from s_emp) r
where r.rw between 3 and 5;
PageHelper
5.3 缓存
将从数据库中加载的数据缓存到内存中,是很多应用程序为了提高性能而采取的一贯做法。MyBatis对通过映射的SELECT语句加载的查询结果提供了内建的缓存支持。默认情况下,启用一级缓存;即,如果你使用同一个SqlSession接口对象调用了相同的SELECT语句,则直接会从缓存中返回结果,而不是再查询一次数据库。
我们可以在SQL映射器XML配置文件中使用<cache />元素添加全局二级缓存。
二级缓存:
二级缓存就是global caching,它超出session范围之外,可以被所有sqlSession共享,它的实现机制和mysql的缓存一样,
开启它只需要在mybatis的配置文件开启settings里的
<setting name="cacheEnabled" value="true"/>
以及在相应的Mapper文件(例如userMapper.xml)里开启
<mapper namespace="dao.userdao">
... select statement ...
<!-- Cache 配置 -->
<cache eviction="FIFO" flushInterval="5000"></cache>
</mapper>
二级缓存: 在不同的session对象之间可以共享缓存数据
1.mybatis-config.xml文件中保证<setting name="cacheEnabled" value="true"/>设置中是缓存功能是开启的,默认就是开启的true
2.在需要二级缓存的xml映射文件中,手动开启缓存功能,在根元素中加入一个标签即可:<cache/>
3.一个session查询完数据之后,需要调用commit或者close方法后,这个数据才会进入到缓存中,然后其他session就可以共享到这个缓存数据了
注意:默认情况下,被二级缓存保存的对象需要实现序列化接口,可以通过cache标签的readOnly属性进行设置
cache标签常用属性:
<cache
eviction="FIFO" <!--回收策略为先进先出-->
flushInterval="60000" <!--自动刷新时间60s-->
size="512" <!--最多缓存512个引用对象-->
readOnly="true"/> <!--true表示对象不能被写出去,即不可以被序列化,false表示可以写出去,即可以被序列化,默认值是false-->
当你加入了<cache />元素,将会出现以下情况:
所有的在映射语句文件定义的<select>语句的查询结果都会被缓存
所有的在映射语句文件定义的<insert>,<update> 和<delete>语句将会刷新缓存
缓存根据最近最少被使用(Least Recently Used,LRU)算法管理
缓存会根据指定的时间间隔来刷新。
缓存会存储1024个对象
六 MyBatis中的注解
之前我们都是在映射器MapperXML配置文件中配置映射语句的。除此之外MyBatis也支持使用注解来配置映射语句。当我们使用基于注解的映射器接口时,我们不再需要在XML配置文件中配置了。如果你愿意,你也可以同时使用基于XML和基于注解的映射语句。
6.1 在映射器Mapper接口上使用注解
MyBatis对于大部分的基于XML的映射器元素(包括<select>,<update>)提供了对应的基于注解的配置项。然而在某些情况下,基于注解配置还不能支持基于XML的一些元素。
6.2 映射语句
MyBatis提供了多种注解来支持不同类型的语句(statement)如 SELECT,INSERT,UPDATE,DELETE。让我们看一下具体怎样配置映射语句
6.2.1 @Insert
我们可以使用 @Insert注解来定义一个INSERT映射语句:
package com.briup.mappers;
public interface StudentMapper{
@Insert("INSERT INTO STUDENTS(STUD_ID,NAME,EMAIL,ADDR_ID, PHONE)
VALUES(#{studId},#{name},#{email},#{address.addr Id},#{phone})")
int insertStudent(Student student);
}
自动生成主键
可以使用 @Options注解的userGeneratedKeys和keyProperty属性让数据库产生 auto_increment(自增长)列的值,然后将生成的值设置到输入参数对象的属性中。
@Insert("INSERT INTO STUDENTS(NAME,EMAIL,ADDR_ID, PHONE)
VALUES(#{name},#{email},#{address.addr Id},#{phone})")
@Options(useGeneratedKeys = true, keyProperty = "studId")
int insert Student(Student student);
这里STUD_ID列值将会通过MySQL数据库自动生成。并且生成的值将会被设置到student对象的studId属性中。
StudentMapper mapper = sqlSession.getMapper(Student Mapper.class);
mapper.insertStudent(student);
int studentId = student.getStudId();
有一些数据库如Oracle,并不支持AUTO_INCREMENT列属性,它使用序列(SEQUENCE)来产生主键的值。
我们可以使用 @SelectKey注解来为任意SQL语句来指定主键值,作为主键列的值。假设我们有一个名为STUD_ID_SEQ的序列来生成STUD_ID主键值。
@Insert("INSERT INTO STUDENTS(STUD_ID,NAME,EMAIL,ADDR_ID, PHONE)
VALUES(#{studId},#{name},#{email},#{address.addrId},#{phone})")
@SelectKey(statement="SELECT STUD_ID_SEQ.NEXTVAL FROM DUAL",
keyProperty="studId", resultType=int.class, before=true)
int insertStudent(Student student);
这里我们使用了 @Select Key来生成主键值,并且存储到了student对象的studId属性上。由于我们设置了before=true,该语句将会在执行INSERT语句之前执行。如果你使用序列作为触发器来设置主键值,我们可以在INSERT语句执行后,从sequence_name.currval获取数据库产生的主键值。
@Insert("INSERT INTO STUDENTS(NAME,EMAIL,ADDR_ID, PHONE)
VALUES(#{name},#{email},#{address.addrId},#{phone})")
@SelectKey(statement="SELECT STUD_ID_SEQ.CURRVAL FROM DUAL",
keyProperty="studId", resultType=int.class, before=false)
int insertStudent(Student student);
6.2.2 @Update
我们可以使用 @Update注解来定义一个UPDATE映射语句,如下所示:
@Update("UPDATE STUDENTS SET NAME=#{name}, EMAIL=#{email},
PHONE=#{phone} WHERE STUD_ID=#{studId}")
int updateStudent(Student student);
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
int noOfRowsUpdated = mapper.updateStudent(student);
6.2.3 @Delete
我们可以使用 @Delete注解来定义一个DELETE映射语句,如下所示:
@Delete("DELETE FROM STUDENTS WHERE STUD_ID=#{studId}")
int deleteStudent(int studId);
@Delete("DELETE FROM STUDENTS WHERE STUD_ID=#{studId}")
int deleteStudent(int studId);
6.2.4 @Select
我们可以使用 @Select注解来定义一个SELECT映射语句。
package com.briup.mappers;
public interface StudentMapper{
@Select("SELECT STUD_ID AS STUDID, NAME, EMAIL, PHONE FROM
STUDENTS WHERE STUD_ID=#{studId}")
Student findStudentById(Integer studId);
}
为了将列名和Student bean属性名匹配,我们为stud_id起了一个studId的别名。如果返回了多行结果,将抛出TooManyResultsException异常。
6.3 结果映射
我们可以将查询结果通过别名或者是 @Results注解与Java Bean属性映射起来。
package com.briup.mappers;
public interface StudentMapper{
@Select("select * from student")
@Results(id="studentResult",value={
@Result(id=true,column="id",property="id"),
@Result(column="name",property="name"),
@Result(column="gender",property="gender"),
@Result(column="birthday",property="birthday")
})
public Student findAll(int id);
}
注意:
@Results注解和映射器XML配置文件元素<resultMap> 对应。然而,早期版本不支持id属性,所以不能重复使用
目前我们使用的版本时3.4.1,可以给@Results注解添加ID属性,因此可以在目前的版本中重复使用。
例如,看下面的 findById()和 findAll()方法:
@Select("select * from student where id = #{id}")
@ResultMap("studentResult")
public Student findById(int id);
@Select("select * from student")
@Results(id="studentResult",value={
@Result(id=true,column="id",property="id"),
@Result(column="name",property="name"),
@Result(column="gender",property="gender"),
@Result(column="birthday",property="birthday")
})
public Student findAll(int id);
在此处我们可以使用@ResultMap("studentResult")引用定义在findAll()方法上的result。
【NOTE】:早起版本必须得重复定义。如果不想重复,这里有一个解决方法。我们可以创建一个映射器Mapper配置文件,然后配置<resultMap>元素,然后使用 @ResultMap注解引用此<resultMap>。 在StudentMapper.xml中定义一个ID为StudentResult的<resultMap>。
xml配置:
<mapper namespace="com.briup.mappers.StudentMapper">
<resultMap type="Student" id="StudentResult">
<id property="id" column="id" />
<result property="name" column="name" />
<result property="gender" column="gender" />
<result property="birthday" column="birthday" />
</resultMap>
</mapper>
在StudentMapper.java中,使用 @Result Map引用名为StudentResult的resultMap:
public interface StudentMapper{
@Select("SELECT * FROM STUDENTS WHERE STUD_ID=#{studId}")
@ResultMap("com.briup.mappers.StudentMapper.StudentResult")
Student findById(int studId);
@Select("SELECT * FROM STUDENTS")
@ResultMap("com.briup.mappers.StudentMapper.StudentResult")
List<Student> findAll();
}
6.4 一对一映射
MyBatis提供了 @One注解来使用嵌套select语句(Nested-Select)加载一对一关联查询数据。让我们看看怎样使用 @One注解获取妻子及其丈夫信息
public interface One2OneMapper {
@Select("select * from husband where id = #{hid}")
@ResultType(Husband.class)
public Husband selectHusbandById(int hid);
@Select("select * from wife where id = #{id}")
@Results(id="wifeAndhusResult",value={
@Result(property="husband",column="h_id",one=@One(select="selectHusbandById"))
})
public Wife selectWifeByWid(int wid);
}
这里我们使用了 @One注解的select属性来指定一个使用了完全限定名的方法上,该方法会返回一个Husband对象。使用column=”h_id”,则wife表中列h_id的值将会作为输入参数传递给selectHusbandById()方法。如果 @One SELECT查询返回了多行结果,则会抛出TooManyResultsException异常。
SqlSession session = openSession();
One2OneMapper mapper = session.getMapper(One2OneMapper.class);
Wife list = mapper.selectWifeByWid(5);
System.out.println(list);
session.close();
我们可以通过基于XML的映射器配置,使用嵌套结果ResultMap来加载一对一关联的查询。而3.5.5版本之前并没有对应的注解支持。但是我们可以在映射器Mapper配置文件中配置<resultMap>并且使用 @ResultMap注解来引用它。
3.5.5版本之后可以写:
@Select("select e.id,e.last_name,e.first_name,e.salary,"
+ " m.id m_id,m.last_name m_last_name "
+ " from s_emp e, s_emp m "
+ " where e.manager_id = m.id "
+ " and e.id = #{id}")
/* @ResultMap("baseMap") */
@Results(id = "empWithManager",value = {
@Result(column = "id",property = "id"),
@Result(property = "manager",one = @One(resultMap = "baseMap",columnPrefix = "m_")) //3.5.5以后支持。
})
Emp selectEmpWithManagerById(int id);
6.5 一对多映射
MyBatis提供了 @Many注解,用来使用嵌套Select语句加载一对多关联查询。现在让我们看一下如何使用 @Many注解获取一个班级及班级中所有学生列表信息:
public interface Many2OneMapper {
@Select("select id cid,name cname from s_clz where id = #{cid}")
@Results(id="clzAndstuResult",value={
@Result(property="id",column="cid"),
@Result(property="name",column="cname"),
@Result(property="stus",column="cid",many=@Many(select="selectStuByCid"))
})
public Clz selectByCid(int cid);
@Select("select * from s_stu where c_id = #{cid}")
public List<Student> selectStuByCid(int cid);
}
这里我们使用了 @Many注解的select属性来指向一个完全限定名称的方法,该方法将返回一个 List<Student>对象。使用column="cid",s_clz表中的检索出的别名列cid列值将会作为输入参数传递给selectByCid方法。
之前我们使用过嵌套结果ResultMap来加载一对多关联的查询。而 My 3.5.5版本之前,并没有对应的注解支持。但是我们可以在映射器Mapper配置文件中配置<resultMap>并且使用 @ResultMap注解来引用它。
3.5.5以后可以写:
@Select("select r.id,r.name,d.id d_id,d.name d_name "
+ " from s_region r,s_dept d "
+ " where r.id = d.region_id ")
@Results(id="regionWithDepart",value = {
@Result(column = "id",property = "id"),
@Result(column = "name",property = "name"),
@Result(property = "departs",many = @Many(columnPrefix = "d_",resultMap = "deptMap")) //3.5.5版本才有了resultMap属性和columnPrefix属性
})
List<Region> selectRegionWithDepart();