QueryDsl⾃定义返回对象，接收分页排序参数，引⽤Mysql函数等⽰例QueryDsl⾃定义返回对象
写在前⾯
记录在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.lect(
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 = Results();
return results.stream().map(CityHotelVo::new).List());
}
转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.lect(Projections.bean(CityHotelVo.class,
c.i
d.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.lect(
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.lect(
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 rialVersionUID =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 ParException {
this.id = id;
this.cityName = cityName;
this.hotelName = hotelName;
this.address = address;
this.formatTime = DateUtils.parLocalDateTime(formatTime);
}
}
1.4、总结，这⾥只是⼏种尝试，可供参考，可能和QueryDsl版本依赖有关，此处当前测试，版本信息
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
&porting.outputEncoding>UTF-8</porting.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.lect(
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())// 只能这样写死的
.Offt())
骗术大全.PageSize());
return jpaQuery.fetchResults();
}
如何封装，实现以下呢？
.
Order())
可以这样做
@Override
public QueryResults<Tuple>findCityAndHotelPage2(Predicate predicate, Pageable pageable){
JPAQueryFactory queryFactory =new JPAQueryFactory(em);
JPAQuery<Tuple> jpaQuery = queryFactory.lect(
QTCity.tCity.id,
QTHotel.tHotel).from(QTCity.tCity)
.leftJoin(QTHotel.tHotel)
.on(QTHotel.tHotel.city.longValue().eq(QTCity.tCity.id.longValue()))
.where(predicate)
.
Offt())
.Offt())
.PageSize());
PathBuilder<Entity> entityPath =new PathBuilder<>(Entity.class,"tCity");
for(Sort.Order order : Sort()){
PathBuilder<Object> path = (Property());
}
return jpaQuery.fetchResults();
}
羊肉怎么煮好吃如图
映射SQL为，正常打印
lect 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.(vo.getStartTime().format(DateTimeFormatter.ofPattern("yyyy-MM-dd"))));
QueryResults<Tuple> results = tCityRepo.findCityAndHotelPage2(builder, pageable);
List<Tuple> list = Results();
List<Vo1> collect = list.stream().map(Vo1::new).List());
return ResultBean.ok(collect);
}
SQL 映射
lect 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.lect(
// todo 待调试
c.i
d.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;
}
SQL 映射
