Fluent Mybatis实际开发中的优势对比

Fluent Mybatis实际开发中的优势对比

之前文章介绍过了Fluent基本框架等，其中有几个重要的方法用到了IQuery和IUpdate对象。 这2个对象是FluentMybatis实现复杂和动态sql的构造类，通过这2个对象fluent mybatis可以不用写具体的xml文件， 直接通过java api可以构造出比较复杂的业务sql语句，做到代码逻辑和sql逻辑的合一。下面接着介绍如何通过IQuery和IUpdate定义强大的动态SQL语句。

表结构 假如有学生成绩表结构如下:

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;

统计2000年到2019年, 三门学科(‘英语', ‘数学', ‘语文')分数按学期,学科统计最低分，最高分和平均分,统计结果按学期和学科排序

SQL：

select school_term, subject, count(score), min(score), max(score), avg(score)

frohttp://m student_score

where school_term between 2000 and 2019

and subject in ('英语', '数学', '语文')

and is_deleted = 0

group by school_term, subject

order by school_term, subject

通过FluentMybatis来具体实现

在StudentScoreDao类上定义接口

@Data

public class ScoreStatistics {

private int schoolTerm;

private String subject;

private long count;

private Integer minScore;

private Integer maxScore;

private BigDecimal avgScore;

}

public interface StudentScoreDao extends IBaseDao {

/**

* 统计从fromYear到endYear年间学科subjects的统计数据

*

* @param fromYear 统计年份区间开始

* @param endYear 统计年份区间结尾

* @param subjects 统计的学科列表

* @return 统计数据

*/

List statistics(int fromYear, int endYear, String[] subjects);

}

在StudentScoreDaoImpl上实现业务逻辑

@Repository

public class StudentScoreDaoImpl extends StudentScoreBaseDao implements StudentScoreDao {

@OveuMiavDkBrride

public List statistics(int fromSchoolTerm, int endSchoolTerm, String[] subjects) {

return super.listPoJos(ScoreStatistics.class, super.query()

.select.schoolTerm().subject()

.count("count")

.min.score("min_score")

.max.score("max_score")

.avg.score("avg_score")

.end()

.where.isDeleted().isFalse()

.and.schoolTerm().between(fromSchoolTerm,http:// endSchoolTerm)

.and.subject().in(subjects)

.end()

.groupBy.schoolTerm().subject().end()

.orderBy.schoolTerm().asc().subject().asc().end()

);

}

}

DaoImpl实现中，除了根据条件返回统计结果，还讲结果按照下划线转驼峰的规则自动转换为ScoreStatistics对象返回。

测试

@RunWith(SpringRunner.class)

@SpringBootTest(classes = QuickStartApplication.class)

public class StudentScoreDaoImplTest {

@Autowired

private StudentScoreDao dao;

@Test

public void statistics() {

List list = dao.statistics(2000, 2019, new String[]{"语文", "数学", "英语"});

System.out.println(list);

}

}

查看控制台输出结果：

DEBUG - ==>  Preparing: SELECT school_term, subject, count(*) AS count, MIN(score) AS min_score, MAX(score) AS max_score, AVG(score) AS avg_score

    FROM student_score

    WHERE is_deleted = ?

    AND school_term BETWEEN ? AND ?

    AND subject IN (?, ?, ?)

    GROUP BY school_term, subject

    ORDER BY school_term ASC, subject ASC 

DEBUG - ==> Parameters: false(Boolean), 2000(Integer), 2019(Integer), 语文(String), 数学(String), 英语(String)

DEBUG - <==      Total: 30

[ScoreStatistics(schoolTerm=2000, subject=数学, count=17, minScore=1, maxScore=93, avgScore=36.0588),

 ...

 ScoreStatistics(schoolTerm=2009, subject=语文, count=24, minScore=3, maxScore=100, avgScore=51.2500)]

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。