MyBatis Usage

📢 This article was translated by gemini-3-flash-preview

MyBatis Series
MyBatis Installation and Getting Started: https://blog.yexca.net/en/archives/215
MyBatis Usage: This article

Delete

Use #{} as a placeholder, with the parameter name inside.

1
2
3
4
5
6
@Mapper
public interface EmpMapper {
    // Delete
    @Delete("delete from mybatis.emp where id = #{id}")
    public void delete(Integer id);
}

Testing:

1
2
3
4
5
6
7
8
9
@SpringBootTest
class Mybatis02ApplicationTests {
    @Autowired
    private EmpMapper empMapper;
    @Test
    void contextLoads() {
        empMapper.delete(17);
    }
}

Usually, you don’t need a return value, but if you do, it returns the number of rows affected by the operation.

1
2
3
4
5
6
@Mapper
public interface EmpMapper {
    // Delete
    @Delete("delete from mybatis.emp where id = #{id}")
    public int delete(Integer id);
}

Placeholders

Parameter placeholders include #{} and ${}.

Placeholder	`#{}`	`${}`
Format	Precompiled	Concatenation
When to Use	Parameter passing, login, etc.	Dynamically setting table or column names
Pros/Cons	Secure, high performance	Prone to SQL injection

Insert

Also uses placeholders. If you have many parameters, you can encapsulate them into an object; the placeholder names should match the object’s property names.

1
2
3
4
5
6
7
@Mapper
public interface EmpMapper {
    // Insert
    @Insert("insert into mybatis.emp(username, name, gender, image, job, entrydate, dept_id, create_time, update_time) "+
            "values (#{username},#{name},#{gender},#{image},#{job},#{entrydate},#{deptId},#{createTime},#{updateTime});")
    public void insert(Emp emp);
}

Testing:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
@SpringBootTest
class Mybatis02ApplicationTests {
    
    @Autowired
    private EmpMapper empMapper;
    
    @Test
    public void testInsert(){
        Emp emp = new Emp();
        emp.setUsername("Tom");
        emp.setName("Tom");
        emp.setGender((short) 1);
        emp.setImage("tom.jpg");
        emp.setJob((short) 1);
        emp.setEntrydate(LocalDate.of(2000,1,1));
        emp.setDeptId(1);
        emp.setCreateTime(LocalDateTime.now());
        emp.setUpdateTime(LocalDateTime.now());

        empMapper.insert(emp);
    }
}

Getting the Primary Key Value

Sometimes you need to retrieve the generated primary key after a successful insertion.

1
2
3
4
5
6
7
8
9
@Mapper
public interface EmpMapper {
    // Get primary key: keyProperty assigns the ID to the emp object's id property; useGeneratedKeys enables it.
    @Options(keyProperty = "id", useGeneratedKeys = true)
    // Insert
    @Insert("insert into mybatis.emp(username, name, gender, image, job, entrydate, dept_id, create_time, update_time) "+
            "values (#{username},#{name},#{gender},#{image},#{job},#{entrydate},#{deptId},#{createTime},#{updateTime});")
    public void insert(Emp emp);
}

Update

Similar to Insert, you can encapsulate parameters into an object.

1
2
3
4
5
6
7
@Mapper
public interface EmpMapper {
    // Update
    @Update("update mybatis.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);
}

Testing:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
@SpringBootTest
class Mybatis02ApplicationTests {
    
    @Autowired
    private EmpMapper empMapper;
    
    @Test
    public void testUpdate(){
        Emp emp = new Emp();
        emp.setUsername("Tom2");
        emp.setName("Tom2");
        emp.setGender((short) 1);
        emp.setImage("tom.jpg");
        emp.setJob((short) 1);
        emp.setEntrydate(LocalDate.of(2000,1,1));
        emp.setDeptId(1);
        emp.setUpdateTime(LocalDateTime.now());
        emp.setId(19);
        empMapper.update(emp);
    }
}

Select (Query)

There are two main types: querying all attributes by ID, and querying based on specific conditions.

Query by ID

Interface:

1
2
3
4
5
6
@Mapper
public interface EmpMapper {
    // Query by ID
    @Select("select * from mybatis.emp where id = #{id}")
    public Emp idSelect(Integer id);
}

Testing:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
@SpringBootTest
class Mybatis02ApplicationTests {
    
    @Autowired
    private EmpMapper empMapper;
    
