preface

I haven’t used Mybatis for about two or three years. Recently, I started to use Mybatis again because of the project, and the experience always feels not smooth. Even if I add Mybatis Plus, IT still fails to meet my expectation.

Looking back over the years with JOOQ, the experience of writing SQL directly in Java was amazing. It implements a DSL that is almost as complete as writing native SQL.

Does mybatis ecology have a similar framework? Coincidentally, there is an official project called Mybatis-dynamic-SQL. This article is to record my experience in integrating Mybatis Dynamic SQL and Mybatis Generator.

I have put the whole project on Github. If you are interested, you can click star. I will keep updating.

To observe the dimension

Before starting the actual coding experience, I listed four dimensions to see if the framework meets my expectations

  1. Mybatis ecological compatibility
  2. Code generation capability
  3. There is no XML
  4. DSL complete degrees

Let’s go through it one by one

Mybatis ecological compatibility

To maintain compatibility with the existing ecosystem of Mybatis, such as some SQL logging plug-ins, paging query plug-ins and generic TypeHandler, level 2 cache, etc

Second, code generation ability

Database entities can be generated automatically, and developers do not need (and are generally not allowed) to modify this part of the auto-generated code, but developers have the flexibility to set the generation rules through configuration items for extension.

It would be even better if code generation tools could be seamlessly integrated with build tools such as Maven and Gradle.

Three, no XML

Zero XML

4. Domain Special Language

Since SQL is written in Java, the framework should have adequate support for frequently used SQL and solutions for infrequently used SQL.

Another point is that the difference between written DSL and native SQL should not be too great, as shown below, which is almost instantly readable

public class UserRepository {
    public User selectByUsernameAndPassword(String username, String password) {
        Tables.User user = Tables.user();
        // select * from user where user.name = ? and user.password = ?
        returnselect(user.all()).from(user) .where(user.username().eq(username)) .and(user.password().eq(password)); }}Copy the code

Introduction of framework

Mybatis Dynamic SQL

Mybatis Dynamic SQL is a framework developed by Mybatis team. It is compatible with the ecology of Mybatis3, but the biggest difference with Mybatis is: You don’t write SQL in XML, you don’t concatenate SQL in annotations (anyone who’s ever concatenated a complex string in Java knows that), you write SQL in Java.

This will bring the following benefits

  • Typesafe: Make sure your SQL parameter types and column types are the same at compile time
  • Expressive: This is what SQL looks like to be executed
  • Flexible: Even if else, and, or can be easily implemented

Mybatis Generator

Mybatis Generator is also an automatic code generation tool produced by Mybatis team. It supports code generation of Mybatis3, Mybatis- dynamic-SQL and other types. A large number of extension points and predefined configuration items are provided, giving users the flexibility to customize generation rules.

In addition, the Generator supports multiple generation modes, and users can select them based on their application scenarios

  • Command mode: Code can be generated from the command line
  • Maven plug-ins: integrate directly into the Maven build tool
  • Java Runtime pattern: Generate by writing Java code and then running it
  • .

Business integration

The next step is to apply the framework to a real business development scenario and see if it meets my expectations.

Business simulation

I simulated a user, order related business scene, complete coverage of add, delete, change and check applications, the whole use of such as the following

The data model corresponds to three tables: user, ORDER, and ORDER_item, as shown in UML below

For full SQL, see v1__init.sql on GITHUB

The project build

I used Gradle to build the project and introduced Lombok, Mapstruct and other apt-based code generation tools.

The complete build environment and dependency libraries are shown below

The whole project is divided into three modules, and the dependencies among modules are as follows

The packages inside each module are as follows

  • API

This module provides HTTP ports to clients that rely on the Core and Dao modules

  • CORE

Concrete business logic is implemented, independent of any Modules

  • DAO

Mapper and Entity generated by Mybatis will be stored in the Module, and the Dao Module will implement the Repository in the Core Module, so it will rely on the Core Module

Integrate Mybatis Generator into Gradle Task

Since Mybatis Generator does not currently have an official Gradle plug-in, I packaged the generated logic into a Gradle Task.

For the complete configuration, see DAO /build.gradle

import org.mybatis.generator.api.MyBatisGenerator
import org.mybatis.generator.config.xml.ConfigurationParser
import org.mybatis.generator.internal.DefaultShellCallback

buildscript {
    repositories {
        mavenCentral()
    }
    // Configure dependencies required by gradle Plugin or task
    dependencies {
        classpath group: 'org.mybatis.generator'.name: 'mybatis-generator-core'.version: '1.4.0'
        classpath group: 'mysql'.name: 'mysql-connector-java'.version: '8.0.25'
    }
}

