使用@Query在JPA存储库中使用多个SELECT语句

Question

我试图执行带有子网的select语句，在存储库中使用带有'native query=true'的@query注释。但它给了我以下错误：

psqlexception：error：“select”或“select”附近的语法错误

@Query(value="SELECT COUNT(cc.customer) AS tot FROM SELECT DISTINCT new RatingsAndReviews(c.customer,c.customerRating) FROM RatingsAndReviews AS c WHERE c.vehicle=?1 AS cc WHERE cc.customerRating=?2",nativeQuery = true)
    Integer getNoOfRatingsForStars(Vehicle vehicle,double starNumber);

sub SELECT查询单独给出如下所示执行时的预期结果

@Query("SELECT DISTINCT new RatingsAndReviews(c.customer,c.customerRating)  FROM RatingsAndReviews AS c WHERE c.vehicle=?1")
    List<RatingsAndReviews> getCustomersWhoRatedVehicle(Vehicle vehicle);

上述查询的结果集是；RatingsAndReviews对象数组

[
    {
        "customerRating": 2.0,
        "customer": {
            "customerId": 1,
            },
        },
    },
    {
        "customerRating": 3.0,
        "customer": {
            "customerId": 2,
            },
        },
    },
    {
        "customerRating": 2.0,
        "customer": {
            "customerId": 3,
            },
        },
    }
]

所以我想要得到的是客户对特定“否”的评分。星星的。比如，多少个3盯着的收视率，2盯着的收视率……等等。

 @Query("SELECT COUNT(cc.customer) AS tot FROM (SELECT DISTINCT new RatingsAndReviews(c.customer,c.customerRating)  FROM RatingsAndReviews AS c WHERE c.vehicle=?1) AS cc WHERE cc.customerRating=?2")
    Integer getNoOfRatingsForStars(Vehicle vehicle,double star);

请纠正我，如果我所做的查询是错误的，任何排序帮助是赞赏的。

Answer 1

首先，手动测试db查询客户端中查询的语法：例如。

SELECT COUNT(cc.customer) AS tot
  FROM
    SELECT DISTINCT new RatingsAndReviews(c.customer,c.customerRating)
    FROM RatingsAndReviews AS c
    WHERE c.vehicle='some test value here' AS cc
  WHERE cc.customerRating=<some test value here>

上面的语法有很多问题，例如，您不能使用new Type()并且应该引用表名而不是类名。