1. Project setup

1.1 Dependency

Add MyBatis Spring Boot starter to pom.xml.

<dependency>
  <groupId>org.mybatis.spring.boot</groupId>
  <artifactId>mybatis-spring-boot-starter</artifactId>
  <version>2.3.1</version>
</dependency>

1.2 Data source configuration (MySQL)

application.properties

spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/database?characterEncoding=utf8&serverTimezone=UTC
spring.datasource.username=your_username
spring.datasource.password=your_password

# Print SQL executed by mappers in this package during development
logging.level.com.example.demo.mapper=debug

1.3 Bootstrapping

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class Application {
  public static void main(String[] args) {
    SpringApplication.run(Application.class, args);
  }
}

Either annotate every mapper interface with @Mapper or enable scanning once:

// @MapperScan("com.example.demo.mapper")

1.4 Minimal mapper to validate connectivity

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

@Mapper
public interface UserMapper {
  @Select("SELECT 1")
  int ping();
}

2. CRUD with annotations

2.1 Parameter binding options

• JavaBean/POJO parameter
• Map parameter
• Multiple discrete parameters annotated via @Param("name")

2.2 Return values for DML

• int indicates affected row count
• void is also supported where you do not care about counts

2.3 Mapping columns to properties

When column names and Java fields differ, either:

• Use SQL aliases (AS) to align with property names

@Select("SELECT 'Alice' AS name")
User loadSample();

• Use @Results/@Result to define a mapping and reuse via @ResultMap

import org.apache.ibatis.annotations.*;

@Mapper
public interface UserMapper {
  @Select("SELECT t_id, t_age, t_name FROM sys_user WHERE t_id = #{id}")
  @Results(id = "userMap", value = {
    @Result(property = "id",   column = "t_id"),
    @Result(property = "age",  column = "t_age"),
    @Result(property = "name", column = "t_name")
  })
  User findById(@Param("id") String id);

  @Select("SELECT t_id, t_age, t_name FROM sys_user WHERE t_name = #{name}")
  @ResultMap("userMap")
  User findByName(@Param("name") String name);
}

2.4 Notes on JavaBean constructors

• If only a single all-args constructor exists, MyBatis will attempt to call it using selected columns in order; @Results may be ignored. A mismatch in column count or order can cause type conversion errors.
• With mulitple constructors and no no-args constructor, MyBatis chooses a constructor whose parameter count matches the columns; if none match, an exception is thrown.
• If a no-args constructor is available, it is used by default, and properties are set via setters.
• Prefer including a no-args constructor; add parameterized constructors only when truly needed.

2.5 Basic examples

import org.apache.ibatis.annotations.*;

@Mapper
public interface UserMapper {
  // Create using POJO
  @Insert("INSERT INTO sys_user (t_id, t_name, t_age) VALUES (#{id}, #{name}, #{age})")
  int insert(User u);

  // Create using Map
  @Insert("INSERT INTO sys_user (t_id, t_name, t_age) VALUES (#{id}, #{name}, #{age})")
  int insertByMap(Map<String, Object> args);

  // Create using discrete parameters
  @Insert("INSERT INTO sys_user (t_id, t_name, t_age) VALUES (#{id}, #{name}, #{age})")
  int insertByParams(@Param("id") String id,
                     @Param("name") String name,
                     @Param("age") int age);

  // Update
  @Update("UPDATE sys_user SET t_name = #{name}, t_age = #{age} WHERE t_id = #{id}")
  int update(User u);

  // Delete by id
  @Delete("DELETE FROM sys_user WHERE t_id = #{id}")
  int deleteById(@Param("id") String id);

  // Delete all
  @Delete("DELETE FROM sys_user")
  int deleteAll();

  // TRUNCATE (row count commonly returns 0)
  @Delete("TRUNCATE TABLE sys_user")
  void truncate();

  // Single row with explicit mapping
  @Select("SELECT t_id, t_age, t_name FROM sys_user WHERE t_id = #{id}")
  @Results(id = "userMap", value = {
    @Result(property = "id",   column = "t_id"),
    @Result(property = "age",  column = "t_age"),
    @Result(property = "name", column = "t_name", javaType = String.class)
  })
  User selectOne(@Param("id") String id);

  // List
  @ResultMap("userMap")
  @Select("SELECT t_id, t_name, t_age FROM sys_user")
  List<User> selectAll();

  // Count
  @Select("SELECT COUNT(*) FROM sys_user")
  int countAll();
}

3. Dynamic SQL in annotations

Wrap dynamic fragments with when using annotations.

<script> ... </script>

3.1 if

Conditional fragments for optional predicates.

