JPA实体将SQL函数结果映射到瞬态字段

import javax.persistence.*;

@Entity
public class Product {
    @Id
    private Long id;

    private String name;

    @Transient
    @Formula("SELECT SUM(price * quantity) FROM order_item WHERE product_id = id")
    private double totalSales;

    // getters and setters
}

import javax.persistence.*;
import java.util.List;

@Entity
public class Product {
    @Id
    private Long id;

    private String name;

    @Transient
    private double totalSales;

    // getters and setters
}

@Repository
public interface ProductRepository extends JpaRepository<Product, Long> {
    @Query("SELECT new com.example.ProductDTO(p.id, p.name, SUM(oi.price * oi.quantity)) " +
           "FROM Product p JOIN OrderItem oi ON p.id = oi.productId GROUP BY p.id, p.name")
    List<ProductDTO> findProductsWithTotalSales();
}

public class ProductDTO {
    private Long id;
    private String name;
    private double totalSales;

    public ProductDTO(Long id, String name, double totalSales) {
        this.id = id;
        this.name = name;
        this.totalSales = totalSales;
    }

    // getters
}