MyBatis动态SQL
1、if判断标签
<!-- if查询员工,要求携带那个字段就查询那个字段-->
<select id = "getEmpsByIf" resultType = "com.qut.mybatis.bean.Employee" >
select * from tbl_employee
where 1 = 1
<!-- test 判断表达式(OGNL) ,遇见特殊符号应该去写转义字符-->
<if test = "id != null">
id = #{id}
</if>
<if test = "lastName != null and lastName != ''">
and lastName like #{lastName}
</if>
<if test = "email != null and email.trim() != ''">
and email = #{email}
</if>
<!-- OGNL会进行字符串与数字的转换 -->
<if test="gender == 0 or gender == 1">
and gender = #{gender}
</if>
</select>
2、where标签会去掉多余的or and标签,但只会去掉前面的不会去掉后面的
<select id = "getEmpsByIf" resultType = "com.qut.mybatis.bean.Employee" >
select * from tbl_employee
<where>
<if test = "id != null">
id = #{id}
</if>
<if test = "lastName != null and lastName != ''">
and lastName like #{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>
3、trim标签
<!-- Trim -->
<select id = "getEmp" resultType = "com.qut.mybatis.bean.Employee">
select * from tbl_employee
<!-- 后面多出的and or
prefix:前缀,给拼接后的字符串加一个前缀
prefixOverrides:前缀覆盖,去掉前缀
suffix:后缀,给拼接后的字符串加一个后缀
suffixOverrides:后缀覆盖,去掉后缀
-->
<trim prefix ="where" suffixOverrides = "and">
<if test = "id != null">
id = #{id} and
</if>
<if test = "lastName != null and lastName != ''">
lastName like #{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>
4、choose标签
<!-- choose
如果带了id就用id去查、如果带了lastName就用lastName去查
-->
<select id = "getEmp" resultType = "com.qut.mybatis.bean.Employee">
select * from tbl_employee
<where>
<choose >
<when test = "id != null"> id = #{id}</when>
<when test = "lastName != null"> lastName like #{lastName}</when>
<when test = "email != null">email = #{email}</when>
<otherwise>
gender = 0
</otherwise>
</choose>
</where>
</select>
5、set标签
<!--set封装修改条件,不需要管多余的逗号问题) -->
<update id = "updateEmp" >
update tbl_employee
<set>
<if test = "lastName != null">
lastName = #{lastName},
</if>
<if test = "email != null">
email = #{emil},
</if>
<if test = "gender != null">
gender = #{gender}
</if>
</set>
<where>
id = #{id}
</where>
</update>
6、foreach
<!-- foreach -->
<select id = "getEmp" resultType = "com.qut.mybatis.bean.Employee">
select * from tbl_employee
<!--
collection:指定要遍历的集合
item:当前遍历的元素
separator:每个元素之间的分隔符
open:遍历所有结果拼接一个开始字符
close:遍历所有结果拼接一个结束字符
index:遍历list是索引,item就是值;遍历map是key,item就是值
#{变量名}:取出当前的值
-->
<foreach collection = "ids" item ="item_id" separator = "," open = " where id (" close = ")" index = "">
#{item_id}
</foreach>
</select>
7、foreach批量保存
<!-- foreach批量保存 -->
<insert id ="addEmps">
insert into tbl_employee(lastName,email,gender,d_id)
values
<foreach collection = "emps" item = "emp" separator = ",">
(#{emp.lastName},#{emp.email},#{emp.gender},#{emp.dept.id})
</foreach>
</insert>
8、内置参数
<!-- 内置参数:不只是方法传递归来的参数可以用来判断-->
<!-- _parameter:整个参数
单个参数:_parameter代表这个参数
多个参数:参数会被封装为map;_parameter代表这个map
_databaseId:如果配置了DatabaseIdProvider标签,_databaseId代表当前数据库的别名。
-->
<select id = "getEmp" resultType = "om.qut.mybatis.bean.Employee">
<if test ="_databaseId == 'mysql'">
select * from tbl_employee
<if test = "_parameter != null">
where id = #{_parameter.lastName}
</if>
</if>
<if test ="_databaseId == 'oracle'">
select * from tbl_employees
</if>
</select>
9、bind标签
<!-- bind:可以将OGNL表达式绑定到变量中 -->
<select id = "getEmp" resultType = "om.qut.mybatis.bean.Employee">
<bind name = " _lastName " value = " '%' + lastName + '%' " />
select * from tbl_employee
<if test = "_parameter != null">
where lastName like #{_lastName}
</if>
</select>
10、sql标签
<!-- sql:用来抽取可重用的sql片段,方便后边引用 -->
<sql id = "insertColumn">
employee_id,lastName,email
</sql>
<!-- <include refid = "insertColumn"></include> -->
MyBatis缓存
/*
* 两级缓存:
* 一级缓存(本地缓存):与数据库同一次会话期间查询到的数据会存放在本地缓存
* 如果以后需要获取相同的数据,直接从缓存中拿没必要再去查询数据库。
* 一级缓存失效情况:
* 1、sqlSession不同
* 2、sqlSession相同,查询条哈不同
* 3、sqlSession相同,两次查询期间执行了增删改操作
* 4、sqlSession相同,手动清除一级缓存 session.clearCache();
* 二级缓存(全局缓存):基于namespace级别的缓存,一个名称空间对应一个二级缓存;
* 工作机制:
* 1、一个会话查询一条数据,这个数据会被放在一级缓存中;
* 2、如果会话关闭,一级缓存的数据会被保存到二级缓存中;新的会话查询信息就可以参照二级缓存的内容;
* 3、sqlSession既有employeeMapper==》employee对象,又有depatementMapper ==》department对象
* 不同的namespace会放在自己对应的缓存(map)中
* 使用步骤:
* 1)开启全局二级缓存配置<setting name = "cacheEnable" value = "true"></setting>
* 2)去mapper.xml文件中配置使用二级缓存 <cache></cache>
* 3)POJO需要实现序列化接口 implements Serializable
* 和缓存有关的设置与属性:
* 1)cacheEnable = true | false 关闭缓存(仅关闭二级缓存)
* 2)每个select标签都有useCache = true | false(仅关闭二级缓存)
* 3)每个增删改标签flushCache = true | false (增删改完成之后会删除缓存,会清除一二级缓存)
* 4)sqlSession.clearCache()只清除一级缓存,跟二级缓存无关
* 5)loaclCacheScope:本地缓存作用域(一级缓存SESSION),当前会话的所有数据保存在会话缓存中
* STATEMENT,可以禁用掉一级缓存。
*
* 新的会话会先去查找二级缓存再去查找一级缓存
* */