【MyBatis】动态SQL ＞ 重点：${...}和#{...}与resultMap和resultType的区别

目录

一、MyBatis动态sql

1.1 动态sql的作用

1.2 动态sql作用论证

1.2.1 条件判断：<if>

1.2.2 循环迭代：<foreach>

1.2.3 SQL片段重用

1.2.4 动态条件组合：<choose><when><otherwise>

1.2.5 <where><if> 标签

1.2.6 <set> <if> 标签

1.3 #{...}与${...}的区别⭐

1.3.1 $ 符号（sql拼接符号) 

1.3.2 # 符号（占位符) 

1.3.3 案例演示 

1.3.4 sql预编译

二、resultMap和resultType的区别⭐

一、MyBatis动态sql

1.1 动态sql的作用

什么是动态sql？动态sql就是在不同的条件下拼接出不同的sql语句。

作用无非就是简化sql，根据不同的条件动态生成sql语句，以实现更灵活和可复用的数据库操作。以下是动态SQL的主要作用：

1. 条件判断：动态SQL可以根据不同的条件判断生成不同的SQL语句。例如，根据用户输入的搜索条件动态生成查询语句，只包含满足条件的字段和条件。

2. 循环迭代：动态SQL可以在SQL语句中进行循环迭代，以处理集合或数组等数据结构。例如，可以通过循环迭代生成批量插入或更新的SQL语句。

3. SQL片段重用：动态SQL可以定义可重用的SQL片段，以便在不同的SQL语句中引用。这样可以减少代码重复，提高代码的可维护性。

4. 动态条件组合：动态SQL可以根据不同的条件组合生成不同的SQL语句。例如，根据用户选择的不同条件动态生成查询语句的WHERE子句。

通过使用动态SQL，开发人员可以根据具体的业务需求灵活地生成SQL语句，避免硬编码固定的SQL语句，提高代码的可读性和可维护性。同时，动态SQL还可以减少数据库的负载，提高数据库操作的性能。

1.2 动态sql作用论证

数据库：t_mvc_book

1.2.1 条件判断：<if>

     <select id="selectBook" resultMap="BaseResultMap" parameterType="com.ycxw.model.Book">
        select * from t_mvc_book where 1=1
        <if test="bid != null">
            and bid = #{bid}
        </if>
        <if test="bname !=null">
            and bname = #{bname}
        </if>
     </select>

在这个示例动态生成查询语句的条件部分中。id属性指定了映射的唯一标识，也是映射的方法名字。

这里我判断了：

如果bname参数不为空，则添加 AND bname = #{bname}条件；如果bid参数不为空，则添加AND bid = #{bid} 条件。

测试：

1.2.2 循环迭代：<foreach>

作用：一般用于批量删除查询；

• collection：我们要循环的数组或集合的名称，collection属性值分别默认用"list"、"array"代替，Map对象没有默认的属性值。但是，在作为入参时可以使用@Param(“keyName”)注解来设置自定义collection属性值，设置keyName后，list、array会失效，这是keyName就必须与collection属性名字一直，不然就会报错找不到Param;
• item：你是用哪个字段进行循环的，我们这里用的是bid字段，所以写bid，该参数为必选项；
• index：在list、array中，index为元素的序号索引。但是在Map中，index为遍历元素的key值，该参数为可选项；
• open：遍历集合时的开始符号，通常与close=")"搭配使用。使用场景IN(),values()时，该参数为可选项；
• separator：元素之间的分隔符，类比在IN()的时候，separator=",",最终所有遍历的元素将会以设定的（,）逗号符号隔开，该参数为可选项；
• close：遍历集合时的结束符号，通常与open="("搭配使用，该参数为可选项；

    <select id="selectByBids" resultMap="BaseResultMap" parameterType="com.ycxw.model.Book">
        select * from t_mvc_book
        where bid in
        <foreach collection="bids" item="bid" open="(" close=")" separator=",">
            #{bid}
        </foreach>
    </select>

在这个示例中，通过循环迭代遍历多个书籍信息。传入的参数是一个叫bids（List集合类型），通过<foreach>标签循环遍历列表中的每个书籍对象，并生成对应的查询id语句。

如果传入的是单个属性可以使用@Param()，如果是map集合，或者单个对象就不需要。

测试：

1.2.3 SQL片段重用

该作用于当我们经常需要使用某表的某些字段时，我们可以它分装起来，便于直接引用。

<sql id="bookColumns">
  bid, bname, price
</sql>

<select id="selectBooks" resultMap="BaseResultMap">
  SELECT <include refid="bookColumns" />
  FROM t_mvc_book
</select>

<select id="selectBooksWithPrice" resultMap="BaseResultMap">
  SELECT <include refid="bookColumns" />, info
  FROM t_mvc_book
</select>

在这个示例中，通过<sql>标签定义了一个名为bookColumns的SQL片段，包含了书籍表的列名。然后，在不同的查询语句中通过<include>标签引用了该SQL片段，实现了列名的重用。

1.2.4 动态条件组合：<choose><when><otherwise>

<choose><when><otherwise>标签作用：选择判断，该标签相当于java中的 if...else...default语句。

<select id="selectBooks" resultMap="BaseResultMap" parameterType="com.ycxw.model.Book">
  SELECT * FROM t_mvc_book
  WHERE 1=1
  <choose>
    <when test="bid != null">
      AND bid= #{bid}
    </when>
    <when test="bname!= null">
      AND bname = #{bname}
    </when>
    <when test="price != null">
      AND price = #{price}
    </when>
    <otherwise>
      AND info= '这是一本好书！'
    </otherwise>
  </choose>
</select>

在这个示例中，根据不同条件生成不同的SQL语句。如果第一个条件不成立就继续往下执行，直到when条件成立后就不会执行下面的判断了。如果没有传入任何参数，则默认生成 AND info= ' 这是一本好书！' 条件。

1.2.5 <where><if> 标签

其实在上面的<if>条件判断时还是有点弊端的，我只是在where条件后面加了 1=1，如果换成一下呢？

<select id="selectBook" resultMap="BaseResultMap" parameterType="com.ycxw.model.Book">
        select * from t_mvc_book where
        <if test="bid != null">
            bid = #{bid}
        </if>
        <if test="bname !=null">
            and bname = #{bname}
        </if>
     </select>

如果第一条件成立那么sql语句是这样的：

 select * from t_mvc_book where bid = value ;

如果第二个呢：

 select * from t_mvc_book where and bname = value ;

这样显然是不对的，所以就用到了 <where><if>。很简单，就是把if 标签的语句放到<where></where>里面而已。

 <select id="selectBook" resultMap="BaseResultMap" parameterType="com.ycxw.model.Book">
        select * from t_mvc_book where
        <where>
            <if test="bid != null">
                and bid = #{bid}
            </if>
            <if test="bname !=null">
                and bname = #{bname}
            </if>
        </where>
  </select>

测试：

1.2.6 <set> <if> 标签

作用：选择性赋值，一般常用语update语句

在以往的修改sql语句中，我们需要修改某个对象的所有属性，若有一项没有输入，默认该值为null。这样就有人想到既然我不需要修改那一项，我就直接去掉带字段，这样又写了一个sql语句。显然是不方便的。利用mybatis的<set><if>标签就可以实现选择性赋值，当我们传一个值就修改一个值，其他字段值不变，也不会置空。

    <update id="updateByPrimaryKeySelective" parameterType="com.ycxw.model.Book">
        update t_mvc_book
        <set>
            <if test="bname != null">
                bname = #{bname,jdbcType=VARCHAR},
            </if>
            <if test="price != null">
                price = #{price,jdbcType=REAL},
            </if>
        </set>
        where bid = #{bid,jdbcType=INTEGER}
    </update>

1.3 #{...}与${...}的区别⭐

在MyBatis中，$符号和#符号是用于参数替换的两种不同的占位符语法。它们在使用方式和替换规则上有所不同。

1.3.1 $ 符号（sql拼接符号) 

1. $符号占位符是简单的字符串替换，不进行预编译和参数类型处理。
2. $符号占位符直接将参数的值替换到SQL语句中，可以用于动态拼接SQL语句的部分内容。
3. $符号占位符存在SQL注入的风险，因为参数值直接替换到SQL语句中，可能导致恶意注入攻击。
4. 没有 '引号'

1.3.2 # 符号（占位符) 

1. #符号占位符是预编译的占位符，会对参数进行类型处理和安全处理。
2. #符号占位符将参数值作为预编译参数传递给数据库，可以防止SQL注入攻击。
3. #符号占位符可以用于动态生成SQL语句的条件部分，例如WHERE子句、ORDER BY子句等。
4. 有引号

1.3.3 案例演示

下面是一个案例演示，展示了$符号和#符号的区别：

<select id="getBooksByPrice" parameterType="Map" resultMap="BaseResultMap">
  SELECT * FROM books
  WHERE price > ${minPrice} AND price < #{maxPrice}
</select>

在这个示例中，getBooksByPrice是一个查询操作，根据传入的minPrice和maxPrice参数来查询价格在指定范围内的书籍。

• 如果使用$符号占位符，例如传入minPrice=10和maxPrice=20，生成的SQL语句为：

  SELECT * FROM books
  WHERE price > 10 AND price < 20

