Using Fluent MyBatis for Complex Queries and Comparing It with MyBatis and MyBatis‑Plus

The author, a senior architect, introduces Fluent MyBatis as a way to construct complex business SQL statements via Java APIs without writing XML mapper files, aiming to unify code and SQL logic.

First, a sample student_score table schema is presented, followed by a concrete reporting requirement: for the year 2000, calculate the count, minimum, maximum, and average scores of three subjects (English, Math, Chinese) per term, with at least two records, and sort the results.

create table `student_score` (
    id bigint auto_increment comment '主键ID' primary key,
    student_id bigint not null comment '学号',
    gender_man tinyint default 0 not null comment '性别, 0:女; 1:男',
    school_term int null comment '学期',
    subject varchar(30) null comment '学科',
    score int null comment '成绩',
    gmt_create datetime not null comment '记录创建时间',
    gmt_modified datetime not null comment '记录最后修改时间',
    is_deleted tinyint default 0 not null comment '逻辑删除标识'
) engine=InnoDB default charset=utf8;

The corresponding raw SQL query is shown:

select school_term,
       subject,
       count(score) as count,
       min(score) as min_score,
       max(score) as max_score,
       avg(score) as max_score
from student_score
where school_term >= 2000
  and subject in ('英语','数学','语文')
  and score >= 60
  and is_deleted = 0
group by school_term, subject
having count(score) > 1
order by school_term, subject;

Three implementations are then provided:

Fluent MyBatis

Using the fluent API, the same query is built programmatically, eliminating XML and reducing boilerplate. Sample Java code creates a SummaryQuery DTO, defines a mapper interface, and executes the query via the generated mapper.

public interface MyStudentScoreMapper {
    List
> summaryScore(SummaryQuery paras);
}

@Data @Accessors(chain = true)
public class SummaryQuery {
    private Integer schoolTerm;
    private List
subjects;
    private Integer score;
    private Integer minCount;
}

SummaryQuery paras = new SummaryQuery()
    .setSchoolTerm(2000)
    .setSubjects(Arrays.asList("英语","数学","语文"))
    .setScore(60)
    .setMinCount(1);
List
> summary = mapper.summaryScore(paras);

Native MyBatis (XML)

The traditional approach requires a mapper XML file with a <select> element, explicit foreach for the subject list, and manual result mapping.

<select id="summaryScore" resultType="map" parameterType="cn.org.fluent.mybatis.springboot.demo.mapper.SummaryQuery">
    select school_term, subject, count(score) as count,
           min(score) as min_score, max(score) as max_score, avg(score) as max_score
    from student_score
    where school_term >= #{schoolTerm}
      and subject in
      <foreach collection="subjects" item="item" open="(" close=")" separator=",">
          #{item}
      </foreach>
      and score >= #{score}
      and is_deleted = 0
    group by school_term, subject
    having count(score) > #{minCount}
    order by school_term, subject
</select>

MyBatis‑Plus

MyBatis‑Plus simplifies configuration but still relies on string‑based SQL fragments; the author notes extra hard‑coded strings and potential maintenance issues.

GlobalConfig config = new GlobalConfig();
DataSourceConfig ds = new DataSourceConfig();
// set DB connection, username, password, driver
StrategyConfig strategy = new StrategyConfig();
strategy.setNaming(NamingStrategy.underline_to_camel)
        .setInclude("student_score")
        .setLogicDeleteFieldName("is_deleted");
new AutoGenerator()
    .setGlobalConfig(config)
    .setDataSource(ds)
    .setStrategy(strategy)
    .setPackageInfo(new PackageConfig().setParent("com.mp.demo"))
    .execute();

After presenting the three approaches, the author summarizes the trade‑offs: Fluent MyBatis offers tighter integration of Java code and SQL with IDE assistance; native MyBatis is verbose and error‑prone; MyBatis‑Plus reduces boilerplate but introduces hard‑coded strings and can be less flexible.

Additional sections show code‑generation configuration for both Fluent MyBatis and MyBatis‑Plus, visual comparison tables, and a concluding recommendation to explore Fluent MyBatis source for deeper understanding.