QueryDsl自定义返回对象，接收分页排序参数，引用Mysql函数等示例

港控/mmm° 2023-07-13 08:19 19阅读 0赞

### 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的一些使用问题，可参考我上文总结，[链接这里][Link 1]

## 一、自定义返回对象 ##

在我们集成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版本依赖有关，此处当前测试，版本信息 ###

## 二、接收分页，排序参数 ##

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();
        }

如图  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyMTA1NjI5_size_16_color_FFFFFF_t_70]  
映射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 ###

## 四、其他 ##

[Link 1]: https://blog.csdn.net/qq_42105629/article/details/100099643
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyMTA1NjI5_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200313172636656.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyMTA1NjI5,size_16,color_FFFFFF,t_70