Comparative Implementation of a Student Score Statistics Query Using Fluent MyBatis, Native MyBatis, and MyBatis‑Plus

The author introduces Fluent MyBatis, a Java API that lets developers build complex SQL statements without writing XML, achieving a tighter integration of code and SQL logic.

Requirement scenario : A student_score table is defined (DDL shown below). The business requirement is to compute, for the year 2000 and the three subjects "English", "Math", and "Chinese", the count, minimum, maximum, and average scores per term and subject, ensuring each group has more than one record, and ordering the results by term and subject.

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 not 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 SQL query is:

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 : The article links to a demo project and shows how the Fluent API can express the same query, with IDE assistance and without XML configuration.

Implementation with native MyBatis : The steps include defining a Mapper interface, a parameter class, the XML statement, and a JUnit test that builds the query parameters and invokes the 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;
}

<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() {
        // Construct query parameters
        SummaryQuery paras = new SummaryQuery()
            .setSchoolTerm(2000)
            .setSubjects(Arrays.asList("英语", "数学", "语文"))
            .setScore(60)
            .setMinCount(1);

        List
> summary = mapper.summaryScore(paras);
        System.out.println(summary);
    }
}

Implementation with MyBatis‑Plus : The article notes that MyBatis‑Plus reduces boilerplate code but still relies on string literals for column names, which can increase maintenance difficulty.

Code generation comparison : Configuration classes for generating entity and DAO code with Fluent MyBatis and with MyBatis‑Plus are shown, highlighting differences such as table prefixes, logic‑delete fields, naming strategies, and output directories.

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

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

Feature overview : An image (not reproduced) lists the capabilities of Fluent MyBatis, such as type‑safe query building, automatic code generation, and seamless integration with Spring Boot.

Comparison summary : A final table (image) evaluates the three frameworks on criteria like code simplicity, configuration effort, runtime safety, and IDE support, helping readers decide which tool fits their project.

Source: The original article is published on juejin.cn (https://juejin.cn/post/6886019929519177735).