task mybatisGenerate {
    def warnings = new ArrayList<String>()
    def overwrite = true
    def configurationFile = projectDir.getAbsolutePath() + '/src/main/resources/generatorConfiguration.xml'
    def configFile = new File(configurationFile)
    def cp = new ConfigurationParser(warnings)
    def config = cp.parseConfiguration(configFile)
    def callback = new DefaultShellCallback(overwrite)
    def myBatisGenerator = new MyBatisGenerator(config, callback, warnings)
    myBatisGenerator.generate(null)}Copy the code

Code generation rules in generatorConfiguration. XML configuration file (on Mybatis generator more configuration can refer to official document).

Be sure to configure targetRuntime=”MyBatis3DynamicSql” in the Context TAB to generate the code required by the MyBatis Dynamic SQL framework

<! DOCTYPEgeneratorConfiguration PUBLIC
        "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
        "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
<generatorConfiguration>
		<! MyBatis3DynamicSql specifies the code to generate mybatis dynamic SQL -->
    <context id="dsql" targetRuntime="MyBatis3DynamicSql">
        <! -- Escape rules -->
        <property name="autoDelimitKeywords" value="true"/>
        <property name="beginningDelimiter" value="`"/>
        <property name="endingDelimiter" value="`"/>

        <! -- Database connection -->
        <jdbcConnection driverClass="com.mysql.cj.jdbc.Driver"
                        connectionURL="jdbc:mysql://localhost:3306/user" userId="root" password="123456"/>

        <! -- mybatis type handler -->
        <javaTypeResolver>
            <property name="useJSR310Types" value="true"/>
        </javaTypeResolver>

        <! -- Code generation path -->
        <javaModelGenerator targetPackage="cc.cc1234.dao.model" targetProject="src/main/java"/>

        <javaClientGenerator targetPackage="cc.cc1234.dao.mapper" targetProject="src/main/java">
						<! Specify parent interface for generated Mapper -->
            <property name="rootInterface" value="org.mybatis.dynamic.sql.util.mybatis3.CommonSelectMapper"/>
        </javaClientGenerator>
			
				<! Table and entity generation rule mapping -->
				<table tableName="user" domainObjectName="UserEntity">
            <generatedKey column="id" sqlStatement="JDBC" identity="true" type="post"/>
            <! The Gender field is handled using a custom typeHandler -->
						<columnOverride column="gender"
                            javaType="cc.cc1234.core.domain.enums.Gender"
                            typeHandler="org.apache.ibatis.type.EnumOrdinalTypeHandler"/>
        </table>
    </context>

</generatorConfiguration>
Copy the code

After the configuration is complete, you only need to execute mybatisGenerator task to generate the code. If you are using Intellij IDEA, you can find the task in the toolbar on the right

After executing the task you can see your generated code in the project

Generated code analysis and use

The generated code mainly includes xxxMapper, xxxSqlSupport and xxxEntity.

  • Entity is the Java Entity corresponding to a database table, with only getters and setters
  • Mapper is the entry point for CRUD, and by default there are already common CRUD methods
  • SqlSupport maintains the mapping between entities and tables, such as a Field corresponding to a column of a table

The complete project is open source on GITHUB (github.com/vran-dev/my…). You can always fork it down and run it, and I’m just going to show you some of the code here,

public final class UserEntityDynamicSqlSupport {
    @generated (value = ". Org. Mybatis generator. API. MyBatisGenerator ", the date = "2021-08-29 T21: on. 7120396 + 08:00", comments="Source Table: user")
    public static final UserEntity userEntity = new UserEntity();

    @generated (value = ". Org. Mybatis generator. API. MyBatisGenerator ", the date = "2021-08-29 T21: on. 7120949 + 08:00", comments="Source field: user.id")
    public static final SqlColumn<Long> id = userEntity.id;

    @generated (value = ". Org. Mybatis generator. API. MyBatisGenerator ", the date = "2021-08-29 T21: on. 7120949 + 08:00", comments="Source field: user.username")
    public static final SqlColumn<String> username = userEntity.username;

    @generated (value = ". Org. Mybatis generator. API. MyBatisGenerator ", the date = "2021-08-29 T21: on. 7120949 + 08:00", comments="Source field: user.gender")
    public static final SqlColumn<Gender> gender = userEntity.gender;

    @generated (value = ". Org. Mybatis generator. API. MyBatisGenerator ", the date = "2021-08-29 T21: on. 7120949 + 08:00", comments="Source Table: user")
    public static final class UserEntity extends SqlTable {
        public final SqlColumn<Long> id = column("id", JDBCType.BIGINT);

        public final SqlColumn<String> username = column("username", JDBCType.VARCHAR);

        public final SqlColumn<Gender> gender = column("gender", JDBCType.TINYINT, "cc.cc1234.dao.EnumOrdinalTypeHandler");

        public UserEntity(a) {
            super("user"); }}}Copy the code

So how do you use this generated code to construct SQL queries

For simple CRUD, it is very simple to use

@Autowired
private UserEntityMapper userEntityMapper;

@Override
public Optional<User> selectByPrimaryKey(Long id) {
    return userEntityMapper.selectByPrimaryKey(id);
}

