/mybatis-dynamic-sql

SQL DSL (Domain Specific Language) for Kotlin and Java. Supports rendering for MyBatis or Spring JDBC Templates

Primary LanguageJavaApache License 2.0Apache-2.0

MyBatis Dynamic SQL

Build Status Coverage Status Maven central Sonatype Nexus (Snapshots) License Quality Gate Status Security Rating

What Is This?

This library is a general purpose SQL generator. Think of it as a typesafe and expressive SQL DSL (domain specific language), with support for rendering SQL formatted properly for MyBatis3 and Spring's NamedParameterJDBCTemplate.

The library also contains extensions for Kotlin that enable an idiomatic Kotlin DSL for SQL.

The library will generate full DELETE, INSERT, SELECT, and UPDATE statements. The DSL implemented by the library is very similar to native SQL but it includes many functions that allow for very dynamic SQL statements. For example, a typical search can be coded with a query like this (the following code is Kotlin, but Java code is very similar):

   fun search(id: String?, firstName: String?, lastName: String?) =
        select(Customer.id, Customer.firstName, Customer.lastName) {
            from(Customer)
            where { Customer.active.isTrue() }
            and { Customer.id (isEqualToWhenPresent(id).map{ it?.padStart(5, '0') }) }
            and { Customer.firstName (isLikeCaseInsensitiveWhenPresent(firstName)
                .map{ "%" + it.trim() + "%" }) }
            and { Customer.lastName (isLikeCaseInsensitiveWhenPresent(lastName)
                .map{ "%" + it.trim() + "%" }) }
            orderBy(Customer.lastName, Customer.firstName)
            limit(500)
        }

This query does quite a lot...

  1. It is a search with three search criteria - any combination of search criteria can be used
  2. Only records with an active status will be returned
  3. If id is specified, it will be padded to length 5 with '0' at the beginning of the string
  4. If firstName is specified, it will be used in a case-insensitive search and SQL wildcards will be appended
  5. If lastName is specified, it will be used in a case-insensitive search and SQL wildcards will be appended
  6. The query results are limited to 500 rows

Using the dynamic SQL features of the library eliminates a lot of code that would be required for checking nulls, adding wild cards, etc. This query clearly expresses the intent of the search in just a few lines.

See the following pages for detailed information:

Page Comments
Quick Start Shows a complete example of building code for this library
MyBatis3 Support Information about specialized support for MyBatis3. The examples on this page are similar to the code generated by MyBatis Generator
Kotlin Support with MyBatis3 Information about the Kotlin extensions and Kotlin DSL when using MyBatis3 as the runtime
Spring Support Information about specialized support for Spring JDBC Templates
Kotlin Support with Spring Information about the Kotlin extensions and Kotlin DSL when using Spring JDBC Template as the runtime
Spring Batch Support Information about specialized support for Spring Batch using the MyBatis Spring Integration

The library test cases provide several complete examples of using the library in various different styles:

Language Runtime Comments Code Directory
Java MyBatis3 Example using Java utility classes for MyBatis in the style of MyBatis Generator ../examples/simple
Java MyBatis3 + MyBatis-Spring Example using MyBatis-Spring integration ../examples/column/comparison
Java MyBatis3 + MyBatis-Spring (Spring Batch) Example using Java utility classes for the MyBatis integration with Spring Batch ../examples/springbatch
Java Spring JDBC Example using Java utility classes for Spring JDBC Template ../examples/spring
Kotlin MyBatis3 Example using Kotlin utility classes for MyBatis in the style of MyBatis Generator ../examples/kotlin/mybatis3/canonical
Kotlin MyBatis3 + MyBatis-Spring Example using MyBatis-Spring integration in Kotlin ../examples/kotlin/mybatis3/column/comparison
Kotlin Spring JDBC Example using Kotlin utility classes for Spring JDBC Template ../examples/kotlin/spring/canonical

Requirements

The library has no dependencies. Java 8 or higher is required.