QueryDsl自定义返回对象,接收分页排序参数,引用Mysql函数等示例
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 findcityHotel() {
JPAQuery query = new JPAQuery<>(em);
QTCity c = QTCity.tCity;
QTHotel h = QTHotel.tHotel;
JPAQuery on = query.select(
c.id,
c.name,
h.name,
h.address).from(c).leftJoin(h).on(c.id.eq(h.city));
QueryResults rts = on.fetchResults();
List 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 findcityHotel_2() {
JPAQuery query = new JPAQuery<>(em);
QTCity c = QTCity.tCity;
QTHotel h = QTHotel.tHotel;
List 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 projectionsFields() {
JPAQuery query = new JPAQuery<>(em);
QTCity c = QTCity.tCity;
QTHotel h = QTHotel.tHotel;
JPAQuery on = query.select(
Projections.fields(CityHotelVo2.class,
c.id,
c.name,
h.address))
.from(c).leftJoin(h).on(c.id.eq(h.city));
List resultList = on.createQuery().getResultList();
return resultList;
}
1.3、方式三
/**
* todo 成功测试
*经测试,使用构造器方式可以映射
* @return
*/
@Override
public List findcityHotel_31() {
QTCity c = QTCity.tCity;
QTHotel h = QTHotel.tHotel;
JPAQueryFactory queryFactory = new JPAQueryFactory(em);
JPAQuery on = queryFactory.select(
Projections.constructor(CityHotelVo2.class,
c.id,
c.name,
h.address))
.from(c).leftJoin(h).on(c.id.eq(h.city));
List 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版本依赖有关,此处当前测试,版本信息
UTF-8
UTF-8
1.8 >2.0.8
4.1.4
1.1.3
二、接收分页,排序参数 QueryDsl 查询中并不支持前端传参,后映射写入sql 条件(order by ‘字段’ ‘排序规则’),如下
@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)
//.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 findCityAndHotelPage2(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())
.offset(pageable.getOffset())
.limit(pageable.getPageSize());
PathBuilder entityPath = new PathBuilder<>(Entity.class, "tCity");
for (Sort.Order order : pageable.getSort()) {
PathBuilder
如图
![QueryDsl自定义返回对象,接收分页排序参数,引用Mysql函数等示例](https://img.it610.com/image/info8/0180abe62bc54d6889b60fab3da52063.jpg)
文章图片
映射SQL为,正常打印
select tcity0_.idas col_0_0_,
thotel1_.idas col_1_0_,
thotel1_.idas id1_1_,
thotel1_.address as address2_1_,
thotel1_.cityas city3_1_,
thotel1_.nameas 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 = "https://www.it610.com/article/1") int page,
@RequestParam(defaultValue = "https://www.it610.com/article/3") int rows,
@RequestParam(defaultValue = "https://www.it610.com/article/id") String sidx,
@RequestParam(defaultValue = "https://www.it610.com/article/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 results = tCityRepo.findCityAndHotelPage2(builder, pageable);
List list = results.getResults();
List collect = list.stream().map(Vo1::new).collect(Collectors.toList());
return ResultBean.ok(collect);
}
SQL 映射
select tcity0_.idas col_0_0_,
thotel1_.idas col_1_0_,
thotel1_.idas id1_1_,
thotel1_.addressas address2_1_,
thotel1_.cityas city3_1_,
thotel1_.hotel_dateas hotel_da4_1_,
thotel1_.hotel_date_time as hotel_da5_1_,
thotel1_.nameas 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 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 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 results = on.fetchResults().getResults();
return results;
}
【QueryDsl自定义返回对象,接收分页排序参数,引用Mysql函数等示例】SQL 映射
select tcity0_.idas col_0_0_,
tcity0_.nameas col_1_0_,
thotel1_.nameas col_2_0_,
thotel1_.addressas 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
四、其他
推荐阅读
- SpringBoot调用公共模块的自定义注解失效的解决
- python自定义封装带颜色的logging模块
- 列出所有自定义的function和view
- 前端代码|前端代码 返回顶部 backToTop
- Spring|Spring Boot 自动配置的原理、核心注解以及利用自动配置实现了自定义 Starter 组件
- 自定义MyAdapter
- Android自定义view实现圆环进度条效果
- Flutter自定义view|Flutter自定义view —— 闯关进度条
- js保留自定义小数点
- django|django 自定义.save()方法