MyBatis Dynamic SQL

What Is This?

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.

Requirements

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

Show Me an Example

One capability is that very expressive dynamic queries can be generated. Here's an example of what's possible:

    @Test
    public void testComplexCondition() {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        try {
            AnimalDataMapper mapper = sqlSession.getMapper(AnimalDataMapper.class);
            
            SelectStatementProvider selectStatement = select(id, animalName, bodyWeight, brainWeight)
                    .from(animalData)
                    .where(id, isIn(1, 5, 7))
                    .or(id, isIn(2, 6, 8), and(animalName, isLike("%bat")))
                    .or(id, isGreaterThan(60))
                    .and(bodyWeight, isBetween(1.0).and(3.0))
                    .orderBy(id.descending(), bodyWeight)
                    .build()
                    .render(RenderingStrategy.MYBATIS3);

            List<AnimalData> animals = mapper.selectMany(selectStatement);
            assertThat(animals.size()).isEqualTo(4);
        } finally {
            sqlSession.close();
        }
    }

How Do I Use It?

The following discussion will walk through an example of using the library to generate a dynamic SELECT or DELETE statement. The full source code for this example is in src/test/java/examples/simple in this repo.

The database table used in the example is defined as follows:

create table SimpleTable (
   id int not null,
   first_name varchar(30) not null,
   last_name varchar(30) not null,
   birth_date date not null, 
   employed varchar(3) not null,
   occupation varchar(30) null,
   primary key(id)
);

First - Define database table and columns

The class org.mybatis.dynamic.sql.SqlTable is used to define a table. A table definition includes the actual name of the table (including schema or catalog if appropriate). A table alias can be applied in a select statement if desired. Your table should be defined by extending the SqlTable class.

The class org.mybatis.dynamic.sql.SqlColumn is used to define columns for use in the library. SqlColumns should be created using the builder methods in SqlTable. A column definition includes:

1. The Java type
2. The actual column name (an alias can be applied in a select statement)
3. The JDBC type
4. (optional) The name of a type handler to use in MyBatis if the default type handler is not desired

We suggest the following usage pattern to give maximum flexibility. This pattern will allow you to use your table and columns in a "qualified" or "un-qualified" manner that looks like natural SQL. For example, in the following a column could be referred to as firstName or simpleTable.firstName.

package examples.simple;

import java.sql.JDBCType;
import java.util.Date;

import org.mybatis.dynamic.sql.SqlColumn;
import org.mybatis.dynamic.sql.SqlTable;

public final class SimpleTableDynamicSqlSupport {
    public static final SimpleTable simpleTable = new SimpleTable();
    public static final SqlColumn<Integer> id = simpleTable.id;
    public static final SqlColumn<String> firstName = simpleTable.firstName;
    public static final SqlColumn<String> lastName = simpleTable.lastName;
    public static final SqlColumn<Date> birthDate = simpleTable.birthDate;
    public static final SqlColumn<Boolean> employed = simpleTable.employed;
    public static final SqlColumn<String> occupation = simpleTable.occupation;
    
    public static final class SimpleTable extends SqlTable {
        public final SqlColumn<Integer> id = column("id", JDBCType.INTEGER);
        public final SqlColumn<String> firstName = column("first_name", JDBCType.VARCHAR);
        public final SqlColumn<String> lastName = column("last_name", JDBCType.VARCHAR);
        public final SqlColumn<Date> birthDate = column("birth_date", JDBCType.DATE);
        public final SqlColumn<Boolean> employed = column("employed", JDBCType.VARCHAR, "examples.simple.YesNoTypeHandler");
        public final SqlColumn<String> occupation = column("occupation", JDBCType.VARCHAR);

        public SimpleTable() {
            super("SimpleTable");
        }
    }
}

Second - Write MyBatis mappers that will use the generated statement

The library will create classes that will be used as input to a MyBatis mapper. These classes include the generated SQL, as well as a parameter set that will match the generated SQL. Both are required by MyBatis. It is intended that these objects be the one and only parameter to a MyBatis mapper method.

The library can be used with both XML and annotated mappers, but we recommend using MyBatis' annotated mapper support in all cases. The only case where XML is required is when you code a JOIN statement - in that case you will need to define your result map in XML due to limitations of the MyBatis annotations in supporting joins.

