之前文章介紹過(guò)了Fluent基本框架等，其中有幾個(gè)重要的方法用到了IQuery和IUpdate對(duì)象。 這2個(gè)對(duì)象是FluentMybatis實(shí)現(xiàn)復(fù)雜和動(dòng)態(tài)sql的構(gòu)造類，通過(guò)這2個(gè)對(duì)象fluent mybatis可以不用寫(xiě)具體的xml文件， 直接通過(guò)java api可以構(gòu)造出比較復(fù)雜的業(yè)務(wù)sql語(yǔ)句，做到代碼邏輯和sql邏輯的合一。下面接著介紹如何通過(guò)IQuery和IUpdate定義強(qiáng)大的動(dòng)態(tài)SQL語(yǔ)句。

表結(jié)構(gòu) 假如有學(xué)生成績(jī)表結(jié)構(gòu)如下:

create table `student_score`( id bigint auto_increment comment ’主鍵ID’ primary key, student_id bigint not null comment ’學(xué)號(hào)’, gender_man tinyint default 0 not null comment ’性別, 0:女; 1:男’, school_term int null comment ’學(xué)期’, subject varchar(30) null comment ’學(xué)科’, scoreint null comment ’成績(jī)’, gmt_create datetime not null comment ’記錄創(chuàng)建時(shí)間’, gmt_modified datetime not null comment ’記錄最后修改時(shí)間’, is_deleted tinyint default 0 not null comment ’邏輯刪除標(biāo)識(shí)’) engine = InnoDB default charset=utf8;

統(tǒng)計(jì)2000年到2019年, 三門學(xué)科(‘英語(yǔ)’, ‘?dāng)?shù)學(xué)’, ‘語(yǔ)文’)分?jǐn)?shù)按學(xué)期,學(xué)科統(tǒng)計(jì)最低分，最高分和平均分,統(tǒng)計(jì)結(jié)果按學(xué)期和學(xué)科排序SQL：

select school_term, subject, count(score), min(score), max(score), avg(score)from student_scorewhere school_term between 2000 and 2019 and subject in (’英語(yǔ)’, ’數(shù)學(xué)’, ’語(yǔ)文’) and is_deleted = 0group by school_term, subjectorder by school_term, subject 通過(guò)FluentMybatis來(lái)具體實(shí)現(xiàn) 在StudentScoreDao類上定義接口

@Datapublic 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<StudentScoreEntity> { /** * 統(tǒng)計(jì)從fromYear到endYear年間學(xué)科subjects的統(tǒng)計(jì)數(shù)據(jù) * * @param fromYear 統(tǒng)計(jì)年份區(qū)間開(kāi)始 * @param endYear 統(tǒng)計(jì)年份區(qū)間結(jié)尾 * @param subjects 統(tǒng)計(jì)的學(xué)科列表 * @return 統(tǒng)計(jì)數(shù)據(jù) */ List<ScoreStatistics> statistics(int fromYear, int endYear, String[] subjects);}

在StudentScoreDaoImpl上實(shí)現(xiàn)業(yè)務(wù)邏輯

@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實(shí)現(xiàn)中，除了根據(jù)條件返回統(tǒng)計(jì)結(jié)果，還講結(jié)果按照下劃線轉(zhuǎn)駝峰的規(guī)則自動(dòng)轉(zhuǎn)換為ScoreStatistics對(duì)象返回。 測(cè)試

@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[]{'語(yǔ)文', '數(shù)學(xué)', '英語(yǔ)'});System.out.println(list); }}

查看控制臺(tái)輸出結(jié)果：

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), 語(yǔ)文(String), 數(shù)學(xué)(String), 英語(yǔ)(String) DEBUG - <== Total: 30 [ScoreStatistics(schoolTerm=2000, subject=數(shù)學(xué), count=17, minScore=1, maxScore=93, avgScore=36.0588), ... ScoreStatistics(schoolTerm=2009, subject=語(yǔ)文, count=24, minScore=3, maxScore=100, avgScore=51.2500)]

到此這篇關(guān)于Fluent Mybatis實(shí)際開(kāi)發(fā)中的優(yōu)勢(shì)的文章就介紹到這了,更多相關(guān)Fluent Mybatis開(kāi)發(fā)內(nèi)容請(qǐng)搜索好吧啦網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持好吧啦網(wǎng)！