MyBatis中的动态SQL

CRUD标签属性说明

1. select标签

用于执行SQL查询操作，查询结果可以是单条记录、多条记录或者是Map。

属性说明：

id：定义 SQL 语句的唯一标识符。用于在 Mapper 接口中调用对应的查询方法。

• 示例：

   <select id="findUserById" resultType="User">

resultType：指定查询结果的类型。可以是一个类的全名或 Map 类型。

• 示例

  resultType="User" 或 resultType="java.util.Map"

特殊情况：

• 当无返回值时，可以直接省略。

• 当返回值为基础数据类型时，会自动填充它的包裹类。

resultMap：指定一个 resultMap，用于映射查询结果到 Java 对象。与 resultType 冲突，通常只使用其一。

• 示例：

   resultMap="userResultMap"

这里讲下它和resultType具体的区别：

• resultType用于简单的映射场景。

• resultMap用于复杂的映射场景和多表查询。

示例：

• User 类：

  public class User {
       private Integer id;
       private String name;
       private String email;
       private List<Order> orders; // 一对多关系
   }

• Order 类：

  public class Order {
       private Integer id;
       private Date date;
       private Double amount;
   }

如果你使用 resultType 来映射上述查询：

<select id="findUserWithOrders" resultType="User">
     SELECT u.user_id, u.user_name, u.user_email, o.order_id, o.order_date, o.amount
     FROM user u
     LEFT JOIN order o ON u.user_id = o.user_id
     WHERE u.user_id = #{id}
 </select>

由于 SQL 查询可能返回多行结果（每行表示一个订单），resultType 无法将这些多行结果正确地映射到 User 对象的 orders 列表中。每个 User 对象在不同的结果行中出现，这导致无法将这些行整合成一个完整的对象结构。

将resultType更换成resultMap

返回的多行数据会被正确地合并到 User 对象的 orders 列表中。每个 User 对象只会有一份，并且所有相关的 Order 对象会被正确地添加到 orders 列表中。

parameterType：指定传入 SQL 语句的参数类型。可以是一个类的全名或 Map 类型。

为什么是Map，因为List集合或数组，都会被MyBatis封装成Map。

• 示例：

   parameterType="java.lang.Integer"

在这里你可能有两个疑问？

1. 对于一个参数的情况，通常MyBatis会自动帮我们进行识别，是否有必要加parameterType参数?

   答：即使只有一个参数，仍然可以使用 parameterType 来明确指定参数的类型。这可以帮助 MyBatis 更好地理解传入的参数，并在某些情况下避免潜在的类型错误。

2. 对于多个参数的情况，应该如何使用parameterType?

   答：当映射的方法有多个参数时，parameterType 无法直接用于指定多个参数的类型。MyBatis 要求将多个参数封装到一个对象中，并使用 parameterType 指定该对象的类型。

   或者用Map<String,Object>接收多个参数。

cache：启用或禁用查询的缓存。通常在 <cache> 元素中配置。

• 示例：

  <select id="findUser" cache="true">

2. update标签

用于执行 SQL 更新操作，如 UPDATE 语句。

id：定义 SQL 语句的唯一标识符。

• 示例：

  <update id="updateUser">

parameterType：指定传入 SQL 语句的参数类型。可以是一个类的全名或 Map 类型。

• 示例：

  parameterType="User"

flushCache：指定是否在执行 UPDATE 操作后刷新缓存。常用值有 true（刷新缓存）和 false（不刷新缓存）。

• 示例：

  flushCache="true"

3. delete标签

用于执行 SQL 删除操作，如 DELETE 语句。

id：定义 SQL 语句的唯一标识符。

• 示例：

  <delete id="deleteUserById">

parameterType：指定传入 SQL 语句的参数类型。可以是一个类的全名或 Map 类型。

• 示例：

  parameterType="java.lang.Integer"

flushCache：指定是否在执行 DELETE 操作后刷新缓存。

• 示例：

  flushCache="true"

4. insert标签

用于执行 SQL 插入操作，如 INSERT 语句。

id：定义 SQL 语句的唯一标识符。

• 示例：

  <insert id="insertUser">

parameterType：指定传入 SQL 语句的参数类型。可以是一个类的全名或 Map 类型。

• 示例：

  parameterType="User"

