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

The article introduces the use of Fluent Mybatis, which allows building complex SQL statements via Java APIs without writing XML, and asks what conveniences it provides compared to native Mybatis and Mybatis Plus.

It defines a typical business scenario: a student_score table with fields such as id, student_id, gender_man, school_term, subject, score, gmt_create, gmt_modified, and is_deleted.

The required query is to count, find the minimum, maximum, and average scores for the three subjects ('English', 'Math', 'Chinese') in each term from the year 2000 onward, only including records with scores >= 60, logical deletion flag = 0, and with at least two records per group, ordered by term and subject.

The SQL statement for this requirement is:

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;

Fluent Mybatis implementation shows how the same query can be expressed using the Fluent Mybatis API, with IDE rendering of the generated code.

Native Mybatis implementation includes:

Mapper interface definition:

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

Parameter entity SummaryQuery :

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

Mapper XML with dynamic foreach for subjects and placeholders for parameters.

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

A JUnit test demonstrates constructing SummaryQuery and invoking 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
> summary = mapper.summaryScore(paras);
        System.out.println(summary);
    }
}

The article then discusses the drawbacks of Mybatis Plus, such as heavy string hard‑coding, and shows a comparison with native Mybatis.

Mybatis Plus implementation is noted to be simpler in code but still relies on string literals for field names, which can cause maintenance issues.

Code generation settings are provided for both Fluent Mybatis and Mybatis Plus:

Fluent Mybatis generator example:

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 generator example:

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

Finally, the article lists Fluent Mybatis features, provides a visual comparison table of the three frameworks, and concludes that while each ORM can achieve the same functionality, Fluent Mybatis offers tighter integration of Java code and SQL, whereas Mybatis Plus reduces boilerplate but introduces hard‑coded strings.

Readers are encouraged to explore the Fluent Mybatis source code for deeper insights.