myBatis 动态sql
1. if
<select id="getMStockOutVoListByCondiTion" resultMap="MStockOutVoMap" parameterType="map">
SELECT mo.out_id,mo.m_id,m.m_name,mo.out_total,mo.out_time,mo.operator FROM m_stock_out mo,material m WHERE mo.m_id=m.m_id
<if test="mName !='' and mName !='%%'">
AND m.m_name LIKE #{mName}
</if>
<if test="outTime != ''">
AND DATE_FORMAT(mo.out_time,'%Y-%m-%d')=#{outTime} <!-- 注意and不能大写 -->
</if>
</select>
2.where
<select id="selectByParams" parameterType="map" resultType="user">
select * from user
<where>
<if test="id != null ">id=#{id}</if>
<if test="name != null and name.length()>0" >and name=#{name}</if>
<if test="gender != null and gender.length()>0">and gender = #{gender}</if>
</where>
</select>
<select id="findUserById" resultType="user">
select * from user
<where>
<if test="id != null">
id=#{id}
</if>
and deleteFlag=0;
</where>
</select>
3.set
<update id="update" parameterType="material" >
UPDATE material
<set>
<if test="materialCode!=null">
material_code=#{materialCode},
</if>
<if test="name!=null">
name=#{name},
</if>
<if test="unit!=null">
unit=#{unit},
</if>
<if test="categoryId!=null">
category_id=#{categoryId},
</if>
</set>
WHERE id=#{id}
</update>
4.choose, when, otherwise,choose类似于switch,只执行其中一个条件
<select id="findActiveBlogLike"
resultType="Blog">
SELECT * FROM BLOG WHERE state = ‘ACTIVE’
<choose>
<when test="title != null">
AND title like #{title}
</when>
<when test="author != null and author.name != null">
AND author_name like #{author.name}
</when>
<otherwise>
AND featured = 1
</otherwise>
</choose>
</select>
5.foreach
sql传递数组或List,mybatis使用foreach解析,我们可以使用foreach中元素进行sql语句的拼接,请求数据
通过一个例子来看:
需求:SELECT * FROM USER WHERE id=1 OR id=10 OR id=16
<if test="ids != null">
<foreach collection="ids" item="user_id" open="AND (" close=")" separator="or" >
每次遍历需要拼接的串
id= #{user_id}
</foreach>
</if>
或者:SELECT * FROM USER WHERE id IN(1,10,16)
<if test="ids != null">
<foreach collection="ids" item="user_id" open="and id IN(" close=")" separator=",">
id= #{user_id}
</foreach>
</if>
其中,collection:指定输入对象中集合属性,item: 每个遍历生成对象,open:开始遍历时拼接串,
close: 结束遍历是拼接的串,separator: 遍历的两个对象中需要拼接的串
6.trim标记是一个格式化的标记,可以完成set或者是where标记的功能
select * from user
<trim prefix="WHERE" prefixoverride="AND |OR">
<if test="name != null and name.length()>0"> AND name=#{name}</if>
<if test="gender != null and gender.length()>0"> AND gender=#{gender}</if>
</trim>
假如说name和gender的值都不为null的话打印的SQL为:
select * from user where [and] name = 'xx' and gender = 'xx'
[and]标记的地方是不存在第一个and的
prefix:前缀
prefixoverride:去掉第一个and或者是or
update user
<trim prefix="set" suffixoverride="," suffix=" where id = #{id} ">
<if test="name != null and name.length()>0"> name=#{name} , </if>
<if test="gender != null and gender.length()>0"> gender=#{gender} , </if>
</trim>
假如说name和gender的值都不为null的话打印的SQL为:update user set name=‘xx’ , gender=‘xx’ [,] where id=‘x’
[,]的地方不存在逗号,而且自动加了一个set前缀和where后缀,上面三个属性的意义如下,其中prefix意义如上:
suffixoverride
:去掉最后一个逗号(也可以是其他的标记,就像是上面前缀中的and一样)
suffix
:后缀
https://www.cnblogs.com/qiankun-site/p/5758924.html
https://blog.csdn.net/little_nai/article/details/53436453