MyBatis in Practice: Mappers, Dynamic SQL and Paging

bee2026-10-0863 min read0 views

Starting with mybatis-spring-boot-starter: the safety line between #{} and ${}, the dynamic SQL tag family, joins and paging — with both XML and annotation styles ready to copy.

#MyBatis

1 / 165

Section

0. The 30-second version

2 / 165

First, bust a myth: MyBatis does not write SQL for you. It does one humble job — you supply the SQL; it carries your method arguments into that SQL and carries the returned rows back into Java objects. Every bit of repetitive labour in between (getting a connection, pre-compiling, setXxx binding, iterating the ResultSet, assigning field by field, wrapping exceptions, returning the connection) is taken off your hands. So learning MyBatis really comes down to two things: where the SQL lives, and how to make it line up with your Java objects.

3 / 165

Six terms, one line each (used throughout):

4 / 165

Mapper interface: the Java interface where you only declare methods, never implement them, e.g. UserMapper.findById(Long id). It is the order slip, not the kitchen
XML mapping file: a list of SQL statements under a namespace; the namespace must be the interface's fully-qualified name and the id must equal the method name — those two threads stitch the pair together
SqlSession: MyBatis's execution entry point, think of it as the counter clerk for one session; selectOne / insert / update all pass through it
Executor: the dispatcher behind SqlSession; it decides whether to consult a cache, reuse statements, or batch
resultMap / resultType: the two ways to pour results back into objects. Use resultType when columns and fields already agree; write a resultMap when they do not (or when associations are involved)
#{} versus ${}: the first is a parameter (pre-compiled, safe); the second is string concatenation (it can rewrite the SQL structure, dangerous). This is the single most important dividing line in the article

5 / 165

Analogy

MyBatis is an interpreter. The client (your Java code) speaks Chinese; the counter (the database) only listens to English. The interpreter's duty is to haul information between them and never invent what the client asked for. The mapping is neat: the Mapper interface is the slip you hand over saying "translate this"; the SQL in the XML is the draft the interpreter prepared; #{} is a blank left in the draft that someone fills with a number at the end (nobody can alter the sentence pattern); ${} is copying the client's raw words straight into the sentence (if they slip in "and drop that table while you're at it", the sentence really does change); resultMap is writing their answer back into your form line by line; and leaving the camelCase flag off is the interpreter translating user_name as "用户名" while your entity files it under userName — so that box stays empty forever. The interpreter hauls; it does not think for you. Hold onto that line and you sidestep eight tenths of MyBatis traps.

6 / 165

Diagram

7 / 165

That tag-family tree is the map of this article: nearly every assembly job you meet in daily work lands in one of these six branches. Section 5 gives copy-ready fragments for each.

8 / 165

After this article you should be able to answer three questions:

9 / 165

I wrote only an interface, not a single implementation class — so who actually executes userMapper.findById(1L)?
When Invalid bound statement (not found) appears, which three threads should I pull?
Why is <if test="keyword != null"> not enough? What does an empty string turn the SQL into?

10 / 165

Feel how easily "mapping" goes wrong first — flip the two switches and the console output on the right changes instantly:

11 / 165

Sandbox

SandboxSame query, mapping switches decide whether your object has values

map-underscore-to-camel-case

Result mapping style

Result

===>  Preparing: SELECT id, user_name, status FROM t_user WHERE id = ?
===> Parameters: 1(Long)
<===      Row: 1 | alice | 1
User{id=1, userName='alice', status=1}
# column user_name reaches userName via the camel rule — no mapping code at all

The laziest correct combination: underscore-conformant columns plus the flag on. Turn this flag on from day one in beginner projects

12 / 165

Tip

of the four cells, only the bottom-right "half filled, half empty" one is hard to spot yourself — it neither errors nor comes back blank. Build the habit real projects use: compare the logged <=== Row line with what your object prints. Mismatch means suspect the mapping first, not the SQL.

13 / 165

Section

1. What MyBatis is for: the trade-off of a semi-automatic ORM

14 / 165

For the data-access layer there is one soul-searching question: should the framework write your SQL? Hand everything to the framework (JPA/Hibernate) and CRUD is lightning fast — until you hit a complex report, a multi-table join, or a performance tuning session, and then you fight the SQL the framework generates. Write everything by hand (JdbcTemplate) and you own the SQL — but shuffling ResultSet rows into objects wears you down.

15 / 165

MyBatis answers with "semi-automatic": it will not write your SQL, it only stitches the tedious gap between SQL and Java objects. You provide the SQL; it handles pre-compilation, parameter binding, result mapping, caching and transaction integration.

16 / 165

Diagram

17 / 165

Table

Option	SQL control	Dev speed	Learning cost	Typical fit
JdbcTemplate	Maximum, all hand-written	Low (lots of boilerplate)	Low	Reports, batch jobs, tiny tools
MyBatis	High (you write SQL, fully controllable)	Medium-high	Medium	Complex queries, performance-sensitive, SQL you want to edit
JPA / Hibernate	Low (framework generates SQL)	High (near-zero CRUD code)	High	Clear domain model, mostly CRUD

18 / 165

Note

there is no absolute winner, only fit. When SQL is complex, tuning matters and the team knows SQL, MyBatis feels natural. When the model is stable and CRUD dominates, JPA is more productive. Let's first see what each layer on the MyBatis chain does, then wire it up.

19 / 165

Section

2. Getting started: dependency, config and @MapperScan

20 / 165

Spring Boot does not bundle MyBatis; you need the official starter. Three pieces and you rarely touch config again:

21 / 165

xml

<dependency>    <groupId>org.mybatis.spring.boot</groupId>    <artifactId>mybatis-spring-boot-starter</artifactId>    <version>3.0.3</version>   <!-- for Spring Boot 3.x / JDK 17+ --></dependency><dependency>    <groupId>com.mysql</groupId>    <artifactId>mysql-connector-j</artifactId>    <scope>runtime</scope></dependency>

22 / 165

Then application.yml. These three keys are what beginners most often miss — and they produce the weirdest bugs:

23 / 165

yaml

