Mybatis动态SQL标签

朱雀 2022-01-07 06:57 297阅读 0赞

### 动态sql ###

###### if ######

<select id="listProduct" resultType="Product">
                select * from product_
                <if test="name!=null">
                    where name like concat('%',#{name},'%')
                </if>        
            </select>

###### where ######

自动去除多余的and和or

<select id="listProduct" resultType="Product">
    	select * from product_
    	<where>
    		<if test="name!=null">
    			and name like concat('%',#{name},'%')
    		</if>		 	
    		<if test="price!=null and price!=0">
    			and price > #{price}
    		</if>	
    	</where>	 	
    </select>

###### set ######

自动去除最后一个逗号

<set>
         	<if test="name != null">name=#{name},</if>
         	<if test="price != null">price=#{price}</if>
        </set>

###### trim ######

和前两个差不多  
prefix 前缀  
prefixOverrides 去掉第一个  
suffixOverrides 去掉最后一个

<select id="listProduct" resultType="Product">
            select * from product_
            <trim prefix="WHERE" prefixOverrides="AND |OR ">
                <if test="name!=null">
                    and name like concat('%',#{name},'%')
                </if>        
                <if test="price!=null and price!=0">
                    and price > #{price}
                </if>
            </trim>      
        </select>

###### 条件分支 ######

如果没有符合的，根据otherwise查询。

<select id="listProduct" resultType="Product">
    	  SELECT * FROM product_ 
    	  <where>
    	  	<choose>
    		  <when test="name != null">
    		    and name like concat('%',#{name},'%')
    		  </when>			  
    		  <when test="price !=null and price != 0">
    		    and price > #{price}
    		  </when>			  		
    	  	  <otherwise>
    	  	  	and id >1
    	  	  </otherwise>
    	  	</choose>
    	  </where>
    </select>

###### foreach ######

可用于根据ID查询多个、删除多个

SELECT * FROM product_ 
     	WHERE ID in
    			<foreach item="item" index="index" collection="list" open="(" separator="," close=")">
          		             #{item}
    			</foreach>

###### bind ######

备用字符串

<select id="listProduct" resultType="Product">
                <bind name="likename" value="'%' + name + '%'" />
                select * from   product_  where name like #{likename}
            </select>