Comparing Fluent MyBatis, MyBatis, and MyBatis‑Plus for Complex SQL Queries

The article introduces three ways to implement a statistical query on a student_score table that calculates count, minimum, maximum, and average scores for three subjects (English, Math, Chinese) per term, with sample size greater than one, and orders the results by term and subject.

First, the raw SQL statement 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;

Then the article shows how to achieve the same functionality with Fluent MyBatis, native MyBatis, and MyBatis‑Plus.

Fluent MyBatis implementation

Using the Fluent API, the query can be built directly in Java without XML mapping, keeping business logic and SQL logic together. The article includes a screenshot of the IDE rendering and a brief code snippet for generating the entity classes:

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

Native MyBatis implementation

The traditional approach requires a Mapper interface, a parameter POJO, an XML mapper file, and a test class:

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 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
#{item}
and score >= #{score}
      and is_deleted = 0
    group by school_term, subject
    having count(score) > #{minCount}
    order by school_term, subject
@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);
    }
}

MyBatis‑Plus implementation

MyBatis‑Plus simplifies the code considerably. The article shows a code‑generation configuration class that sets up global, data source, and strategy configurations, then runs the generator:

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 and Comparison Summary

The article provides a feature matrix image for Fluent MyBatis and a final comparison chart that highlights the trade‑offs: Fluent MyBatis offers strong IDE support and unified code‑SQL logic but requires learning its API; MyBatis is verbose and error‑prone; MyBatis‑Plus reduces boilerplate but still relies on string‑based mappings.

In conclusion, the author encourages readers to explore the official source code of Fluent MyBatis for deeper understanding and invites them to join a community group for further discussion.