MyBatis——动态SQL
一、动态SQL:
1.条件判断:
(1)if标签:
<if test=" age != null and age != '' "> and age = #{age} </if>
(2)choose标签:类似java中的switch语句;一个when成立即结束choose,如果都不满足执行otherwise;
<choose> <when test=""></when> <when test=""></when> <otherwise></otherwise> </choose>
2.拼关键字:
(1)where标签:自动忽略首个and/or;
<where> <if test="state != null"> AND state = #{state} </if> <if test="title != null"> AND title like #{title} </if> <if test="author != null and author.name != null"> AND author_name like #{author.name} </if> </where>
(2)set标签:自动剔除不相关的逗号;
使用if+set组合标签,某项值为null时,保持数据库原值;
<set> <if test=" userName!=null and userName!='' "> user_name=#{userName}, </if> <if test=" userPassword!=null and userPassword!='' "> user_password=${userPassword}, </if> </set>
(3)trim标签:自定义剔除;可用于代替where/set;
参数:
1)prefix:给sql语句拼接的前缀;
2)suffix:给sql语句拼接的后缀;
3)prefixesToOverride:去除sql语句前面的关键字或者字符;
4)suffixesToOverride:去除sql语句后面的关键字或者字符;
where: <trim prefix="where" prefixOverrides="and|or"></trim> set: <trim prefix="set" suffixOverrides=","></trim>
3.循环:foreach标签:主要和in配合使用;
<foreach collection="" item="" index="" open="" close="" separator="" ></foreach>
(1)item:元素迭代的别名;
(2)index:从0开始的迭代次数;
(3)open:开始;
(4)close:结束;
(5)separator:分隔符;
<select id="selectPostIn" resultType="domain.blog.Post"> SELECT * FROM POST P WHERE ID in <foreach item="item" index="index" collection="list" open="(" separator="," close=")"> #{item} </foreach> </select>
(6)collection:传入的参数类型;
1)Map集合:
1、键名(Stirng)+键值(list集合);collection=键名;parameterType=不写;
2)List集合:
1、简单类型集合:collection=list(小写);parameterType=简单类型;
2、对象集合:collection=list;parameterType=对象类型/Object;
3)数组:
1、简单类型数组:collection=array(小写);parameterType=简单类型;
2、对象数组:collection=array(小写);parameterType=对象类型/Object;
二、SQL片段:
(1)标签:<sql id="">sql语句</sql>
(2)引用:<include refid="id"></include>
//如果要引用的片段不在同一映射文件内,refid=namespace+id;