Master MyBatis: Common Return Values, Exceptions, and SQL Optimization Tips

MyBatis Summary

MyBatis is a powerful persistence framework that supports custom SQL, stored procedures, and advanced mappings, eliminating most JDBC boilerplate. Below are some practical tips for everyday use.

1. Return values when no records match

String : returns null

Object : returns null

List or other collection : returns an empty collection []

Boolean : returns false

When an INSERT condition is not satisfied (e.g., using a DUAL statement), it returns false

2. Common exceptions thrown by MyBatis

Not all exceptions need to be treated as fatal errors; some can be handled gracefully (e.g., duplicate favorite actions).

(1) Insert statements

DataIntegrityViolationException – violation of NOT NULL or size constraints

DuplicateKeyException – violation of unique key constraint

CannotAcquireLockException – timeout on FOR UPDATE NOWAIT

(2) Update statements

If conditions are not met, the method returns false

(3) Bug‑related scenarios (e.g., missing table columns)

MyBatisSystemException

BadSqlGrammarException

3. Transaction and FOR UPDATE

Using FOR UPDATE acquires a row‑level exclusive lock. After the lock is obtained, subsequent SELECT statements see the latest data. If the query includes a primary key, the lock is on that row; otherwise, the entire table may be locked. It is advisable to add NOWAIT or a timeout such as FOR UPDATE WAIT 3.

SQL Optimization

Constraint conditions : Add table constraints to prevent dirty data.

LIMIT 1 : When only one row is needed, use LIMIT 1 to avoid full table scans.

Naming style : Use uppercase for SQL keywords and lowercase for identifiers to improve readability.

LIKE patterns : Avoid leading % (e.g., LIKE '%term') because it disables index usage; prefer LIKE 'term%' when the column is indexed.

Indexing searchable fields dramatically increases query efficiency.

Self‑join vs. subquery : Self‑join often outperforms subqueries.

Views : Serve as named queries; they are pre‑compiled, cannot accept parameters, and do not store data, but they isolate table operations.

Temporary tables : Follow the “small table drives large table” principle; use EXISTS for large tables and IN for small tables.

select * from A where cc in (select cc from B); // inefficient, uses index on A.cc</code>
    <code>select * from A where exists (select cc from B where cc = A.cc); // efficient, uses index on B.cc

When not to use indexes :

Very small data sets.

Highly uniform data distribution (e.g., gender split 50/50).

Unique index with multiple columns : Primarily ensures data integrity; querying by a single column does not gain performance benefits.

Composite index order : The order of columns matters; an index (x, y, z) matches queries that filter on x, y, and z in that sequence, but not queries that filter only on y.

Join tables :

Limit joins to three tables to avoid exponential nesting.

Ensure join columns are indexed and have identical data types across tables.

Index invalidation cases :

OR conditions where only one side is indexed.

Columns used in indexes should be defined NOT NULL.

Data type recommendation : Prefer int2 over bool for boolean‑like fields to improve extensibility.