keyProperty：指定用于接收生成的主键的属性名称。如果插入语句生成了主键值，这个属性会被赋值。

• 示例：

  keyProperty="id"

useGeneratedKeys：指定是否使用自动生成的主键。设置为 true 时，MyBatis 会使用 JDBC 的 getGeneratedKeys 方法获取生成的主键。

• 示例：

  useGeneratedKeys="true"

在这里举一个keyProperty和useGeneratedKeys的使用案例：

User类：

public class User {
     private Integer id; // 自动生成的主键
     private String name;
     private String email;
     
     // getters and setters
 }

xml映射文件中：

<insert id="insertUser" useGeneratedKeys="true" keyProperty="id">
     INSERT INTO user (user_name, user_email)
     VALUES (#{name}, #{email})
 </insert>

Service层：

User user = new User();
 user.setName("John Doe");
 user.setEmail("john.doe@example.com");
 userMapper.insertUser(user);
 // 现在 user 对象的 id 属性将包含自动生成的主键值
 System.out.println("Generated ID: " + user.getId());

动态SQL常用标签说明

1. if

<select id="findUserById" resultType="user">
     select * from user where 
         <if test="id != null">
                id=#{id}
         </if>
     and deleteFlag=0;
 </select>

if标签参数说明：

注意：prepend和suffix不是所有的Mybatis版本都支持

test：指定一个表达式，用于判断是否包含 <if> 标签内的 SQL 语句。

• 示例：test="id != null" 检查 id 是否不为 null。

prepend：指定在 <if> 标签内的 SQL 语句之前附加的 SQL 片段。

• 示例：

  <if test="id != null" prepend="and">
       id = #{id}
   </if>

  在 id 不为 null 时，and 会被添加到 SQL 语句的前面。

suffix：指定在 <if> 标签内的 SQL 语句之后附加的 SQL 片段。

• 示例：

   <if test="status != null" suffix="or">
       status = #{status}
   </if>

  在 status 不为 null 时，or 会被添加到 SQL 语句的后面。

2. Where

<select id="findUserById" resultType="user">
     select * from user 
         <where>
             <if test="id != null">
                 id=#{id}
             </if>
             and deleteFlag=0;
         </where>
 </select>

会对最后拼接的SQL语句中的"and"、"or"关键字进行剔除。

3. trim

温馨提示：prefix（前缀）

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

解读：当WHERE后出现AND或者OR时，会自动剔除。

4. Set

 <update id="updateUser" parameterType="com.dy.entity.User">
     update user set 
         <if test="name != null">
             name = #{name},
         </if> 
         <if test="password != null">
             password = #{password},
         </if> 
         <if test="age != null">
             age = #{age}
         </if> 
         <where>
             <if test="id != null">
                 id = #{id}
             </if>
             and deleteFlag = 0;
         </where>
 </update>

问题：当只有name属性不为null时，最后拼接成的sql语句是这样的update set name = #{name}, where ........ ?

可以看到name后面多了一个"，"。

解决方案一：用set标签将if判断包起来。

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

解决方案二：用trim标签将if判断包起来。

温馨提示：suffix（后缀）

 <trim prefix="SET" suffixOverrides=",">
     <if test="name != null">name = #{name},</if> 
     <if test="password != null">password = #{password},</if> 
     <if test="age != null">age = #{age},</if> 
 </trim>

5. foreach

对应for循环

<select id="selectPostIn" resultType="domain.blog.Post">
     SELECT *
     FROM POST P
     WHERE ID in
     <foreach item="item" index="index" collection="list"
         open="(" separator="," close=")">
         #{item}
     </foreach>
 </select>

参数说明：

item：遍历集合的单个子项。

index：指定当前元素的索引。

collection：指定要遍历的集合（或数组）的名称。这个集合需要在方法参数中传递。

open：指定在循环开始时添加的前缀（通常用于括号）。

separator：指定在每个元素之间添加的分隔符（通常用于逗号分隔）。

close：指定在循环结束时添加的后缀（通常用于闭合括号）。

示例：where in (item1,item2,item3,...)

6. Choose

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

解读：当title和author都不为null的时候， 那么选择二选一（前者优先）， 如果都为null, 那么就选择 otherwise中的， 如果tilte和author只有一个不为null, 那么就选择不为null的那个。