By Jinji
If you compare the statistics of the two most popular SQL mapping frameworks in the global Java field on Google Trends over the past year, you can see that MyBatis has dominated the developer market in East Asia and topped the list of the most popular Java database access frameworks in China.
MyBatis’s confidence comes from its vast ecology and the support of many large domestic factories. Among the various MyBatis extensions, there are also many precious projects. Fluent MyBatis from Alibaba’s Technical Team is one of the unique new stars.
From iBatis to MyBatis and then to many peripheral tools represented by MyBatis Plus in the domestic team, the development process of the Batis series package shows the ups and downs in the XML development history. iBatis was originally created in 2002. At that time, XML was still very popular in Java and the entire software technology industry. Like many projects in the same period, iBatis abruptly stuffed piles of XML into thousands of projects.
Many years later, projects from the same period as iBatis in the community have gradually set their foot out of the field. A few outstanding projects like Spring, which is still in use today, are gradually abandoning XML and moving towards code-based configuration. iBatis has always been a conservative in this respect. Even after MyBatis took over iBatis's role, it only grandly introduced @Select/@Insert/@Update/@Delete annotations (and the corresponding four Provider annotations) that support code execution SQL in response to developers' complaints about the XML flood. This was in the middle of 2010, and then there was no further progress. By the end of 2016, Jeff Butler, one of the major contributors to MyBatis, created the MyBatis Dynamic SQL project. Since then, MyBatis has finally begun to fully embrace SQL code building without XML.
During the six-year gap between MyBatis and MyBatis Dynamic SQL, the open-source community has given rise to many non-governmental XML-free code solutions based on MyBatis, such as Tk Mybatis, MyBatis Plus, and other built-in Mapper and extension libraries, which generate CRUD automatically. Each solution has received many likes. The conditional constructor function of MyBatis Plus was imperfect but also popular due to the lack of similar solutions at that time. In addition to the MyBatis community, JOOQ has been developing silently and is a pure Java Dynamic SQL execution library with a history almost as long as MyBatis. It has a small user base but a good reputation. Nowadays, if you type "MyBatis vs. JOOQ" on any search engine, JOOQ will always be chosen unanimously. The reasons are unanimous. It is simple, flexible, XML-free, and it’s of a typical Java type. While in the MyBatis camp, if the conditional constructor of MyBatis Plus is chosen to vie with JOOQ, it won’t stand longer than three rounds. However, it is a pity that JOOQ is not fundamentally as well-off as MyBatis since it embarked too early on the road of commercial database to charge for licenses, which saved MyBatis from facing its middle-aged crisis in public opinion.
Fluent MyBatis was created at the end of 2019. It may be young compared to MyBatis Dynamic SQL, but it shows a great growth potential to surpass the established competitors.
In terms of implementation, MyBatis Plus overwrites and replaces some internal types of MyBatis. Its overall mechanism is heavy, but it is possible to hide some functional details that users do not need to pay attention to in internal logic. On the contrary, the implementation mechanism of MyBatis Dynamic SQL is very lightweight. It is developed based on the MyBatis-native Provider series annotations, and it has no hidden logic. It generates the corresponding classes of Entity, DynamicSqlSupport, and Mapper for each user table and puts in the user's source code directory automatically. Therefore, more details are exposed, and the code is more invasive. Fluent MyBatis takes the strengths of the two, and the overall mechanism is closer to MyBatis Dynamic SQL. Besides, based on the native Provider annotation, Entity class and default blank Dao class are generated for each table of the user. The difference is it also generates many standard auxiliary classes automatically that developers cannot change through the JVM compile-time code enhancement function. These codes do not need to be placed in the user's source code directory but can be used directly during coding. This provides multiple features and makes the user code clean and tidy.
In terms of usage, Fluent MyBatis also draws on the best practices from its predecessors. It abandons unnecessary annotations and configurations and reuses the MyBatis connection, with all functions out-of-the-box. In addition, Fluent MyBatis provides all table fields, conditions, and operations in the form of method calls, thus obtaining a better IDE syntax assistance than other similar projects. Let’s look at a simple example:
// Use Fluent MyBatis to construct a query statement
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", "Chinese"})
.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 easy to read, but field name and min/max/avg methods need static references, which is slightly inferior to Fluent MyBatis.
// Use MyBatis Dynamic SQL to construct a query statement
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", "Chinese"))
.and(score, isGreaterThanOrEqualTo(60))
.and(isDeleted, isEqualTo(false))
.groupBy(schoolTerm, subject)
.having(count(score), isGreaterThan(1)) //The having method is not currently supported
.orderBy(schoolTerm, subject)
.build(isDeleted, isEqualTo(false))
.render(RenderingStrategies.MYBATIS3)
);JOOQ has a long history. The code written by JOOQ is full of constant fields. It’s functions are more powerful but its aesthetics are poorer.
// Use JOOQ to construct a query statement
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", "Chinese"),
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 basic SQL operations, and string concatenation is required for fields, conditions, aliases, etc., which is prone to SQL exceptions caused by spelling errors.
// Use MyBatis Plus to construct a query statement
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", "Chinese")
.ge("score", 60)
.eq("is_deleted", false)
.groupBy("school_term", "subject")
.having("count(score)>1")
.orderByAsc("school_term", "subject")
);In terms of functional integrity in building Dynamic SQL in Java, the current sorting order is MyBatis Plus :arrow_right: MyBatis Dynamic SQL :arrow_right: Fluent MyBatis :arrow_right: JOOQ.
MyBatis Plus’s function constructor fails in functionality. It can neither express JOIN and UNION statements nor provide slightly complex SQL statements, such as nested queries. MyBatis Dynamic SQL supports JOIN and UNION statements but does not support nested queries yet, and it 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 syntaxes, which are sufficient for most scenarios. JOOQ is truly a master of everything. It supports the standard SQL syntax, the proprietary keywords, and built-in methods unique to various vendors, such as MySQL's ON DUPLICATE KEY UPDATE, PostgreSQL's WINDOW, and Oracle's CONNECT BY. Integrating various SQL syntaxes is a trivial and labor-consuming task. Considering that the total amount of SQL syntaxes is virtually unchanged, I believe the gap between all parties will be narrowed gradually over time.
In addition to basic SQL functions, it is worth mentioning that Fluent MyBatis supports dynamic table name change (FreeQuery/FreeUpdate feature). In the development process of the Alibaba Apsara DevOps, the dimension table for aggregation calculation needs to be dynamically selected based on various nested queries and view conditions. Thanks to the dynamic table naming function of Fluent MyBatis, code reuse is made possible while preserving the convenience of syntax construction to the greatest extent.
Compared with a bulk of XML files, Java code has advantages in readability and maintainability. With the joint efforts of officials and the community, a new and code-based MyBatis ecosystem is rising. Take a look back; the once glorious Plus Expansions are all has-beens now.
The most conspicuous feature of Fluent MyBatis is its convenient IDE grammar hint.
Objects, such as Entity, Mapper, Query, and Update, are generated automatically based on the data table so that all database fields and SQL operations can be streamlined into smooth stream statements. Even nested queries can be well-proportioned:
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 City")
.end()
).end();It’s easy to tell that the SQL corresponding to the preceding 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 City'
)In addition, the JOIN syntax implemented by Fluent MyBatis has been adjusted several times, and 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();The design of using the Lambda statement to express JOIN conditions fully conforms to the habits of Java developers and meets the needs of IDE syntax hints. It is a delicate design.
Conditional filtering can be configured for streams in Fluent MyBatis. For example, only update fields whose values are not null:
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 preceding code is equivalent to the following XML content in MyBatis:
<update id="updateById" parameterType="...">
update student
<set>
<if test="name != null">
`name` = #{name,jdbcType=VARCHAR},
</if>
<if test="phone != null">
phone = #{phone,jdbcType=VARCHAR},
</if>
<if test="email != null">
email = #{email,jdbcType=VARCHAR},
</if>
<if test="gender != null">
gender = #{gender,jdbcType=TINYINT},
</if>
<if test="gmtModified != null">
gmt_modified = #{gmtModified,jdbcType=TIMESTAMP},
</if>
</set>
where id = #{id,jdbcType=INTEGER}
</update>Java's stream code readability is much higher than the cascading structure of angle bracket in another angle bracket in XML files.
Streams are sustainable. For more complex branch conditions, Fluent MyBatis can make full use of the following statement to give full play to the flexibility of Java code:
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 determination based on the state of external variables is beyond the reach of MyBatis's XML files.
The code of Fluent MyBatis consists of two sub-projects: Fluent Generator and Fluent MyBatis. The combination of the two is similar to the MyBatis Dynamic SQL and MyBatis Generator. Fluent Generator generates Entity and Dao objects required by Fluent Batis automatically by reading tables in the database, and Fluent MyBatis provides a functional DSL for writing SQL statements.
The code of the Fluent Generator sub-item is 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, using a chain constructor to pass in the name of the table you want to read, such as the directory where the generated file is stored. Based on this information, the Fluent Generator reads out the table structure from the database, generates Entity and Dao types of Java files for each table, and places these files in the specified location. It is worth mentioning that the configuration method of Fluent Generator is completely codeable. Although MyBatis Generator supports pure-code configuration, using the XML file configuration in the official examples is still better.
The Dao type generated by the Fluent Generator is an empty class by default. It is only a recommended data query layer structure. By inheriting their respective BaseDao types, they can operate the Mapper conveniently.
The code for the Fluent MyBatis subproject is slightly abundant and divided into three modules:
The fluent-mybatis module defines annotations, data models, and other auxiliary types related to code generation. Most of them are like the heroes behind the scenes. Usually, developers do not directly use the classes in this package.
The fluent-mybatis-test module contains a wide range of test cases, which makes up for the incomplete documents of Fluent MyBatis in the current stage. If you cannot find solutions for problems with Fluent MyBatis in the documentation, fluent-mybatis-test might be a good library to check.
The fluent-mybatis-processor module works similarly to the Lombook Library. It does not modify the original type, but it scans annotations on the Entity type and dynamically generates new auxiliary classes. The Entity class produced by Fluent Generator is like Pandora's Box, which contains the secrets of Fluent MyBatis’s magic. The FluentMybatisProcessor class is the magician of the performance. It changes each entity class shaped like XyzEntity into a series of auxiliary classes. The key ones include:
A typical Fluent MyBatis workflow assembles the execution object based on the generated Query or Update type and then sends the object to the 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 update query statements
int effectedRecordCount = mapper.updateBy(
new StudentUpdate()
.set.userName().is("u2")
.set.isDeleted().is(true)
.set.homeAddressId().isNull().end()
.where.isDeleted().eq(false).end()
);The Query and Update types support the IQuery/IUpdate interface and the IWrapper interface. This is a fine design, as the former is used to assemble objects, and the latter is used to read the content of objects. Many methods in Mapper type can receive objects of the IQuery or IUpdate interface type and then forward the request through @InsertProvider, @SelectProvider, @UpdateProvider, or @DeleteProvider on the method to the generated Provider type. Providers take the passed-in IWrapper execution objects from the convention Map parameter, use the MapperSql tool class to assemble the SQL statement, and hand it over to MyBatis for execution.
There are also some methods in Mapper that directly accept Map objects, which can omit the process of describing SQL with IQuery/IUpdate for simple insertion and query. The imported original Map object will also be read from the Provider. After the SQL statement is assembled with MapperSql, it will be sent to MyBatis for execution.
This Provider-based implementation of Fluent MyBatis can provide users with a smooth SQL construction experience and fully reuse many native advantages of MyBatis, such as various DB connectors and a sound SQL injection prevention mechanism, to ensure the stability and reliability of the core logic.
It is natural for technicians to pursue excellence. I work for the Alibaba Cloud Apsara DevOps Product Team, and we are using Fluent MyBatis. If you are also tired of the lifeless XML files in MyBatis, you might as well say goodbye to them. Let’s embrace a more fluent experience with Fluent MyBatis.
2,599 posts | 764 followers
FollowAlibaba Developer - April 22, 2021
Alibaba Cloud Native Community - March 25, 2024
Frank Zhang - August 4, 2020
Alibaba Clouder - August 26, 2021
Alibaba Cloud Community - August 5, 2024
Alibaba Cloud Community - May 7, 2024
2,599 posts | 764 followers
Follow
ApsaraDB RDS for SQL Server
An on-demand database hosting service for SQL Server with automated monitoring, backup and disaster recovery capabilities
Learn More
Database for FinTech Solution
Leverage cloud-native database solutions dedicated for FinTech.
Learn More
Oracle Database Migration Solution
Migrate your legacy Oracle databases to Alibaba Cloud to save on long-term costs and take advantage of improved scalability, reliability, robust security, high performance, and cloud-native features.
Learn More
Database Migration Solution
Migrating to fully managed cloud databases brings a host of benefits including scalability, reliability, and cost efficiency.
Learn MoreMore Posts by Alibaba Clouder
Dikky Ryan Pratama May 9, 2023 at 5:47 am
Very inspiring!