在写sql语句时如果使用语句拼接,根据不同的参数组织不同的语句,经常出现少或多一个and,缺少空格,最后出现逗号等问题,mybatis通过动态sql来解决这些问题。 mybatis中包含有if、choose、when、otherwise、trim、where、set、foreach等标签 动态sql中的表达式使用的是OGNL表达式 使用if标签来判断是否符合该条件 有时后续一个条件都没有,将不需要使用where条件,此时SQL就变成了 出现这种情况有两种方式解决 由于where只会去除开头的and或or,有时使用where元素与预期不符,此时可以使用trim标签 trim标签有几个子元素prefix/prefixOverrides/suffixOverrides choose与when和otherwise组合使用,分支选择,相当于switch-case,只会进入一个分支 set标签用于动态更新,只更新需要更新的列,忽略其他不更新的列,set元素会在字符串开头添加set,并且会删除掉额外的逗号 也可以使用trim来实现 使用foreach标签来进行对集合的遍历 mybatis包含两个内置参数 bind可以将OGNL表达式的值绑定到一个变量中,并将其绑定到当前上下文,方便后来引用这个变量的值 sql标签用于抽取可重用的sql片段,使用include标签来引用sql标签动态sql
if标签
<!-- 使用动态sql 测试if标签 -->
<select id="getEmployeeByConditon" resultMap="emp">
select * from employee where id = #{id}
<if test="name != null and name!=''">
and name like #{name}
</if>
<if test="salary != null and salary != 0">
and name = #{salary}
</if>
</select>
where标签
select * from employee where
<!-- 使用动态sql 测试if和where标签 -->
<select id="getEmployeeByCondition" resultMap="emp">
select * from employee
<where>
<if test="id != null and id != 0">
and id = #{id}
</if>
<if test="name != null and name!=''">
and name like #{name}
</if>
<if test="salary != null and salary != 0">
and salary = #{salary}
</if>
</where>
</select>
trim标签
<select id="getEmployeeByCondition" resultMap="emp">
select * from employee
<trim prefix="where" suffixOverrides="and | or">
<if test="id != null and id != 0">
id = #{id} and
</if>
<if test="name != null and name!=''">
name like #{name} and
</if>
<if test="salary != null and salary != 0">
salary = #{salary} and
</if>
</trim>
</select>
choose标签
<select id="getEmployeeByConditionChoose" resultMap="emp">
select * from employee
<trim prefix="where" suffixOverrides="and | or" prefixOverrides="and | or">
<!-- 有id时用id查,有name时用name查,否则查询所有 -->
<choose>
<when test="id != null and id != 0">
id = #{id} and
</when>
<when test="name != null and name!=''">
name like #{name} and
</when>
<otherwise>
</otherwise>
</choose>
</trim>
</select>
set标签
<update id="updateEmployeeBySet">
update employee
<set>
<if test="name != null and name != ''">
name = #{name},
</if>
<if test="salary != null and salary != 0">
salary = #{salary},
</if>
</set>
where id = #{id}
</update>
<update id="updateEmployeeBySet">
update employee
<trim prifix="set" suffixOverrides=",">
<if test="name != null and name != ''">
name = #{name},
</if>
<if test="salary != null and salary != 0">
salary = #{salary},
</if>
</trim>
where id = #{id}
</update>
foreach标签
<select id="selectByIds" resultType="User">
select * from user
<where>
id in
<!-- collection:指定遍历的集合 list类型参数会特殊封装到map中,key为list
item: 将当前遍历出的元素赋值给指定的变量
separator: 每个元素之间的分隔符
open: 遍历所有结果拼接一个开始的字符
close: 遍历所有结果拼接一个结束的字符
index: 索引。遍历list的时候,index是索引,item是当前值;遍历map的时候,index是map的key,item是map的值
-->
<foreach collection="list" index="index" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</where>
</select>
内置参数
<select id="selectUserByCondition" resultType="User">
select * from user
<if test="_parameter != null">
<where>
<if test="id != null and id != 0">
and id = #{_parameter.id}
</if>
<if test="name != null">
and name = #{_parameter.name}
</if>
<if test="account != null and account != 0">
and account = #{_parameter.account}
</if>
</where>
</if>
</select>
bind绑定
<select id="selectUserByCondition" resultType="User">
select * from user
<where>
<if test="id != null and id != 0">
and id = #{id}
</if>
<if test="name != null">
<bind name="_name" value="'%'+name+'%'"/>
and name like #{_name}
</if>
<if test="account != null and account != 0">
and account = #{account}
</if>
</where>
</select>
sql标签
<sql id="insertColumn">
`name`,account
</sql>
<insert id="addUser">
insert into `user` (
<include refid="insertColumn">
</include>
)
values (#{name},#{account})
</insert>