MyBatis is an open-source Java persistence framework that simplifies data base interactions by reducing JDBC boilerplate code. It supports custom SQL, stored procedures, and advanced mappings. This article covers basic CRUD operations using annotations, XML mappings, and dynamic SQL.

1. Basic CRUD with MyBatis

Start by creating a Spring Boot project with MySQL and MyBatis dependencies. Define entity classes under pojo, then configure MyBatis in application.properties.

1.1 Delete Operation

Delete a record by primary key using the #{ } placeholder:

@Mapper
public interface EmpMapper {
    @Delete("DELETE FROM emp WHERE id = #{id}")
    public int deleteById(Integer id);
}

The return value indicates the number of deleted rows. Precompiled SQL with #{ } offers better performance and prevents SQL injection.

1.2 Insert Operation

Insert an employee with auto-increment ID:

@Insert("INSERT INTO emp (username, name, gender, image, job, entrydate, dept_id, create_time, update_time) " +
        "VALUES (#{username}, #{name}, #{gender}, #{image}, #{job}, #{entrydate}, #{deptId}, #{createTime}, NOW())")
public int insert(Emp emp);

To retrieve the generated primary key, add @Options(useGeneratedKeys = true, keyProperty = "id").

1.3 Update Operation

@Update("UPDATE emp SET username=#{username}, name=#{name}, gender=#{gender}, image=#{image}, " +
        "job=#{job}, entrydate=#{entrydate}, dept_id=#{deptId}, update_time=#{updateTime} WHERE id=#{id}")
public void update(Emp emp);

1.4 Select Operation

Fetching an employee by ID:

@Select("SELECT * FROM emp WHERE id = #{id}")
public Emp selectById(Integer id);

If database columns use snake_case (e.g., dept_id) and Java fields use camelCase (e.g., deptId), results may be incomplete. Resolve with one of these methods:

• Alias: Map columns explicitly in SQL: SELECT id, dept_id AS deptId, ...
• Manual mapping: Use @Results and @Result
• Enable camel case mapping: Set mybatis.configuration.map-underscore-to-camel-case=true

1.5 Conditional Query

Search with multiple optional parameters:

@Select("SELECT * FROM emp WHERE name LIKE CONCAT('%', #{name}, '%') " +
        "AND gender = #{gender} AND entrydate BETWEEN #{begin} AND #{end} " +
        "ORDER BY update_time DESC")
public List<Emp> selectByCondition(@Param("name") String name,
                                   @Param("gender") Short gender,
                                   @Param("begin") LocalDate begin,
                                   @Param("end") LocalDate end);

Note: The LIKE clause uses CONCAT to safely concatenate the pattern instead of ${ } which is vulnerable to SQL injection.

2. XML Mapping Files

MyBatis supports two ways to define SQL: annotations and XML mapping files. XML mappings provide more flexibility for complex queries.

Create an XML file in the resources/mapper dircetory with the following structure:

<?xml version="1.0" encoding="UTF-8" ?>

<mapper namespace="com.example.mapper.EmpMapper">
    <!-- SQL statements go here -->
</mapper>

Use the MyBatisX plugin for IDEA to easily navigate between mapper interfaces and XML files.

The choice between annotations and XML depends on the project. Annotations are quick for simple queries, while XML is better for complex dynamic SQL or multiple result mappings.

3. Dynamic SQL

Static SQL queries cannot handle optional parameters. For example, searching only by name without gender or date range fails with the fixed query above. Dynamic SQL constructs queries based on provided input.

MyBatis provides tags like <if>, <where>, <set>, <foreach>, and <sql>/<include>.

3.1 Using <if> and <where>

Rewrite the conditional query in XML:

<select id="selectByCondition" resultType="com.example.pojo.Emp">
    SELECT * FROM emp
    <where>
        <if test="name != null">
            name LIKE CONCAT('%', #{name}, '%')
        </if>
        <if test="gender != null">
            AND gender = #{gender}
        </if>
        <if test="begin != null and end != null">
            AND entrydate BETWEEN #{begin} AND #{end}
        </if>
    </where>
    ORDER BY update_time DESC
</select>

The <where> tag automatically removes leading AND or OR if no conditions exist, or removes the entire WHERE clause when all conditions are null.

3.2 Dynamic Udpate with <set>

To update only non-null fields, replace the annotation with an XML mapper:

<update id="update">
    UPDATE emp
    <set>
        <if test="username != null"> username = #{username}, </if>
        <if test="name != null"> name = #{name}, </if>
        <if test="gender != null"> gender = #{gender}, </if>
        <if test="image != null"> image = #{image}, </if>
        <if test="job != null"> job = #{job}, </if>
        <if test="entrydate != null"> entrydate = #{entrydate}, </if>
        <if test="deptId != null"> dept_id = #{deptId}, </if>
        <if test="createTime != null"> create_time = #{createTime}, </if>
        <if test="updateTime != null"> update_time = NOW(), </if>
    </set>
    WHERE id = #{id}
</update>

The <set> tag removes trailing commas correctly.

3.3 Batch Deletion with <foreach>

Implement delete from emp where id in (18,19,20) using dynamic SQL:

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

In the mapper interface:

public void deleteByIds(List<Integer> ids);

3.4 Reusable SQL Fragments

Use <sql> to define common column lists or conditions, then include them with <include>:

<sql id="empColumns"> id, username, name, gender, image, job, entrydate, dept_id </sql>

<select id="findAll" resultType="com.example.pojo.Emp">
    SELECT <include refid="empColumns" /> FROM emp
</select>