The last one was "Spring Boot - custom Banner pattern", and this one is "smoother MyBatis".

MyBatis's strength comes from its vast ecology and the support of many large domestic factories.

- the plass are gone

From iBatis to MyBatis, and then to many peripheral tools typically represented by MyBatis Plus in domestic teams, the development process of "Batis" series packages is almost another history of the rise and fall of XML. The original iBatis was born in 2002. At that time, XML was still quite popular in Java and even the whole software technology industry. Like many projects in the same period, iBatis stubbornly stuffed piles of XML into thousands of projects.

Many years later, the community comrades who once worked side by side with iBatis have faded out of the historical stage. A few outstanding people like Spring have gradually abandoned XML and developed in the way of coded configuration. iBatis has always been a conservative in this regard, even after MyBatis took over the mantle of iBatis, Only the @ Select/@Insert/@Update/@Delete annotation supporting code execution SQL (and the corresponding four Provider annotations) has been launched It used to resist developers make complaints about XML flooding, which was in mid 2010, then no action. Until the end of 2016, Jeff Butler, one of the main contributors of MyBatis, officially created the MyBatis Dynamic SQL project, and MyBatis finally began to fully embrace the construction of coded SQL without XML.

During the six-year window period from MyBatis to MyBatis Dynamic SQL, the open source community has spawned many non-governmental XML free code schemes based on MyBatis, among which Tk Mybatis, MyBatis Plus and other extension libraries with built-in Mapper and automatic CRUD generation are more popular. Once launched, they have won a lot of praise. Including the "conditional constructor" function in MyBatis Plus, which was not actually complete, it was also popular due to the lack of similar solutions at that time. At the same time, outside the MyBatis community, JOOQ, which has been developing silently, is a pure Java dynamic SQL execution library with almost the same long history as MyBatis. Its user group is small, but it has a good reputation. Nowadays, if you enter "MyBatis vs JOOQ" on any search engine, you can still get the result of almost one-sided selection of JOOQ. The reasons given by everyone are also very consistent: concise, flexible, no XML, very "Java". In the MyBatis camp, if you take out the "conditional constructor" of MyBatis Plus to face it, you will be kicked out of the challenge arena in just three rounds. It's a pity that JOOQ's family is not as rich as MyBatis. It took the road of commercial database supporting selling License fees early, which saved MyBatis from its own middle-aged crisis in public opinion.

Fluent MyBatis was born at the end of 2019. Even though it is a younger generation compared with MyBatis Dynamic SQL, it is still in the growth stage, and it has a taste of being better than blue.

In terms of implementation, MyBatis Plus overwrites and replaces some methods of internal types of MyBatis. The overall mechanism is heavy, but it can hide some functional details into the internal logic that users do not need to pay attention to; On the contrary, the implementation mechanism of MyBatis Dynamic SQL is very lightweight. It is not only completely developed based on MyBatis's native Provider series annotations, but also has no hidden logic. The corresponding Entity, DynamicSqlSupport and Mapper classes are automatically generated for each table of the user, and all of them are put into the user's source code directory. Therefore, more details are exposed and the code is slightly intrusive. Fluent MyBatis takes the advantages of both. The overall mechanism is closer to MyBatis Dynamic SQL. Similarly, based on the native Provider annotation, fluent MyBatis generates Entity class and default blank Dao class for each user's table. The difference is that it also automatically generates many standard auxiliary classes that cannot be changed by developers through JVM compilation time code enhancement, These codes do not need to be put into the user's source code directory, but can be used directly during coding, which not only provides rich functions, but also ensures the cleanliness of the user's code.

In terms of usage, Fluent MyBatis also draws on the best practices of its predecessors. Without fancy annotations and configurations, it directly reuses MyBatis connections, and all functions are available out of the box. At the same time, because Fluent MyBatis provides all table fields, conditions and operations in the form of method calls, it obtains better IDE syntax assistance than other similar projects. Take a less complicated example:

// Using Fluent MyBatis to construct query statements
mapper.listMaps(new StudentScoreQuery()
    .select
    .schoolTerm()
    .subject()
    .count.score("count")
    .min.score("min_score")
    .max.score("max_score")
    .avg.score("avg_score")
    .end()
    .where.schoolTerm().ge(2000)
    .and.subject.in(new String[]{"English", "mathematics", "language"})
    .and.score().ge(60)
    .and.isDeleted().isFalse()
    .end()
    .groupBy.schoolTerm().subject().end()
    .having.count.score.gt(1).end()
    .orderBy.schoolTerm().asc().subject().asc().end()
);

