mysql - 显示客户下的所有订单的详细信息

Question

我正在为我的大学项目开发​​一个电子商务网站(Spring MVC、java、mySql、Hibernate)。我有各种模型，如客户、产品、订单、custOrderHistory 等。

我能够显示所有客户详细信息(在管理页面中)和所有产品供客户浏览。

但我面临的问题是，如何显示客户的订单和订单历史记录？

我的订单表有 orderId、productId、数量和总价。但是要显示产品名称，我必须使用 productId 并从产品表中检索产品名称。

我不知道该怎么做。

到目前为止，我一直在使用 jSTL foreach 循环迭代 productList.jsp 页面中的产品并将它们显示在表格中。但正如我上面所说，我需要一次从 3-4 个表中检索数据，例如 productName(来自产品表)、productManuacturer(也来自产品表)、ShippingAddress(来自客户表)。我不确定该怎么做。

编辑 1:我在别人的电脑上问过这个问题，当时我的代码不在身边。所以，我在这里给出我的代码。

1. 客户.java

   package com.site.model;
   
   import com.fasterxml.jackson.annotation.JsonIgnore;
   import org.hibernate.validator.constraints.NotEmpty;
   import javax.persistence.*;
   import java.io.Serializable;
   
   @Entity
   public class Customer implements Serializable {
   
   private static final long serialVersionUID = -3280023076408333682L;
   
   @Id
   @GeneratedValue
   private int customerId;
   
   @NotEmpty(message = "  The customer name must not be blank.")
   private String customerName;
   
   @NotEmpty (message = "  The customer email must not be blank.")
   private String customerEmail;
   private String customerPhone;
   
   @NotEmpty (message = "  The username must not be blank.")
   private String username;
   
   @NotEmpty (message = "  The password must not be blank.")
   private String password;
   private boolean enabled;
   
   @OneToOne
   @JoinColumn(name = "billingAddressId")
   private BillingAddress billingAddress;
   
   @OneToOne
   @JoinColumn(name = "shippingAddressId")
   private ShippingAddress shippingAddress;
   
   @OneToOne()
   @JoinColumn(name = "cartId")
   @JsonIgnore
   private Cart cart;
   
   public int getCustomerId() {
       return customerId;
   }
   
   public void setCustomerId(int customerId) {
       this.customerId = customerId;
   }
   
   public String getCustomerName() {
       return customerName;
   }
   
   public void setCustomerName(String customerName) {
       this.customerName = customerName;
   }
   
   public BillingAddress getBillingAddress() {
       return billingAddress;
   }
   
   public void setBillingAddress(BillingAddress billingAddress) {
       this.billingAddress = billingAddress;
   }
   
   public ShippingAddress getShippingAddress() {
       return shippingAddress;
   }
   
   public void setShippingAddress(ShippingAddress shippingAddress) {
       this.shippingAddress = shippingAddress;
   }
   
   public String getCustomerPhone() {
       return customerPhone;
   }
   
   public void setCustomerPhone(String customerPhone) {
       this.customerPhone = customerPhone;
   }
   
   public String getCustomerEmail() {
       return customerEmail;
   }
   
   public void setCustomerEmail(String customerEmail) {
       this.customerEmail = customerEmail;
   }
   
   public String getUsername() {
       return username;
   }
   
   public void setUsername(String username) {
       this.username = username;
   }
   
   public String getPassword() {
       return password;
   }
   
   public void setPassword(String password) {
       this.password = password;
   }
   
   public boolean isEnabled() {
       return enabled;
   }
   
   public void setEnabled(boolean enabled) {
       this.enabled = enabled;
   }
   
   public Cart getCart() {
       return cart;
   }
   
   public void setCart(Cart cart) {
       this.cart = cart;
   }
   
   }
   

