MyBatis Dynamic SQL

What Is This?

This library is a framework for creating 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, or suitable statement fragments. 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 will generate these types of statements:

DELETE statements with a flexible WHERE clauses
INSERT statements of two types: one that will insert null values into columns (a "full" insert), or one that will ignore null input values and their associated columns (a "selective" insert)
SELECT statements with a flexible column list, a flexible WHERE clause, and support for "distinct" and "order by"
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 and a "selective" update that will ignore null input values

The primary goals of the library are:

Typesafe - to the extent possible, the library will ensure that parameter types match the database column types
Expressive - statements are built in a way that clearly communicates their meaning (thanks to Hamcrest for some inspiration)
Flexible - where clauses can be built using any combination of and, or, and nested conditions
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.
Small - the library is a very 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);
            
            SelectSupport selectSupport = 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(selectSupport);
            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 where clause for a 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 columns

The class org.mybatis.dynamic.sql.SqlColumn is used to define columns for use in the library. Typically these should be defined as public static variables in a class or interface. A column definition includes:

The Java type
The table's actual column name
The JDBC type
(optional) An alias for the column
(optional) The name of a type handler to use in MyBatis if the default type handler is not desired

For example:

package examples.simple;

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

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

public interface SimpleTableDynamicSqlSupport {
    SqlTable simpleTable = SqlTable.of("SimpleTable");
    SqlColumn<Integer> id = simpleTable.column("id", JDBCType.INTEGER).withAlias("A_ID");
    SqlColumn<String> firstName = simpleTable.column("first_name", JDBCType.VARCHAR);
    SqlColumn<String> lastName = simpleTable.column("last_name", JDBCType.VARCHAR);
    SqlColumn<Date> birthDate = simpleTable.column("birth_date", JDBCType.DATE);
    SqlColumn<Boolean> employed = simpleTable.column("employed", JDBCType.VARCHAR).withTypeHandler("examples.simple.YesNoTypeHandler");
    SqlColumn<String> occupation = simpleTable.column("occupation", JDBCType.VARCHAR);
}

Second - Write XML or annotated mappers that will use the generated where clause

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

For example, an annotated mapper might look like this:

package examples.simple;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.mybatis.dynamic.sql.delete.render.DeleteSupport;
import org.mybatis.dynamic.sql.select.render.SelectSupport;

public class SimpleTableAnnotatedMapper {
    
    @Select({
        "${fullSelectStatement}"
    })
    @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(SelectSupport selectSupport);

    @Delete({
        "${fullDeleteStatement}"
    })
    int delete(DeleteSupport deleteSupport);
}

An XML mapper might look like this:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="examples.simple.SimpleTableXmlMapper">

  <resultMap id="SimpleTableResult" type="examples.simple.SimpleTableRecord">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="first_name" jdbcType="VARCHAR" property="firstName" />
    <result column="last_name" jdbcType="VARCHAR" property="lastName" />
    <result column="birth_date" jdbcType="DATE" property="birthDate" />
    <result column="employed" jdbcType="VARCHAR" property="employed" typeHandler="examples.simple.YesNoTypeHandler" />
    <result column="occupation" jdbcType="VARCHAR" property="occupation" />
  </resultMap>

  <select id="selectMany" resultMap="SimpleTableResult">
    ${fullSelectStatement}
  </select>

  <delete id="delete">
    ${fullDeleteStatement}
  </delete>
</mapper>

Third - Create where clauses for your queries

Where clauses are created by combining your column definition (from the first step above) with a condition for the column. This library includes a large number of type safe conditions. All conditions can be accessed through expressive static methods in the org.mybatis.dynamic.sql.SqlConditions interface.

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

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

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

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

The "between" condition is also expressive:

        SelectSupport selectSupport = 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:

        SelectSupport selectSupport = 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 builders and conditions
import static org.mybatis.dynamic.sql.SqlBuilder.*;
import static org.mybatis.dynamic.sql.SqlConditions.*;

Fourth - Use your where clauses

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

    @Test
    public void testSelectByExample() {
        SqlSession session = sqlSessionFactory.openSession();
        try {
            SimpleTableXmlMapper mapper = session.getMapper(SimpleTableXmlMapper.class);
            
            SelectSupport selectSupport = selectByExample()
                    .where(id, isEqualTo(1))
                    .or(occupation, isNull())
                    .build()
                    .render(RenderingStrategy.MYBATIS3);
            
            List<SimpleTableRecord> rows = mapper.selectMany(selectSupport);
            
            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.

mrfsong363 / mybatis-dynamic-sql Goto Github PK