java - 无法在 JPA 查询中进行投影

Question

抱歉，SQL 查询很困惑。

我有这个数据结构

@Entity
public class Stock {

//Composite PK?
@Id
@NotNull
private String id;
@NotNull
private String product_id;
@NotNull
private Integer quantity;
@NotNull
private LocalDateTime timestamp;

还有

public class ProductSold {

    private String productId;
    private Integer itemsSold;

两个类都有适当的构造函数。

我有相应的库存存储库

public interface StockRepository extends JpaRepository<Stock, String> {

我想做的是一个棘手的 SQL 查询(注释更容易可视化查询)，它应该返回 ProductSold 列表而不是 Stock

 /*
SELECT (t1.quantity - t2.quantity) as itemsSold, t1.product_id as pd
FROM stock t1 CROSS JOIN
 stock t2
WHERE MONTH(t1.timestamp) = 8 AND DAY(t1.timestamp) = 26
AND MONTH(t2.timestamp) = 8 AND DAY(t2.timestamp) = 27
AND t1.product_id = t2.product_id
ORDER BY itemsSold DESC
LIMIT 3;
 */

//Not working, not too sure why, says it can't find column quantity. In my view it should be working since it's a valid SQL Query.
@Query(value = "SELECT  new com.stock.stock.model.ProductSold(t1.product_id as productId, (t1.quantity - t2.quantity) as itemsSold )" +
        "    FROM Stock t1 CROSS JOIN Stock t2" +
        "    WHERE MONTH(t1.timestamp) = ?1 AND DAY(t1.timestamp) = ?2" +
        "    AND MONTH(t2.timestamp) = ?3 AND DAY(t2.timestamp) = ?4" +
        "    AND t1.product_id = t2.product_id" +
        "    ORDER BY itemsSold DESC" +
        "    LIMIT 3", nativeQuery = true)
List<ProductSold> findItemsSoldByTimestamp(int month1, int day1, int month2, int day2);

但是我得到了这个:

org.h2.jdbc.JdbcSQLSyntaxErrorException:SQL 语句中存在语法错误“SELECT NEW COM.[*]STOCK.STOCK.MODEL.PRODUCTSOLD(T1.PRODUCT_ID AS ProductID, (T1.QUANTITY - T2.QUANTITY) AS ITEMSSOLD ) ....

知道出了什么问题吗？我尝试不在 SQL 表达式中进行构造，但出现 ConverterNotFoundException

Answer 1

您正在使用仅在 JPQL 中可用的构造函数表达式，但您还将查询标记为 native ，即 SQL，但它不是。