MyBatis

Reading

Mapping between properties and column names:

<resultMap id="uniqueIdentifier" type="mappedType">
    <id column="tableColumnName" property="entityPropertyName"></id> 
    <result column="tableColumnName" property="entityPropertyName"></result>
[//]: # (    id is for primary key mapping, result is for non-primary key mapping)
</resultMap>

Difference Between # and $

• #: Represents a precompiled statement. During SQL compilation, placeholders like ? replace unknown content, preventing SQL injection.
• $: Represents SQL string concatenation, which is vulnerable to SQL injection.

---

Three Methods for Conditional Queries

For example, querying specific information about the Brand entity:

1. Use @Param to tell MyBatis the mapping between the entity class and the database table.
2. Create a new instance of the entity.
3. Use a Map where:
   • The key is the entity class property name (e.g., "status").
   • The value is the actual value (e.g., status).

List<Brand> selectBrandCondition(@Param("status") int status)
List<Brand> selectBrandCondition(Brand brand)
List<Brand> selectBrandCondition(Map map)

Dynamic Conditional Search

<where>
    <if test="specificCondition">
        sqlStatement
    </if>
</where>

<where>
    <choose> <!-- Similar to a switch in Java -->
        <when test="conditionContent"> <!-- Similar to a case in Java -->
            sqlStatement
        </when>
    </choose>
    <!-- Wrapping with <where> eliminates the need for a default case -->
</where>

The <where> tag automatically resolves some SQL syntax issues. For example, if an <if> condition evaluates to false and does not execute, it prevents leftover issues like an extra AND in the original SQL statement.

---

Create

Returning Primary Keys

When data is successfully inserted, sometimes it is necessary to retrieve the primary key of the inserted record.

<insert id="xxx" useGeneratedKeys="true" keyProperty="id">

• useGeneratedKeys: Defaults to false. Set to true to enable automatic key generation.
• keyProperty: Specifies the name of the primary key property in the entity class.

Update

<set> Tag

The <set> tag helps prevent unmodified fields from being mistakenly updated to null. It dynamically constructs the SET clause of an UPDATE statement based on the provided conditions.

Example:

<update id="updateEntity">
    UPDATE table_name
    <set>
        <if test="field1 != null">
            field1 = #{field1},
        </if>
        <if test="field2 != null">
            field2 = #{field2},
        </if>
    </set>
    WHERE id = #{id}
</update>

Delete

batch delete

<foreach> Tag

The <foreach> tag is used to handle collections in MyBatis, such as arrays, lists, or maps. It is often used for batch operations like IN queries or batch inserts/updates.

Attributes:

• collection: Specifies the collection to iterate (e.g., an array, list, or map).
• item: Represents each item in the collection during iteration.
• index (optional): Represents the index or key of the item in the collection.
• separator: Specifies the string to separate each iteration, like ,.
• open and close (optional): Define the starting and ending characters for the generated content (e.g., ( and )).

---

Example: IN Query for Batch Conditions

<select id="selectByIds" resultType="Entity">
    SELECT * FROM table_name
    WHERE id IN
    <foreach collection="list" item="id" open="(" separator="," close=")">
        #{id}
    </foreach>
</select>