For example, a mapper might look like this:

package examples.simple;

import org.apache.ibatis.annotations.DeleteProvider;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.SelectProvider;
import org.mybatis.dynamic.sql.delete.render.DeleteStatementProvider;
import org.mybatis.dynamic.sql.select.render.SelectStatementProvider;
import org.mybatis.dynamic.sql.util.SqlProviderAdapter;

public class SimpleTableAnnotatedMapper {
    
    @SelectProvider(type=SqlProviderAdapter.class, method="select")
    @Results(id="SimpleTableResult", value= {
            @Result(column="A_ID", property="id", jdbcType=JdbcType.INTEGER, id=true),
            @Result(column="first_name", property="firstName", jdbcType=JdbcType.VARCHAR),
            @Result(column="last_name", property="lastName", jdbcType=JdbcType.VARCHAR),
            @Result(column="birth_date", property="birthDate", jdbcType=JdbcType.DATE),
            @Result(column="employed", property="employed", jdbcType=JdbcType.VARCHAR, typeHandler=YesNoTypeHandler.class),
            @Result(column="occupation", property="occupation", jdbcType=JdbcType.VARCHAR)
    })
    List<SimpleTableRecord> selectMany(SelectStatementProvider selectStatement);

    @DeleteProvider(type=SqlProviderAdapter.class, method="delete")
    int delete(DeleteStatementProvider deleteStatement);
}

Third - Create dynamic statements

Select statements are created by combining your column and table definitions (from the first step above) with condition for the column. This library includes a large number of type safe conditions. All SQL construction methods can be accessed through expressive static methods in the org.mybatis.dynamic.sql.SqlBuilder interface.

For example, a very simple select statement can be defined like this:

        SelectStatementProvider selectStatement = select(count())
                .from(simpleTable)
                .where(id, isEqualTo(3))
                .build()
                .render(RenderingStrategy.MYBATIS3);

Or this (also note that you can give a table an alias):

        SelectStatementProvider selectStatement = select(count())
                .from(simpleTable, "a")
                .where(id, isNull())
                .build()
                .render(RenderingStrategy.MYBATIS3);

A delete statement looks like this:

        DeleteStatementProvider deleteStatement = deleteFrom(simpleTable)
                .where(occupation, isNull())
                .build()
                .render(RenderingStrategy.MYBATIS3);

The "between" condition is also expressive:

        SelectStatementProvider selectStatement = select(count())
                .from(simpleTable)
                .where(id, isBetween(1).and(4))
                .build()
                .render(RenderingStrategy.MYBATIS3);

More complex expressions can be built using the "and" and "or" conditions as follows:

        SelectStatementProvider selectStatement = select(count())
                .from(simpleTable)
                .where(id, isGreaterThan(2))
                .or(occupation, isNull(), and(id, isLessThan(6)))
                .build()
                .render(RenderingStrategy.MYBATIS3);

All of these statements rely on a set of expressive static methods. It is typical to import the following:

// import all column definitions for your table
import static examples.simple.SimpleTableDynamicSqlSupport.*;

// import the SQL builder
import static org.mybatis.dynamic.sql.SqlBuilder.*;

Fourth - Use your statements

In a DAO or service class, you can use the generated statement as input to your mapper methods. Here's an example from examples.simple.SimpleTableAnnotatedMapperTest:

    @Test
    public void testSelectByExample() {
        SqlSession session = sqlSessionFactory.openSession();
        try {
            SimpleTableXmlMapper mapper = session.getMapper(SimpleTableXmlMapper.class);
            
            SelectStatementProvider selectStatement = select(id.as("A_ID"), firstName, lastName, birthDate, employed, occupation)
                    .from(simpleTable)
                    .where(id, isEqualTo(1))
                    .or(occupation, isNull())
                    .build()
                    .render(RenderingStrategy.MYBATIS3);
            
            List<SimpleTableRecord> rows = mapper.selectMany(selectStatement);
            
            assertThat(rows.size()).isEqualTo(3);
        } finally {
            session.close();
        }
    }

The code in the folder src/test/java/examples/simple shows how to use the library for INSERT and UPDATE statements in addition to the examples shown here. It shows a suggested usage of the library to enable a complete range of CRUD operations on a database table. Lastly, it is an example of the code that could be created by a future version of MyBatis Generator.