MyBatis中常用的SQL語(yǔ)句詳解

更新時(shí)間：2025年03月10日 09:39:55 作者：冰糖心書(shū)房

MyBatis是一種優(yōu)秀的持久層框架,它支持定制化SQL、存儲(chǔ)過(guò)程以及高級(jí)映射,本文介紹了MyBatis中常用的SQL語(yǔ)句,包括基本查詢、多條件查詢、關(guān)聯(lián)查詢、分頁(yè)查詢、插入、更新、刪除等,并結(jié)合MyBatis的特性進(jìn)行了說(shuō)明

1. SELECT (查詢)

基本查詢：

<select id="selectUserById" parameterType="int" resultType="User">
  SELECT id, username, password, email FROM users WHERE id = #{id}
</select>

id: 該 SQL 語(yǔ)句的唯一標(biāo)識(shí)符，用于在 Java 代碼中引用。
parameterType: 傳入?yún)?shù)的類型 (可選，MyBatis 可以自動(dòng)推斷)。
resultType: 查詢結(jié)果映射的 Java 類型 (POJO)。
#{id}: 占位符，表示傳入的參數(shù)，MyBatis 會(huì)自動(dòng)進(jìn)行參數(shù)綁定和類型轉(zhuǎn)換，防止 SQL 注入。

多條件查詢 (動(dòng)態(tài) SQL)：

<select id="selectUsers" parameterType="map" resultType="User">
  SELECT id, username, password, email FROM users
  <where>
    <if test="username != null and username != ''">
      AND username LIKE CONCAT('%', #{username}, '%')
    </if>
    <if test="email != null and email != ''">
      AND email = #{email}
    </if>
  </where>
</select>

<where>: 自動(dòng)添加 WHERE 關(guān)鍵字，并處理多余的 AND 或 OR。
<if>: 根據(jù)條件判斷是否包含該 SQL 片段。
test: 條件表達(dá)式，使用 OGNL 表達(dá)式。
LIKE: 模糊查詢，CONCAT('%', #{username}, '%') 用于構(gòu)建模糊查詢條件。

關(guān)聯(lián)查詢 (JOIN)：

<select id="selectUserWithOrders" parameterType="int" resultMap="userWithOrdersResultMap">
  SELECT
    u.id AS user_id,
    u.username,
    o.id AS order_id,
    o.order_date
  FROM users u
  LEFT JOIN orders o ON u.id = o.user_id
  WHERE u.id = #{id}
</select>

<resultMap id="userWithOrdersResultMap" type="User">
  <id property="id" column="user_id"/>
  <result property="username" column="username"/>
  <collection property="orders" ofType="Order">
    <id property="id" column="order_id"/>
    <result property="orderDate" column="order_date"/>
  </collection>
</resultMap>

LEFT JOIN: 左連接，獲取用戶及其所有訂單。
resultMap: 自定義結(jié)果映射，用于處理復(fù)雜的關(guān)聯(lián)關(guān)系。
<collection>: 映射一對(duì)多關(guān)系，將訂單列表映射到 User 對(duì)象的 orders 屬性。

分頁(yè)查詢 :

<!-- MySQL -->
<select id="selectUsersByPage" parameterType="map" resultType="User">
  SELECT id, username, password, email FROM users LIMIT #{offset}, #{pageSize}
</select>

<!-- Oracle -->
<select id="selectUsersByPage" parameterType="map" resultType="User">
  SELECT * FROM (
    SELECT t.*, ROWNUM rn FROM (
      SELECT id, username, password, email FROM users
    ) t WHERE ROWNUM <= #{endRow}
  ) WHERE rn > #{startRow}
</select>

LIMIT: MySQL 中的分頁(yè)關(guān)鍵字。
ROWNUM: Oracle 中的偽列，用于分頁(yè)。
需要根據(jù)不同的數(shù)據(jù)庫(kù)方言使用不同的分頁(yè)語(yǔ)法。 MyBatis 提供了 Dialect 接口來(lái)支持不同的數(shù)據(jù)庫(kù)方言。

2. INSERT (插入)

基本插入:

<insert id="insertUser" parameterType="User">
  INSERT INTO users (username, password, email) VALUES (#{username}, #{password}, #{email})
</insert>

獲取自增主鍵:

<insert id="insertUser" parameterType="User" useGeneratedKeys="true" keyProperty="id">
  INSERT INTO users (username, password, email) VALUES (#{username}, #{password}, #{email})
</insert>

useGeneratedKeys="true": 啟用獲取自增主鍵。
keyProperty="id": 指定將自增主鍵值設(shè)置到 User 對(duì)象的哪個(gè)屬性 (通常是 id)。

批量插入 (foreach):

<insert id="batchInsertUsers" parameterType="java.util.List">
  INSERT INTO users (username, password, email) VALUES
  <foreach collection="list" item="user" separator=",">
    (#{user.username}, #{user.password}, #{user.email})
  </foreach>
</insert>

<foreach>: 循環(huán)遍歷 list 集合，生成多個(gè)插入值。
collection: 指定要遍歷的集合。
item: 集合中每個(gè)元素的別名。
separator: 分隔符，用于分隔每次循環(huán)生成的 SQL 片段。

3. UPDATE (更新)

基本更新:

<update id="updateUser" parameterType="User">
  UPDATE users SET username = #{username}, password = #{password}, email = #{email} WHERE id = #{id}
</update>

動(dòng)態(tài)更新 (set + if):

<update id="updateUserSelective" parameterType="User">
  UPDATE users
  <set>
    <if test="username != null and username != ''">
      username = #{username},
    </if>
    <if test="password != null and password != ''">
      password = #{password},
    </if>
    <if test="email != null and email != ''">
      email = #{email},
    </if>
  </set>
  WHERE id = #{id}
</update>

<set>: 自動(dòng)添加 SET 關(guān)鍵字，并處理多余的逗號(hào)。
只更新傳入的非空字段。

4. DELETE (刪除)

基本刪除:

<delete id="deleteUserById" parameterType="int">
  DELETE FROM users WHERE id = #{id}
</delete>

批量刪除 (foreach):

<delete id="deleteUsersByIds" parameterType="java.util.List">
  DELETE FROM users WHERE id IN
  <foreach collection="list" item="id" open="(" close=")" separator=",">
    #{id}
  </foreach>
</delete>

open: 循環(huán)開(kāi)始前添加的字符串。
close: 循環(huán)結(jié)束后添加的字符串。

5. 其他常用標(biāo)簽和特性

<sql>: 定義可重用的 SQL 片段。

<sql id="userColumns">
  id, username, password, email
</sql>

<select id="selectUserById" parameterType="int" resultType="User">
  SELECT <include refid="userColumns"/> FROM users WHERE id = #{id}
</select>

#{} vs ${}:
- #{}: 預(yù)編譯參數(shù)，防止 SQL 注入，推薦使用。
- ${}: 字符串替換，直接將值拼接到 SQL 語(yǔ)句中，有 SQL 注入風(fēng)險(xiǎn)，謹(jǐn)慎使用 (例如，用于動(dòng)態(tài)表名或列名)。
typeAliases: 定義類型別名，簡(jiǎn)化 resultType 和 parameterType 的書(shū)寫(xiě)。
<cache>: 開(kāi)啟二級(jí)緩存，提高查詢性能。
Result Maps (結(jié)果映射): 處理復(fù)雜類型和關(guān)聯(lián)關(guān)系的映射。