Configuring MyBatis Dynamic SQL with XML and Annotation: Setup, Code Samples, and Pros & Cons

First, add the MyBatis dependency that uses XML mapper files, then modify the configuration files (.properties and .yml) to specify the mapper locations and type‑aliases package.

In the mybatis.mapper-locations=classpath:mapper/*.xml setting, MyBatis will search the mapper directory on the classpath for XML files that define SQL statements and result mappings.

The mybatis.type-aliases-package=com.example.big_event.pojo property tells MyBatis which package to scan for Java classes that can be referenced in XML using short type aliases, such as using User instead of the fully‑qualified com.example.big_event.pojo.User .

When writing the XML mapper, ensure the namespace matches the mapper interface class and the id attribute matches the method name that contains the dynamic SQL. The resultType should point to the entity class.

Note: The XML file name must be identical to the mapper interface name.

Using Annotation‑Based Dynamic SQL

Advantages:

Concise: SQL is written directly on the method, keeping code in one place.

Easy debugging: The SQL is visible during debugging.

Consistency: Business logic and SQL stay together, improving readability.

Disadvantages:

Complex SQL becomes hard to maintain within annotations.

Method bodies can become bloated with long or intricate queries.

Annotations have limitations and may not support all dynamic SQL features.

Using XML Files for Dynamic SQL

Advantages:

High flexibility: XML supports tags like <if> , <choose> , <foreach> for complex query building.

Clear separation: SQL is isolated from Java code, making both easier to read.

Easy modification: Changing SQL does not require recompiling Java code.

Reusability: Common SQL fragments can be extracted and reused across mappers.

Disadvantages:

Debugging is more involved because SQL is not directly in the code.

Learning curve: Developers must learn MyBatis XML syntax.

Configuration management can become complex with many XML files.

Conclusion

The choice between annotation‑based and XML‑based dynamic SQL depends on project size and team preferences: small projects with simple queries may benefit from annotations, while larger projects with complex queries are better served by XML files.