英文:
How to sum the cost of each item in a One To Many relationship JPA?
问题
在一个一对多关系中，计算订单的总金额时，当顾客购买订单属性中的一定数量的商品时，他想知道如何开始进行调查以执行该操作。我感到有些困惑，我真的很想学习。
@Entity
@Table(name = "orders")
class Order(
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
val id: Int? = 0,
val name: String? = "",
val address: String? = "",
val phone: String? = "",
val totalOrderPrice: Long? = null, // 根据每个商品的成本计算的总金额
@OneToMany(cascade = [CascadeType.MERGE])
@JoinColumn(name = "order_id")
val items: List<Items>? = mutableListOf()
)
@Entity
@Table(name = "items")
class Items(
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
val id: Long? = null,
val name: String? = null,
val price: Long? = null // 商品成本
)
英文:
How could I calculate the total of an order in a one to many relationship, when the customer buys a certain number of items in the order attribute, he wants to know, he could start to investigate, to carry out that. I'm really a little confused, I really want to learn
@Entity
@Table(name = "orders")
class Order(
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
val id: Int? = 0,
val name: String? = "",
val address: String? = "",
val phone: String? = "",
val totalOrderPrice: Long? // total based on the cost of each item
@OneToMany(cascade = [CascadeType.MERGE])
@JoinColumn(name = "order_id")
val items: List<Items>? = mutableListOf()
)
@Entity
@Table(name = "items")
class Items(
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
val id: Long? = null,
val name: String? = null,
val price: Long?, // cost item
)
答案1
得分: 1
// 首先，您需要更正映射关系。在子实体中，您应该有父实体的引用。这意味着在“Items”类中添加对订单ID的引用，列名为“orderId”。然后您的类应该如下所示：
@Entity
@Table(name = "items")
class Items(
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
val id: Long? = null,
val orderId: Int? = null,
val name: String? = null,
val price: Long?, // 物品成本
)
现在，您在此处有3个选项来计算订单的总价格：
1. 编写JPA查询如下：
@Query("SELECT SUM(m.price) FROM Items m where orderId = :orderId")
Long getOrderPrice(@Param("orderId") Long orderId);
2. 编写原生SQL查询如下：
@Query("SELECT SUM(price) FROM items where order_id = :orderId", nativeQuery = true)
Long getOrderPrice(@Param("orderId") Long orderId);
3. 在Java中使用流或循环，计算总价格：
Long sum = order.getItems().map(i -> i.getPrice()).stream().reduce(Long::sum).get();
英文:
First you need to correct your mapping. In child entity, you should have parent entity's reference. Which mean in "Items" class add reference to Order id as column "orderId". Then your class should look like this
@Entity
@Table(name = "items")
class Items(
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
val id: Long? = null,
val orderId: int? = null,
val name: String? = null,
val price: Long?, // cost item
)
Now, You have 3 options here to calculate the total price of order
-
Write JPA query like this
@Query("SELECT SUM(m.price) FROM Items m where orderId = :orderId")
Long getOrderPrice(@Param("orderId") Long orderId);
-
Write Native SQL query like this
@Query("SELECT SUM(price) FROM items where order_id = :orderId", nativeQuery = true)
Long getOrderPrice(@Param("orderId") Long orderId);
-
In Java using streams or for loop, calculate the total price
Long sum = order.getItems.map(i->i.getprice()).stream().reduce(Long::sum).get();
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论