Comparing Fluent MyBatis, Native MyBatis, and MyBatis‑Plus for Complex Student Score Queries

Fluent MyBatis allows developers to avoid writing XML mapper files by constructing complex SQL statements through a Java API, thereby unifying business logic and SQL logic.

Scenario and Table Definition

We consider a typical business requirement on a student_score table with columns such as id , student_id , gender_man , school_term , subject , score , gmt_create , gmt_modified , and is_deleted :

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 business requirement is to calculate, for the years from 2000 onward, the count, minimum, maximum, and average scores of three subjects (English, Math, Chinese) per term, only when the sample size is greater than one, and to order the results by term and subject.

SQL Statement

select school_term,
       subject,
       count(score) as count,
       min(score)   as min_score,
       max(score)   as max_score,
       avg(score)   as avg_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;

Implementation with Fluent MyBatis

Fluent MyBatis provides a fluent API that can express the above query without XML. The author links to a demo repository (https://gitee.com/fluent-mybatis/fluent-mybatis-docs/tree/master/spring-boot-demo/) where the generated code can be inspected.

Implementation with Native MyBatis

Steps include defining a mapper interface, a parameter POJO, an XML mapper file, and a test class:

public interface MyStudentScoreMapper {
    List<Map<String, Object>> summaryScore(SummaryQuery paras);
}

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

<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 avg_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>

@RunWith(SpringRunner.class)
@SpringBootTest(classes = QuickStartApplication.class)
public class MybatisDemo {
    @Autowired
    private MyStudentScoreMapper mapper;

    @Test
    public void mybatis_demo() {
        SummaryQuery paras = new SummaryQuery()
            .setSchoolTerm(2000)
            .setSubjects(Arrays.asList("英语", "数学", "语文"))
            .setScore(60)
            .setMinCount(1);
        List<Map<String, Object>> summary = mapper.summaryScore(paras);
        System.out.println(summary);
    }
}

While functional, this approach requires multiple files and manual XML editing, making it relatively cumbersome.

Implementation with MyBatis‑Plus

MyBatis‑Plus reduces boilerplate but still relies heavily on string literals for column names. The author shows a code‑generation configuration that produces entity, mapper, and service classes automatically:

public class AppEntityGenerator {
    static final String url = "jdbc:mysql://localhost:3306/fluent_mybatis_demo?useSSL=false&useUnicode=true&characterEncoding=utf-8";
    @Tables(
        url = url, username = "root", password = "password",
        basePack = "cn.org.fluent.mybatis.springboot.demo",
        srcDir = "spring-boot-demo/src/main/java",
        daoDir = "spring-boot-demo/src/main/java",
        gmtCreated = "gmt_create", gmtModified = "gmt_modified", logicDeleted = "is_deleted",
        tables = @Table(value = {"student_score"})
    )
    static class Abc {}
}

public class CodeGenerator {
    static String dbUrl = "jdbc:mysql://localhost:3306/fluent_mybatis_demo?useSSL=false&useUnicode=true&characterEncoding=utf-8";
    @Test
    public void generateCode() {
        GlobalConfig config = new GlobalConfig();
        DataSourceConfig dataSourceConfig = new DataSourceConfig();
        dataSourceConfig.setDbType(DbType.MYSQL)
            .setUrl(dbUrl)
            .setUsername("root")
            .setPassword("password")
            .setDriverName(Driver.class.getName());
        StrategyConfig strategyConfig = new StrategyConfig();
        strategyConfig.setCapitalMode(true)
            .setEntityLombokModel(false)
            .setNaming(NamingStrategy.underline_to_camel)
            .setColumnNaming(NamingStrategy.underline_to_camel)
            .setEntityTableFieldAnnotationEnable(true)
            .setFieldPrefix(new String[]{"test_"})
            .setInclude(new String[]{"student_score"})
            .setLogicDeleteFieldName("is_deleted")
            .setTableFillList(Arrays.asList(
                new TableFill("gmt_create", FieldFill.INSERT),
                new TableFill("gmt_modified", FieldFill.INSERT_UPDATE)));
        config.setActiveRecord(false)
            .setIdType(IdType.AUTO)
            .setOutputDir(System.getProperty("user.dir") + "/src/main/java/")
            .setFileOverride(true);
        new AutoGenerator().setGlobalConfig(config)
            .setDataSource(dataSourceConfig)
            .setStrategy(strategyConfig)
            .setPackageInfo(new PackageConfig()
                .setParent("com.mp.demo")
                .setController("controller")
                .setEntity("entity"))
            .execute();
    }
}

MyBatis‑Plus still requires manual handling of column names, which can be error‑prone when the database schema changes.

Comparison Summary

After reviewing the three frameworks, the author concludes that Fluent MyBatis offers the most concise and type‑safe way to write complex queries without XML, while native MyBatis is the most verbose, and MyBatis‑Plus sits in the middle but suffers from hard‑coded string literals.

Overall, Fluent MyBatis provides better developer experience for query‑heavy applications, whereas MyBatis‑Plus may be preferable when rapid code generation is a priority.