Master MyBatis in Spring Boot: Essential Tips, Dynamic SQL, and Advanced Queries

MyBatis Overview

MyBatis is a popular open‑source ORM framework that supports custom SQL, stored procedures and advanced mappings. It eliminates most JDBC boilerplate by allowing SQL statements in XML files to be bound to Java methods.

Integration with Spring Boot

Add the mybatis-spring-boot-starter dependency to pom.xml, configure mapper locations in application.yml, and enable DAO scanning with @MapperScan.

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

mybatis:
  mapper-locations:
    - classpath:dao/*.xml

@Configuration
@MapperScan("com.macro.mall.tiny.dao")
public class MyBatisConfig {
}

Basic CRUD Operations

Select

Define an entity class (e.g., UmsAdmin) and a DAO interface with a method such as UmsAdmin selectByIdSimple(Long id). Implement the SQL in an XML mapper.

public class UmsAdmin implements Serializable {
    private Long id;
    private String username;
    private String password;
    // other fields...
}

public interface UmsAdminDao {
    UmsAdmin selectByIdSimple(Long id);
}

<select id="selectByIdSimple" resultType="com.macro.mall.tiny.model.UmsAdmin">
    select * from ums_admin where id = #{id}
</select>

Insert

Use selectKey to retrieve the generated primary key.

<insert id="insert">
    insert into ums_admin(username, password, ...) values (#{username}, #{password}, ...)
    <selectKey keyProperty="id" resultType="long" order="AFTER">
        SELECT LAST_INSERT_ID()
    </selectKey>
</insert>

Update

<update id="updateById">
    update ums_admin
    set username = #{username},
        password = #{password},
        ...
    where id = #{id}
</update>

Delete

<delete id="deleteById">
    delete from ums_admin where id = #{id}
</delete>

Dynamic SQL

MyBatis provides tags such as if, choose, where, set and foreach to build flexible statements.

If

<select id="selectByUsernameAndEmailLike" resultType="...">
    select * from ums_admin
    where 1=1
    <if test="username != null and username != ''">
        and username like concat('%', #{username}, '%')
    </if>
    <if test="email != null and email != ''">
        and email like concat('%', #{email}, '%')
    </if>
</select>

Choose

<select id="selectByUsernameAndEmailLike2" resultType="...">
    select * from ums_admin
    where 1=1
    <choose>
        <when test="username != null and username != ''">
            and username like concat('%', #{username}, '%')
        </when>
        <when test="email != null and email != ''">
            and email like concat('%', #{email}, '%')
        </when>
        <otherwise>
            and 1=2
        </otherwise>
    </choose>
</select>

Where

<select id="selectByUsernameAndEmailLike3" resultType="...">
    select * from ums_admin
    <where>
        <if test="username != null and username != ''">
            and username like concat('%', #{username}, '%')
        </if>
        <if test="email != null and email != ''">
            and email like concat('%', #{email}, '%')
        </if>
    </where>
</select>

Set

<update id="updateByIdSelective">
    update ums_admin
    <set>
        <if test="username != null and username != ''">username = #{username},</if>
        <if test="password != null and password != ''">password = #{password},</if>
        <!-- other fields -->
    </set>
    where id = #{id}
</update>

Foreach

<insert id="insertBatch">
    insert into ums_admin(username, password, ...) values
    <foreach collection="entityList" item="item" separator=",">
        (#{item.username}, #{item.password}, ...)
    </foreach>
</insert>

Advanced Queries

One‑to‑One Mapping

Create an extension class that adds a UmsResourceCategory field to UmsResource, then use a ResultMap with association to map the joined columns.

public class UmsResourceExt extends UmsResource {
    private UmsResourceCategory category;
}

<resultMap id="ResourceWithCategoryMap" type="UmsResourceExt" extends="BaseResultMap">
    <association property="category" resultMap="UmsResourceCategoryDao.BaseResultMap" columnPrefix="category_"/>
</resultMap>

One‑to‑Many Mapping

Define an extension class that holds a list of resources and configure a collection element in the ResultMap.

public class UmsResourceCategoryExt extends UmsResourceCategory {
    private List<UmsResource> resourceList;
}

<resultMap id="selectCategoryWithResourceMap" type="UmsResourceCategoryExt" extends="BaseResultMap">
    <collection property="resourceList" columnPrefix="resource_" resultMap="UmsResourceDao.BaseResultMap"/>
</resultMap>

Pagination

Use the PageHelper starter and call PageHelper.startPage(pageNum, pageSize) before a query; the result can be wrapped in PageInfo.

@Service
public class UmsResourceServiceImpl implements UmsResourceService {
    @Autowired
    private UmsResourceDao umsResourceDao;

    @Override
    public PageInfo<UmsResource> page(Integer pageNum, Integer pageSize, Long categoryId) {
        PageHelper.startPage(pageNum, pageSize);
        List<UmsResource> list = umsResourceDao.selectListByCategoryId(categoryId);
        return new PageInfo<>(list);
    }
}

Conclusion

The article summarizes common MyBatis usage patterns, including Spring Boot integration, basic CRUD, dynamic SQL, advanced mappings and pagination, providing a handy reference for developers.

Source code: https://github.com/macrozheng/mall-learning/tree/master/mall-tiny-mybatis