Understanding MyBatis Dynamic SQL, OOM Incidents, and the Importance of Backend Parameter Validation

1 What is MyBatis Dynamic SQL

If you have used JDBC or similar frameworks, you know how painful it is to concatenate SQL strings conditionally, remembering spaces and commas.

MyBatis leverages powerful OGNL expressions to generate SQL dynamically based on parameter conditions.

The most common use case is to include part of a WHERE clause only when certain conditions are present.

This example returns a list of blog posts; if no "title" is provided, all ACTIVE blogs are returned.

If a "title" is supplied, a fuzzy search on the title column is performed.

Adding another parameter such as "author" is straightforward, as shown in the next diagram:

The where tag inserts a WHERE clause only when its child elements produce content, and it automatically removes leading AND/OR.

MyBatis also supports other dynamic tags such as choose (when, otherwise), trim (where, set), foreach, etc.

2 My First Online OOM Incident

I once worked for an e‑commerce company's user center, which provided registration, query, and update APIs.

At that time, RPC frameworks like Dubbo were not open‑source, so services were exposed via HTTP with XML payloads.

To avoid writing many similar interfaces, I created a generic getUserByConditions endpoint that could query users by username, nickname, phone number, or user ID.

The underlying mapping used iBatis (the predecessor of MyBatis). The SQLMap is shown below:

When building dynamic SQL, conditions are usually appended after the WHERE clause. Starting the clause with WHERE 1 = 1 makes it easy to prepend additional AND conditions.

After deployment, the user center suffered OOM every few hours because a full‑table scan on the user table (over 10 million rows) was triggered.

Log analysis revealed that the frontend sometimes sent empty strings for parameters, and the backend performed no validation, causing the query to match all rows.

The simple fix was to add proper parameter validation on the server side.

3 Frontend & Backend Parameter Validation

To improve robustness, both front‑end and back‑end should validate request parameters; the back‑end validation is mandatory.

1. Frontend validation improves user experience by catching obvious errors (e.g., malformed email) before a network request.

However, it cannot replace backend validation because clients can bypass it and send malicious data.

2. Backend validation ensures data integrity and prevents system‑wide failures.

In a Spring Boot project, one can use hibernate-validator annotations on DTOs and the @Validated annotation on controller methods.

For example, a UserDTO might require userName length 2‑10 and account / password length 6‑20, enforced via constraint annotations (see image).

Method‑level validation is added similarly (see image).

4 Balancing Reuse and Specialization

The original getUserByConditions handled four different query parameters to save development time.

With more experience, I now prefer splitting generic interfaces into dedicated ones for clearer contracts and easier maintenance.

For example, the generic interface can be replaced by four specific endpoints:

Query by user ID

Query by nickname

Query by phone number

Query by username

Each specialized query has a simpler SQLMap, as illustrated below:

Fine‑grained interfaces improve maintainability and reduce the risk of ambiguous WHERE 1=1 constructs.

5 Defensive Programming Awareness

Early in my career I focused only on delivering features without considering resource consumption or potential side effects.

Over time I adopted a defensive mindset: estimating resource usage, identifying risks, and writing code that guards against them.

How much system resources does this code consume?

How can we mitigate risks through preventive coding?

This mindset is akin to “situational awareness” in games, where you interpret map information to avoid danger.

6 Conclusion

The first OOM incident was caused by missing backend validation when using MyBatis dynamic SQL.

Going forward, I emphasize three practices: proper backend validation, balancing reuse with specialized interfaces, and cultivating defensive programming awareness.

Such habits help prevent recurring production incidents.

https://mybatis.org/mybatis-3/zh_CN/dynamic-sql.html https://blog.csdn.net/CSDN2497242041/article/details/122272752