MyBatis-动态SQL

素颜马尾好姑娘i 2021-12-10 14:01 671阅读 0赞

### 文章目录 ###

*  动态SQL语句
 *   *  1.if语句
     *  2.choose,when,otherwise
     *  3.where语句
     *  4.set语句
     *  5.trim
     *  6.foreach语句
     *  7.bind
     *  8.sql语句

# 动态SQL语句 #

**MyBatis的强大特性之一便是它的动态SQL。如果你有使用JDBC或其它类似框架的经验，你就能体会到根据不同条件拼接SQL语句的痛苦。例如拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号。利用动态SQL这一特性可以彻底摆脱这种痛苦。**

## 1.if语句 ##

**动态 SQL 通常要做的事情是根据条件包含 where 子句的一部分。比如：**

<select id="queryUser" resultMap="baseMap"
     	 resultType="com.sxt.bean.User" parameterType="user"> 
    	select id ,name ,age  from t_user 
    	where 1 =1 
    	<if test="username!=null">
    	     and name = #{username}
    	</if>  
    </select>

**测试1：**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDUwMjgwNA_size_16_color_FFFFFF_t_70]

**测试2：**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDUwMjgwNA_size_16_color_FFFFFF_t_70 1]

## 2.choose,when,otherwise ##

**有时我们不想应用到所有的条件语句，而只想从中择其一项。针对这种情况，MyBatis 提供了 choose 元素，它有点像 Java 中的 switch 语句**

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

## 3.where语句 ##

**在使用if语句做动态条件处理的时候如果所有条件都不满足，那么得到的SQL语句如下:**

select * from t_user where

在这种情况下，我们一般会加一个1=1来匹配语法规则

<select id="queryUser" resultMap="baseMap"
    	 	 resultType="com.sxt.bean.User" parameterType="user"> 
    		select id ,name ,age  from t_user 
    		where 1 =1  
    		<if test="username!=null">
    		     and name = #{username}
    		</if>  
    	</select>

此时可以使用标签来处理这种情况

<select id="queryUser" resultMap="baseMap"
    	 	 resultType="com.sxt.bean.User" parameterType="user"> 
    		select id ,name ,age  from t_user 
    		<where>
    			<if test="username!=null">
    			     and name = #{username}
    			</if>  
    		</where>
    	</select>

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDUwMjgwNA_size_16_color_FFFFFF_t_70 2]  
 ------------------------------------------------------------------------------------------------------------------------![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDUwMjgwNA_size_16_color_FFFFFF_t_70 3]  
 ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDUwMjgwNA_size_16_color_FFFFFF_t_70 4]

## 4.set语句 ##

set主要也是用来解决更新问题的。

<update id="updateBookById">
    	update t_book
    	<set>
    			<if test="author!=null"> author=#{author},</if>
    			<if test="name!=null"> b_name=#{name},</if>
    			<if test="price!=null"> price=#{price},</if>
    	</set>
    	where id=#{id};
    </update>

## 5.trim ##

**trim标记是一个格式化的标记，可以完成set或者是where标记的功能**  
![在这里插入图片描述][2019071219550827.png]  
**代替where的用法：**

<select id="queryUser" resultMap="baseMap" resultType="com.sxt.bean.User"
    		parameterType="user">
    		select id ,name ,age from t_user
    		
    		<trim prefix="where" prefixOverrides="AND |OR ">
    			<if test="username!=null">
    				 and name = #{username}
    			</if>
    			<if test="age != 0">
    				and age = #{age}
    			</if>
    		</trim>
    	</select>

**代替 set的用法：**

<update id="updateUser" parameterType="User">
    	update t_user
    	<trim prefix="set" suffixOverrides=",">
    		<if test="username!=null">
    			name = #{username},
    		</if>
    		<if test="age != 0">
    			age = #{age}
    		</if>
    	</trim>
    	where id=#{id}
    </update>

**或者：**

<update id="updateUser" parameterType="User">
    	update t_user
    	set 
    	<trim  suffixOverrides=",">
    		<if test="username!=null">
    			name = #{username},
    		</if>
    		<if test="age != 0">
    			age = #{age}
    		</if>
    	</trim>
    	where id=#{id}
    </update>

![在这里插入图片描述][20190712195809780.png]

## 6.foreach语句 ##

**foreach用来遍历，遍历的对象可以是数组，也可以是集合。**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDUwMjgwNA_size_16_color_FFFFFF_t_70 5]  
接口中的方法：

public interface UserMapper {
    	// 如果不指定@Param 默认是array
    	public List<User> queryUserByIds(@Param("ids")List<Integer> ids);
    
    	public int insertUser(@Param("users")List<User> users);
    }

<select id="queryUserByIds" resultType="user">
    	select * from t_user where id in 
    	<foreach collection="ids" open="(" close=")" separator="," item="id" >
    		#{id}
    	</foreach>
    </select>
    
    <insert id="insertUser">
    	insert into t_user(name,age)values
    	<foreach collection="users" item="user" separator=",">
    		(#{user.name},#{user.age})
    	</foreach>
    </insert>

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDUwMjgwNA_size_16_color_FFFFFF_t_70 6]

## 7.bind ##

bind 元素可以从 OGNL 表达式中创建一个变量并将其绑定到上下文。

<select id="getUserById" resultMap="baseMap" resultType="com.sxt.bean.User">
    	
    	<bind name="aaa" value="12"/>
    	select
    	id ,name ,age from t_user where id=${aaa}
    </select>

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDUwMjgwNA_size_16_color_FFFFFF_t_70 7]

## 8.sql语句 ##

sql片段一般用来定义sql中的列  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDUwMjgwNA_size_16_color_FFFFFF_t_70 8]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDUwMjgwNA_size_16_color_FFFFFF_t_70]: /images/20211210/381ff96a27e747f28bf50c38265b0755.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDUwMjgwNA_size_16_color_FFFFFF_t_70 1]: /images/20211210/8cbddc2d61854dd6a43b55bbae066109.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDUwMjgwNA_size_16_color_FFFFFF_t_70 2]: /images/20211210/ddd27dd7a1d4406089e92b4edd5b3844.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDUwMjgwNA_size_16_color_FFFFFF_t_70 3]: /images/20211210/a35acb3fda3d4e0fb2d4dceea0a5d6e9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDUwMjgwNA_size_16_color_FFFFFF_t_70 4]: /images/20211210/ef905342eeb7485fbcd7062031f5c798.png
[2019071219550827.png]: /images/20211210/6affc09d63894a899fb4ee6231ff7026.png
[20190712195809780.png]: /images/20211210/9429b7b14689495785191a0cbb512bf5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDUwMjgwNA_size_16_color_FFFFFF_t_70 5]: /images/20211210/eeace94c1ccc466785339f75da443c72.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDUwMjgwNA_size_16_color_FFFFFF_t_70 6]: /images/20211210/514edd4b9c844c25924b78761a7395cf.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDUwMjgwNA_size_16_color_FFFFFF_t_70 7]: /images/20211210/41873e2a47ad46a18f80420aa2f0870d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDUwMjgwNA_size_16_color_FFFFFF_t_70 8]: /images/20211210/f1e1aab54ad54d2fb5f75780dbeead05.png