mybatis关于动态SQL

超、凢脫俗 2021-11-09 02:44 365阅读 0赞

# where，if 标签 #

<select id="queryByCountryCity" resultType="com.sz.pojo.Addresses">
                select * from addresses
                <where>
    
                    <if test="city != null and city!=''">
                        and city = #{ city}
                    </if>
    
                    <if test="country != null">
                        and country = #{ country}
                    </if>
    
                </where>
    
        </select>

*  where标签可以消除字段前面的and，if判断字段属于某种情况就执行if标签里的内容

# set 标签 #

<update id="update" parameterType="com.sz.pojo.Addresses">
                update addresses
                set
                <if test="city != null and city != ''">
                    city = #{ city},
                </if>
                <if test="country != null and country != ''">
                    country = #{ country},
                </if>
                <if test="street != null and street != ''">
                    street = #{ street},
                </if>
                <if test="state != null and state != ''">
                    state = #{ state},
                </if>
                <if test="zip != null and zip != ''">
                    zip = #{ zip},
                </if>
        </update>

*  set 标签用于消除每个字段后的逗号

# choose,when,otherwise #

<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>

*  choose标签是选择的，第一个when满足条件就不会继续向下查询第二个when了，  
    前面的when如果都不满足，那么就会用otherwise里的条件

# trim #

*  trim标签是用于补或删sql语句中缺少或多余的元素

*  prefix:补前面缺少的元素
 *  prefixOverrides:去掉前面多余的元素
 *  suffix:补后面缺少的元素
 *  suffixOverrides:去掉后面多余的元素

# foreach #

*  foreach：遍历传进来的集合里的所有元素

<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>

# bind #

<select id="selectBlogsLike" resultType="Blog">
      <bind name="_city" value="'%' + city + '%'" />
      SELECT * FROM BLOG
      WHERE title LIKE #{ _city}
      
    </select>

# sql #

<sql id="baseColumn">
    	country,state,city
    <sql>

<select  id="listAll" resultType="com....">
    	select 
    	<include refid="baseColumn"/>
    	from addresses
    </select>