The status quo

One of the great features of MyBatis is its dynamic SQL. If you have experience with JDBC or other similar frameworks, you know the pain of concatenating SQL statements based on different conditions. For example, when concatenating, make sure you don’t forget to add necessary Spaces, and take care to remove the comma from the last column name of the list. You can take advantage of dynamic SQL to get rid of this pain.

When it comes to Mybatis dynamic Sql, most people immediately think of the picture is like this

<select id="findActiveBlogLike"
     resultType="Blog">SELECT * FROM BLOG WHERE state = 'ACTIVE'<if test="title ! = null">
    AND title like #{title}
  </if>
  <if test="author ! = null and author.name ! = null">
    AND author_name like #{author.name}
  </if>
</select>
Copy the code

Or something like this

<select id="findActiveBlogLike"
     resultType="Blog">SELECT * FROM BLOG WHERE state = 'ACTIVE'<choose>
    <when test="title ! = null">
      AND title like #{title}
    </when>
    <when test="author ! = null and author.name ! = null">
      AND author_name like #{author.name}
    </when>
    <otherwise>
      AND featured = 1
    </otherwise>
  </choose>
</select>
Copy the code

Conditional concatenation of SQL statements is realized by dynamic SQL elements in Mapper XML.

I have to say that the implementation of Mybatis has been very powerful, greatly improving the efficiency of our SPLicing SQL. But for programmers, making conditional judgments in code is far more comfortable, flexible, and confident than in XML, and we want to enjoy the powerful MyBatis mapping, Let Dynamic SQL write more concise, more controllable — so the real Mybatis Dynamic SQL — Mybatis Dynamic SQL emerged at the historic moment.

MyBatis Dynamic SQL

The project address

Github.com/mybatis/myb…

The official documentation

Mybatis.org/mybatis-dyn…

Project introduction

Initial Release – another awarding 17, 2017 the Last Published: 23 November 2019 | Version: 1.1.4

On December 17, 2017, the first version of this project was released. The latest version is 1.1.4 released on 2019-11-23, which is one of the official projects of Mybatis.

The author

Jeff Butler [https://github.com/jeffgbutler], is Mybatis organization members, is the main contributor of Mybatis, at the same time is also our common tools Mybatis Generator of the author.

Mybatis Generator

Mybatis Genrator 1.4.0 Mybatis Genrator 1.4.0 Mybatis Genrator 1.4.0 Mybatis Genrator 1.4.0 Mybatis Genrator 1.4.0 Mybatis Genrator 1.4.0 Mybatis Genrator 1.4.0 This version has made major changes, mainly

  • New Runtime for Kotlin using MyBatis Dynamic SQL
  • New Runtime for Java using MyBatis Dynamic SQL
  • MyBatis Dynamic SQL is now the default runtime
  • Move to Java 8
  • Remove support for iBatis2

The main thing is to remove iBatis2 support and use MyBatis Dynamic SQL as the default runtime, which undoubtedly brings great convenience to MyBatis users. This will be described in detail in future articles.

Introduction to the

This project is a framework for generating dynamic SQL statements. Think of it as a type-safe SQL template library with additional support for MyBatis3 and Spring JDBC.

The library will generate complete DELETE, INSERT, SELECT, and UPDATE formatting statements that can be used by MyBatis or Spring. The most common use is to generate statements and a set of matching parameters that can be used directly by Mybatis. It also generates statements and parameter objects that are compatible with Spring JDBC Templates.

The library works by implementing a domain-specific Language (DSL) similar to SQL, which creates an object that contains a complete SQL statement and all the parameters required for that statement. This object can be used directly by Mybatis as a parameter passed into a Mapper method, just as we normally do.

features

The library can generate the following types of SQL statements (and more, see Change Log for the latest information) :

  • Flexible WHERE clauses that support subqueries
  • DELETE statement with flexible WHERE clause
  • Multiple INSERT statements:
    • An insert statement for a single record and will be insertednullValue (a “full” insert)
    • An insert statement for a single record and will ignore the input value ofnullColumn of (a “selective” insert)
    • Insert with SELECT statement
    • Multi-line inserts (similar to
      )
    • Batch inserts (Spring Batch or JDBC Batch)
    • Support to return autoincrement primary key
  • SELECT statements with flexible lists and FLEXIBLE WHERE clauses, supporting DISTINCT, count(DISTINCT…) , group by, joins, Unions, “Order by,” and so on.
  • UPDATE statements with flexible WHERE clauses, like insert statements, have two types of updates:
    • “Completely” updated
    • “Selective” updates
The target

The main objectives of this library are:

  • Type safety – The library will try to ensure that parameter types match database column types
  • Expressive – statements are constructed in a way that clearly conveys their meaning (some inspiration from Hamcrest)
  • Flexible — WHERE clauses can be built using any combination of AND, or, and nested conditions
  • Extensible – This library will generate statements that work with MyBatis3, Spring JDBC Template, or pure JDBC. It can also be extended to generate clauses for other frameworks. If you don’t have a built-in conditional statement that meets your needs, you can easily customize it (writing code is what you’re good at, after all).
  • Small – No dependency passing

prompt

Java versions update rapidly, but to this day, perhaps the biggest impact for Java developers is Java8, which has introduced TYPE_USE Annotations, functional programming, Lamda, the new date and time handling library, Optional, and more. Even some say — Java8 brought developer tools and opportunities “www.infoq.com/articles/Ty…” Such as:

  • Java type system enhancement Framework, or static type compile-time Checker.
  • GridExcel 】 【 github.com/liuhuagui/g… Using functional programming makes Excel reading and writing easier.

Similarly, Java8 led to the introduction of MyBatis Dynamic SQL, where functional programming is used to its fullest extent, as we will see later.

So, if you want to use MyBatis Dynamic SQL, please make sure your working environment is: Java8+

Quick to use

steps

Using MyBatis Dynamic SQL requires the following steps:

  • Create Table and Column objects
  • Create Mappers (XML or Java Based) for Mybatis3
  • Write SQL and use it

For discussion purposes, let’s first show the table structure used to perform CRUD:

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));Copy the code