@Override
public Long insertAndReturnPrimaryKey(User user) {
    final UserEntity entity = userRecordConverter.from(user);
    userEntityMapper.insertSelective(entity);
    return entity.getId();
}

@Override
public boolean deleteByPrimaryKey(Long id) {
    int delete = userEntityMapper.delete(d -> d.where(UserEntityDynamicSqlSupport.id, isEqualTo(id)));
    return delete == 1;
}

@Override
public boolean updateByPrimaryKey(User userEntity) {
    var record = userRecordConverter.from(userEntity);
    return userEntityMapper.updateByPrimaryKeySelective(record) == 1;
}
Copy the code

Even complex queries and type mappings are not difficult to write, and the feel of writing is not that different from native SQL. It is basically easy to read at a glance.

    /**
     * <pre>
     *     select user.id, order_item.goods_name, sum(order_item.goods_quantity) from user
     *      inner join `order` on user.id = `order`.user_id
     *      inner join order_item on `order`.id = order_item.order_id
     *      where user.id = 1 group by order_item.goods_name
     * </pre>
     *
     * @param userId
     * @return* /
@Override
public List<UserPurchasedGoods> selectUserPurchasedOrders(Long userId) {

    // alias
    val user = UserEntityDynamicSqlSupport.userEntity;
    val orderItem = OrderItemEntityDynamicSqlSupport.orderItemEntity;
    val order = OrderEntityDynamicSqlSupport.orderEntity;

    // build sql dsl
    val sqlDsl = select(user.id, orderItem.goodsName, sum(orderItem.goodsQuantity).as("goods_quantity"))
        .from(user)
        .join(order).on(user.id, equalTo(order.userId))
        .join(orderItem).on(order.id, equalTo(orderItem.orderId))
        .where(user.id, isEqualTo(userId))
        .groupBy(orderItem.goodsName)
        .build()
        .render(RenderingStrategies.MYBATIS3);

    // execute and map
    return userEntityMapper.selectMany(sqlDsl, map -> {
        val result = new UserPurchasedGoods();
        result.setGoodsQuantity(Integer.parseInt(map.get("goods_quantity").toString()));
        result.setGoodsName(map.get("goods_name").toString());
        return result;
    });
}
Copy the code

Unsupported SQL

In the process of verification, it was found that some SQL in Mybatis Dynamic SQL is not encapsulated in THE DSL (i.e. there are corresponding chain call methods), for example

INSERT INTO.ON DUPLICATE KEY UPDATE...
Copy the code

or

INSERT IGNORE INTO...
Copy the code

There is also an official issue (github.com/mybatis/myb…) However, although DSL is not supported, it is possible to write such native SQL using Mybatis’ native @INSERT annotation, which requires inheriting Mapper generated by Mybatis Generator.

Due to many DB implementation of SQL syntax is not unified, so in the business encountered this situation is quite common, fortunately, Mybatis Dynamic SQL compatible with Mybatis annotation function, that almost all can write native SQL way to solve this problem.

A Bug

I accidentally encountered a bug in the Demo. I used Mybatis’ built-in EnumOrdinalTypeHandler to handle the reading and writing of enumeration type fields. It turns out that the Handler doesn’t handle properly and throws exceptions when involving Inserrt or Update statements.

Finally, it was found to be a small bug through the debug, which has been issued (github.com/mybatis/myb…). In the form of feedback to the official team, the official also made feedback.

To solve this problem, in addition to mybatis Dynamic SQL needs to be changed, myBatis Generator also needs to be changed, which has a longer release cycle.

If you’re interested, you can keep following it.

conclusion

Finally from their own set several dimensions to make a summary

To observe the dimension The star instructions
Mybatis ecological compatibility Being fostered fostered fostered fostered Almost compatible with mybatis existing plug-ins can be used, and the learning cost is low
Code generation capability Being fostered fostered fostered Configuration items can not fully cover the features supported by MyBatis, but it is very extensible and can be implemented by extension
There is no XML Being fostered fostered fostered fostered It can be done
DSL completeness Do do do A lot of SQL is still not supported, which pales in comparison to JOOQ
Community activity Do do do Not many people use Mybatis DynamICC SQL, but the official has been kept updated. The frequency of Mybatis Generator updates is not very active.

After the overall experience, I think it is feasible to introduce Mybatis Dynamic SQL into the production environment. Before introducing Mybatis Dynamic SQL, I just need to consider: does the PROJECT SQL really need to be separated from the code during a certain period of time?

The essence of choice is still trade off.

If you can’t stand myBatis XML and annotations, but must use MyBatis ecology, then MyBatis Dynamic SQL may be your choice.

If you don’t mind myBatis ecology, I actually recommend JOOQ, which has a more complete DSL and more powerful code generation capabilities.

A link to the

  1. Mybatis-Dynamic-SQL-Integeration-demo
  2. Mybatis Generator docs
  3. Myabtis Dynamic SQL docs