mybatis动态SQL简单使用

mybatis动态SQL

1. if
2. choose(when,otherwise)
3. trim(where, set)
4. foreach

• if元素

  • <select>
    	select * from dual where 1=1
      <if test="'1'=='1' and '2'!='1'">
      	and 2=2
      </if>
    </select>
    

• choose元素

  • <select>
    	select * from dual where 1=1
      <choose><!--switch-->
      	<when test="'1'=='1' and '2'!='1'"><!--case-->
        	and 2=2
        </when>
        <when test="'2'=='2' and '2'!='1'">
        	and 3=3
        </when>
        <otherwise><!--default-->
        	and xxx is not null;
        </otherwise>
      </choose>
    </select>
    

• trim|where|set元素

  • <!--
    trim and where
    prefix-语句前缀
    predixOverrides-要去掉开头哪种字符
    -->
    <select>
    	select * from dual
      <trim prefix="where" predixOverrides="and">
      <if test="'1'=='1' and '2'!='1'">
      	and 2=2
      </if>
      </trim>
    </select>
    

  • <!--
    set遇到逗号会把对应的逗号去掉
    -->
    <update>
    	update user
      <set>
      	<if test="name!=null">
        	name=#{name},
        </if>
      </set>
    </update>
    
    <!--
    trim
    suffixOverides-要去掉结尾哪种字符
    -->
    <update>
    	update user
      	<trim profix="set" suffixOverides=",">
      	<if test="name!=null">
        	name=#{name},
        </if>
        </trim>
    </update>
    

• foreach

  • <!--
    collection配置的是传递过来的参数名称，他可以是数组、list、set等集合
    item配置的循环中的具体元素
    index是指元素在集合的位置下标
    open和colse配置的是以什么符号将这些元素包装起来
    separator是各个元素的间隔符
    -->
    <select>
    	select * from user where id in
      <foreach item="id" index="index" collection="userlist" open="(" separator="," close=")">
    		#{id}
      </foreach>
    </select>
    

• bind元素

  • 作用：定义sql的上下文变量，可定义多个使用

  • <select>
      <bind name = "pattern" value="'%'+ name +'%'"
    	select id,name from user where name like #{name}
    </select>