MyBatis Dynamic SQL: a typesafe SQL DSL for Java and Kotlin

A SQL generator, not a query builder

MyBatis Dynamic SQL is described as a general purpose SQL generator, and the README tells you how to think about it: think of it as a typesafe and expressive SQL DSL, with support for rendering SQL formatted properly for MyBatis3 and Spring's NamedParameterJDBCTemplate.

Two things follow from that description. First, it generates full DELETE, INSERT, SELECT and UPDATE statements, so it is not limited to the read path. Second, the library also contains extensions for Kotlin that enable an idiomatic Kotlin DSL for SQL, so the Java and Kotlin surfaces are deliberately different rather than the Kotlin version being a transliteration.

The DSL is described as very similar to native SQL, but with many functions that allow for very dynamic SQL statements. That similarity is deliberate and it is the point. A team that already writes SQL should be able to read the generated code and recognise it, and a reviewer should be able to check it against what they intended.

The typical example in the README is a search, and it is worth reading because it is the shape of most real queries:

   data class SearchParameters(val id: String?, val firstName: String?, val lastName: String?)

   fun search(searchParameters: SearchParameters) =
        select(id, firstName, lastName) {
            from(Customer)
            where {
                active isEqualTo true
                and { id isEqualToWhenPresent searchParameters.id }
                and {
                    firstName(isLikeCaseInsensitiveWhenPresent(searchParameters.firstName)
                        .map { "%" + it.trim() + "%" })
                }
                and {
                    lastName(isLikeCaseInsensitiveWhenPresent(searchParameters.lastName)
                        .map { "%" + it.trim() + "%" })
                }
            }
            orderBy(lastName, firstName)
            limit(500)
        }

The claim the documentation makes about this snippet is that it does quite a lot. It is a search with three search criteria where any combination can be used, only active records are returned, `id` is used as a filter if specified, `firstName` and `lastName` are used in case-insensitive searches with SQL wildcards appended, and the results are limited to 500 rows.

The stated benefit is the elimination of a lot of code that would be required for checking nulls and adding wildcards, and the point that the query clearly expresses the intent of the search in just a few lines.

WhenPresent is the feature the rest of the library supports

The single most useful idea in this library is the family of `WhenPresent` conditions, and everything else in the design exists to support it.

Look at what the example does. `isEqualToWhenPresent searchParameters.id` adds an equality filter only when the parameter is not null, and removes it from the generated SQL entirely when it is null. `isLikeCaseInsensitiveWhenPresent` does the same for a case-insensitive match, and takes a `map` on the value so the wildcard wrapping happens on the parameter and not in the SQL text.

This is the pattern that hand-written dynamic SQL gets wrong. The usual approaches are string concatenation with an `if` in the caller, which produces a different SQL string per combination and makes the query impossible to unit test, or a wrapper library with an always-true condition when a filter is absent, which changes the plan the database chooses. This library instead omits the condition.

The `map` on the value is a small thing with a large effect on safety. The value is transformed in code, where the compiler can help, and the SQL receives a parameter rather than a literal with wildcards pasted into it.

The Kotlin DSL is where this reads best, because the `and { }` blocks scope the conditions and the `isEqualToWhenPresent` infix form reads like a predicate rather than a method call. The Java side offers the same capability through utility classes, and the README's documentation table points at separate pages for Kotlin with MyBatis3 and Kotlin with Spring, so the Kotlin experience is documented per runtime rather than once.

Two runtimes, and the SQL is formatted for each

The library renders SQL for a specific runtime, and the two supported runtimes format the output differently.

For MyBatis3 there is specialised support, and the documentation notes that the examples on that page are similar to the code generated by MyBatis Generator. That is a useful anchor for people coming from the generator, since the DSL and the generated code express the same query in different styles.

For Spring there is specialised support for Spring JDBC Templates, with the `NamedParameterJDBCTemplate` named in the description. There is a separate page for Kotlin with Spring, one for Kotlin with MyBatis3, and one for Spring Batch support using the MyBatis Spring Integration.

The Spring Batch page is worth singling out, because batch processing is where the difference between rendering for a template and rendering for MyBatis shows up. A batch job writes a parameterised statement once and runs it over a chunk of data, so the rendered SQL has to be exactly what the template expects, with the right parameter placeholders and no accidental literal.

