MyBatis Deep Dive: 8 High-Frequency Interview Questions from Core Principles to Source Code Analysis
This comprehensive guide covers MyBatis core architecture, #{} vs ${} SQL injection risks, first/second-level caching, plugin mechanism with PageHelper, MyBatis-Plus features, Hibernate/JPA comparison, and SqlSession thread-safety — all with source-code-level diagrams and interview answer templates.
MyBatis Working Principle: SqlSessionFactory → SqlSession → Executor → StatementHandler
The execution flow is a chained invocation: factory produces session, session delegates to executor, executor schedules statement handler, which finally handles JDBC interaction. The article uses a restaurant analogy: SqlSessionFactory is the resident chef, SqlSession is a single order, Executor is the runner, and StatementHandler is the cook.
Component Responsibilities
1. SqlSessionFactory
Creates SqlSession objects
Built once at startup via SqlSessionFactoryBuilder from mybatis-config.xml and Mapper.xml
Holds all configuration (environments, mappings, plugins)
Thread-safe, globally shared
2. SqlSession
Top-level API for DB interaction (CRUD, transaction management)
Short-lived: created per request, must be closed
Thread-unsafe — never share as instance field
Delegates execution to Executor; provides getMapper() for proxy objects
3. Executor
Schedules SQL execution, manages caches and transactions
Lifecycle tied to SqlSession
Three implementations: SimpleExecutor (default, new Statement each time), ReuseExecutor (reuses Statement), BatchExecutor (batch updates)
Maintains first-level cache (localCache) and second-level cache
On query: checks first-level cache first; on miss, delegates to StatementHandler
Before commit, clears cache and triggers flush
4. StatementHandler
Wraps JDBC Statement; real JDBC interaction happens here
Created per SQL execution
Core target for plugins (e.g., PageHelper intercepts here)
Uses ParameterHandler to set parameters, ResultSetHandler to map results
End-to-End Flow for userMapper.selectById(1)
SqlSession gets command, finds SQL via MappedStatement
Delegates to Executor, which checks cache, then creates StatementHandler
StatementHandler obtains Connection, creates PreparedStatement, binds parameter 1, executes
ResultSetHandler maps rows to User object
SqlSession commits/rolls back transaction, closes, ending lifecycle
#{} vs ${} — SQL Injection Root Cause
Core Difference
#{}: Pre-compiled (PreparedStatement). MyBatis replaces #{} with ? and uses JDBC set methods. Parameters treated as data. ${}: String substitution (Statement). MyBatis directly concatenates the variable value into SQL. Parameters treated as code.
Comparison
Processing : #{} pre-compiled with placeholder ?; ${} direct string concatenation
SQL Injection Risk : #{} none — auto-escapes special chars; ${} extreme — no escaping
Use Cases : #{} most parameters (WHERE, INSERT/UPDATE values); ${} dynamic table/column names, ORDER BY fields
Performance : #{} better — plan cache reuse; ${} worse — new SQL each time
Type Handling : #{} auto Java↔JDBC conversion; ${} pure string, no conversion
Underlying Mechanism : #{} PreparedStatement; ${} Statement or concatenated string
Quoting : #{} auto-adds quotes for strings; ${} raw output, no processing
Injection Demo
Safe with #{}
<select id="login" resultType="User">
SELECT * FROM user WHERE username = #{username} AND password = #{password}
</select>Input: username="admin' OR '1'='1" Pre-compiled SQL: SELECT * FROM user WHERE username = ? AND password = ? Executed as:
SELECT * FROM user WHERE username = 'admin\' OR \'1\'=\'1' AND password = 'xxx'Single quotes escaped → injection neutralized
Dangerous with ${}
<select id="login" resultType="User">
SELECT * FROM user WHERE username = ${username} AND password = ${password}
</select>Same input produces:
SELECT * FROM user WHERE username = 'admin' OR '1'='1' AND password = 'xxx'— '1'='1' always true, login bypassed.
Worse: username="admin'; DROP TABLE user; --" →
SELECT * FROM user WHERE username = 'admin'; DROP TABLE user; --' AND password = 'xxx'— table dropped.
When to Use ${}
Only for dynamic table names, column names, or sort fields — these cannot be placeholders. Example:
@Select("SELECT * FROM users ORDER BY ${orderColumn} ${direction}")
List<User> findAllOrdered(@Param("orderColumn") String orderColumn, @Param("direction") String direction);Best Practices
Use #{} for 99% of cases
Only use ${} for dynamic identifiers
Always whitelist-validate ${} inputs:
if (!Arrays.asList("id", "create_time", "update_time").contains(sortField)) {
throw new IllegalArgumentException("Illegal sort field");
}Never use ${} in WHERE, INSERT, or UPDATE values
First-Level & Second-Level Cache Mechanisms
Analogy
First-level cache = your personal notebook in your backpack (SqlSession-scoped, private, discarded when session ends)
Second-level cache = shared whiteboard in library lobby (Mapper namespace-scoped, shared across sessions, persisted via transaction commit)
First-Level Cache (SqlSession-scoped)
Enabled by default, cannot be disabled
Backed by a HashMap; key = statementId + SQL + parameters
Same SqlSession, same query → first hit DB, subsequent hits cache
Invalidated when: SqlSession closes; any insert/update/delete executes; clearCache() called; different MappedStatement used
Thread-isolated (each SqlSession has its own); in distributed systems may cause stale reads
SqlSession session = sqlSessionFactory.openSession();
UserMapper mapper = session.getMapper(UserMapper.class);
User user1 = mapper.findById(1); // DB hit, cached
User user2 = mapper.findById(1); // Cache hit, no SQL
session.close(); // Cache goneSecond-Level Cache (Mapper namespace-scoped)
Disabled by default; enable in mybatis-config.xml: <setting name="cacheEnabled" value="true"/> and add <cache/> in Mapper.xml
Multiple SqlSessions share same Mapper's cache
On SqlSession close, first-level cache data flushes to second-level cache
Invalidated on: insert/update/delete (clears namespace cache); TTL expiry; size limit
Cached objects must implement Serializable
Multi-table joins prone to dirty data; default implementation not recommended for production
Comparison
Scope : First-level = SqlSession; Second-level = Mapper (namespace)
Default : First-level on; Second-level off
Can Disable : First-level no; Second-level yes
Sharing : First-level single SqlSession; Second-level multiple SqlSessions
Implementation : First-level HashMap; Second-level PerpetualCache (default)
Invalidation : First-level session close / write / manual; Second-level write / TTL / size limit
Dirty Read Risk : First-level low; Second-level high
Typical Use : First-level short transactions, avoid repeat queries; Second-level read-heavy, write-light, cross-session sharing
Interview Bonus Points
First-level cache implemented in BaseExecutor.query(): check cache first, then DB
Second-level cache uses decorator pattern: LoggingCache, SynchronizedCache, LruCache, etc.
Any write (insert/update/delete) immediately clears first-level cache — even before commit. Common misconception.
Read-only trap : readOnly="true" returns same object reference. One session mutates → other sessions see dirty data. Use readOnly="false" (returns serialized copy) unless objects are immutable.
Distributed evolution: replace second-level cache with Redis; Spring Cache ( @Cacheable) acts as business-level L2 cache.
Best practice: prefer L1; use Redis for L2; avoid caching frequently modified data; avoid L2 for multi-table joins.
Plugin Mechanism & PageHelper Pagination
Plugin Core: Dynamic Proxy + Chain of Responsibility
Plugins are Interceptors. JDK dynamic proxy wraps target objects, inserting custom logic before/after method calls.
Four Interceptable Objects
Executor : SQL execution & transaction — interceptable methods: update, query, commit, rollback
StatementHandler : JDBC Statement handling — interceptable methods: prepare, parameterize, batch, update, query
ParameterHandler : Parameter setting — interceptable methods: getParameterObject, setParameters
ResultSetHandler : Result mapping — interceptable methods: handleResultSets, handleOutputParameters
At creation, these four objects get wrapped by plugin proxies, forming a responsibility chain.
Interceptor Interface
public interface Interceptor {
Object intercept(Invocation invocation) throws Throwable;
default Object plugin(Object target) { return Plugin.wrap(target, this); }
default void setProperties(Properties properties) {}
}Plugin.wrapuses JDK dynamic proxy to nest proxies in configured order.
Custom Plugin Steps
Implement Interceptor (override intercept, plugin, setProperties)
Add @Intercepts + @Signature to specify target object/method
Register in MyBatis config
PageHelper Core: SQL Rewriting
Intercepts
Executor.query(MappedStatement, Object, RowBounds, ResultHandler).
Four Key Steps
Store : PageHelper.startPage(1,10) creates Page object, stores in ThreadLocal — thread-isolated, auto-cleaned.
Intercept : Plugin wraps Executor via dynamic proxy; on query, interceptor fires.
Rewrite :
Read params from ThreadLocal; if absent, pass through.
Generate count SQL: SELECT count(0) FROM (originalSQL) tmp_count — strips useless ORDER BY.
Dialect-specific rewrite: MySQL adds LIMIT ?,?; Oracle nests ROWNUM; SQL Server uses OFFSET/FETCH.
Execute rewritten query.
Wrap results + total into Page (extends ArrayList), return.
Clean : finally block removes ThreadLocal params to prevent leakage.
Dialect Examples
MySQL : LIMIT 0,10 — simplest
Oracle :
SELECT * FROM (SELECT t.*, ROWNUM rn FROM (orig) t WHERE ROWNUM <= 10) WHERE rn > 0— two-level nesting
SQL Server 2012+ : OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY — standard syntax
PostgreSQL : LIMIT 10 OFFSET 0 — LIMIT/OFFSET order differs
Common Pitfalls
Only the first query after startPage() gets paginated (params auto-cleared).
Complex queries (DISTINCT, GROUP BY, multi-table) → count SQL often wrong. Fix: custom countMethod in Mapper.
No nested pagination — ThreadLocal holds one param set.
Don't sort in Java after pagination; use SQL ORDER BY or PageHelper.orderBy().
ThreadLocal leak if exception before cleanup; v5.x uses try-finally.
Deep pagination ( LIMIT 100000,10) degrades; consider cursor pagination: WHERE id > lastId LIMIT 10.
MyBatis-Plus Core Features
Enhancement tool: "only enhance, not change". Simplifies CRUD.
1. Generic CRUD
BaseMapper<T>(DAO): 17 methods — insert, deleteById, updateById, selectById, selectList... IService<T> (Service): 20+ methods — save, removeById, updateById, getById, list, page...
Zero XML/SQL for single-table ops.
2. Wrapper Condition Builder
Lambda support — compile-time field validation
Chainable: and, or, in, like, between, orderBy, groupBy
Example:
Wrappers.<User>lambdaQuery().like(User::getName, "张").ge(User::getAge, 18).orderByDesc(User::getCreateTime)3. Pagination Plugin
Config: MybatisPlusInterceptor + PaginationInnerInterceptor(DbType.MYSQL). Usage:
Page<User> page = new Page<>(1,10); userMapper.selectPage(page, wrapper);— returns total + records.
4. ID Generation Strategies
ASSIGN_ID : Snowflake (default) — distributed, globally unique Long
AUTO : DB auto-increment — single DB/table
INPUT : User-provided — business keys (order no.)
ASSIGN_UUID : UUID — simple distributed
Annotation: @TableId(type = IdType.ASSIGN_ID).
5. Logical Delete
Config: logic-delete-field: deleted, logic-delete-value: 1, logic-not-delete-value: 0. Entity: @TableLogic private Integer deleted;. Delete executes UPDATE; selects auto-filter deleted rows.
6. Auto Fill
Fields: @TableField(fill = FieldFill.INSERT) for createTime, FieldFill.INSERT_UPDATE for updateTime. Implement MetaObjectHandler with insertFill / updateFill using strictInsertFill / strictUpdateFill.
7. Optimistic Lock
Plugin: OptimisticLockerInnerInterceptor. Entity: @Version private Integer version;. Update auto-adds WHERE id=? AND version=? and increments version.
8. Code Generator
FastAutoGenerator (3.5.1+): fluent API for datasource, package, strategy (tables, Lombok), output.
Other Features
Multi-data source via @DS Performance analysis (dev-mode SQL timing)
Block full-table update/delete
MP-Join for complex joins
Dynamic table name
MyBatis vs Hibernate/JPA — Selection Guide
Core Difference
MyBatis: Semi-ORM, SQL mapper. Developer writes & controls SQL; maps ResultSet to objects.
Hibernate/JPA: Full ORM. Hides SQL; object-oriented DB operations.
Key Comparison
Core Idea : MyBatis handwritten SQL → object mapping; Hibernate/JPA object-table mapping → auto SQL
ORM Level : MyBatis semi; Hibernate/JPA full
SQL Control : MyBatis full control; Hibernate/JPA auto-generated, customizing hard
Dynamic Query : MyBatis <if>, <foreach> intuitive; Hibernate/JPA Criteria API / QueryDSL steep learning
Association Mapping : MyBatis resultMap + association / collection explicit; Hibernate/JPA @OneToMany auto (N+1, LazyException risks)
Performance : MyBatis higher — extreme tuning possible; Hibernate/JPA moderate — overhead
Learning Curve : MyBatis gentle; Hibernate/JPA steep
DB Portability : MyBatis poor — SQL dialect-specific; Hibernate/JPA good — dialect auto-adapt
Caching : MyBatis basic L1/L2; Hibernate/JPA powerful multi-level
Complex Queries : MyBatis strong — flexible; Hibernate/JPA weak — JPQL/native SQL painful
Dev Efficiency : MyBatis slow for simple CRUD, fast for complex; Hibernate/JPA fast for simple CRUD, slow for complex
Code Volume : MyBatis more (Mapper + XML); Hibernate/JPA less (extend JpaRepository)
Debugging : MyBatis easy — SQL visible; Hibernate/JPA hard — generated SQL opaque
Decision Tree
Choose MyBatis when: high-performance internet apps; complex, volatile queries; team skilled in SQL; legacy MyBatis codebase.
Choose Hibernate/JPA when: enterprise admin systems, CRUD-heavy; need rapid delivery; possible DB migration; team knows JPA.
Best Practice: Hybrid — Spring Data JPA for simple CRUD, MyBatis for complex queries. Avoid forcing all queries into JPQL. Monitor SQL performance regardless.
Why SqlSession Is Thread-Unsafe
Root Cause
SqlSession holds multiple mutable states without any synchronization.
Three Fundamental Reasons
1. First-Level Cache (PerpetualCache → HashMap)
private final Map<Object, Object> cache = new HashMap<>(); // no syncConcurrent read/write → ConcurrentModificationException, cache corruption, dirty reads, lost updates.
Thread A puts {1: userA}; Thread B concurrently reads/writes same key → resize loops, size corruption, or half-cleared state after Thread A's write triggers cache clear.
2. Transaction Context (Executor → Transaction → Connection)
SqlSession holds Executor, which holds Transaction wrapping a JDBC Connection.
Shared session → one thread commits, another's unfinished work auto-commits; one closes connection, other gets "connection closed" exception; isolation levels interfere.
Analogy: two people turning same faucet — water everywhere.
3. Executor State (dirty, closed flags, batch queue)
private boolean dirty; // non-atomic, no visibility guaranteeThread A sets dirty=true; Thread B reads stale false → flush skipped → data inconsistency.
Instruction reordering + visibility issues.
Executor also holds L1 cache reference, transaction state, batch statement queue — all unsynchronized.
Design Intent
SqlSession = one DB session = one request. Lightweight, short-lived. Official doc: "Do not share across threads."
Correct Usage
Per-thread independent SqlSession: SqlSessionFactory.openSession() Spring: SqlSessionTemplate uses ThreadLocal to bind per-thread SqlSession
Never store SqlSession as static/instance field; keep as method-local
Interview Extras
L1 cache default impl = PerpetualCache (unsafe HashMap) SqlSessionTemplate core = ThreadLocal proxy
Distinguish L1 (SqlSession-scoped, unsafe) vs L2 (Mapper-scoped, thread-safe)
In distributed systems, L1 can cause cross-node inconsistency
Signed-in readers can open the original source through BestHub's protected redirect.
This article has been distilled and summarized from source material, then republished for learning and reference. If you believe it infringes your rights, please contactand we will review it promptly.
How this landed with the community
Was this worth your time?
0 Comments
Thoughtful readers leave field notes, pushback, and hard-won operational detail here.
