##mybatis动态sql
mybatis使用OGNL表达式语言来支持它的动态sql语句,使用起来非常便捷。
###1、if用法
#####a、在WHERE条件里使用if
假设现在有一个新的需求: 实现一个用户管理高级查询功能,根据输入的条件去检索用户信息。这个功能还需要支持以下三种情况: 1、当只输入用户名时,需要根据用户名进行模糊查询;2、当只输入邮箱时, 根据邮箱进行完全匹配;3、当同时输入用户名和邮箱时,用这两个条件去查询匹配的用户。
使用if来实现的动态sql如下:
<select id="selectByUser" resultType="tk.mybatis.simple.model.SysUser">
select id,
user_name userName ,
user_password userPassword ,
user_ema userEmail ,
user_info userInfo ,
head_img headImg ,
create_time createTime
from sys_user
where 1 = 1
<if test= " userName != null and userName !='' ">
and user_name like concat('%',#{userName},'%')
</if>
<if test = "userEmail != null and userEmail !=''" >
and user_ema = #{userEmail}
</if>
</select>复制代码
if标签有一个必填的属性值test
,test的属性值是一个符合IGNL要求的判断表达式,表达式结果可以是true或者false。除此以外所有非0的值都为true,只有0是false。OGNL判断在后面会单独列出来。这里忽略测试部分内容,只关注用法。
#####a、在UPADTE更新列里使用if
现在要实现这样一个需求:只更新有变化的字段。需要注意,更新的时候不能将原来有值但没有发生变化的字段更新为空或null。通过if 标签可以实现这种动态列更新。
<update id="updateByIdSelective">
update sys_user
set
<if test="userName!=null and userName!=''">
user_name= #{userName},
</if>
<if test="userPassword!=null and userPassword!=''">
user_password= #{userPassword} ,
</if>
<if test="userEmail!=null and userEmail!=''">
user_ema = #{userEmail},
</if>
<if test="userInfo!=null and userInfo!=''">
user_info= #{userInfo},
</if>
<if test="headImg!=null">
head_img = #{headImg, jdbcType=BLOB},
</if>
<if test="createTime!=null">
create_time = #{createTime, jdbcType=TIMESTAMP},
</if>
id = #{id}
where id = #{id}
</update>复制代码
这里使用了一个小技巧,通过
id = #{id}
where id = #{id}复制代码
两句sql保证在上面任何条件成立或不成立的情况下,拼接完成的最终的sql语句不会出错。
#####在INSERT 动态插入列中使用if
在数据库表中插入数据的时候,如果某一列的参数值不为空,就使用传入的值,如果传入参数为空,就使用数据库中的默认值(通常是空),而不使用传入的空值。使用if 就可以实现这种动态插入列的功能。
<insert id="insert4" useGeneratedKeys="true" keyProperty="id">
insert into sys_user(
user_name, user_password,
<if test="userEmail!=null and userEmail!=''">
user_ema,
</if>
user_info, head_img, create_time)
values(
#{userName}, #{userPassword},
<if test="userEmail!=null and userEmail!=''">
#{userEmail} ,
</if>
#{userInfo}, #{headImg,jdbcType=BLOB} ,
#{createTime, jdbcType=TIMESTAMP})
</insert>复制代码
###2、choose用法if
标签没法实现if--else的功能,要实现if--else的功能需要用到choose、when、otherwise
标签。一个choose
标签中包含when
和otherwise
两个标签,一个choose
中至少有一个when
,有0或1个otherwise
。
在己有的sys_
user表中,除了主键id外,我们认为user_
name (用户名)也是唯一的,所有的用户名都不可以重复。现在进行如下查询:当参数id 有值的时候优先使用id 查询,当id 没有值时就去判断用户名是否有值,如果有值就用用户名查询,如果用户名也没有值,就使SQL查询无结果。
在UserMapper接口里新增对应的方法:SysUser selectByIdOrUserName(SysUser sysUser) ;
在xml文件里使用choose
标签完成sql:
<select id="selectByIdOrUserName" resultType="tk.mybatis.simple.model.SysUser" >
select id ,
user_name userName ,
user_password userPassword ,
user_ema userEmail ,
user_info userInfo ,
head_img headImg ,
create_time createTime
from sys_user
where 1 = 1
<choose>
<when test="id!=null" >
and id= #{id}
</when >
<when test="userName!=null and userName!=''">
and user_name = #{userName}
</when>
<otherwise>
and 1 = 2
</otherwise >
</choose>
</select>复制代码
###3、where用法
使用<where>
标签改写selectByUser方法如下:
<select id="selectByUser" resultType="tk.mybatis.simple.model.SysUser">
select id,
user_name userName ,
user_password userPassword ,
user_ema userEmail ,
user_info userInfo ,
head_img headImg ,
create_time createTime
from sys_user
<where>
<if test= " userName != null and userName !='' ">
and user_name like concat('%',#{userName},'%')
</if>
<if test = "userEmail != null and userEmail !=''" >
and user_ema = #{userEmail}
</if>
</where>
</select>复制代码
这里使用了<where>
标签,当if条件成立的时候,会自动去掉and字段,如果不成立,也不会出现where字段,保证sql不会出错,确保了sql的整洁、贴切。
###4、set用法
修改updateByIdSelective方法,使用<set>
标签:
<update id="updateByIdSelective">
update sys_user
<set>
<if test="userName!=null and userName!=''">
user_name= #{userName},
</if>
<if test="userPassword!=null and userPassword!=''">
user_password= #{userPassword} ,
</if>
<if test="userEmail!=null and userEmail!=''">
user_ema = #{userEmail},
</if>
<if test="userInfo!=null and userInfo!=''">
user_info= #{userInfo},
</if>
<if test="headImg!=null">
head_img = #{headImg, jdbcType=BLOB},
</if>
<if test="createTime!=null">
create_time = #{createTime, jdbcType=TIMESTAMP},
</if>
id = #{id}
</set>
where id = #{id}
</update>复制代码
在set标签的用法中,SQL后面的逗号没有问题了,但是如果set元素中没有内容,照样会出现SQL错误,所以为了避免错误产生,类似id=#{id}这样必然存在的赋值仍然有保留的必要。从这一点来看,set标签并没有解决全部的问题,使用时仍然需要注意。
###5、trim用法
trim 标签有如下属性。
prefix :当trim 元素内包含内容时,会给内容增加prefix 指定的前缀。
prefixOverrides :当trim 元素内包含内容时,会把内容中匹配的前缀字符串去掉。
suffix :当trim 元素内包含内容时,会给内容增加suffix 指定的后缀。
suffixOverrides :当trim 元素内包含内容时,会把内容中匹配的后缀字符串去掉。<where><set>
标签实质也是通过<trim>
标签去实现的:
where 标签对应trim 的实现如下:
<trim prefix="WHERE" prefixOverrides="AND |OR " >
..
</trim>复制代码
set 标签对应的trim 实现如下:
<trim prefix="SET" suffixOverrides="," >
..
</trim>复制代码
###6、foreach用法
在sql查询中经常会使用到IN的用法,如in(1,2,3),在mybatis中可以使用${}的方式来实现,但是会导致sql注入,所以使用foreach搭配#{}来实现。
#####a、foreach实现in
在UserMapper接口里添加selectByIdList方法:List<SysUser> selectByIdList(List<Long> idList);
在xml文件中添加新的查询标签:
<select id= "selectByIdList" resultType= "tk.mybatis.simple.model.SysUser">
select id,
user_name userName ,
user_password userPassword ,
user_ema userEmail,
user_info userInfo ,
head_img headImg,
create_time createTime
from sys_user
where id in
<foreach collection="list" open="(" close=")" separator=","
item="id" index ="i" >
#{id}
</foreach>
</select>复制代码
foreach 包含以下属性。
collection: 必填,值为要选代循环的属性名。这个属性值的情况有很多。
item :变量名,值为从法代对象中取出的每一个值。
index :索引的属性名,在集合数组情况下值为当前索引值, 当选代循环的对象是Map类型时,这个值为Map的key(键值)。
open:整个循环内容开头的字符串。
close: 整个循环内容结尾的字符串。
separator:每次循环的分隔符。
collection属性的值:
1、集合时使用list
2、数组时使用array
3、多个参数的时候推荐使用@Param注解
4、当是map的时候,可以使用@Param注解,也可以使用默认的_parameter
5、参数使用对象的时候,直接使用对象属性名,存在对象嵌套的时候可以使用对象属性名.属性来调用复制代码
#####b、foreach实现批量插入
insert into sys_user(
user_name , user_password, user_ema ,
user_info , head_img , create_time)
values
#{user.userName}, #{user.userPassword},#{user.userEmail} ,
#{user.userInfo} , #{user.headImg, jdbcType=BLOB} ,
#{user.createTime , jdbcType=TIMESTAMP})
</foreach>
</insert >复制代码
当使用mysql数据库的时候,可以在<insert>
标签上增加useGeneratedKeys和k eyProperty 两个属性,来批量返回插入的自增主键。
#####c、foreach实现动态update
<update id="updateByMap">
update sys_user
set
<foreach collection="_parameter" item="val" index="key" separator=",">
${key}=#{val}
</foreach>
where id=#{id}
</update>复制代码
这里的key作为列名,对应的值作为该列的值,通过foreach将需要更新的字段拼接在SQL语句中。
###7、bind用法
bind 标签可以使用OGNL表达式创建一个变量井将其绑定到上下文中。例如selectByUser方法中用到了like查询条件:
<if test= " userName != null and userName !='' ">
and user_name like concat('%',#{userName},'%')
</if>复制代码
但是mysql的concat函数是支持多个参数的,oracle只支持2个参数,可以使用bind标签实现,避免了数据库切换带了的问题:
<if test= " userName != null and userName !='' ">
<bind name="userNameLike" value ="'%'+userName+'%'"/>
and user_name like #{userNameLike}
</if>复制代码
###8、多数据库支持
mybatis可以根据不同的数据库厂商执行不同的语句,这种实现是基于databaseId。MyBatis 会加载不带databaseId属性和带有匹配当前数据库databaseld 属性的所有语句。想要支持这一功能,需要在mybatis-config.xml中配置下面的项:<databaseIdProvider type="DB_VENDOR" />
<databaseidProvider type= ” DB VENDOR ” >
<property name ="SQL Server" value ="sqlserver" />
<property name ="DB2" value ="db2" />
<property name ="Oracle" value ="oracle" />
<property name ="MySQL" value ="mysql"/>
<property name ="PostgreSQL" value ="postgresql" />
<property name ="Derby" value ="derby" />
<property name ="HSQL" value ="hsqldb" />
<property name ="H2" value ="h2" /〉
</data:baseIdProvider>复制代码
在select、insert、delete、update、selectKey、sql
上都是有databaseId属性的,对于不同版本的like用法如下:
<select id="selectByUser" databaseId="mysql"resultType="tk.mybatis.simple.model.SysUser" >
select* from sys_userand where user_name like concat('%',#{userName},'%'}
</select>
<select id="selectByUser" databaseId="oracle" resultType="tk.mybatis.simple.model.SysUser" >
select * from sys userand
where user_name like '%'||#{userName}||'%'
</select>复制代码
如果只是要改变部分的sql,也可以搭配if标签来使用,selectByUser 方法可以修改如下。这样就可以针对局部来适配不同的数据库了。
<select id="selectByUser" resultType="tk.mybatis.simple.model.SysUser" >
select id,
user_name userName ,
user_password userPassword ,
user_ema userEmail ,
user_info userInfo ,
head_img headImg ,
create_time createTime
from sys_user
<where>
< if test="userName!=null and userName!=''">
<if test="_databaseId =='mysql'">
and user_name like concat('%',#{userName},'%')
</if>
<if test="_databaseid=='oracle'">
and user_name like '%'||#{userName}||'%'
</if>
</if>
<if test="userEmail!='' and userEmail!=null" >
and user_ema = #{userEmail}
</if>
</where >
< /select>复制代码
###9、OGNL用法
- e1 or e2
- e1 and e2
- e1 == e2 或e1 eq e2
- e1 != e2 或e1 neq e2
- e1 lt e2 :小于
- e1 lte e2 :小于等于,其他表示为gt (大于)、gte (大于等于)
- e1 + e2 、e1 食e2 、e1/e2 、e1 - e2 、e1 % e2
- !e 或not e :非,取反
- e.method(args) : 调用对象方法
- e.property : 对象属性值
- el[ e2 ] : 按索引取值( List 、数组和Map)
- @class@method(args):调用类的静态方法
- @class@field :调用类的静态字段值