MyBatis Dynamic SQL

This library is a framework for generating dynamic SQL statements. Think of it as a typesafe SQL templating library, with additional support for MyBatis3 and Spring JDBC Templates.

The library will generate full DELETE, INSERT, SELECT, and UPDATE statements formatted for use by MyBatis or Spring. The most common use case is to generate statements, and a matching set of parameters, that can be directly used by MyBatis. The library will also generate statements and parameter objects that are compatible with Spring JDBC templates.

The library works by implementing an SQL-like DSL that creates an object containing a full SQL statement and any parameters required for that statement. The SQL statement object can be used directly by MyBatis as a parameter to a mapper method.

The library will generate these types of SQL statements:

  • DELETE statements with flexible WHERE clauses
  • INSERT statements of several types:
    • A statement that inserts a single record and will insert null values into columns (a “full” insert)
    • A statement that inserts a single record that will ignore null input values and their associated columns (a “selective” insert)
    • A statement that inserts into a table using the results of a SELECT statement
    • A parameter object is designed for inserting multiple objects with a JDBC batch
  • SELECT statements with a flexible column list, a flexible WHERE clause, and support for distinct, “group by”, joins, unions, “order by”, etc.
  • UPDATE statements with a flexible WHERE clause. Like the INSERT statement, there are two varieties of UPDATE statements:
    • A “full” update that will set null values
    • A “selective” update that will ignore null input values

The primary goals of the library are:

  1. Typesafe - to the extent possible, the library will ensure that parameter types match the database column types
  2. Expressive - statements are built in a way that clearly communicates their meaning (thanks to Hamcrest for some inspiration)
  3. Flexible - where clauses can be built using any combination of and, or, and nested conditions
  4. Extensible - the library will render statements for MyBatis3, Spring JDBC templates or plain JDBC. It can be extended to generate clauses for other frameworks as well. Custom where conditions can be added easily if none of the built in conditions are sufficient for your needs.
  5. Small - the library is a small dependency to add. It has no transitive dependencies.

This library grew out of a desire to create a utility that could be used to improve the code generated by MyBatis Generator, but the library can be used on it’s own with very little setup required.