之前文章介绍过了Fluent基本框架等，其中有几个重要的方法用到了IQuery和IUpdate对象。 这2个对象是FluentMybatis实现复杂和动态sql的构造类，通过这2个对象fluent mybatis可以不用写具体的xml文件， 直接通过java api可以构造出比较复杂的业务sql语句，做到代码逻辑和sql逻辑的合一。下面接着介绍如何通过IQuery和IUpdate定义强大的动态SQL语句。
表结构 假如有学生成绩表结构如下:
|
1
2
3
4
5
6
7
8
9
10
11
12
|
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：
|
1
2
3
4
5
6
7
|
select school_term, subject, count(score), min(score), max(score), avg(score)from student_scorewhere school_term between 2000 and 2019 and subject in ('英语', '数学', '语文') and is_deleted = 0group by school_term, subjectorder by school_term, subject |
- 通过FluentMybatis来具体实现
- 在StudentScoreDao类上定义接口
|
1
2
3
4
5
6
7
8
9
|
@Datapublic class ScoreStatistics { private int schoolTerm; private String subject; private long count; private Integer minScore; private Integer maxScore; private BigDecimal avgScore;} |
|
1
2
3
4
5
6
7
8
9
10
11
|
public interface StudentScoreDao extends IBaseDao<StudentScoreEntity> { /** * 统计从fromYear到endYear年间学科subjects的统计数据 * * @param fromYear 统计年份区间开始 * @param endYear 统计年份区间结尾 * @param subjects 统计的学科列表 * @return 统计数据 */ List<ScoreStatistics> statistics(int fromYear, int endYear, String[] subjects);} |
在StudentScoreDaoImpl上实现业务逻辑
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
@Repositorypublic class StudentScoreDaoImpl extends StudentScoreBaseDao implements StudentScoreDao { @Override public List<ScoreStatistics> 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, endSchoolTerm) .and.subject().in(subjects) .end() .groupBy.schoolTerm().subject().end() .orderBy.schoolTerm().asc().subject().asc().end() ); }} |
- DaoImpl实现中，除了根据条件返回统计结果，还讲结果按照下划线转驼峰的规则自动转换为ScoreStatistics对象返回。
- 测试
|
1
2
3
4
5
6
7
8
9
10
11
12
|
@RunWith(SpringRunner.class)@SpringBootTest(classes = QuickStartApplication.class)public class StudentScoreDaoImplTest { @Autowired private StudentScoreDao dao; @Test public void statistics() { List<ScoreStatistics> 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)]
到此这篇关于Fluent Mybatis实际开发中的优势的文章就介绍到这了,更多相关Fluent Mybatis开发内容请搜索服务器之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持服务器之家！
原文链接：https://blog.csdn.net/qq_27933251/article/details/115378245