mybatis:  mapper-locations: classpath:mapper/*.xml     # where the XML lives  type-aliases-package: com.example.demo.entity # entity package, so XML can omit FQNs  configuration:    map-underscore-to-camel-case: true          # user_name -> userName, must be on    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl  # print SQL to console# optional: the PageHelper paging plugin, see section 7

24 / 165

Add the scanning annotation on the application class so the container knows where mappers live:

25 / 165

Code

Codejava

@SpringBootApplication@MapperScan("com.example.demo.mapper")   // scan them all at once, no @Mapper per interfacepublic class DemoApplication {    public static void main(String[] args) {        SpringApplication.run(DemoApplication.class, args);    }}

Notes

mapper-locations is empty by default; XML must sit in the right place or you get Invalid bound statement (not found)
with map-underscore-to-camel-case: true, DB user_name maps to userName automatically — no @Results needed
@MapperScan and per-interface @Mapper are alternatives; the former is tidier

Trap: XML placed under a package inside src/main/java is not copied into target/classes by default. Put XML in src/main/resources/mapper/, or add a resources block to the pom — otherwise you are guaranteed an Invalid bound statement error.

26 / 165

Dependencies and configuration each have three or four viable combinations — do not memorise them, generate them. Start with the pom: what do MyBatis + MySQL alone give you, how many lines does a test class lose once H2 and Test are ticked, and where does each scope land:

27 / 165

Generator

GeneratorWhich dependencies a MyBatis project actually needspom.xml3 / 8

Tick MyBatis / MySQL / H2 / Test and compare the three generated scopes: runtime only matters once it runs, test lives only inside tests, compile is what leaks downstream — the rule from article 2 makes its first real appearance here

groupIdartifactIdJava version

Output

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>3.3.4</version> <!-- 版本由 BOM 统管，子依赖不写 version -->
        <relativePath/>
    </parent>

    <groupId>com.example</groupId>
    <artifactId>demo-service</artifactId>
    <version>0.0.1-SNAPSHOT</version>

    <properties>
        <java.version>17</java.version>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>3.0.3</version>
            <!-- 第三方 starter：必须写版本 -->
        </dependency>
        <dependency>
            <groupId>com.mysql</groupId>
            <artifactId>mysql-connector-j</artifactId>
            <scope>runtime</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
</project>

Why each choice matters

parentInheriting 3.3.4 starter-parent means no spring-boot-starter-* needs a version; the moment someone adds an explicit version to one starter, that one wins — the most common source of dependency drift.

WebAnything that serves HTTP needs it: DispatcherServlet, embedded Tomcat and JSON mapping come inside this starter.

MyBatis 3.xA third-party starter: Boot’s BOM does not manage it, so the version must be explicit.

MySQL 驱动scope=runtime: not needed to compile, loaded at runtime via SPI — do not promote it to compile.

28 / 165

Then the yml: datasource alone is the minimum that lets a mapper run; logging is what puts ===> Preparing on screen (Sections 8 and 11 both depend on it); profile shows how dev and prod databases split across files:

29 / 165

Generator

GeneratorA configuration that runs and also shows its SQLapplication.yml2 / 4

datasource supplies url, credentials and pool settings; logging raises org.apache.ibatis and your mapper package so SQL and parameters appear in the log; profile keeps dev and prod apart — and in that prod copy, never set log-impl to StdOutImpl

App name

Output

server:
  port: 8080

spring:
  application:
    name: demo-service
  datasource:
    url: jdbc:mysql://127.0.0.1:3306/bee_order?useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
    username: ${DB_USER:root}          # ${} 占位符：环境变量优先，冒号后是默认值
    password: ${DB_PASS:}
    hikari:
      maximum-pool-size: 20
      minimum-idle: 5
      connection-timeout: 30000
      max-lifetime: 1740000            # 必须小于 MySQL 的 wait_timeout
      pool-name: beeHikari

logging:
  level:
    root: INFO
    com.example.demoservice: DEBUG
    org.springframework.jdbc.core.JdbcTemplate: DEBUG   # 打 SQL 与参数
  file:
    name: logs/app.log
  logback:
    rollingpolicy: { max-file-size: 50MB, max-history: 14 }

Why each choice matters

datasourcePool settings only apply here; constructing HikariDataSource in code ignores every one of them.

loggingLevels work per package; root=DEBUG floods you with third-party output — never in production.

30 / 165

Note

those two artefacts together answer everything in this section. Keep one self-check habit: for every box you tick, ask "if I remove it, which log line or which error disappears" — far more useful than memorising property names.

31 / 165

Section

3. Your first mapper: the safety line between #{} and ${}

32 / 165

First the trio of entity, interface and XML. This is the skeleton for everything that follows:

33 / 165

java

// Entity: camelCase fields; the DB column may differ — underscore mapping or resultMap bridges itpublic class User {    private Long id;    private String userName;   // DB column user_name    private Integer status;    // getters / setters omitted}

34 / 165

java

public interface UserMapper {    User findById(Long id);    List<User> findByStatus(@Param("status") Integer status);    int insert(User user);}

35 / 165

xml

<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"        "http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mapper namespace="com.example.demo.mapper.UserMapper">    <select id="findById" resultType="User">        SELECT id, user_name, status FROM t_user WHERE id = #{id}    </select>    <select id="findByStatus" resultType="User">        SELECT id, user_name, status FROM t_user WHERE status = #{status}    </select>    <insert id="insert" useGeneratedKeys="true" keyProperty="id">        INSERT INTO t_user (user_name, status) VALUES (#{userName}, #{status})    </insert></mapper>

36 / 165

Animation

37 / 165

That one covers "how many steps a SQL takes". The animation below answers the question beginners actually have: the interface has no implementation class — so who does the work? Seven hand-offs that introduce all five names at once: MapperProxy, SqlSession, Executor, StatementHandler, ResultSetHandler.

38 / 165

Animation

39 / 165

The method name must match the XML id, and namespace must be the interface's fully-qualified name. Miss either and startup fails outright.

40 / 165

Now the heart of this section: #{} versus ${} is not "quoted vs unquoted" — it is "pre-compiled" vs "string-concatenated".

41 / 165

Table

Form	What happens underneath	Injection-safe	Correct use
`#{name}`	Produces a `PreparedStatement` `?`; the value goes through `setXxx`	Yes	99% of all parameter passing
`${name}`	The value is concatenated into the SQL string	Risky	Table names / column names / sort fields — structural fragments

42 / 165

Here is a real injection scene. This one wants to sort by a field from the client:

43 / 165

java

@Select("SELECT * FROM t_user ORDER BY ${orderBy}")List<User> listOrderBy(@Param("orderBy") String orderBy);

44 / 165

If orderBy comes straight from a request parameter, an attacker sends id; DROP TABLE t_user--, and the concatenated SQL becomes ... ORDER BY id; DROP TABLE t_user--. ${} is not pre-compiled; the SQL structure is rewritten and injection happens.

45 / 165

Code

Codejava

// Safe version: whitelist the sort column, only allow fixed values, then use ${} for structureprivate static final Set<String> ALLOWED = Set.of("id", "user_name", "status", "created_at");public List<User> listOrderBy(String raw) {    if (raw == null || !ALLOWED.contains(raw)) {        raw = "id";              // illegal input falls back to a safe default    }    return userMapper.listOrderBy(raw);   // raw is now guaranteed to be whitelisted}

Notes

#{}: a parameterised query — the database treats the value as data, and it can never change the SQL grammar
${}: string substitution — the value becomes part of the SQL structure, usable only where parameterisation is impossible (table / column names, sort direction)
One line to remember: use #{} wherever you can; when ${} is unavoidable, whitelist first

46 / 165

Those three lines are the verdict; the animation shows where the road forks. Watch frames ③ and ④ — the SQL shape is already frozen at the PreparedStatement moment, so anything arriving afterwards can only fill a slot and can never rewrite the sentence; ${} did its damage before frame ① by copying the value into the sentence itself:

47 / 165

Animation

48 / 165

Then run the experiment yourself. On "injection scene" you see a literal ' OR 1=1 -- swallow the whole WHERE clause; on "when you must concatenate" you see why #{} is helpless in the ORDER BY position — the one place in this section where #{} genuinely cannot be used:

49 / 165

Kernel lab

TeaVMPrepared statements and injection: where #{} and ${} really divergeidle

Cycle hash -> dollar -> inject -> order: hash pairs the ? with setXxx, dollar shows raw substitution, inject replays a real injected statement, order explains the one case where concatenation is unavoidable

Scenario

Click “Run demo” to execute the AOT-compiled Java kernel right in your browser, step by step.

50 / 165

There is only one rule, but where it applies and where it does not is what actually blocks beginners — because whether a position can be parameterised depends on what role that position plays in SQL grammar. Play a round: pick a position on the left, then the syntax it needs on the right; a wrong pair explains itself:

51 / 165

Match

MatchThis position: #{} or ${}?Matched 0/6 · Missed 0

Six real call sites. The single test: is this position a *value* or a piece of *structure*?

Pick a card on the left first

52 / 165

Section

4. Parameters and result mapping

53 / 165

With a single parameter MyBatis takes it directly. With several you need @Param to name them, otherwise you are stuck with the awkward aliases arg0/param1:

54 / 165

java

// Multiple params need @Param, else the XML only sees #{arg0} #{arg1} or #{param1} #{param2}List<User> search(@Param("keyword") String keyword,                  @Param("status") Integer status,                  @Param("offset") int offset,                  @Param("size") int size);

55 / 165

When field and column names diverge, declare a resultMap; for joins use association (one-to-one) and collection (one-to-many):

56 / 165

Code

Codexml

<resultMap id="OrderWithItems" type="Order">    <id     property="id"         column="order_id"/>    <result property="orderNo"    column="order_no"/>    <result property="createdAt"  column="created_at"/>    <!-- one-to-one: order -> user -->    <association property="user" javaType="User">        <id     property="id"       column="u_id"/>        <result property="userName" column="u_name"/>    </association>    <!-- one-to-many: order -> item list -->    <collection property="items" ofType="OrderItem">        <id     property="id"       column="item_id"/>        <result property="productId" column="product_id"/>        <result property="quantity"  column="quantity"/>    </collection></resultMap><select id="findOrderDetail" resultMap="OrderWithItems">    SELECT o.id AS order_id, o.order_no, o.created_at,           u.id AS u_id, u.user_name AS u_name,           i.id AS item_id, i.product_id, i.quantity    FROM t_order o    JOIN t_user u ON u.id = o.user_id    LEFT JOIN t_order_item i ON i.order_id = o.id    WHERE o.id = #{id}</select>

Notes

association handles "an order belongs to one user"; collection handles "an order contains many items"
The trick is in the SQL: alias the clashing columns from different tables (u_id / item_id), then let resultMap file them home
If field and column names already agree and camelCase maps cleanly, you can skip resultMap entirely and use resultType

57 / 165

Kernel lab

TeaVMSee where data access sits in the request chainidle

Take /users/42 and locate the Controller -> Service -> Mapper calls

Scenario

Click “Run demo” to execute the AOT-compiled Java kernel right in your browser, step by step.

58 / 165

Tip

The demo above runs the /users/42 request. You will see that the mapper is only the last link: Controller takes params -> Service orchestrates business and transactions -> Mapper runs SQL. Understand this chain and you can reason about which layer a transaction belongs on.

59 / 165

That chain reads like seven nouns, but it is really a piece of code you can step through. Below is userMapper.findById(1L) spread out: the seven lines on the left are the order actually taken, the right panel shows what you are holding at each moment. Press step repeatedly and watch step 3 — that is where Invalid bound statement (not found) is born:

60 / 165

Stepper

StepperStep by step: there is no implementation class, so who executes this line1 / 7

Seven beats. At step 2 you still hold nothing but a proxy; SQL appears for the first time at step 4 — there is no code of yours anywhere in between

Code under debug

1User u = userMapper.findById(1L); // you own an interface, no implementation class

2// MapperProxy.invoke(proxy, method, args) // the JDK dynamic proxy is the only entry

3// mapperInterface + methodName form the id // e.g. com.example.UserMapper.findById

4MappedStatement ms = cfg.getMappedStatement(id); // use that key to find the SQL

5// SqlSession.selectOne(ms, param) // the execution entry you never call

6// Executor.query(ms, param) // asks the first-level cache first

7// ResultSetHandler rebuilds a User // camelCase or resultMap lands here

Variables now

userMapper really is	a JDK proxy object
method name	findById
implementation class	does not exist

Call stack

1UserService.list

2UserMapper.findById

1At startup @MapperScan registers these interfaces as beans, so what gets injected is not some UserMapperImpl but the proxy built by Proxy.newProxyInstance. Failing to find an implementation class is not a memory lapse — there never was one.

61 / 165

Section

5. The dynamic SQL tag family

62 / 165

Dynamic SQL is MyBatis's most practical feature: one XML fragment composes different SQL depending on the parameters. Here is each tag, copy-ready.

63 / 165

Analogy

dynamic SQL is a fill-in-the-blanks official form. You draft a template — "Mr/Ms ___ is requesting leave because ___" — and only the sentences needed today get filled in. <where> and <set> are the pedantic proofreader: they delete a leftover leading "AND" or trailing comma so the document stays grammatical (otherwise SQL throws a syntax error). <foreach> is like joining a list of names with commas — except when there is not a single name, what you print is an empty "( )", and the reader has no idea what to do with it (IN () syntax error). Remember those three roles and you never have to memorise tag names again.

64 / 165

First question, deliberately light:

65 / 165

Quiz

Check yourselfFor this query, what SQL is produced when both keyword and status are null? SELECT * FROM t_user <where> <if test="keyword != null">AND user_name LIKE #{keyword}</if> <if test="status != null">AND status = #{status}</if> </where>

Pick one — you get feedback right away

66 / 165

Section

5.1 if: append conditionally

67 / 165

Code

Codexml

<select id="search" resultType="User">    SELECT * FROM t_user    WHERE 1 = 1    <if test="keyword != null and keyword != ''">        AND user_name LIKE CONCAT('%', #{keyword}, '%')    </if>    <if test="status != null">        AND status = #{status}    </if></select>

Notes

test holds an OGNL expression; for strings check both != null and != ''
WHERE 1 = 1 is the old trick to absorb the first AND; the <where> in the next subsection kills it gracefully

68 / 165

Section

5.2 choose / when / otherwise: pick exactly one

69 / 165

xml

<choose>    <when test="orderBy == 'price'">ORDER BY price ASC</when>    <when test="orderBy == 'sales'">ORDER BY sales DESC</when>    <otherwise>ORDER BY id DESC</otherwise>   <!-- fallback so there is always a sort --></choose>

70 / 165

Section

5.3 where / set / trim: auto add and strip keywords

71 / 165

Code

Codexml

<select id="search2" resultType="User">    SELECT * FROM t_user    <where>        <if test="keyword != null and keyword != ''">            AND user_name LIKE CONCAT('%', #{keyword}, '%')        </if>        <if test="status != null">AND status = #{status}</if>    </where></select><update id="updateSelective">    UPDATE t_user    <set>        <if test="userName != null">user_name = #{userName},</if>        <if test="status != null">status = #{status},</if>    </set>    WHERE id = #{id}</update>

Notes

<where>: adds WHERE only when there is content, and strips a leading AND / OR
<set>: adds SET only when there is content, and strips a trailing comma
<trim prefix="(" suffix=")" prefixOverrides="AND |OR "> is the general form when you need custom affixes

72 / 165

Section

5.4 foreach: IN queries and batch inserts

73 / 165

Code

Codexml

<!-- IN query: expand a collection into (1, 2, 3) --><select id="findByIds" resultType="User">    SELECT * FROM t_user    WHERE id IN    <foreach collection="ids" item="id" open="(" separator="," close=")">        #{id}    </foreach></select><!-- Batch insert: multiple VALUES in a single INSERT --><insert id="batchInsert">    INSERT INTO t_user (user_name, status) VALUES    <foreach collection="list" item="u" separator=",">        (#{u.userName}, #{u.status})    </foreach></insert>

Notes

collection: use list for a List, array for an array, or the name given by @Param
open / close / separator wrap and delimit; inside foreach, #{id} resolves to the current item

74 / 165

Section

5.5 sql / include: reusable fragments

75 / 165

Code

Codexml

<sql id="baseColumns">id, user_name, status, created_at</sql><select id="findById" resultType="User">    SELECT <include refid="baseColumns"/> FROM t_user WHERE id = #{id}</select>

Notes

Extract a repeated column list into <sql>; change it once and everything updates
<include> can also take <property> children for parameterised reuse

76 / 165

Section

6. Annotation style: @Select / @Insert / @Results

77 / 165

Simple SQL reads better right on the interface — a joy during prototyping:

78 / 165

java

public interface UserMapper {    @Select("SELECT id, user_name, status FROM t_user WHERE id = #{id}")    @Results(id = "userMap", value = {        @Result(column = "id",        property = "id", id = true),        @Result(column = "user_name", property = "userName"),        @Result(column = "status",    property = "status")    })    User findById(Long id);    @Insert("INSERT INTO t_user (user_name, status) VALUES (#{userName}, #{status})")    @Options(useGeneratedKeys = true, keyProperty = "id")    int insert(User user);    @Update("UPDATE t_user SET status = #{status} WHERE id = #{id}")    int updateStatus(@Param("id") Long id, @Param("status") Integer status);}

79 / 165

The home turf of each style is clear:

80 / 165

Table

Dimension	Annotations	XML
Readability	Simple SQL reads well	Complex SQL stays structured
Dynamic SQL	Must wrap in `<script>`, awkward	Native tags, natural
Reuse	Fragments hard to reuse	`<sql>` / `<include>` at hand
Join mapping	`@Results` works but gets cramped	`resultMap` is clean
Recompile after editing SQL	Yes	No — just edit XML

81 / 165

Point: **dynamic SQL, complex joins, reusable fragments** always go in XML; only a one- or two-line query deserves an annotation. Do not cram all three cases into annotations just to avoid one more file — that only doubles the maintenance cost.

82 / 165

Section

7. Paging: from hand-written limit to a plugin

83 / 165

The rawest way is to splice paging parameters into the SQL by hand:

84 / 165

xml

<select id="page" resultType="User">    SELECT * FROM t_user ORDER BY id DESC LIMIT #{offset}, #{size}</select>

85 / 165

It works, but you also write a separate COUNT(*) query and hand-compute offsets — not recommended. The common choice is the PageHelper plugin.

86 / 165

Its mechanism is clever: one moment before the query you set the paging parameters; PageHelper stores them in a ThreadLocal, then a MyBatis interceptor rewrites the pending SQL (appending LIMIT and running a COUNT), and clears the ThreadLocal afterwards.

87 / 165

Code

Codejava

// 1. Dependency: pagehelper-spring-boot-starter (version aligned with Boot)// 2. Usage: right next to the query — no other SQL in betweenPageHelper.startPage(1, 10);              // page 1, 10 rows eachList<User> list = userMapper.search(null, 1, null, null);PageInfo<User> pageInfo = new PageInfo<>(list);   // wraps total count and pagesSystem.out.println(pageInfo.getTotal());System.out.println(pageInfo.getPages());

Notes

startPage and the real query must be adjacent; inserting any other query in between lets the paging state leak
PageInfo reads the COUNT result from the ThreadLocal and derives total / pages
Remember: PageHelper.startPage affects only the very next SQL statement

88 / 165

"Affects only the first one" is not something you believe by reading — you believe it once you see the rewrite. Click through these five boxes; box ① (where the parameters wait) and box ⑤ (when they are cleared) together decide what happens if you squeeze another query in between:

89 / 165

Diagram

FlowWhat PageHelper actually rewrites (click through it)1 / 5

Go from ① to ⑤; this chain is the reason startPage must sit right next to the query

→

① Parameters enter a ThreadLocal

startPage(1, 10) queries nothing. It merely hangs the paging object on the current thread. No SQL has started yet — the parameters are waiting for you.

All clearThe whole mechanism has one soft spot: the distance between the ThreadLocal and the next statement. So keep nothing between startPage and your query.

90 / 165

Once a table passes a few million rows, deep paging with LIMIT offset, size gets slower and slower (it scans every preceding row). Use cursor paging anchored on the primary key instead:

91 / 165

Code

Codexml

<!-- Cursor paging: WHERE id < #{lastId} ORDER BY id DESC LIMIT #{size}     always scans only size rows, so page N is as fast as page 1 --><select id="nextPage" resultType="User">    SELECT * FROM t_user    <where>        <if test="lastId != null">AND id &lt; #{lastId}</if>    </where>    ORDER BY id DESC    LIMIT #{size}</select>

Notes

Trap: cursor paging gives up "jump to page N". It fits feeds and order lists — the "keep scrolling" case. Admin tables that must jump precisely to page N still need LIMIT offset or PageHelper.

92 / 165

Beyond paging, MyBatis has one more genuinely numeric knob, and you already saw its cost in the previous article: an unattended slow query keeps grip of a connection until the pool drains (the scene in article 28, section 12). default-statement-timeout is the fuse fitted to every statement — measured in seconds, and the default 0 means no limit at all. Drag it and see where it earns its keep:

93 / 165

Tuner

TunerHow long may one statement run

mybatis.configuration.default-statement-timeout

5secondsNow 0 – 60

The usual answer: slow ones die, fast ones are untouched

5-10 seconds is where most online systems land
Past the line the statement is cancelled: the connection returns immediately and one query cannot drain the pool
The failure is explicit and carries the statement, so the log names the offender
Pair it with MySQL's long_query_time — reading both is what turns a timeout into a cause

Connections held12%

False kills8%

This knob pairs with connectionTimeout from the last article: one bounds how long a slow statement may hold a connection, the other how long a waiting caller may stand. Both need a ceiling.

94 / 165

Section

8. What first- and second-level caches really are

95 / 165

MyBatis has two cache levels, and their defaults differ sharply — most caching bugs trace back to them:

96 / 165

Table

Level	Scope	Default	Toggle	Classic pitfall
First level	Same SqlSession	On	Cannot be disabled (only tuned)	Reading stale data within one session
Second level	Same namespace (across SqlSessions)	Off	`<cache/>` or `@CacheNamespace`	In a cluster each node caches its own copy and they go stale after a write

97 / 165

The whole difference between the two levels is the single word scope: the first travels with the SqlSession (with Spring, that is one transaction), the second with the namespace (across sessions, but only inside this process). Keep the two columns apart and each of the "mysterious behaviours" below gets an owner:

98 / 165

Diagram

99 / 165

The first-level cache lives inside the SqlSession. Within one SqlSession, identical SQL and parameters are queried only once; the second call hits the cache. Inside one transaction this is usually fine, but three scenarios involving invalidation / dirty reads deserve memorising:

100 / 165

java

// Scenario: inside a single SqlSession (in Spring, a single transaction)User u1 = mapper.findById(1L);      // queries the DB, fills the first-level cacheUser u2 = mapper.findById(1L);      // cache hit, no query// When this cache is cleared:// 1) any insert / update / delete runs (flushCache)// 2) sqlSession.clearCache() is called explicitly// 3) the SqlSession closes (transaction ends)

101 / 165

The real trap: you assume every mapper call re-queries the DB, but within one transaction the second read comes from the cache. If another thread updates that row in between, you read the old value — the so-called "dirty read within one SqlSession".

102 / 165

The second-level cache is off by default. Switch it on and query results of a namespace are cached across SqlSessions, saving repeated queries across requests. But in a cluster, each node holds its own copy: node A writes, node B's cache never invalidates, and B serves stale data. Therefore:

103 / 165

enable it only for dictionary-like, read-heavy, rarely-written data where brief inconsistency is acceptable
in a cluster, prefer a centralised cache like Redis over the local second-level cache
with either level, if you need "read the latest value immediately after a write", clear the cache explicitly or bypass it

104 / 165

Second question: it asks you to line up the transaction boundary with the cache boundary (many engineers never realise they are the same thing):

105 / 165

Quiz

Check yourselfA service method is annotated @Transactional. Inside it userMapper.findById(1L) returns u1; meanwhile another thread commits an update to that row; the method then calls findById(1L) again and gets u2. What happens?

Pick one — you get feedback right away

106 / 165

Section

9. Two traps you must remember

107 / 165

Trap one: map-underscore-to-camel-case off, and every field is null.

108 / 165

The DB column is user_name, the field is userName. With the flag off, MyBatis matches column names exactly, finds no userName column, and the field stays null. The table has rows, the SQL returned them — yet every field is empty. People debug for ages thinking it is a SQL problem when it is the mapping flag. Fix: switch it on globally, or write an explicit resultMap.

109 / 165

Trap two: #{} does not work for a sort field, and ${} is risky.

110 / 165

ORDER BY #{orderBy} becomes ORDER BY ?; the database treats ? as a constant value, so the sort silently fails (or errors). But ${orderBy} reopens the injection surface. The only right answer is a whitelist: allow only a predefined set of column names and fall back to a default otherwise — see the ALLOWED code in section 3. The same rule applies to dynamic table names and dynamic columns.

111 / 165

Warning

whenever you see ${}, immediately ask "can the user control this value?". If yes, whitelist it; only if no is it even tentatively safe.

112 / 165

Section

10. Take MyBatis apart: four kernel experiments

113 / 165

All four demos below run the real kernel (WASM); click a parameter and it reruns. Suggested order: use the first to build the "interface → SQL" feel, the second to watch dynamic SQL assembly, the third to see why a cache can lie to you inside one transaction, and the fourth for what a paging plugin actually rewrites.

114 / 165

Section

10.1 One select, plus dynamic SQL / caches / paging (`mybatis`)

115 / 165

Kernel lab

TeaVMMapper proxy to result map: five switchable parametersidle

Run "one select" first to learn the five class names, then cycle through dynamic / l1 / l2 / page to see four different rewrites

Scenario

Click “Run demo” to execute the AOT-compiled Java kernel right in your browser, step by step.

116 / 165

What to stare at for each parameter:

117 / 165

Table

Parameter	What you will see	Matches section
One select	The full relay MapperProxy → SqlSession → Executor → StatementHandler → ResultSetHandler	The animation in section 3
Dynamic SQL	`<if>` / `<where>` deciding whether a fragment enters the final SQL	Section 5
First-level cache	The second identical query inside one SqlSession emits no SQL at all	Section 8
Second-level cache	Cross-SqlSession hits on the namespace cache; any write clears the whole block	Section 8
Paging rewrite	`LIMIT` appended to your SQL plus an extra `COUNT` run	Section 7

118 / 165

Tip

watch "first-level cache" and "paging rewrite" back to back and two commonly confused facts click into place — PageHelper's ThreadLocal applies only to the very next SQL, and the first-level cache's scope is the SqlSession (≈ one transaction). Those two properties explain most of MyBatis's "spooky behaviour".

119 / 165

Section

10.2 Who did this hauling before MyBatis (`jdbc`)

120 / 165

Everything MyBatis took over was once yours to write. Switch to "ResultSet → object" to see the per-column boilerplate, then to "forgetting to release" to see connections leaking — that is precisely which layers a semi-automatic ORM removes.

121 / 165

Kernel lab

TeaVMThe JdbcTemplate skeleton: what MyBatis carries for youidle

Compare map (hand-written mapping) with leak (forgotten close) to feel where the value sits

Scenario

Click “Run demo” to execute the AOT-compiled Java kernel right in your browser, step by step.

122 / 165

Section

10.3 When SQL is slow, the pool surfaces the disease (`pool`)

123 / 165

A mapper only produces SQL; that SQL still has to borrow a connection. Switch to "queue at the limit" and "wait timeout": a single query whose foreach expands into thousands of IN values can hold a connection long enough that the whole pool starts queueing and eventually times out everywhere. MyBatis performance problems show their symptoms on the connection pool.

124 / 165

Kernel lab

TeaVMBorrow, queue, leak: the knock-on effect of slow SQLidle

Switch to "leak detection" for the stack-trace warning — a manual getConnection without close in mapper-heavy code looks exactly like this

Scenario

Click “Run demo” to execute the AOT-compiled Java kernel right in your browser, step by step.

125 / 165

Section

10.4 Did my update vanish? Check the rollback rules first (`txprop`)

126 / 165

An <update> returning 0 affected rows does not mean "the statement failed" — it may simply have matched nothing. And if an outer method throws and rolls back, your earlier successful writes disappear with it. Switch to "rollback rules" to watch rollbackFor interact with checked exceptions:

127 / 165

Kernel lab

TeaVMSeven propagation types and rollback rulesidle

Switch to "rollback rules": why an IOException leaves the transaction committed while the mapper's write has already been sent

Scenario

Click “Run demo” to execute the AOT-compiled Java kernel right in your browser, step by step.

128 / 165

Note

remember these three layers — the mapper produces SQL → the pool supplies the connection → the transaction decides whether the changes count. Diagnose any data-access problem by peeling from the top down.

129 / 165

Enough buttons — type it yourself now. This console talks to the same in-browser Java kernel, and every reply is computed there: start with beans to check the mappers really were registered, use cond to flip a switch and watch the container step aside, then walk the three themes of this article (prepared statements, dynamic SQL, the first-level cache) with lab:

130 / 165

Console

131 / 165

Note

lab mybatis l1 followed straight by lab pool timeout makes a useful pair: the first shows "the second identical query inside one SqlSession sends no statement", the second shows "one long-held statement can drain the whole pool". The same wish — fewer statements — is what saves you in the first and what ambushes you in the second.

132 / 165

Section

11. Common errors quick lookup

133 / 165

Table

Error text (fragment)	Actual cause	30-second self-rescue	Dig deeper in
`org.apache.ibatis.binding.BindingException: Invalid bound statement (not found)`	Interface method and XML are not bound: wrong `namespace`, `id` differing from the method name, XML not covered by `mapper-locations`, or XML placed under `src/main/java` and never packaged	Pull three threads: ① is `namespace` the interface's fully-qualified name ② does `id` equal the method name ③ does `target/classes/mapper/` actually contain that XML	Sections 2 and 3
`java.sql.SQLSyntaxErrorException: ... near 'DROP TABLE'` / syntax error	`${}` pasted external input into the SQL structure — a live injection scene	Change that spot to `#{}`; if a dynamic table/column is genuinely needed, whitelist it server-side first (see `ALLOWED` in section 3)	Section 3
`SQLSyntaxErrorException: ... near ')'`, log shows `WHERE id IN ()`	`<foreach>` received an empty collection but still emitted `open="(" close=")"`, producing empty parentheses	Guard the call and return an empty list early, or wrap the whole `IN (...)` block in `<if test="ids != null and ids.size() > 0">`	Section 5.4
No error at all, yet every entity field is `null` (the table clearly has rows)	`map-underscore-to-camel-case` off, or a `resultMap` whose `column` was written as the Java property name	Turn the camelCase flag on; for joins and aliases check that every `<result column="..."/>` matches the column alias in the SQL exactly	Section 9
`TooManyResultsException: Expected one result (or null) to be returned by selectOne(), but found: N`	The method returns a single object while the SQL returns several rows (usually row multiplication after a join, or a missing unique condition)	Return `List<T>`, or make the SQL guarantee at most one row (unique index / `LIMIT 1`)	Section 4
`PersistenceException: Parameter 'status' not found. Available parameters are [arg1, arg0, param1, param2]`	A multi-parameter method lacks `@Param`, so the XML can only reach the `arg0/param1` aliases	Add `@Param("status")` to each parameter; pass a single object instead and reference its properties directly	Section 4
`BindingException: Error evaluating expression 'keyword != null'. Cause: ... OgnlException`	The `<if test>` references a parameter name that was never declared, so OGNL cannot resolve it	Compare `@Param` names against the `test` expression including case; string checks need `keyword != null and keyword != ''`	Section 5
Stale reads after clustering; restarting one node "fixes" it	Each JVM keeps its own local second-level cache; a write on node A never invalidates node B	Remove `<cache/>` and move to a centralised cache such as Redis; give dictionary data an expiry anyway	Section 8

134 / 165

Tip

the two nastiest rows above raise no error at all (fields all null; reading stale data). Their shared remedy is the same habit: turn on log-impl: org.apache.ibatis.logging.stdout.StdOutImpl and always judge by "how many SQL statements really ran, with what parameters, returning which rows".

135 / 165

The first row of that table is the one beginners cannot parse, even though the message contains the answer. Here is a real stack — do not read the analysis, just click the frame you think is guilty:

136 / 165

Triage

Error triageBindingException: Invalid bound statement (not found)

The interface method found no SQL: the key is printed inside the message

You followed a tutorial exactly: mapper interface written, XML written, startup clean, injection fine — then the first call to findById throws BindingException. The newcomer's first thought is that MyBatis is broken.

org.apache.ibatis.binding.BindingException: Invalid bound statement (not found): com.example.demo.mapper.UserMapper.findById

at org.apache.ibatis.binding.MapperMethod$SqlCommand.<init>(MapperMethod.java:235)

at org.apache.ibatis.binding.MapperMethod.<init>(MapperMethod.java:53)

at org.apache.ibatis.binding.MapperProxy.lambda$cachedMapperMethod$0(MapperProxy.java:98)

at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:92)

at jdk.proxy2/jdk.proxy2.$Proxy87.findById(Unknown Source)

at com.example.demo.service.UserService.getUser(UserService.java:29)

Click the frame you blame — guessing is allowed

No pressure: guess the exception first, then which line actually made the call.

137 / 165

Section

12. Hands-on exercises

138 / 165

Section

Tier 1 · Follow along: a minimal CRUD + dynamic query + batch insert project

139 / 165

Goal: get the canonical structure working — one line on the interface, all SQL in XML — and watch dynamic SQL assemble three different statements. All files given, copy them directly.

140 / 165

java

package com.example.demo.entity;public class User {    private Long id;    private String userName;   // DB column user_name    private Integer status;    // getter / setter / toString omitted}

141 / 165

java

package com.example.demo.mapper;import com.example.demo.entity.User;import org.apache.ibatis.annotations.Mapper;import org.apache.ibatis.annotations.Param;import java.util.List;@Mapperpublic interface UserMapper {    User findById(Long id);    List<User> search(@Param("keyword") String keyword, @Param("status") Integer status);    List<User> findByIds(@Param("ids") List<Long> ids);    int batchInsert(@Param("list") List<User> list);    int updateSelective(User user);}

142 / 165

src/main/resources/mapper/UserMapper.xml (it must live under resources — put it in the java source folder and you get Invalid bound statement):

143 / 165

xml

<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"        "http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mapper namespace="com.example.demo.mapper.UserMapper">    <sql id="baseColumns">id, user_name, status</sql>    <select id="findById" resultType="User">        SELECT <include refid="baseColumns"/> FROM t_user WHERE id = #{id}    </select>    <select id="search" resultType="User">        SELECT <include refid="baseColumns"/> FROM t_user        <where>            <if test="keyword != null and keyword != ''">                AND user_name LIKE CONCAT('%', #{keyword}, '%')            </if>            <if test="status != null">AND status = #{status}</if>        </where>        ORDER BY id DESC    </select>    <select id="findByIds" resultType="User">        SELECT <include refid="baseColumns"/> FROM t_user        WHERE id IN        <foreach collection="ids" item="id" open="(" separator="," close=")">#{id}</foreach>    </select>    <insert id="batchInsert">        INSERT INTO t_user (user_name, status) VALUES        <foreach collection="list" item="u" separator=",">(#{u.userName}, #{u.status})</foreach>    </insert>    <update id="updateSelective">        UPDATE t_user        <set>            <if test="userName != null">user_name = #{userName},</if>            <if test="status != null">status = #{status},</if>        </set>        WHERE id = #{id}    </update></mapper>

144 / 165

The test that drives it:

145 / 165

java

@SpringBootTestclass UserMapperTest {    @Autowired UserMapper mapper;    @Test    void dynamicSql() {        mapper.search(null, null);      // (1) no filter        mapper.search("ali", null);     // (2) keyword only        mapper.search(null, 1);         // (3) status only    }}

146 / 165

Expected console SQL log (only present with log-impl enabled; the shape must match):

147 / 165

text

===>  Preparing: SELECT id, user_name, status FROM t_user ORDER BY id DESC===> Parameters: <>===>  Preparing: SELECT id, user_name, status FROM t_user WHERE user_name LIKE ? ORDER BY id DESC===> Parameters: %ali%(String)===>  Preparing: SELECT id, user_name, status FROM t_user WHERE status = ? ORDER BY id DESC===> Parameters: 1(Integer)===>  Preparing: INSERT INTO t_user (user_name, status) VALUES (?, ?), (?, ?), (?, ?)===> Parameters: alice(String), 1(Integer), bob(String), 1(Integer), cindy(String), 0(Integer)===>  Preparing: UPDATE t_user SET status = ? WHERE id = ?===> Parameters: 2(Integer), 1(Long)

148 / 165

Confirm four things against that log:

149 / 165

In case (1) there is not even a WHERE — that is <where> behaving correctly, not a bug
In (2) and (3) the leading AND was stripped and every parameter is a ? (the doing of #{})
<sql> + <include> keep the column list defined in exactly one place
The UPDATE mentions only status, because <set> assembles non-null fields and deletes the trailing comma

150 / 165

Section

Tier 2 · Variants: change one thing at a time and record what you observe

151 / 165

Set map-underscore-to-camel-case to false and rerun → you will observe: <=== Row clearly has data, yet toString() prints userName=null. That is trap one in the flesh — worth more than reading it ten times.
Replace <where> in search with a hard-coded WHERE 1 = 1 → you will observe: same results today, but the day someone forgets the AND prefix the SQL breaks. Feel what <where> was absorbing on your behalf.
Call findByIds(List.of()) with an empty collection → you will observe: the log shows WHERE id IN () and a SQLSyntaxErrorException is thrown. Fix it yourself: wrap the block in <if test="ids != null and ids.size() > 0">, or short-circuit in the service.
Add ORDER BY ${orderBy} to search and pass id; DROP TABLE t_user-- → you will observe: the logged SQL structure really did change. Delete that line immediately afterwards, and write in your notes the iron rule "${} only ever receives whitelisted values".
Change findById's return type to List<User> while leaving SELECT * FROM t_user unfiltered → do the reverse and meet TooManyResultsException once, so you recognise it instantly next time.

152 / 165

Section

Tier 3 · Build one: a real admin order-list endpoint

153 / 165

Requirement: implement GET /admin/orders?keyword=&status=&page=1&size=20&sortField=id&order=desc with MyBatis, covering multi-condition filtering + paging + sorting. Acceptance checklist:

154 / 165

[ ] One method on the mapper interface, all SQL in XML; filters expressed with <where> + <if> and no WHERE 1=1 anywhere
[ ] The sort field goes through a server-side whitelist (Set<String> ALLOWED) with id as fallback; direction allows only the two literals ASC / DESC, expressed with <choose>
[ ] Two paging implementations, each with a documented use case: PageHelper (startPage glued to the query) and cursor paging (WHERE id < #{lastId}); state which one serves "jump to page N"
[ ] Multi-value status filters expand with <foreach>, and an empty collection emits no SQL — it returns an empty page
[ ] With log-impl on, paste into the README the SQL actually produced by each combination (at least four: unfiltered / single condition / multi-condition plus sort / cursor next-page)
[ ] Load-test the endpoint with 100 concurrent clients while capturing hikaricp.connections.pending and the MySQL slow query log; decide whether the bottleneck is SQL, mapping or the pool, and write a five-line conclusion
[ ] Bonus: enable <cache/> on one query, then change the data from a second instance and reproduce "the other node serves stale rows", and explain why production should use Redis instead of the local second-level cache

155 / 165

Finish these three tiers and your MyBatis skill moves from "knowing the tags" to "knowing what happens at every step, and which layer to inspect when it breaks".

156 / 165

Section

13. Key-point self-check

157 / 165

Self-check

when Invalid bound statement (not found) appears, what are your three threads? Answer: whether namespace equals the interface's fully-qualified name, whether id equals the method name, and whether that XML exists in the compiled target/classes.

158 / 165

Self-check

state the essential difference between #{} and ${} in one sentence. Answer: one is a pre-compiled parameter (a value can never enter the sentence pattern), the other is string concatenation (a value can rewrite the pattern).

159 / 165

Self-check

what does <where> emit when not a single <if> matched? Answer: nothing — not even the WHERE keyword, which is why WHERE 1=1 is unnecessary.

160 / 165

Self-check

inside one transaction, does a second findById(1L) send SQL? Answer: by default no — the first-level cache hits within the SqlSession, which is also why you cannot see another thread's freshly committed value.

161 / 165

Self-check

why must you not enable the second-level cache in a cluster? Answer: every JVM holds its own copy; a write on node A never invalidates node B, so you serve stale data. Centralised caching is the answer.

162 / 165

Mantra: **the interface orders, the XML cooks; leave camelCase off and every field is null. `#{}` fills numbers, `${}` rewrites sentences — whitelist before you ever use it. `<where>` trims the head, `<set>` trims the tail, and `<foreach>` must not face an empty collection. The first-level cache follows the transaction; keep the second-level cache out of the cluster.**

163 / 165

Section

14. Decision card and summary

164 / 165

Decision

DecisionA new project needs lots of multi-table joins and reporting SQL; the team knows SQL and wants freedom to edit SQL anytime. Should the data-access layer use MyBatis or Spring Data JPA?

165 / 165

Summary

All of MyBatis condenses into four lines — use #{} whenever you can, and whitelist before any ${}; compose dynamic SQL with tags and put complex queries in XML; map joins with resultMap and check the camelCase flag when fields do not line up; trust only the first-level cache by default and never count on the second in a cluster. Memorise these four and you will dodge the vast majority of MyBatis traps in advance.