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.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);
}
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_.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;
}
SQL 映射
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)
3.3、示例3
3.4、示例4