    @Test
    public void testIdSelect(){
        Emp emp = empMapper.idSelect(19);
        System.out.println(emp);
    }
}

Result:

1
Emp(id=19, username=Tom2, password=123456, name=Tom2, gender=1, image=tom.jpg, job=1, entrydate=2000-01-01, deptId=null, createTime=null, updateTime=null)

Some results are null because the field names in the Emp class don’t match the database column names.

There are three ways to fix this:

Using Aliases

1
2
3
4
5
6
7
@Mapper
public interface EmpMapper {
    // Method 1: Aliases
    @Select("select id, username, password, name, gender, image, job, entrydate, dept_id deptId, create_time createTime, update_time updateTime" +
            " from mybatis.emp where id = #{id}")
    public Emp idSelect(Integer id);
}

Using the @Results Annotation

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
@Mapper
public interface EmpMapper {
    // Method 2: @Results and @Result annotations
    @Results({
            @Result(column = "dept_id", property = "deptId"),
            @Result(column = "create_time", property = "createTime"),
            @Result(column = "update_time", property = "updateTime")
    })
    @Select("select * from mybatis.emp where id = #{id}")
    public Emp idSelect(Integer id);
}

Using Automatic Mapping (CamelCase)

If your database uses underscores (a_column) and your Java properties use camelCase (aColumn), you can enable automatic mapping.

Add this to application.properties:

1
mybatis.configuration.map-underscore-to-camel-case=true

Then you can use the original simple code:

1
2
3
4
5
6
@Mapper
public interface EmpMapper {
    // Query by ID
    @Select("select * from mybatis.emp where id = #{id}")
    public Emp idSelect(Integer id);
}

Tip: You can install the MyBatisX plugin in IDEA for easier development.

Query by Conditions

Requirement: Search for employees based on name (fuzzy match), gender (exact match), and entry date range.

1
2
3
4
5
6
7
8
@Mapper
public interface EmpMapper {
    // Conditional query
    // Note: ${name} is used here within single quotes, which allows concatenation inside quotes.
    @Select("select * from mybatis.emp where name like '%${name}%' and gender = #{gender} and " +
            "entrydate between #{begin} and #{end} order by update_time desc")
    public List<Emp> conditionSelect(String name, short gender, LocalDate begin, LocalDate end);
}

Using interpolation ${} for string concatenation is insecure (SQL injection risk).

1
2
3
4
5
6
7
@Mapper
public interface EmpMapper {
    // More secure conditional query
    @Select("select * from mybatis.emp where name like concat('%',#{name},'%') and gender = #{gender} and " +
            "entrydate between #{begin} and #{end} order by update_time desc")
    public List<Emp> conditionSelect(String name, short gender, LocalDate begin, LocalDate end);
}

This uses the concat function. Testing:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
@SpringBootTest
class Mybatis02ApplicationTests {
    
    @Autowired
    private EmpMapper empMapper;
    
    @Test
    public void testConditionSelect(){
        List<Emp> empList = empMapper.conditionSelect("Zhang", (short) 1, LocalDate.of(2010, 1, 1), LocalDate.of(2020, 1, 1));
        System.out.println(empList);
    }
}

Parameter Name Note

In SpringBoot 2.x+, variable names inside #{} are automatically mapped to method parameters. In 1.x or standalone MyBatis, you must use the @Param annotation.

1
2
3
4
5
@Mapper
public interface EmpMapper {
    // In 1.x or standalone MyBatis, add @Param annotations:
    public List<Emp> conditionSelect(@Param("name") String name, @Param("gender") short gender, @Param("begin") LocalDate begin, @Param("end") LocalDate end);
}

Using XML Mapper Files

Annotations are convenient for short SQL, but XML files are better for long or complex queries to keep the code clean. Guidelines:

The XML filename must match the Mapper interface name and be placed in the same package structure (within resources).
The XML namespace must match the fully qualified name of the Mapper interface.
The SQL id in XML must match the method name in the interface, and return types must match.

XML

In a Maven project, non-Java files go in src/main/resources. Create a directory structure matching the Mapper’s package.

If the interface is net.yexca.mapper.EmpMapper.java, the XML should be at src/main/resources/net/yexca/mapper/EmpMapper.xml.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!-- Header found on the official MyBatis site -->
<mapper namespace="net.yexca.mapper.EmpMapper">
    <!-- id matches the method name -->
    <!-- resultType is the type for a single record -->
    <select id="conditionSelect" resultType="net.yexca.pojo.Emp">
        select * from mybatis.emp where name like concat('%',#{name},'%') and gender = #{gender} and
        entrydate between #{begin} and #{end} order by update_time desc
    </select>
</mapper>

Interface Class

1
2
3
4
@Mapper
public interface EmpMapper {
    public List<Emp> conditionSelect(String name, short gender, LocalDate begin, LocalDate end);
}

Dynamic SQL

Dynamic SQL changes based on user input or external conditions.

For example, in the conditional query above, if a user only provides one parameter instead of all three, the query might return nothing. Dynamic SQL solves this.

where & if

Use the test attribute to evaluate conditions. If true, the SQL snippet is appended.

Example: append the name condition only if it’s not null.

1
2
3
<if test="name!=null">
    name like concat('%',#{name},'%')
</if>

The <where> tag manages the WHERE keyword and automatically removes leading AND or OR.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
<select id="conditionSelect" resultType="net.yexca.pojo.Emp">
        select * from mybatis.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>

set

The <set> tag replaces the SET keyword and automatically removes trailing commas. Used in UPDATE statements.

Converting a standard UPDATE to dynamic SQL:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
<update id="update2">
    update mybatis.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="updateTime != null">update_time = #{updateTime}</if>
    </set>
    where id = #{id};
</update>

foreach

The <foreach> tag is used for iterating over collections.

1
2
3
4
      <!-- Collection to iterate, element name, separator, and surrounding SQL fragments -->
<foreach collection="ids" item="id" separator="," open="(" close=")">
    #{id}
</foreach>

If ids is [13, 14, 15], this generates (13,14,15).

Requirement: Batch delete by IDs.

Interface:

1
public void deleteByIds(List<Integer> ids);

XML:

1
2
3
4
5
6
<delete id="deleteByIds">
    delete from mybatis.emp where id in
    <foreach collection="ids" item="id" separator="," open="(" close=")">
        #{id}
    </foreach>
</delete>

Testing:

1
2
3
4
5
@Test
public void testDeleteByIds(){
    List<Integer> ids = Arrays.asList(13,14,15);
    empMapper.deleteByIds(ids);
}

sql & include

To avoid duplicating SQL snippets, use the <sql> tag to define reusable parts and the <include> tag to call them.

1
2
3
4
5
6
7
<!-- Unique ID for the snippet -->
<sql id = "commonCode">
    <!-- SQL statement -->
</sql>

<!-- Reference the snippet using refid -->
<include refid = "commonCode" />