之前文章介紹過了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
