SpringDataJPA學(xué)習(xí)記錄(四)--使用QueryDSL
標(biāo)簽（空格分隔）： springJPA
1.問(wèn)題的提出
最近再看公司項(xiàng)目中有如下代碼,看了后簡(jiǎn)直不能忍.缺點(diǎn)列出來(lái)的話,如下:
- 返回類型Object[]數(shù)組,至于每一個(gè)下標(biāo)對(duì)應(yīng)哪個(gè)字段,沒(méi)法直觀的看到,例如object[11]是什么類型?字段名是什么?這個(gè)就無(wú)法直觀得知.
- sql中復(fù)雜的關(guān)系導(dǎo)致不可維護(hù),每一個(gè)接手的人都要研究sql半天
- 該種動(dòng)態(tài)拼接條件方法導(dǎo)致類似的代碼會(huì)大量重復(fù),所以IDEA打開的時(shí)候黃了半邊天.
- 該查詢?yōu)榉猪?yè)查詢,這樣寫的話,還要再copy一個(gè)count查詢才能拿到總數(shù),無(wú)疑又是代碼重復(fù).
- JPA這種框架目的就是少些原生sql語(yǔ)句,大量這樣的操作的話,還不如使用dbUtil這樣的工具類查詢.
@Override
public List<Object[]> findByPcardCardOrder(
PcardCardOrder pcardCardOrder,String applyInstName2,Integer page, Integer rows) {
StringBuffer sql = new StringBuffer(
"SELECT p.*"
+",p2.vcard_make_des"
+",p3.cardnum_rule_id,p3.vtype_nm"
+",p4.cn_card_bin,p4.cn_nm"
+",p5.inst_id,p5.inst_name,p5.apply_range,p5.card_name,p5.card_type,p5.bin_card_material"
+",p6.inst_name AS apply_inst_name "
+",p7.inst_name AS apply_inst_name2"
+ ",p8.inst_name as receive_inst_name"
+ " FROM "
+" tbl_pcard_card_order p LEFT JOIN tbl_pcard_vcard_make p2 ON p.make_id = p2.vcard_make_id"
+" LEFT JOIN tbl_pcard_vtype p3 ON p2.vcard_make_vtype_id=p3.vtype_id"
+" LEFT JOIN tbl_pcard_cardnum_rule p4 ON p3.cardnum_rule_id=p4.cn_id"
+" LEFT JOIN tbl_pcard_cardbin p5 ON p4.cn_card_bin=p5.card_bin"
+" LEFT JOIN tbl_pcard_institution p6 ON p5.apply_range=p6.inst_id"
+" LEFT JOIN tbl_pcard_institution p7 ON p.apply_inst_id=p7.inst_id"
+" LEFT JOIN tbl_pcard_institution p8 ON p.receive_inst=p8.inst_id"
+" WHERE 1=1 ");
int i = 1;
Map<String, Object> map = new HashMap<String, Object>();
if (!StringUtils.isEmpty(pcardCardOrder.getCordId())) {
sql.append(" and p.cord_id=");
sql.append("?" + i);
map.put(i + "", pcardCardOrder.getCordId());
i++;
}
if (!StringUtils.isEmpty(pcardCardOrder.getAppointMchtcard())) {
sql.append(" and p.appoint_mchtcard=");
sql.append("?" + i);
map.put(i + "", pcardCardOrder.getAppointMchtcard());
i++;
}
if (!StringUtils.isEmpty(pcardCardOrder.getMakeId())) {
sql.append(" and p.make_id like ");
sql.append("?" + i);
map.put(i + "","%%"+ pcardCardOrder.getMakeId()+"%%");
i++;
}
if (!StringUtils.isEmpty(applyInstName2)) {
sql.append(" and p7.inst_name like ");
sql.append("?"+i);
map.put(i+"","%%"+applyInstName2+"%%");
i++;
}
sql.append(" order by p.ct_dm desc");
Query query = entityManager.createNativeQuery(sql.toString());
for (String key : map.keySet()) {
query.setParameter(key, map.get(key));
}
if (page != null && rows != null) {
query.setFirstResult(rows * (page - 1));
query.setMaxResults(rows);
}
return query.getResultList();
}
2.學(xué)習(xí)QueryDSL
queryDSL就可以避免上面全部的問(wèn)題,在解決問(wèn)題之前先學(xué)習(xí)如何使用.
2.1 QueryDSL簡(jiǎn)介
- QueryDSL僅僅是一個(gè)通用的查詢框架，專注于通過(guò)Java API構(gòu)建類型安全的SQL查詢聋呢。
- Querydsl可以通過(guò)一組通用的查詢API為用戶構(gòu)建出適合不同類型ORM框架或者是SQL的查詢語(yǔ)句蜓耻，也就是說(shuō)QueryDSL是基于各種ORM框架以及SQL之上的一個(gè)通用的查詢框架互拾。
- 借助QueryDSL可以在任何支持的ORM框架或者SQL平臺(tái)上以一種通用的API方式來(lái)構(gòu)建查詢斟叼。目前QueryDSL支持的平臺(tái)包括JPA,JDO,SQL,Java Collections,RDF,Lucene,Hibernate Search丧凤。
- 官網(wǎng)地址:點(diǎn)擊進(jìn)入
2.2配置到項(xiàng)目
首先對(duì)于queryDSL有兩個(gè)版本,com.mysema.querydsl和com.querydsl,前者是3.X系列后者是4.X系列,這里使用的是后者.
第一步:Maven引入依賴:
<!--query dsl-->
<dependency>
<groupId>com.querydsl</groupId>
<artifactId>querydsl-jpa</artifactId>
<version>${querydsl.version}</version>
</dependency>
<dependency>
<groupId>com.querydsl</groupId>
<artifactId>querydsl-apt</artifactId>
<version>${querydsl.version}</version>
<scope>provided</scope>
</dependency>
<!--query dsl end-->
第二步:加入插件,用于生成查詢實(shí)例
<!--該插件可以生成querysdl需要的查詢對(duì)象利虫，執(zhí)行mvn compile即可-->
<plugin>
<groupId>com.mysema.maven</groupId>
<artifactId>apt-maven-plugin</artifactId>
<version>1.1.3</version>
<executions>
<execution>
<goals>
<goal>process</goal>
</goals>
<configuration>
<outputDirectory>target/generated-sources/java</outputDirectory>
<processor>com.querydsl.apt.jpa.JPAAnnotationProcessor</processor>
</configuration>
</execution>
</executions>
</plugin>
執(zhí)行mvn compile之后,可以找到該target/generated-sources/java,然后IDEA標(biāo)示為源代碼目錄即可.
1.jpg
2.3實(shí)體類
城市類:
@Entity
@Table(name = "t_city", schema = "test", catalog = "")
public class TCity {
//省略JPA注解標(biāo)識(shí)
private int id;
private String name;
private String state;
private String country;
private String map;
}
旅館類:
@Entity
@Table(name = "t_hotel", schema = "test", catalog = "")
public class THotel {
//省略JPA注解標(biāo)識(shí)
private int id;
private String name;
private String address;
private Integer city;//保存著城市的id主鍵
}
2.4 單表動(dòng)態(tài)分頁(yè)查詢
Spring Data JPA中提供了QueryDslPredicateExecutor接口,用于支持QueryDSL的查詢操作,這樣的話單表動(dòng)態(tài)查詢就可以參考如下代碼:
//查找出Id小于3,并且名稱帶有`shanghai`的記錄.
//動(dòng)態(tài)條件
QTCity qtCity = QTCity.tCity;
//該P(yáng)redicate為querydsl下的類,支持嵌套組裝復(fù)雜查詢條件
Predicate predicate = qtCity.id.longValue().lt(3)
.and(qtCity.name.like("shanghai"));
//分頁(yè)排序
Sort sort = new Sort(new Sort.Order(Sort.Direction.ASC,"id"));
PageRequest pageRequest = new PageRequest(0,10,sort);
//查找結(jié)果
Page<TCity> tCityPage = tCityRepository.findAll(predicate,pageRequest);
2.5多表動(dòng)態(tài)查詢
QueryDSL對(duì)多表查詢提供了一個(gè)很好地封裝,看下面代碼:
/**
* 關(guān)聯(lián)查詢示例,查詢出城市和對(duì)應(yīng)的旅店
* @param predicate 查詢條件
* @return 查詢實(shí)體
*/
@Override
public List<Tuple> findCityAndHotel(Predicate predicate) {
JPAQueryFactory queryFactory = new JPAQueryFactory(em);
JPAQuery<Tuple> jpaQuery = queryFactory.select(QTCity.tCity,QTHotel.tHotel)
.from(QTCity.tCity)
.leftJoin(QTHotel.tHotel)
.on(QTHotel.tHotel.city.longValue().eq(QTCity.tCity.id.longValue()));
//添加查詢條件
jpaQuery.where(predicate);
//拿到結(jié)果
return jpaQuery.fetch();
}
城市表左連接旅店表,當(dāng)該旅店屬于這個(gè)城市時(shí)查詢出兩者的詳細(xì)字段,存放到一個(gè)Tuple的多元組中.相比原生sql,簡(jiǎn)單清晰了很多.
那么該怎么調(diào)用這個(gè)方法呢?
@Test
public void findByLeftJoin(){
QTCity qtCity = QTCity.tCity;
QTHotel qtHotel = QTHotel.tHotel;
//查詢條件
Predicate predicate = qtCity.name.like("shanghai");
//調(diào)用
List<Tuple> result = tCityRepository.findCityAndHotel(predicate);
//對(duì)多元組取出數(shù)據(jù),這個(gè)和select時(shí)的數(shù)據(jù)相匹配
for (Tuple row : result) {
System.out.println("qtCity:"+row.get(qtCity));
System.out.println("qtHotel:"+row.get(qtHotel));
System.out.println("--------------------");
}
System.out.println(result);
}
這樣做的話避免了返回Object[]數(shù)組,下面是自動(dòng)生成的sql語(yǔ)句:
select
tcity0_.id as id1_0_0_,
thotel1_.id as id1_1_1_,
tcity0_.country as country2_0_0_,
tcity0_.map as map3_0_0_,
tcity0_.name as name4_0_0_,
tcity0_.state as state5_0_0_,
thotel1_.address as address2_1_1_,
thotel1_.city as city3_1_1_,
thotel1_.name as name4_1_1_
from
t_city tcity0_
left outer join
t_hotel thotel1_
on (
cast(thotel1_.city as signed)=cast(tcity0_.id as signed)
)
where
tcity0_.name like ? escape '!'
2.6 多表動(dòng)態(tài)分頁(yè)查詢
分頁(yè)查詢對(duì)于queryDSL無(wú)論什么樣的sql只需要寫一遍,會(huì)自動(dòng)轉(zhuǎn)換為相應(yīng)的count查詢,也就避免了文章開始的問(wèn)題4,下面代碼是對(duì)上面的查詢加上分頁(yè)功能:
@Override
public QueryResults<Tuple> findCityAndHotelPage(Predicate predicate,Pageable pageable) {
JPAQueryFactory queryFactory = new JPAQueryFactory(em);
JPAQuery<Tuple> jpaQuery = queryFactory.select(QTCity.tCity.id,QTHotel.tHotel)
.from(QTCity.tCity)
.leftJoin(QTHotel.tHotel)
.on(QTHotel.tHotel.city.longValue().eq(QTCity.tCity.id.longValue()))
.where(predicate)
.offset(pageable.getOffset())
.limit(pageable.getPageSize());
//拿到分頁(yè)結(jié)果
return jpaQuery.fetchResults();
}
和上面不同之處在于這里使用了offset和limit限制查詢結(jié)果.并且返回一個(gè)QueryResults,該類會(huì)自動(dòng)實(shí)現(xiàn)count查詢和結(jié)果查詢,并進(jìn)行封裝.
調(diào)用形式如下:
@Test
public void findByLeftJoinPage(){
QTCity qtCity = QTCity.tCity;
QTHotel qtHotel = QTHotel.tHotel;
//條件
Predicate predicate = qtCity.name.like("shanghai");
//分頁(yè)
PageRequest pageRequest = new PageRequest(0,10);
//調(diào)用查詢
QueryResults<Tuple> result = tCityRepository.findCityAndHotelPage(predicate,pageRequest);
//結(jié)果取出
for (Tuple row : result.getResults()) {
System.out.println("qtCity:"+row.get(qtCity));
System.out.println("qtHotel:"+row.get(qtHotel));
System.out.println("--------------------");
}
//取出count查詢總數(shù)
System.out.println(result.getTotal());
}
生成的原生count查詢sql,當(dāng)該count查詢結(jié)果為0的話,則直接返回,并不會(huì)再進(jìn)行具體數(shù)據(jù)查詢:
select
count(tcity0_.id) as col_0_0_
from
t_city tcity0_
left outer join
t_hotel thotel1_
on (
cast(thotel1_.city as signed)=cast(tcity0_.id as signed)
)
where
tcity0_.name like ? escape '!'
生成的原生查詢sql:
select
tcity0_.id as id1_0_0_,
thotel1_.id as id1_1_1_,
tcity0_.country as country2_0_0_,
tcity0_.map as map3_0_0_,
tcity0_.name as name4_0_0_,
tcity0_.state as state5_0_0_,
thotel1_.address as address2_1_1_,
thotel1_.city as city3_1_1_,
thotel1_.name as name4_1_1_
from
t_city tcity0_
left outer join
t_hotel thotel1_
on (
cast(thotel1_.city as signed)=cast(tcity0_.id as signed)
)
where
tcity0_.name like ? escape '!' limit ?
查看打印,可以發(fā)現(xiàn)對(duì)應(yīng)的city也都是同一個(gè)對(duì)象,hotel是不同的對(duì)象.
Paste_Image.png
3.改造
有了上面的經(jīng)驗(yàn),改造就變得相當(dāng)容易了.
首先前面的一堆sql可以寫成如下形式,無(wú)非是多了一些select和left join
JPAQueryFactory factory = new JPAQueryFactory(entityManager);
factory.select($.pcardCardOrder)
.select($.pcardVcardMake.vcardMakeDes)
.select($.pcardVtype.cardnumRuleId,$.pcardVtype.vtypeNm)
.select($.pcardCardbin)
.leftJoin($.pcardVcardMake).on($.pcardCardOrder.makeId.eq($.pcardVcardMake.vcardMakeId))
//......省略
查詢條件使用Predicate代替,放在service拼接,或者寫一個(gè)生產(chǎn)條件的工廠都可以.
jpaQuery.where(predicate);
最后的分頁(yè)處理就和之前的一樣了
jpaQuery.offset(pageable.getOffset())
.limit(pageable.getPageSize());
return jpaQuery.fetchResults();
個(gè)人感覺Query DSL和Spring Data JPA是絕配.更多請(qǐng)參考Demo代碼:
