Mybatis动态sql用法

Mybati动态sql标签

mybatis里的动态sql元素有<if> , <choose> , <when> ,<trim> , <set> , <foreach> , <bind>

为什么要加入动态sql呢？

因为开发人员使用jdbc或者其他的框架进行sql开发时，会根据需求去手动拼装sql,这是一个非常麻烦而且痛苦的工作，

所以mybatis提供了对sql语句的动态组装功能，而且很强大，恰巧可以解决这类问题。

下面对动态sql的节点一一举例说明

1.<if> 判断语句用于条件判断

    <select id="findCustomerBynameAndjob" parameterType = "map" resultType ="customer">
    select id ,username,jobs,phone from t_customer where 1=1
    <if test="username!=null and username!='' ">
    and username like concat('%',#{username},'%')
    
    </if>
    <if test="jbos!=null and jobs != '' ">
    and jobs=#{jobs}
    </if>
    
    
    </select>

2.<choose>(<when>、<otherwise>) xian相当于java中的swich..case..default 语句，用于多条件分支判断

    <select id="findCustomerBynameAndjob1" parameterType = "map" resultType ="customer">
    select id ,username,jobs,phone from t_customer where 1=1
    <choose>
    <when test="username!=null and username!='' ">
    and username like concat('%',#{username},'%')
    </when>
    <when test="jbos!=null and jobs != '' ">
     
    and jobs=#{jobs}
    
    </when>
    <otherwise>
    and phone is not null
    </otherwise>
    </choose>
    </select>

3.<where>,<trim>,<set>辅助元素，用于处理一些sql拼装，特殊字符问题

	<select id="findCustomerBynameAndjob2" parameterType = "map" resultType ="customer">
    select id ,username,jobs,phone from t_customer 
    <where>
    <if test="username!=null and username!='' ">
    and username like concat('%',#{username},'%')
    
    </if>
    <if test="jbos!=null and jobs != '' ">
    and jobs=#{jobs}
    </if>
    
    </where>
    </select>
   
    <select id="findCustomerBynameAndjob3" parameterType = "map" resultType ="customer">
    select id ,username,jobs,phone from t_customer 
    <trim prefix = "where" prefixOverrides = "and">
    <if test="username!=null and username!='' ">
    and username like concat('%',#{username},'%')
    
    </if>
    <if test="jbos!=null and jobs != '' ">
    and jobs=#{jobs}
    </if>
    
    </trim>
    </select>
    
 	<update id="updateCustomer" parameterType = "map">
 	update t_customer
 	<set>
 	<if test="username!=null and username!='' ">
    username = #{username},
    
    </if>
    <if test="jobs!=null and jobs != '' ">
    jobs = #{jobs},
    </if>
 	</set>
	where id = #{id}
 	</update>

4.<foreach>循环语句，常用于in 语句等列举条件中，后面跟一个集合，用法是in(   );里面是一个集合

	<select id="findCustomerBynameAndjob4" parameterType = "list" resultType ="customer">
 	select id,username,jobs,phone from t_customer where id in
 	<foreach item = "id" index = "index" collection="list"
 	open = "(" separator = ","  close = ")">
 	#{id}	
 	</foreach>
 	</select>

5.<bind>从ognl表达式中创建一个变量，并将其绑定到shan上下文，chan常用于模糊查询的sql中

 	<select id="findCustomerBynameAndjob5" parameterType = "customer" resultType 
    ="customer">
  	<bind name="pattern_username" value="'%'+_parameter.getUsername()+'%'"/>
    select id ,username,jobs,phone from t_customer where 
	username like #{pattern_username} 
    </select>