The syntax of MyBatis Dynamic SQL is also beautiful, but field names and min/max/avg and other methods need static references, which is slightly inferior to Fluent MyBatis.

// Constructing query statements using MyBatis Dynamic SQL
mapper.selectMany(
    select(
        schoolTerm,
        subject,
        count(score).as("count"),
        min(score).as("min_score"),
        max(score).as("max_score"),
        avg(score).as("avg_score")
    ).from(studentScore)
    .where(schoolTerm, isGreaterThanOrEqualTo(2000))
    .and(subject, isIn("English", "mathematics", "language"))
    .and(score, isGreaterThanOrEqualTo(60))
    .and(isDeleted, isEqualTo(false))
    .groupBy(schoolTerm, subject)
    .having(count(score), isGreaterThan(1)) //Currently, the having method is not supported
    .orderBy(schoolTerm, subject)
    .build(isDeleted, isEqualTo(false))
    .render(RenderingStrategies.MYBATIS3)
);

JOOQ has a long history. The code written is full of constant fields, which has powerful functions but poor aesthetics.

// Using JOOQ to construct query statements
dslContext.select(
    STUDENT_SCORE.GENDER_MAN,
    STUDENT_SCORE.SCHOOL_TERM,
    STUDENT_SCORE.SUBJECT,
    count(STUDENT_SCORE.SCORE).as("count"),
    min(STUDENT_SCORE.SCORE).as("min_score"),
    max(STUDENT_SCORE.SCORE).as("max_score"),
    avg(STUDENT_SCORE.SCORE).as("avg_score")
)
.from(STUDENT_SCORE)
.where(
    STUDENT_SCORE.SCHOOL_TERM.ge(2000),
    STUDENT_SCORE.SUBJECT.in("English", "mathematics", "language"),
    STUDENT_SCORE.SCORE.ge(60),
    STUDENT_SCORE.IS_DELETED.eq(false)
)
.groupBy(
    STUDENT_SCORE.GENDER_MAN,
    STUDENT_SCORE.SCHOOL_TERM,
    STUDENT_SCORE.SUBJECT
)
.having(count().ge(1))
.orderBy(
    STUDENT_SCORE.SCHOOL_TERM.asc(),
    STUDENT_SCORE.SUBJECT.asc()
)
.fetch();

The condition constructor of MyBatis Plus only encapsulates the basic SQL operations. String splicing is used for fields, conditions, aliases, etc., which is prone to SQL exceptions caused by spelling errors.

// Constructing query statements using MyBatis Plus
mapper.selectMaps(new QueryWrapper<StudentScore>()
    .select(
        "school_term",
        "subject",
        "count(score) as count",
        "min(score) as min_score",
        "max(score) as max_score",
        "avg(score) as avg_score"
    )
    .ge("school_term", 2000)
    .in("subject", "English", "mathematics", "language")
    .ge("score", 60)
    .eq("is_deleted", false)
    .groupBy("school_term", "subject")
    .having("count(score)>1")
    .orderByAsc("school_term", "subject")
);

In terms of functional integrity of Java dynamic SQL construction, the current ranking is mybatis plus < mybatis dynamic SQL < fluent mybatis < jooq.

MyBatis Plus conditional constructor failed in functionality. It not only failed to express JOIN and UNION statements, but also failed to recruit slightly complex SQL such as nested queries. MyBatis Dynamic SQL supports JOIN and UNION statements, does not support nested queries, and lacks a small amount of standard SQL syntax such as HAVING. Fluent MyBatis supports multi table JOIN, UNION, nested query and almost all standard SQL syntax, which is suitable for most scenarios. JOOQ is the real king. It not only supports the standard SQL syntax, but also the proprietary keywords and built-in methods unique to various manufacturers, such as ON DUPLICATE KEY UPDATE of MySQL, windows of PostgreSQL, CONNECT BY of Oracle, etc. Considering that the total amount of SQL has changed, it is no longer difficult to fill in the basic SQL syntax.

