QueryDSL-JPA

QueryDSL簡介

1 QueryDSL僅僅是一個通用的查詢框架，專注于通過Java API構建類型安全的SQL查詢。

2 Querydsl可以通過一組通用的查詢API為用戶構建出適合不同類型ORM框架或者是SQL的查詢語句，也就是說QueryDSL是基于各種ORM框架以及SQL之上的一個通用的查詢框架。

3 借助QueryDSL可以在任何支持的ORM框架或者SQL平臺上以一種通用的API方式來構建查詢。目前QueryDSL支持的平臺包括JPA,JDO,SQL,Java Collections,RDF,Lucene,Hibernate Search。

創建項目

首先對于queryDSL有兩個版本,com.mysema.querydsl和com.querydsl,前者是3.X系列后者是4.X系列,這里使用的是后者.

第一步:Maven引入依賴

com.querydsl

querydsl-jpa

${querydsl.version}

com.querydsl

querydsl-apt

${querydsl.version}

provided

第二步:加入插件,用于生成查詢實例

com.mysema.maven

apt-maven-plugin

1.1.3

process

target/generated-sources/java

com.querydsl.apt.jpa.JPAAnnotationProcessor

執行mvn compile之后,可以找到該target/generated-sources/java,然后IDEA標示為源代碼目錄即可.

實體類

城市類:

@Entity

@Table(name = "t_city", schema = "test", catalog = "")

public class TCity {

//省略JPA注解標識

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注解標識

private int id;

private String name;

private String address;

private Integer city;//保存著城市的id主鍵

}

單表動態分頁查詢

Spring Data JPA中提供了QueryDslPredicateExecutor接口,用于支持QueryDSL的查詢操作,這樣的話單表動態查詢就可以參考如下代碼:

//查找出Id小于3,并且名稱帶有`shanghai`的記錄.

//動態條件

QTCity qtCity = QTCity.tCity;

//該Predicate為querydsl下的類,支持嵌套組裝復雜查詢條件

Predicate predicate = qtCity.id.longValue().lt(3)

.and(qtCity.name.like("shanghai"));

//分頁排序

Sort sort = new Sort(new Sort.Order(Sort.Direction.ASC,"id"));

PageRequest pageRequest = new PageRequest(0,10,sort);

//查找結果

Page tCityPage = tCityRepository.findAll(predicate,pageRequest);

多表動態查詢

QueryDSL對多表查詢提供了一個很好地封裝,看下面代碼:

/**

* 關聯查詢示例,查詢出城市和對應的旅店

* @param predicate 查詢條件

* @return 查詢實體

*/

@Override

public List findCityAndHotel(Predicate predicate) {

JPAQueryFactory queryFactory = new JPAQueryFactory(em);

JPAQuery 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);

//拿到結果

return jpaQuery.fetch();

}

城市表左連接旅店表,當該旅店屬于這個城市時查詢出兩者的詳細字段,存放到一個Tuple的多元組中.相比原生sql,簡單清晰了很多.

那么該怎么調用這個方法呢?

@Test

public void findByLeftJoin(){

QTCity qtCity = QTCity.tCity;

QTHotel qtHotel = QTHotel.tHotel;

//查詢條件

Predicate predicate = qtCity.name.like("shanghai");

//調用

List result = tCityRepository.findCityAndHotel(predicate);

//對多元組取出數據,這個和select時的數據相匹配

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[]數組,下面是自動生成的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 '!'

多表動態分頁查詢

分頁查詢對于queryDSL無論什么樣的sql只需要寫一遍,會自動轉換為相應的count查詢,也就避免了文章開始的問題4,下面代碼是對上面的查詢加上分頁功能:

@Override

public QueryResults findCityAndHotelPage(Predicate predicate,Pageable pageable) {

JPAQueryFactory queryFactory = new JPAQueryFactory(em);

JPAQuery 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());

//拿到分頁結果

return jpaQuery.fetchResults();

}

和上面不同之處在于這里使用了offset和limit限制查詢結果.并且返回一個QueryResults,該類會自動實現count查詢和結果查詢,并進行封裝.

調用形式如下:

@Test

public void findByLeftJoinPage(){

QTCity qtCity = QTCity.tCity;

QTHotel qtHotel = QTHotel.tHotel;

//條件

Predicate predicate = qtCity.name.like("shanghai");

//分頁

PageRequest pageRequest = new PageRequest(0,10);

//調用查詢

QueryResults result = tCityRepository.findCityAndHotelPage(predicate,pageRequest);

//結果取出

for (Tuple row : result.getResults()) {

System.out.println("qtCity:"+row.get(qtCity));

System.out.println("qtHotel:"+row.get(qtHotel));

System.out.println("--------------------");

}

//取出count查詢總數

System.out.println(result.getTotal());

}

生成的原生count查詢sql,當該count查詢結果為0的話,則直接返回,并不會再進行具體數據查詢:

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 ?

查看打印,可以發現對應的city也都是同一個對象,hotel是不同的對象.

總結

以上是生活随笔為你收集整理的querydsl动态 sql_QueryDSL-JPA的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。