Detailed explanation of commonly used SQL statements in MyBatis

The commonly used SQL statements in MyBatis are basically the same as standard SQL statements, but MyBatis provides some additional features and tags to more conveniently build and manage SQL statements.

The following lists commonly used SQL statements in MyBatis and explains them in combination with the characteristics of MyBatis:

1. SELECT (Query)

Basic query:

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

id: A unique identifier for this SQL statement, used to reference in Java code.
parameterType: The type of the passed parameter (optional, MyBatis can be automatically inferred).
resultType: The Java type (POJO) of query result map.
#{id}: Placeholder, indicating the passed parameters. MyBatis will automatically perform parameter binding and type conversion to prevent SQL injection.

Multi-condition query (dynamic SQL):

<select  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>: Automatically addWHEREKeywords and handle redundantANDorOR。
<if>: Determine whether the SQL fragment is included based on the conditions.
test: Conditional expression, using OGNL expression.
LIKE: Fuzzy query,CONCAT('%', #{username}, '%')Used to construct fuzzy query conditions.

Associative Query (JOIN):

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

<resultMap  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: Connect left to get the user and all orders.
resultMap: Custom result maps for handling complex associations.
<collection>: Map one-to-many relationships, map the order list toUserThe object'sordersproperty.

Pagination query:

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

<!-- Oracle -->
<select  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: Pagination keywords in MySQL.
ROWNUM: Pseudo columns in Oracle for pagination.
Different pagination syntax needs to be used according to different database dialects. MyBatis providesDialectInterface to support different database dialects.

2. INSERT (insert)

Basic Insert:

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

Get the auto-increment primary key:

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

useGeneratedKeys="true": Enable Get auto-increment primary key.
keyProperty="id": Specify setting the autoincrement primary key value toUserWhich property of the object (usuallyid)。

Foreach:

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

<foreach>: Circular traversallistSet, generate multiple insert values.
collection: Specifies the collection to traverse.
item: The alias for each element in the collection.
separator: Delimiter, used to separate SQL fragments generated by each loop.

3. UPDATE (Update)

Basic update:

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

Dynamic update (set + if):

<update  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>: Automatically addSETKeywords and handle unnecessary commas.
Update only the incoming non-empty fields.

4. DELETE (Delete)

Basic deletion:

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

Foreach:

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

open: The string added before the loop starts.
close: The string added after the loop ends.

5. Other common labels and features

<sql>: Define reusable SQL snippets.

<sql >
  id, username, password, email
</sql>

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

#{} vs ${}:
- #{}: Precompiled parameters to prevent SQL injection, recommended.
- ${}: String replacement, splicing values directly into SQL statements, there is risk of SQL injection, and use with caution (for example, for dynamic table names or column names).
typeAliases: Define type alias, simplifyresultTypeandparameterTypewriting.
<cache>: Turn on Level 2 cache to improve query performance.
Result Maps:Handle mapping of complex types and associations.

Summarize

The core of MyBatis is to separate SQL statements from Java code and configure them through XML or annotations. Use as much as possible#{}Perform parameter binding to prevent SQL injection.

The above is personal experience. I hope you can give you a reference and I hope you can support me more.