Define the constants Tables and Columns

Org. Mybatis. Dynamic. SQL. SqlTable class is used to define a Table. The Table definition contains the actual representation (even the appropriate schema and catalog). The alias of the Table can be used in SQL statements if desired. Your Table should inherit from the SqlTable class. Org. Mybatis. Dynamic. SQL. SqlColumn class is used to define the columns for use in libraries, SqlColumns should be based on SqlTable to create. A column definition contains:

  • The Java type
  • The actual column name (an alias can be applied in a select statement)
  • The JDBC type (optional)
  • If you don’t need the default Type Handler, you can give the fully qualified name of the Type Handler you want to use in Mybatis.

(note: Unlike PO (Persistent Object, which maps data structures in the persistence layer (usually relational databases)), Table and Column objects are strictly abstractions of tables and columns, defined as static constants. Allows you to repeatedly reuse SQL statements in SQL statement construction, just as you would handwritten SQL statements on the command line.

The following usage patterns are recommended to provide maximum flexibility. This mode allows you to use your Tables and columns with “qualified” or “un-qualified” habits, just like writing SQL statements on the command line. For example, the column below could be written 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(a) {
            super("SimpleTable"); }}}Copy the code

Create MyBatis3 Mappers

This library can create classes for MyBatis mapper input. These classes contain the generated SQL and the set of matched parameters. Both are required for MyBatis. These objects are the only parameters of the MyBatis Mapper method.

Note: MyBatis Dynamic SQL works fine without XML files, but that doesn’t mean it doesn’t support XML, as MyBatis was originally designed as an XML-driven framework. If you use relational queries and need complex mappings, then use XML

in combination with MyBatis Dynamic SQL or better, your XML may only need to contain some

).

For example, a Mapper could look like this:

package examples.simple;

import java.util.List;

import org.apache.ibatis.annotations.DeleteProvider;
import org.apache.ibatis.annotations.InsertProvider;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.ResultMap;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.SelectProvider;
import org.apache.ibatis.annotations.UpdateProvider;
import org.apache.ibatis.type.JdbcType;
import org.mybatis.dynamic.sql.delete.render.DeleteStatementProvider;
import org.mybatis.dynamic.sql.insert.render.InsertStatementProvider;
import org.mybatis.dynamic.sql.select.render.SelectStatementProvider;
import org.mybatis.dynamic.sql.update.render.UpdateStatementProvider;
import org.mybatis.dynamic.sql.util.SqlProviderAdapter;

@Mapper
public interface SimpleTableAnnotatedMapper {

    @InsertProvider(type=SqlProviderAdapter.class, method="insert")
    int insert(InsertStatementProvider<SimpleTableRecord> insertStatement);

    @UpdateProvider(type=SqlProviderAdapter.class, method="update")
    int update(UpdateStatementProvider updateStatement);

    @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);

    @SelectProvider(type=SqlProviderAdapter.class, method="select")
    @ResultMap("SimpleTableResult")
    SimpleTableRecord selectOne(SelectStatementProvider selectStatement);

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

    @SelectProvider(type=SqlProviderAdapter.class, method="select")
    long count(SelectStatementProvider selectStatement);
}
Copy the code

Execute SQL with Mybatis3

In a DAO or service class, generated statements can be used as input to mapper methods. Here is one from examples. Simple. SimpleTableAnnotatedMapperTest example:

    @Test
    public void testSelectByExample(a) {
        try (SqlSession session = sqlSessionFactory.openSession()) {
            SimpleTableAnnotatedMapper mapper = session.getMapper(SimpleTableAnnotatedMapper.class);
            
            SelectStatementProvider selectStatement = select(id.as("A_ID"), firstName, lastName, birthDate, employed, occupation)
                    .from(simpleTable)
                    .where(id, isEqualTo(1))
                    .or(occupation, isNull())
                    .build()
                    .render(RenderingStrategies.MYBATIS3);

            List<SimpleTableRecord> rows = mapper.selectMany(selectStatement);

            assertThat(rows.size()).isEqualTo(3); }}Copy the code

The last

To learn more about Mybatis Dynamic SQL advanced usage, how it works, pits in practice, and how to use Mybatis Generator friendlier…