Mybatis-动态SQL

动态SQL

if

<!-- resultType 和实体类关联起来 -->
<select id="listProduct" resultType="Product">
	select * from product_
	<if test="name!=null">
	<!-- #{name}会自动获取传进来对象的name属性值-->
		where name like concat('%',#{name},'%')
	</if>		 	
</select>

Map<String,Object> params = new HashMap<>(); 
params.put("name","a");
List<Product> ps2 = session.selectList("listProduct",params);
for (Product p : ps2) {
    System.out.println(p);
}      

where

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

当没有name参数，却有price参数的时候

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

<where>标签会进行自动判断
如果任何条件都不成立，那么就在sql语句里就不会出现where关键字
如果有任何条件成立，会自动去掉多出来的 and 或者 or

同理，update语句里使用set标签

 <update id="updateProduct" parameterType="Product" >
     update product_
     <set>
         <if test="name != null">name=#{name},</if>
         <if test="price != null">price=#{price}</if>
           
     </set>
      
      where id=#{id}   
 </update>

trim

用来定制想要的功能，比如where标签就可以用

<trim prefix="WHERE" prefixOverrides="AND |OR ">
  ... 
</trim>

set标签就可以用

<trim prefix="SET" suffixOverrides=",">
  ...
</trim>

if else

使用where、choose包裹 when、when、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

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

参考 Oracle 的 in 语法

# 薪水要么等于3000要么等于6000
select * from employees where salary in(3000,6000)

bind

    <mapper namespace="com.how2java.pojo">
    
<!-- 		 本来的模糊查询方式 -->
<!--         <select id="listProduct" resultType="Product"> -->
<!--             select * from   product_  where name like concat('%',#{0},'%') -->
<!--         </select> -->
             
<!-- 		把模糊查询改为bind标签 -->   
	        <select id="listProduct" resultType="Product">
	            <bind name="likename" value="'%' + name + '%'" />
	            select * from   product_  where name like #{likename}
	        </select>
         
    </mapper>