Fluent Mybatis vs Mybatis vs Mybatis Plus: Which ORM Wins for Complex SQL?

Requirement Scenario

We use a typical student_score table structure to demonstrate how to implement a statistical query that counts, finds the minimum, maximum, and average scores for three subjects (English, Math, Chinese) per term, with a sample size greater than one.

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;

SQL requirement : "Statistics for the three subjects ('English', 'Math', 'Chinese') in the year 2000 and later, counting only scores >= 60, grouping by term and subject, and returning rows with a sample count greater than 1, ordered by term and subject."

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;

Implementation Comparison

Fluent Mybatis Implementation

Fluent Mybatis allows you to construct complex SQL statements directly through a Java API, eliminating the need for XML mapper files and reducing boilerplate code in DAO layers.

Native Mybatis Implementation

Define the Mapper interface.

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

Define the parameter class SummaryQuery.

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

Create the mapper XML with the SQL.

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

Write a test to invoke the mapper.

@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);
    }
}

Mybatis Plus Implementation

Mybatis Plus simplifies the code further but relies heavily on string literals for field names, which can cause maintenance issues when the database schema changes.

Code Generation Settings Comparison

Fluent Mybatis Code Generation

public class AppEntityGenerator {
    static final String url = "jdbc:mysql://localhost:3306/fluent_mybatis_demo?useSSL=false&useUnicode=true&characterEncoding=utf-8";

    public static void main(String[] args) {
        FileGenerator.build(Abc.class);
    }

    @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 {}
}

Mybatis Plus Code Generation

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();
    }
}

Fluent Mybatis Feature Overview

Comparison Summary

After reviewing the three frameworks for the same functional requirement, Fluent Mybatis offers the most concise API with strong IDE support, Mybatis Plus reduces boilerplate but introduces hard‑coded strings, and native Mybatis requires the most manual XML configuration. Choose the one that best fits your project's maintainability and productivity needs.