2. Cart.java

   打包 com.site.model;

   导入 com.fasterxml.jackson.annotation.JsonIgnore;

   导入 javax.persistence.*;导入 java.io.Serializable；导入 java.util.ArrayList；导入 java.util.List;

   @实体公共(public)类 Cart 实现 Serializable{

   private static final long serialVersionUID = -2479653100535233857L;

   @ID@生成值private int cartId;

   @OneToMany(mappedBy = "cart", cascade= CascadeType.ALL, fetch = FetchType.EAGER)private List cartItems = new ArrayList();

   @OneToOne@JoinColumn(name = "customerId")@Json忽略私有(private)客户客户；

   私有(private)双重总计；

   public int getCartId() { 返 repo 物车编号；

   public void setCartId(int cartId) { this.cartId = cartId;

   公共(public)双 getGrandTotal() { 返回总计；

   public void setGrandTotal(double grandTotal) { this.grandTotal = grandTotal;

   公共(public)列表 getCartItems() { 返 repo 物车元素；

   public void setCartItems(List cartItems) { this.cartItems = cartItems;

   公共(public)客户 getCustomer() { 回头客；

   public void setCustomer(客户客户){ this.customer = 客户；

3. CartItem.java

   package com.site.model;
   

   导入 com.fasterxml.jackson.annotation.JsonIgnore;

   导入 javax.persistence.*;导入 java.io.Serializable；导入 java.util.List;

   @实体公共(public)类 CartItem 实现序列化{

   private static final long serialVersionUID = -904360230041854157L;
   
   @Id
   @GeneratedValue
   private int cartItemId;
   
   @ManyToOne
   @JoinColumn(name="cartId")
   @JsonIgnore
   private Cart cart;
   
   @ManyToOne
   @JoinColumn(name = "productId")
   private Product product;
   private int quantity;
   private double totalPrice;
   
   @ManyToMany(cascade=CascadeType.ALL, mappedBy="cartItems")
   @JsonIgnore
   private List<OrderHistory> orderHistoryList;
   
   public int getCartItemId() {
       return cartItemId;
   }
   
   public void setCartItemId(int cartItemId) {
       this.cartItemId = cartItemId;
   }
   
   public Product getProduct() {
       return product;
   }
   
   public void setProduct(Product product) {
       this.product = product;
   }
   
   public int getQuantity() {
       return quantity;
   }
   
   public void setQuantity(int quantity) {
       this.quantity = quantity;
   }
   
   public double getTotalPrice() {
       return totalPrice;
   }
   
   public void setTotalPrice(double totalPrice) {
       this.totalPrice = totalPrice;
   }
   
   public Cart getCart() {
       return cart;
   }
   
   public void setCart(Cart cart) {
       this.cart = cart;
   }
   
   public List<OrderHistory> getOrderHistoryList() {
       return orderHistoryList;
   }
   
   public void setOrderHistoryList(List<OrderHistory> orderHistoryList) {
       this.orderHistoryList = orderHistoryList;
   }
   }
   

4. CustomerOrder.java

   package com.site.model;
   

   导入 javax.persistence.*;导入 java.io.Serializable;

   @实体公共(public)类 CustomerOrder 实现序列化 {

   private static final long serialVersionUID = -3608286390950243118L;
   
   @Id
   @GeneratedValue
   private int customerOrderId;
   
   @OneToOne
   @JoinColumn(name = "cartId")
   private Cart cart;
   
   @OneToOne
   @JoinColumn(name = "customerId")
   private Customer customer;
   
   @OneToOne
   @JoinColumn(name = "billingAddressId")
   private BillingAddress billingAddress;
   
   @OneToOne
   @JoinColumn(name = "shippingAddressId")
   private ShippingAddress shippingAddress;
   
   
   public int getCustomerOrderId() {
       return customerOrderId;
   }
   
   public void setCustomerOrderId(int customerOrderId) {
       this.customerOrderId = customerOrderId;
   }
   
   public Cart getCart() {
       return cart;
   }
   
   public void setCart(Cart cart) {
       this.cart = cart;
   }
   
   public Customer getCustomer() {
       return customer;
   }
   
   public void setCustomer(Customer customer) {
       this.customer = customer;
   }
   
   public BillingAddress getBillingAddress() {
       return billingAddress;
   }
   
   public void setBillingAddress(BillingAddress billingAddress) {
       this.billingAddress = billingAddress;
   }
   
   public ShippingAddress getShippingAddress() {
       return shippingAddress;
   }
   
   public void setShippingAddress(ShippingAddress shippingAddress) {
       this.shippingAddress = shippingAddress;
   }
   
   }
   

5. OrderHistory.java

   打包 com.site.model;

   导入 javax.persistence.*;导入 java.io.Serializable；导入 java.util.ArrayList；导入 java.util.List;

   @实体公共(public)类 OrderHistory 实现序列化 {

   private static final long serialVersionUID = 1083533250613139445L;
   
   @Id
   @GeneratedValue
   private int orderHistoryId;
   private int customerId;
   private String customerName;
   private int customerOrderId;
   private int cartId;
   
   @ManyToMany(cascade = CascadeType.ALL)
   @JoinTable(name = "cartItem_orderHistory", joinColumns = @JoinColumn(name = "orderHistoryId"),
           inverseJoinColumns = @JoinColumn
                   (name = "cartItemId"))
   private List<CartItem> cartItems = new ArrayList<CartItem>();
   private double grandTotal;
   private String billingAddress;
   private String shippingAddress;
   
   public int getOrderHistoryId() {
       return orderHistoryId;
   }
   
   public void setOrderHistoryId(int orderHistoryId) {
       this.orderHistoryId = orderHistoryId;
   }
   
   public int getCustomerId() {
       return customerId;
   }
   
   public void setCustomerId(int customerId) {
       this.customerId = customerId;
   }
   
   public String getCustomerName() {
       return customerName;
   }
   
   public void setCustomerName(String customerName) {
       this.customerName = customerName;
   }
   
   public int getCustomerOrderId() {
       return customerOrderId;
   }
   
   public void setCustomerOrderId(int customerOrderId) {
       this.customerOrderId = customerOrderId;
   }
   
   public int getCartId() {
       return cartId;
   }
   
   public void setCartId(int cartId) {
       this.cartId = cartId;
   }
   
   public List<CartItem> getCartItems() {
       return cartItems;
   }
   
   public void setCartItems(List<CartItem> cartItems) {
       this.cartItems = cartItems;
   }
   
   public double getGrandTotal() {
       return grandTotal;
   }
   
   public void setGrandTotal(double grandTotal) {
       this.grandTotal = grandTotal;
   }
   
   public String getBillingAddress() {
       return billingAddress;
   }
   
   public void setBillingAddress(String billingAddress) {
       this.billingAddress = billingAddress;
   }
   
   public String getShippingAddress() {
       return shippingAddress;
   }
   
   public void setShippingAddress(String shippingAddress) {
       this.shippingAddress = shippingAddress;
   }
   }
   

编辑2:如何将数据存储在ustomerorder 表和orderhistory 表中，以及如何在jsp 页面上显示？

Answer 1

您应该在订单表中有一个 CustomerId 列，用于检测订单与哪个客户相关联。只需在其中插入已登录的用户 ID 并通过与客户表进行连接来获取客户名称。