QueryDsl自定義傳回對象
- 寫在前面
- 一、自定義傳回對象
- 1.1、方式一
- 1.2、方式二
- 1.3、方式三
- 1.4、總結，這裡隻是幾種嘗試，可供參考，可能和QueryDsl版本依賴有關，此處目前測試，版本資訊
- 二、接收分頁，排序參數
- 三、引用Mysql函數
- 3.1、示例1
- 3.2、示例2
- 3.3、示例3
- 3.4、示例4
- 四、其他
寫在前面
記錄在queryDsl使用過程中的一些實作，關于JPA內建queryDsl，以及自定義存儲庫的問題，這裡就不詳細介紹了，主要總結下QueryDsl的一些使用問題，可參考我上文總結，連結這裡
一、自定義傳回對象
在我們內建queryDsl時，一般是這樣用的
@Override
public List<CityHotelVo> findcityHotel() {
JPAQuery<CityHotelVo> query = new JPAQuery<>(em);
QTCity c = QTCity.tCity;
QTHotel h = QTHotel.tHotel;
JPAQuery<Tuple> on = query.select(
c.id,
c.name,
h.name,
h.address).from(c).leftJoin(h).on(c.id.eq(h.city));
QueryResults<Tuple> rts = on.fetchResults();
List<Tuple> results = rts.getResults();
return results.stream().map(CityHotelVo::new).collect(Collectors.toList());
} 轉Vo實作
public CityHotelVo(Tuple t) {
this.id = t.get(QTCity.tCity.id);
this.cityName = t.get(QTCity.tCity.name);
this.hotelName = t.get(QTHotel.tHotel.name);
this.address = t.get(QTHotel.tHotel.address);
} 傳回的是一個List，我們還需将tuple手動轉成我們自定義的VO對象,以下總結了可自動Tuple轉VO的幾種實作。
1.1、方式一
/**
* 方式一：使用Bean投影
* todo 這裡暫未調通
* @return
*/
@Override
public List<CityHotelVo> findcityHotel_2() {
JPAQuery<CityHotelVo> query = new JPAQuery<>(em);
QTCity c = QTCity.tCity;
QTHotel h = QTHotel.tHotel;
List<CityHotelVo> results1 = query.select(Projections.bean(CityHotelVo.class,
c.id.as("id"),
c.name.as("cityName"),
h.name.as("hotelName"),
h.address.as("address"))).from(c).leftJoin(h).on(c.id.eq(h.city)).fetchResults().getResults();
return results1;
} 1.2、方式二
這種方式是可以的
/**
* 方式二 fields 投影
* todo 調試成功
* @return
*/
@Override
public List<CityHotelVo2> projectionsFields() {
JPAQuery<CityHotelVo> query = new JPAQuery<>(em);
QTCity c = QTCity.tCity;
QTHotel h = QTHotel.tHotel;
JPAQuery<CityHotelVo2> on = query.select(
Projections.fields(CityHotelVo2.class,
c.id,
c.name,
h.address))
.from(c).leftJoin(h).on(c.id.eq(h.city));
List<CityHotelVo2> resultList = on.createQuery().getResultList();
return resultList;
} 1.3、方式三
/**
* todo 成功測試
* 經測試，使用構造器方式可以映射
* @return
*/
@Override
public List<CityHotelVo2> findcityHotel_31() {
QTCity c = QTCity.tCity;
QTHotel h = QTHotel.tHotel;
JPAQueryFactory queryFactory = new JPAQueryFactory(em);
JPAQuery<CityHotelVo2> on = queryFactory.select(
Projections.constructor(CityHotelVo2.class,
c.id,
c.name,
h.address))
.from(c).leftJoin(h).on(c.id.eq(h.city));
List<CityHotelVo2> results = on.fetchResults().getResults();
return results;
} 注意這種構造器方式，隻支援對數值和String類型的映射處理，當你定義了Date等等類型，需要在構造函數中，構造如下
@Data
@Accessors(chain = true)
public class CityHotelVo4 implements Serializable {
private static final long serialVersionUID = 2546523L;
private Integer id;
private String cityName;
private String hotelName;
private String address;
private LocalDateTime formatTime;
public CityHotelVo4(Integer id, String cityName, String hotelName, String address, String formatTime) throws ParseException {
this.id = id;
this.cityName = cityName;
this.hotelName = hotelName;
this.address = address;
this.formatTime = DateUtils.parseLocalDateTime(formatTime);
}
} 1.4、總結，這裡隻是幾種嘗試，可供參考，可能和QueryDsl版本依賴有關，此處目前測試，版本資訊
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<java.version>1.8</java.version>
<springboot.start.version>2.0.8</springboot.start.version>
<queryDsl>4.1.4</queryDsl>
<queryslBuild>1.1.3</queryslBuild>
</properties> 二、接收分頁，排序參數
QueryDsl 查詢中并不支援前端傳參，後映射寫入sql 條件（order by ‘字段’ ‘排序規則’），如下
@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)
// .orderBy(new OrderSpecifier<>(Order.DESC,QTCity.tCity.id))
.orderBy(QTCity.tCity.id.asc()) // 隻能這樣寫死的
.offset(pageable.getOffset())
.limit(pageable.getPageSize());
return jpaQuery.fetchResults();
} 如何封裝，實作以下呢？
.orderBy(pageable.getProperty,pageable.getOrder()) 可以這樣做
@Override
public QueryResults<Tuple> findCityAndHotelPage2(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())
.offset(pageable.getOffset())
.limit(pageable.getPageSize());
PathBuilder<Entity> entityPath = new PathBuilder<>(Entity.class, "tCity");
for (Sort.Order order : pageable.getSort()) {
PathBuilder<Object> path = entityPath.get(order.getProperty());
jpaQuery.orderBy(new OrderSpecifier(com.querydsl.core.types.Order.valueOf(order.getDirection().name()), path));
}
return jpaQuery.fetchResults();
} 如圖
映射SQL為,正常列印
select tcity0_.id as col_0_0_,
thotel1_.id as col_1_0_,
thotel1_.id as id1_1_,
thotel1_.address as address2_1_,
thotel1_.city as city3_1_,
thotel1_.name as name4_1_
from t_city tcity0_
left outer join t_hotel thotel1_ on (cast(thotel1_.city as signed) = cast(tcity0_.id as signed))
order by tcity0_.name desc
limit ? 需要注意的是，new PathBuilder<>(Entity.class, “tCity”)，這個起别名的時候是需要注意的，可以先運作測試，再對此調整！！
三、引用Mysql函數
3.1、示例1
@GetMapping("/s11")
public ResultBean s11(CityHotelVo vo,
@RequestParam(defaultValue = "1") int page,
@RequestParam(defaultValue = "3") int rows,
@RequestParam(defaultValue = "id") String sidx,
@RequestParam(defaultValue = "asc") String sord) {
Pageable pageable = PageRequest.of(page - 1, rows, "desc".equals(sord) ? Sort.Direction.DESC : Sort.Direction.ASC, sidx);
BooleanBuilder builder = this.builder1(vo);
QTCity c = QTCity.tCity;
// 此處引入了内置的 Mysql 函數
StringTemplate dateExpr = Expressions.stringTemplate("DATE_FORMAT({0},'%Y-%m-%d')", c.cityDateTime);
builder.and(dateExpr.gt(vo.getStartTime().format(DateTimeFormatter.ofPattern("yyyy-MM-dd"))));
QueryResults<Tuple> results = tCityRepo.findCityAndHotelPage2(builder, pageable);
List<Tuple> list = results.getResults();
List<Vo1> collect = list.stream().map(Vo1::new).collect(Collectors.toList());
return ResultBean.ok(collect);
} select tcity0_.id as col_0_0_,
thotel1_.id as col_1_0_,
thotel1_.id as id1_1_,
thotel1_.address as address2_1_,
thotel1_.city as city3_1_,
thotel1_.hotel_date as hotel_da4_1_,
thotel1_.hotel_date_time as hotel_da5_1_,
thotel1_.name as name6_1_
from t_city tcity0_
left outer join t_hotel thotel1_ on (cast(thotel1_.city as signed) = cast(tcity0_.id as signed))
where date_format(tcity0_.city_date_time, '%Y-%m-%d') > ?
order by tcity0_.id desc
limit ? 3.2、示例2
@Override
public List<CityHotelVo4> dateFormat(CityHotelVo vo) {
JPAQueryFactory queryFactory = new JPAQueryFactory(em);
QTCity c = QTCity.tCity;
QTHotel h = QTHotel.tHotel;
StringTemplate dateFormat = Expressions.stringTemplate("DATE_FORMAT({0},'%Y-%m-%d')", c.cityDateTime);
// 拼接内置函數
JPAQuery<CityHotelVo4> on = queryFactory.select(
Projections.constructor(CityHotelVo4.class,
// todo 待調試
c.id.as("id"),
c.name.as("cityName"),
h.name.as("hotelName"),
h.address,
dateFormat.as("formatTime")
)
)
.from(c).leftJoin(h).on(c.id.eq(h.city));
List<CityHotelVo4> results = on.fetchResults().getResults();
return results;
} select tcity0_.id as col_0_0_,
tcity0_.name as col_1_0_,
thotel1_.name as col_2_0_,
thotel1_.address as col_3_0_,
date_format(tcity0_.city_date_time, '%Y-%m-%d') as col_4_0_
from t_city tcity0_
left outer join t_hotel thotel1_ on (tcity0_.id = thotel1_.city) ![Java String.format方法的簡單使用[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)