<if test="name != null and name.trim().length() > 0">
  AND t_name LIKE CONCAT('%', #{name}, '%')
</if>

3.2 choose/when/otherwise

Mutually exclusive branching similar to switch/case.

<choose>
  <when test="id != null">AND t_id = #{id}</when>
  <otherwise>AND t_name LIKE CONCAT('%', #{name}, '%')</otherwise>
</choose>

3.3 where and set

• adds WHERE only when inner conditions exist and strips leading AND/OR
• trims trailing commas in UPDATE

3.4 bind

Define a temporary variable for reuse.

<bind name="likeName" value="'%' + name + '%'"/>

3.5 foreach

Iterate over a collection (e.g., for IN clauses or batch values).

<foreach collection="list" item="it" open="(" separator="," close=")">
  #{it}
</foreach>

If the input list is ["a","b","c"], the result becomes (#{it},#{it},#{it}) with bound values ('a','b','c').

3.6 Dynamic SQL examples

@Mapper
public interface UserMapper {
  // Optional filters via if
  @Select({
    "<script>",
    "SELECT t_id, t_name, t_age",
    "FROM sys_user",
    "<where>",
    "  <if test='id != null'>",
    "    AND t_id = #{id}",
    "  </if>",
    "  <if test='name != null and name.trim().length() &gt; 0'>",
    "    AND t_name LIKE CONCAT('%', #{name}, '%')",
    "  </if>",
    "</where>",
    "</script>"
  })
  @ResultMap("userMap")
  List<User> queryWithIf(User probe);

  // choose/when/otherwise
  @Select({
    "<script>",
    "SELECT t_id, t_name, t_age",
    "FROM sys_user",
    "<where>",
    "  <choose>",
    "    <when test='id != null'>AND t_id = #{id}</when>",
    "    <otherwise>AND t_name LIKE CONCAT('%', #{name}, '%')</otherwise>",
    "  </choose>",
    "</where>",
    "</script>"
  })
  @ResultMap("userMap")
  List<User> queryWithChoose(User probe);

  // Dynamic UPDATE via set
  @Update({
    "<script>",
    "UPDATE sys_user",
    "<set>",
    "  <if test='name != null'>t_name = #{name},</if>",
    "  <if test='age  != null'>t_age  = #{age},</if>",
    "</set>",
    "WHERE t_id = #{id}",
    "</script>"
  })
  int updateSelective(User u);

  // Batch insert using foreach
  @Insert({
    "<script>",
    "INSERT INTO sys_user (t_id, t_name, t_age)",
    "VALUES",
    "<foreach collection='list' item='u' separator=','>",
    "  (#{u.id}, #{u.name}, #{u.age})",
    "</foreach>",
    "</script>"
  })
  int batchInsert(List<User> users);

  // IN clause with foreach
  @Select({
    "<script>",
    "SELECT t_id, t_name, t_age",
    "FROM sys_user",
    "WHERE t_name IN",
    "<foreach collection='names' item='n' open='(' separator=',' close=')'>",
    "  #{n}",
    "</foreach>",
    "</script>"
  })
  @ResultMap("userMap")
  List<User> findByNames(@Param("names") List<String> names);

  // bind for LIKE patterns
  @Select({
    "<script>",
    "<bind name='pattern' value="+'\''+"%"+"\''+" + name + "+'\''+"%"+"\''+" />", // see simpler variant below
    "SELECT t_id, t_name, t_age",
    "FROM sys_user",
    "WHERE t_name LIKE #{pattern}",
    "</script>"
  })
  @ResultMap("userMap")
  List<User> searchByBind(@Param("name") String name);
}

Simpler bind line if not embedding quotes in Java:

4. Trnasactions

MyBatis participates in Spring-managed transactions. Annotate service-layer methods with @Transactional.

import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
public class UserService {
  private final UserMapper mapper;

  public UserService(UserMapper mapper) { this.mapper = mapper; }

  @Transactional
  public void replaceUsers(List<User> batch) {
    mapper.deleteAll();
    mapper.batchInsert(batch);
  }
}

5. SQL builder options

5.1 Provider methods with the SQL helper

Use org.apache.ibatis.jdbc.SQL to construct queries in Java.

import org.apache.ibatis.annotations.SelectProvider;
import org.apache.ibatis.jdbc.SQL;

public interface UserMapper {
  class UserSql {
    public String buildSelect(User probe) {
      return new SQL() {{
        SELECT("t_id, t_name, t_age");
        FROM("sys_user");
        if (probe.getId() != null) WHERE("t_id = #{id}");
        if (probe.getName() != null && !probe.getName().isEmpty())
          WHERE("t_name LIKE CONCAT('%', #{name}, '%')");
      }}.toString();
    }
  }

  @SelectProvider(type = UserSql.class, method = "buildSelect")
  @ResultMap("userMap")
  List<User> selectByProvider(User probe);
}

5.2 MyBatis Dynamic SQL (library)

Alternatively, adopt the mybatis-dynamic-sql library for type-safe builders and multi-dialect support. It allows composing queries with Java DSL and integrating with MyBatis mappers via providers.