Jpa使用@Query写分页查询问题

因为是一个新功能查询和其他模块不相干，所以我简单想用@query写一个分页，下面是是代码

dao层代码：

@Query(value = "SELECT " +
            "  t1.id , " +
            "  t1.store_member_id, " +
            "  t1.`code`, " +
            "  t1.activity_name, " +
            "  GROUP_CONCAT( CONCAT( t2.product_name, '×', t2.total_number ) ORDER BY t2.product_name ) AS sale_order_product_info, " +
            "  GROUP_CONCAT( CONCAT( t3.product_name, '×', t3.total_number ) ORDER BY t3.product_name ) AS gift_order_product_info  " +
            "FROM " +
            "  d1_sale_order AS t1 " +
            "  JOIN d1_sale_order_product AS t2 ON t2.order_type_id = t1.id " +
            "  JOIN d1_sale_order_product AS t3 ON t3.sale_order_id = t1.id  " +
            "WHERE " +
            "  t1.type = 1  " +
            "  AND  ( :storeMemberId IS NULL OR :storeMemberId = '' OR t1.store_member_id = :storeMemberId ) " +
            "  AND  ( :code IS NULL OR :code = '' OR t1.`code` LIKE CONCAT('%', :code, '%') ) " +
            "  AND  ( :activityName IS NULL OR :activityName = '' OR t1.activity_name LIKE CONCAT('%', :activityName, '%') ) " +
            "GROUP BY " +
            "  t1.id, " +
            "  t1.store_member_id, " +
            "  t1.`code`, " +
            "  t1.activity_name "
            ,nativeQuery = true)
    Page<Map<String, Object>> findAllBasicSaleOrder(
            @Param("storeMemberId") String storeMemberId,
            @Param("code") String code,
            @Param("activityName") String activityName,
            Pageable pageable);

service层代码

 public Page<FindAllSaleOrderVm> findAllBasicSaleOrder(String id, String code,String activityName, Pageable pageable) {
        Page<Map<String, Object>> findAllBasicSaleOrder = saleOrderDao.findAllBasicSaleOrder(id,code,activityName,pageable);
        if (findAllBasicSaleOrder == null || findAllBasicSaleOrder.isEmpty()) {
            return null;
        }
        List<FindAllSaleOrderVm> resultList = BaseUtils.mapListToModelList(findAllBasicSaleOrder.getContent(), FindAllSaleOrderVm.class);

        return new PageImpl<>(resultList, pageable, findAllBasicSaleOrder.getTotalElements());
    }

但是查询一直报错：

SQL Error: 1054, SQLState: 42S22
Unknown column 't1' in 'field list'

之后我把代码复制到navicat里面运行根本没有问题，咋看咋没问题。

然后同事说把代码打印打开看看sql打印，打开之后一看sql是这样的

SELECT
	count( t1 ) 
FROM
	d1_sale_order AS t1
	JOIN d1_sale_order_product AS t2 ON t2.order_type_id = t1.id
	JOIN d1_sale_order_product AS t3 ON t3.sale_order_id = t1.id 
WHERE
	t1.type = 1 
	AND ( ? IS NULL OR ? = '' OR t1.store_member_id = ? ) 
	AND ( ? IS NULL OR ? = '' OR t1.`code` LIKE CONCAT( '%', ?, '%' ) ) 
	AND ( ? IS NULL OR ? = '' OR t1.activity_name LIKE CONCAT( '%', ?, '%' ) ) 
GROUP BY
	t1.id,
	t1.store_member_id,
	t1.`code`,
	t1.activity_name

那么问题就来了，在 COUNT() 中使用 t1 是不正确的，COUNT() 函数应该计算的是列的数量（如 COUNT(t1.id) 或 COUNT(*)），而不是表的别名。你应该使用 COUNT(t1.id) 或 COUNT(*) 来计算符合条件的行数。

此时就需要自己手写sql（在后面加入countQuery =“查询总条数的sql”）来查总条数了，不能让Jpa自己生成了。

  @Query(value = "SELECT " +
            "  t1.id , " +
            "  t1.store_member_id, " +
            "  t1.`code`, " +
            "  t1.activity_name, " +
            "  GROUP_CONCAT( CONCAT( t2.product_name, '×', t2.total_number ) ORDER BY t2.product_name ) AS sale_order_product_info, " +
            "  GROUP_CONCAT( CONCAT( t3.product_name, '×', t3.total_number ) ORDER BY t3.product_name ) AS gift_order_product_info  " +
            "FROM " +
            "  d1_sale_order AS t1 " +
            "  JOIN d1_sale_order_product AS t2 ON t2.order_type_id = t1.id " +
            "  JOIN d1_sale_order_product AS t3 ON t3.sale_order_id = t1.id  " +
            "WHERE " +
            "  t1.type = 1  " +
            "  AND  ( :storeMemberId IS NULL OR :storeMemberId = '' OR t1.store_member_id = :storeMemberId ) " +
            "  AND  ( :code IS NULL OR :code = '' OR t1.`code` LIKE CONCAT('%', :code, '%') ) " +
            "  AND  ( :activityName IS NULL OR :activityName = '' OR t1.activity_name LIKE CONCAT('%', :activityName, '%') ) " +
            "GROUP BY " +
            "  t1.id, " +
            "  t1.store_member_id, " +
            "  t1.`code`, " +
            "  t1.activity_name "
            ,countQuery = "SELECT  " +
            "    count(*) " +
            "FROM  " +
            "    d1_sale_order AS t1 " +
            "    JOIN d1_sale_order_product AS t2 ON t2.order_type_id = t1.id " +
            "    JOIN d1_sale_order_product AS t3 ON t3.sale_order_id = t1.id " +
            "WHERE  " +
            "    t1.type = 1 " +
            "    AND ( " +
            "        :storeMemberId IS NULL  " +
            "        OR :storeMemberId = ''  " +
            "        OR t1.store_member_id = :storeMemberId " +
            "    ) " +
            "    AND ( " +
            "        :code IS NULL  " +
            "        OR :code = ''  " +
            "        OR t1.`code` LIKE CONCAT('%', :code, '%') " +
            "    ) " +
            "    AND ( " +
            "        :activityName IS NULL  " +
            "        OR :activityName = ''  " +
            "        OR t1.activity_name LIKE CONCAT('%', :activityName, '%') " +
            "    ) " +
            "GROUP BY  " +
            "    t1.id,  " +
            "    t1.store_member_id,  " +
            "    t1.`code`,  " +
            "    t1.activity_name  "
            ,nativeQuery = true)
    Page<Map<String, Object>> findAllBasicSaleOrder(
            @Param("storeMemberId") String storeMemberId,
            @Param("code") String code,
            @Param("activityName") String activityName,
            Pageable pageable);