Avoid OOM Disasters: Master MyBatis Dynamic SQL and Robust 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 based on conditions, remembering spaces and trimming trailing commas. MyBatis leverages powerful OGNL expressions to generate SQL dynamically according to parameter conditions.

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

This example returns all active blog posts when no title parameter is supplied; if title is provided, a fuzzy search on the title column is performed.

Adding a second parameter such as author is straightforward—just add another condition, 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 OR.

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

2 My First Online OOM Incident

I once worked on a user‑center service for an e‑commerce platform that handled registration, queries, and updates. At that time, RPC frameworks like Dubbo were not open‑source, so all services were exposed via HTTP with XML payloads.

To avoid writing many similar endpoints, I created a generic getUserByConditions API that could query users by username, nickname, phone number, or user ID, using iBatis (the predecessor of MyBatis).

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

After deployment, the service suffered OOM every few hours. Log analysis revealed frequent full‑table scans on the user table, which contained over ten million rows.

The root cause was that the front‑end sometimes sent empty strings for parameters, and the back‑end performed no validation, leading to a query like WHERE title = '' which turned into a full‑table scan and triggered OOM.

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

3 Front‑End and Back‑End Parameter Validation

Dividing a system into front‑end and back‑end teams often leads to finger‑pointing when bugs appear. Robust systems require both sides to validate inputs; back‑end validation is mandatory.

Front‑end validation improves user experience by catching obvious errors (e.g., malformed email) before a request reaches the server.

However, front‑end checks cannot replace back‑end validation because clients can bypass them and send malicious or malformed data.

Back‑end validation ensures data integrity and prevents runtime failures. In Spring Boot, the hibernate-validator library can be used.

For POST or PUT requests, parameters are usually bound to a DTO. Adding @Validated to the DTO triggers automatic validation, e.g., enforcing a username length of 2‑10 characters and a password length of 6‑20 characters.

Method‑level validation annotations can also be applied.

Even with DTO validation, reusing a generic DAO can still cause issues if callers pass incorrect parameters.

Using a MyBatis interceptor could solve the problem, but it adds complexity. Instead, I advocate balancing reuse and specialization.

4 Balancing Reuse and Specialization

The original getUserByConditions API handled four different query scenarios to save development time. As I gained experience, I shifted toward creating dedicated endpoints for clear business requirements.

For example, the generic API can be split into four specific ones:

Query by user ID

Query by nickname

Query by phone number

Query by username

The corresponding SQLMap for “query by user ID” becomes much simpler.

Fine‑grained interfaces are easier to maintain and avoid the uncertainty introduced by generic WHERE 1 = 1 patterns, even when validation is in place.

To avoid manual boilerplate, I plan to build a code generator that produces fine‑grained SQLMaps and mappers automatically.

5 Defensive Programming Mindset

Early in my career I wrote code mechanically without considering resource consumption or potential side effects. Over time, I cultivated a habit of asking two questions for every piece of code:

How much system resources will this code consume?

How can I mitigate risks and practice preventive programming?

This mindset is similar to “situational awareness” in games: observing the mini‑map, predicting opponent moves, and choosing safe actions.

In software, defensive programming means anticipating resource usage, preventing invalid inputs, and designing code that gracefully handles unexpected conditions.

6 Conclusion

My first online OOM incident was caused by missing back‑end validation when using MyBatis dynamic SQL. To prevent similar production failures, I now emphasize three practices: mandatory back‑end validation, balancing reusable and specialized interfaces, and fostering a defensive programming mindset.