In addition to the basic skills of SQL, it is particularly worth mentioning the unique skill of Fluent MyBatis: support dynamic table name change (FreeQuery/FreeUpdate feature). In the development process of cloud effect project, because it is necessary to dynamically select the dimension table of aggregation calculation based on various nested queries and view conditions, thanks to the dynamic table name function of Fluent MyBatis, it is possible to make code reuse possible while preserving the convenience of syntax construction to the greatest extent.

Compared with dense XML files, Java code has obvious advantages in readability and maintainability. With the joint promotion of the government and the community, a new and coded MyBatis ecosystem is rising. Suddenly looking back, the once proud "Plus extensions" are not fragrant.

II. Elegant data flow

When you first know Fluent MyBatis, the most obvious feature you can feel is its convenient IDE syntax prompt.

The Entity, Mapper, Query, Update and other objects automatically generated based on the data table turn all database fields and SQL operations into methods and string them into flat flow statements. Even nested queries can be displayed in a staggered manner:

new StudentQuery()
    .where.isDeleted().isFalse()
    .and.grade().eq(4)
    .and.homeCountyId().in(CountyDivisionQuery.class, q -> q
        .selectId()
        .where.isDeleted().isFalse()
        .and.province().eq("Zhejiang Province")
        .and.city().eq("Hangzhou")
        .end()
    ).end();

It is easy to see that the SQL corresponding to the above statement is:

SELECT * FROM student
WHERE is_deleted = false
AND grade = 4
AND home_county_id IN (
    SELECT id FROM county_division 
    WHERE is_deleted = false
    AND province = 'Zhejiang Province'
    AND city = 'Hangzhou'
)

Moreover, after several adjustments to the JOIN syntax implemented by Fluent MyBatis, the current version is also very beautiful:

JoinBuilder.from(
    new StudentQuery("t1", parameter)
        .selectAll()
        .where.age().eq(34)
        .end()
).join(
    new HomeAddressQuery("t2", parameter)
        .where.address().like("address")
        .end()
).on(
    l -> l.where.homeAddressId(),
    r -> r.where.id()
).endJoin().build();

Among them, the design of using Lambada statement to express the JOIN condition is not only fully in line with the habits of Java developers, but also well matches the needs of IDE syntax tips. It is wonderful to think carefully.

The flow in Fluent MyBatis can set conditional filtering, such as "only update fields with non empty values":

new StudentUpdate()
    .update.name().is(student.getName(), If::notBlank)
    .set.phone().is(student.getPhone(), If::notBlank)
    .set.email().is(student.getEmail(), If::notBlank)
    .set.gender().is(student.getGender(), If::notNull)
    .end()
    .where.id().eq(student.getId()).end();

The above code is equivalent to the following XML content in MyBatis:

 

Obviously, the readability of Java's streaming code is much higher than the angle bracket set angle bracket cascade structure of XML files.

Flow is continuous. For more complex branching conditions, Fluent MyBatis can make use of the following statements to give full play to the flexibility of Java code:

StudentQuery studentQuery = Refs.Query.student.aliasQuery()
    .select.age().end()
    .where.age().isNull().end()
    .groupBy.age().apply("id").end();
if (config.shouldFilterAge()) {
    studentQuery.having.max.age().gt(1L).end();
} else if (config.shouldOrder()) {
    studentQuery.orderBy.id().desc().end();
}

This judgment based on the state of external variables is beyond the capability of MyBatis's XML file.

Analysis of three minute source code

The code of Fluent MyBatis consists of two subprojects: Fluent Generator and Fluent MyBatis. This combination is similar to MyBatis Dynamic SQL, which is a partner of MyBatis Generator: the Fluent Generator automatically generates the Entity and Dao objects required by Fluent MyBatis by reading the tables in the database; Fluent MyBatis provides a functional DSL for writing SQL statements.

The code of the Fluent Generator subproject is simple and straightforward. The program entry is in the FileGenerator type at the outermost layer of the package structure tree. The developer directly calls the build() method of this class and uses the chain constructor to pass in the table name to be read and the directory to store the generated files. The Fluent Generator reads the table structure from the database according to this information, and then generates Java files of Entity and Dao types for each table, which are placed in the agreed location, and the whole logic is completed at one go. It is worth mentioning that the configuration method of Fluent Generator is completely coded. Although MyBatis Generator supports pure coded configuration, it is better to continue to use the style of XML file configuration input in the official example.

