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=)