注意，$符号占位符直接将参数的值替换到SQL语句中，不进行预编译和参数类型处理。

• 如果使用#符号占位符，例如传入minPrice=10和maxPrice=20，生成的SQL语句为：

  SELECT * FROM books
  WHERE price > ? AND price < ?

注意，#符号占位符将参数值作为预编译参数传递给数据库，可以防止SQL注入攻击。

💡💡💡小提示：表名作为变量时，必须使用 ${ }
这是因为，表名是字符串，使用 sql 占位符替换字符串时会带上单引号 ''，这会导致 sql 语法错误，例如：

select * from #{tableName} where name = #{name};

预编译之后的sql 变为：

select * from ? where name = ?;

假设我们传入的参数为 tableName = "books" , name = "ycxw"，那么在占位符进行变量替换后，sql 语句变为：

select * from 'books' where name='ycxw';

上述 sql 语句是存在语法错误的，表名不能加单引号 ''（注意: 反引号 ``是可以的）

1.3.4 sql预编译

1. SQL预编译是一种将SQL语句和参数分开处理的技术。它的基本原理是将SQL语句中的参数部分使用占位符代替，然后将参数值与SQL语句分开传递给数据库执行。这样可以提高数据库的性能和安全性。
2. 在SQL预编译中，首先将SQL语句发送给数据库进行预编译，数据库会对SQL语句进行语法分析和优化，并生成一个执行计划。然后，应用程序将参数值与占位符一起发送给数据库执行计划，数据库会将参数值填充到执行计划中的占位符位置，最后执行SQL语句。

通过使用SQL预编译，可以实现以下优势：

• 提高性能：由于SQL语句只需要预编译一次，后续执行只需要传递参数值，减少了语法分析和优化的开销，提高了执行效率。
• 防止SQL注入攻击：通过使用占位符，可以将参数值与SQL语句分开处理，避免了恶意用户通过参数注入恶意代码的风险。
• 简化参数处理：应用程序只需要关注参数值的传递，而不需要担心参数的类型和转义处理，减少了开发的复杂性。

通过这个案例演示，可以看到$符号和#符号在占位符语法上的区别。在实际使用中，应根据具体的需求和安全性考虑选择合适的占位符语法。一般来说，推荐使用#符号占位符，以提高安全性和防止SQL注入攻击。

二、resultMap和resultType的区别⭐

1️⃣ resultType 是一种简单的映射方式，它指定了查询结果的类型。通常情况下，resultType可以是Java的基本类型（如int、String等）或者自定义的Java类。当查询结果只有一个列时，可以使用resultType。

例如，考虑以下数据库表格"users"：

+----+----------+-----------+
| id | username |  password |
+----+----------+-----------+
| 1  |  John    |  123456   |
| 2  |  Jane    |  abcdef   |
+----+----------+-----------+

如果我们想要查询id为1的用户的用户名，可以使用以下MyBatis配置：

<select id="getUserUsername" resultType="java.lang.String">
  SELECT username FROM users WHERE id = #{id}
</select>

在这个例子中，我们使用了resultType="java.lang.String"来指定查询结果的类型为String。这样，MyBatis会将查询结果直接映射为一个String对象。

        2️⃣ resultMap 是一种更为灵活的映射方式，它允许我们定义复杂的映射规则。通过resultMap，我们可以将查询结果映射为一个自定义的Java对象，而不仅仅是基本类型。

例如，我们可以定义一个User类来表示数据库中的用户：

public class User {
  private Integer id;
  private String username;
  private String password;

  // 省略构造函数、getter和setter方法
}

然后，我们可以使用resultMap来将查询结果映射为User对象：

<resultMap id="userResultMap" type="com.entity.User">
  <id property="id" column="id" />
  <result property="username" column="username" />
  <result property="password" column="password" />
</resultMap>

<select id="getUser" resultMap="userResultMap">
  SELECT * FROM users WHERE id = #{id}
</select>

在这个例子中，我们定义了一个名为"userResultMap"的resultMap，指定了User类作为映射的类型。然后，我们使用<id>和<result>标签来指定每个属性的映射规则。

通过使用resultMap，MyBatis会将查询结果映射为一个User对象，其中id、username和password属性会被正确地填充。

总结：

resultType是指定查询结果的数据类型。它可以是Java的基本数据类型、JavaBean或者其他自定义的数据类型。当查询结果只有一个字段时，可以使用resultType来指定结果的数据类型。

resultMap是用于将查询结果映射到Java对象的规则集合。它定义了查询结果与Java对象之间的映射关系。resultMap可以指定多个映射规则，用于处理复杂的查询结果。它可以映射查询结果中的每个字段到Java对象的属性，也可以进行一些特殊的映射操作，如级联映射、关联映射等。