The Dao type generated by Fluent Generator is empty by default. It is just a recommended data query layer structure. By inheriting their BaseDao types, they can obtain the ability to operate Mapper conveniently.

The code of Fluent MyBatis subproject is slightly richer, which is divided into three modules:

• Fluent mybatis contains various common base classes
• Fluent mybatis test test case
• Fluent mybatis processor compile time code generator

The fluent mybatis module defines annotations, data models and other auxiliary types related to code generation. Most of them are behind the scenes Heroes: developers usually don't use the classes in this package directly.

The Fluent MyBatis test module contains rich test cases, which makes up for the incomplete documents of Fluent MyBatis at the current stage to a certain extent. If you can't find many problems in the use of Fluent MyBatis in the document, you will have unexpected gains by looking through the test cases in the code base.

The principle of the Fluent MyBatis processor module is similar to that of the Lombook tool library, but it does not modify the original type, but scans the annotation on the Entity type, and then dynamically generates new auxiliary classes. The Entity class produced by Fluent Generator is like a Pandora's box, which contains the secret of Fluent MyBatis magic. The FluentMybatisProcessor class is the magician of the whole performance. It transforms each Entity class shaped like XyzEntity into a series of auxiliary classes, among which the key ones include:

• XyzBaseDao: inherits the BaseDao type and implements the IBaseDao interface, including methods to obtain Entity related Mapper, Query and Update types. It is the parent of the blank Dao class generated by Fluent Generator for the user.
• XyzMapper: implements the IEntityMapper, IRichMapper and IWrapperMapper interfaces, which are used to construct Query and Update objects and execute SQL instructions of type IQuery or IUpdate.
• XyzQuery: inherits BaseWrapper and BaseQuery types, implements IWrapper and IQuery interfaces, and is used to assemble the basic container of query statements.
• XyzUpdate: inherits BaseWrapper and BaseUpdate types, implements IWrapper and IBaseUpdate interfaces, and is used to assemble the basic container of update statements.
• XyzSqlProvider: inherits the BaseSqlProvider type and is used for final assembly of SQL statements.
• There are also XyzMapping, XyzDefaults, XyzFormSetter, XyzEntityHelper, XyzWrapperHelper, etc. Many types generated by the fluent mybatis processor module will be used when writing business code.

A typical Fluent MyBatis workflow is to assemble the execution object through the generated Query or Update type, and then send it to Mapper object for execution. For example:

// Construct and execute query statements
List<StudentEntity> users = mapper.listEntity(
    new StudentQuery()        .select.name().score().end()
        .where.userName().like("user").end()
        .orderBy.id().asc().end()
        .limit(20, 10)
);


// Construct and execute update statements
int effectedRecordCount = mapper.updateBy(
    new StudentUpdate()
        .set.userName().is("u2")
        .set.isDeleted().is(true)
        .set.homeAddressId().isNull().end()
        .where.isDeleted().eq(false).end()
);

Query and Update types not only implement the IQuery/IUpdate interface, but also implement the IWrapper interface. The former is used to assemble objects and the latter is used to read object contents. This is a very intentional design. Many methods in Mapper type can receive objects of IQuery or IUpdate interface type, and then transfer the actual request to the generated Provider type through @ InsertProvider, @ SelectProvider, @ UpdateProvider or @ DeleteProvider annotation on the method. Providers take the incoming IWrapper execution object from the agreed Map parameters, use MapperSql tool class to assemble SQL statements, and finally hand it over to MyBatis for execution.

In Mapper, there are also some methods to directly accept Map objects, which can eliminate the process of describing SQL with IQuery/IUpdate and carry out simple insertion and query. The original Map object passed in will also be read out in the Provider, and the SQL statement will be assembled with MapperSql, and then handed over to MyBatis for execution.

This Provider based implementation of Fluent MyBatis can not only provide users with smooth SQL construction experience, but also fully reuse many advantages of MyBatis, such as rich DB connectors, sound anti SQL injection mechanism and so on, so as to ensure the stability and reliability of core logic.

• The above is the sharing of "smoother MyBatis".
• You are also welcome to exchange and discuss. If there are any inaccuracies in this article, I hope you will forgive me.
• It's not easy to create. Your support is my biggest motivation. If I can help you, give me a compliment~~~