The test suite is organised by runtime and language, and the README tabulates it. There is a Java example using the utility classes for MyBatis in the style of MyBatis Generator, one using MyBatis3 with MyBatis-Spring integration, and one using MyBatis3 with MyBatis-Spring in the Spring Batch style, each pointing at a directory under `src/test/java/examples/`. The examples are the specification in executable form, which is a better arrangement than a separate sample project that drifts.

The build is Maven, with `pom.xml` at the root, the `mvnw` and `mvnw.cmd` wrappers, and a `checkstyle-override.xml` alongside a `LICENSE_HEADER` file for the header check. The `renovate.json` handles dependency updates, and the CI badge points at a Java CI workflow on the `master` branch.

Version 2.0.0 after a year and a half on 1.5.x

The release history has one feature that matters for anyone already on the older line.

The tagged releases are mybatis-dynamic-sql 1.5.1 on 2024-04-30, mybatis-dynamic-sql 1.5.2 on 2024-06-03, and mybatis-dynamic-sql 2.0.0 on 2026-03-12. So the 1.5.x line shipped two releases a month apart and then went quiet for twenty-one months before a major version arrived.

The licence is Apache-2.0, with `LICENSE`, `LICENSE_HEADER` and `NOTICE` files at the repository root, and the `LICENSE_HEADER` is there for the header check that the build enforces on source files.

The repository layout is conventional for a Maven library. `src/` holds the code, the site documentation and the tests, and the `src/site/markdown/docs/` directory holds the documentation pages that the README links to, including `quickStart.md`, `mybatis3.md`, `kotlinMyBatis3.md`, `spring.md`, `kotlinSpring.md` and `springBatch.md`. Keeping the docs as Markdown in the repository means the same page can be published to the site and read on GitHub.

There is also a `RELEASING.md` at the root, which is the release process, and a `CHANGELOG.md`. The quality tooling is visible in the badges rather than in prose: a Coveralls coverage badge, a SonarCloud quality gate status badge, and a SonarCloud security rating badge. A quality gate that fails the build is a stronger signal than a coverage percentage, and having both a coverage report and a security rating suggests the project treats the static analysis side as part of the definition of done.

The last push was on 2026-10-17, and the repository is not archived, so the code is being worked on.

What it does not do, and when the wrong choice

Three limits are worth being explicit about.

The first is that this is a SQL generator, not a mapper. It produces SQL text and parameter bindings for you to execute. It does not run the query, it does not turn a result set into objects, and it does not manage a session or a transaction. If what you want is an ORM, this is the wrong layer, and MyBatis itself is a different tool with a different purpose. The MyBatis3 support in this library is about rendering SQL in the form MyBatis expects, not about replacing its mapping layer.

The second is the dialect question. The DSL has a notion of tables and columns with types, and those types have to match the schema you actually have. The documentation is organised around the supported runtimes rather than around a list of databases, so the questions of dialect portability, reserved words and type mapping are not answered in what the README shows. If your project runs the same query against several databases, that is a gap to check before committing to it.

The third is that the benefit is concentrated. For a query with no conditional filters, a plain string constant in your mapper is shorter and easier to read than the DSL equivalent. The library earns its keep in the search-with-optional-filters case, and the README's own example is exactly that case.

The alternative worth naming is not another DSL. It is writing the dynamic SQL yourself in the layer you already use, with a small helper for the null checks, and accepting the stringly-typed tests that come with it. The trade is that you keep total control over the SQL text and add no dependency, at the cost of the compiler checking your column and condition names.

Editorial conclusion

MyBatis Dynamic SQL fits a Java or Kotlin team that writes queries by hand and keeps getting null checks and wildcard handling wrong, and that wants a compiler-checked DSL without giving up control of the SQL. It does not fit a team that wants a runtime query builder with an object mapper, since the library generates SQL text rather than mapping rows, and it does not fit a project that wants to write all its SQL in XML or annotations with no code generation at all. Verify first that the runtime you already use is one of the supported ones, MyBatis3 or Spring JDBC Templates, since the renderers differ and there is a separate Spring Batch integration, that the dialect-specific column definitions in your schema match the ones the DSL expects, and that your ORM's own query facilities are not already solving the same problem, because the DSL's benefit is concentrated entirely in the conditional-filtering case.

Frequently asked questions

Official sources

1. License: Apache-2.0
2. mybatis/mybatis-dynamic-sql on GitHub
3. Project website
4. README
5. Releases