MyBatis Integration in Spring Boot 3: Mappers, XML, and Dynamic SQL

Introduction

JdbcTemplate is great for simple SQL, but real business systems usually need clearer SQL organization, reusable mappers, and safer parameter binding. MyBatis gives you that balance: you write SQL explicitly while Spring Boot handles mapper scanning and integration.

In this chapter, you will integrate mybatis-spring-boot-starter, build Mapper interfaces with XML mappings, and apply practical patterns for pagination, transactions, and dynamic SQL.

Prerequisites

  • Completed:
    • 10_jdbc_datasource_and_connection_pool_basics.md
  • A running MySQL/PostgreSQL database
  • Basic SQL (SELECT, INSERT, UPDATE, DELETE)
  • Familiarity with Spring service/controller layering

Why MyBatis in Spring Boot Projects

MyBatis is a strong fit when:

  • SQL quality and tuning matter (indexes, joins, complex reports)
  • teams want SQL visibility instead of hidden ORM queries
  • legacy SQL assets need gradual migration

Typical layering:

text
Controller
    ↓
Service (@Transactional)
    ↓
Mapper interface (MyBatis)
    ↓
XML SQL / annotations
    ↓
Database

Add Dependencies

Keep your JDBC starter and DB driver from the previous chapter, then add MyBatis:

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

For pagination, PageHelper is widely used with MyBatis:

xml
<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper-spring-boot-starter</artifactId>
    <version>2.1.0</version>
</dependency>

Tip

Version Alignment

Use MyBatis Spring Boot Starter 3.x for Spring Boot 3 / Jakarta APIs. Older 2.x starters are for Spring Boot 2.

Basic MyBatis Configuration

application.yml example:

Key settings:

  • mapper-locations: where XML mapper files live
  • type-aliases-package: short class names in XML (User instead of full package)
  • map-underscore-to-camel-case: maps user_name -> userName
  • log-impl: prints SQL in local dev (disable in production)

Enable mapper scanning on your main application class:

Prepare Table and Entity

Example table:

sql
CREATE TABLE t_user (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(64) NOT NULL,
    email VARCHAR(128) NOT NULL,
    status TINYINT NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Entity class:

Mapper Interface Basics

@Param is important when a method has multiple parameters. MyBatis uses it to bind names in XML (#{id}, #{email}).

XML Mapper: CRUD and Result Mapping

Create src/main/resources/mapper/UserMapper.xml:

Notes:

  • namespace must match mapper interface fully qualified name
  • id in XML must match interface method name
  • useGeneratedKeys="true" fills user.id after insert
  • resultMap is useful when column names do not map cleanly to fields

Parameter Binding: #{} vs ${}

  • #{name}: prepared statement parameter (safe, preferred)
  • ${name}: string substitution (unsafe for user input)

Good:

xml
WHERE username = #{username}

Risky (SQL injection risk):

xml
ORDER BY ${sortColumn}

If you must use ${} (for example dynamic sort columns), whitelist allowed values in service code before passing to mapper.

Dynamic SQL in Real Queries

Search endpoint often needs optional filters. Use dynamic SQL blocks:

Mapper method:

java
List<User> searchUsers(@Param("username") String username,
                       @Param("status") Integer status);

Common dynamic tags:

  • <if>: conditional fragment
  • <where>: auto-handles leading AND/OR
  • <set>: dynamic UPDATE fields
  • <foreach>: IN (...) lists

IN query example:

xml
<select id="findByIds" resultMap="UserResultMap">
    SELECT id, username, email, status, created_at
    FROM t_user
    WHERE id IN
    <foreach collection="ids" item="id" open="(" separator="," close=")">
        #{id}
    </foreach>
</select>

Service Layer with Transactions

Keep business rules in service, not controller:

@Transactional ensures insert/update operations run in one DB transaction. If an exception is thrown, changes roll back.

Warning

@Transactional only works on Spring-managed beans and public methods called through the Spring proxy. We cover common pitfalls in the dedicated transaction chapter.

Pagination with PageHelper

PageHelper intercepts the next mapper query and adds paging SQL.

Service example:

Controller example:

PageInfo includes total count, page number, and page size metadata for frontend pagination UI.

Expose CRUD API (End-to-End Check)

Quick manual test:

bash
curl -X POST "http://localhost:8080/api/users?username=alice&email=alice@example.com"
curl "http://localhost:8080/api/users/1"
curl "http://localhost:8080/api/users?pageNum=1&pageSize=10"

Common Integration Issues

Invalid bound statement (not found)

Check:

  • mapper interface method name matches XML id
  • XML namespace matches interface package + class name
  • mapper-locations path is correct (classpath:mapper/*.xml)

Could not autowire UserMapper

Check:

  • @MapperScan base package includes mapper package
  • mapper is an interface, not a class without @Mapper/@MapperScan

SQL works in client but fails in app

Check:

  • schema/table names
  • DB user permissions
  • time zone or encoding params in JDBC URL

PageHelper returns all rows

Usually PageHelper.startPage(...) was not called immediately before the target mapper query, or another query ran in between.

Practical Design Tips

  • Keep complex SQL in XML; keep simple one-liners in annotations if your team allows it
  • Prefer #{} binding everywhere user input is involved
  • Put transaction boundaries in service layer, not controller layer
  • Add indexes for columns used in WHERE, ORDER BY, and join keys
  • Turn off SQL stdout logging in production; use proper logging levels instead

Quick Recap

In this chapter, you learned:

  1. How to add and configure mybatis-spring-boot-starter in Spring Boot 3
  2. How Mapper interfaces and XML files work together
  3. Safe parameter binding and dynamic SQL patterns
  4. How to combine @Transactional service methods with mapper operations
  5. How to implement pagination with PageHelper

Next chapter will introduce Spring Data JPA and compare when to choose JPA versus MyBatis.

FAQ

Should I use XML or annotation-based MyBatis mappings?

Both work. XML is usually easier for complex SQL and team review. Annotations are fine for very small queries. Many teams use XML for production SQL and annotations only for trivial lookups.

Do I still need spring-boot-starter-jdbc with MyBatis?

Yes, in most setups. MyBatis still relies on a DataSource and connection pool configured by Spring Boot JDBC auto-configuration.

Why use PageHelper instead of writing LIMIT manually?

PageHelper reduces repetitive paging boilerplate and provides consistent metadata (total, pages, etc.). For very custom SQL, manual LIMIT/OFFSET is still valid.

Can I mix MyBatis and JPA in one project?

Yes, technically possible, but it increases complexity. Prefer one primary persistence style per bounded module unless you have a clear migration strategy.

Is ${} ever safe?

Only when values are strictly controlled by your code (for example whitelisted sort fields), never directly from client input.