Insert Operations

Retrieving Auto-Generated Primary Keys

<insert id="saveCustomer" useGeneratedKeys="true" keyProperty="customerId"
        parameterType="com.example.domain.Customer">
  INSERT INTO customer(name, points, created_at)
  VALUES (#{name}, #{points}, #{createdAt})
</insert>

Batch Inserts

<insert id="batchSaveOrders">
  INSERT INTO order_header(customer_id, amount, status)
  VALUES
  <foreach collection="orders" item="o" separator=",">
    (#{o.customerId}, #{o.amount}, #{o.status})
  </foreach>
</insert>

Note: enable allowMultiQueries=true in the JDBC URL when using MySQL.

Update Operations

<update id="setCustomerPoints">
  UPDATE customer SET points = #{points} WHERE customer_id = #{customerId}
</update>

Dynamic Updates

<update id="patchCustomer" parameterType="Customer">
  UPDATE customer
  <set>
    <if test="name != null">name = #{name},</if>
    <if test="email != null">email = #{email},</if>
    <if test="points != null">points = #{points},</if>
  </set>
  WHERE customer_id = #{customerId}
</update>

Delete Operations

<delete id="removeCustomer">
  DELETE FROM customer WHERE customer_id = #{customerId}
</delete>

Query Ptaterns

Flxeible LIKE Conditions

<select id="findCustomersByName" resultType="Customer">
  SELECT * FROM customer
  WHERE
  <choose>
    <when test="_databaseId == 'mysql'">
      name LIKE CONCAT('%', #{keyword}, '%')
    </when>
    <when test="_databaseId == 'postgresql' or _databaseId == 'oracle'">
      name LIKE '%' || #{keyword} || '%'
    </when>
  </choose>
</select>

IN Clause with Dynamic Lists

<select id="fetchOrdersByIds" resultType="Order">
  SELECT * FROM order_header
  WHERE order_id IN
  <foreach collection="idList" open="(" close=")" separator="," item="id">
    #{id}
  </foreach>
</select>

Date Filtering

<select id="listOrdersBetweenDates" resultType="Order">
  SELECT * FROM order_header
  WHERE 1=1
  <if test="start != null">
    AND order_date >= #{start,jdbcType=DATE}
  </if>
  <if test="end != null">
    AND order_date <= #{end,jdbcType=TIMESTAMP}
  </if>
</select>

FIND_IN_SET Usage

SELECT FIND_IN_SET('red', 'blue,red,green');  -- returns 2
SELECT FIND_IN_SET('yellow', 'blue,red,green'); -- returns 0

Handling NULL Aggregates

SELECT COALESCE(SUM(amount), 0) AS total_amount
FROM order_header
WHERE customer_id = #{customerId};

Derived Column Filtering

SELECT
  DATEDIFF(CURDATE(), DATE(FROM_UNIXTIME(created_at/1000))) AS days_since
FROM customer
HAVING days_since BETWEEN 0 AND 30;

SQL Fragments and Reuse

<sql id="customerColumns">
  customer_id, name, email, points, created_at
</sql>

<select id="getCustomerById" resultMap="customerMap">
  SELECT <include refid="customerColumns"/>
  FROM customer
  WHERE customer_id = #{id}
</select>

Trim for Flexible Clauses

<update id="updateCustomer">
  UPDATE customer
  <trim prefix="SET" suffixOverrides=",">
    <if test="name != null">name = #{name},</if>
    <if test="email != null">email = #{email},</if>
    <if test="points != null">points = #{points}</if>
  </trim>
  WHERE customer_id = #{customerId}
</update>

Batch Execution Mode

try (SqlSession session =
         factory.openSession(ExecutorType.BATCH)) {
    OrderMapper mapper = session.getMapper(OrderMapper.class);
    for (Order o : orderList) {
        mapper.insert(o);
    }
    session.commit();
}

Interceptor Extension Points

• Executor – central execution engine
• StatementHandler – prepaers and executes SQL
• ParameterHandler – binds parameters
• ResultSetHandler – maps result sets to objects