java - 从数据库中删除表 OrderItem

Question

确定开始:我在 Spring 做药房项目。我正在使用 Eclipse 和数据库 PostgreSQL。

在数据库中，我有表名称:OrderItem、CustomerOrder、Product 和 Register(这是带有有关用户的通行证和信息的类)。

好的，我想删除具有给定 ID 的 CustomerOrder。

co.deleteCustomerOrder(co.getCustomerOrder(id));

如果我这样做，我会遇到这样的错误:

详细信息:Key(orderid)=(1421007984736) 在表“orderItem”中有调用错误:表“customerorder”上的修改或删除违反了外键“fk60163f61b0099af4”表“orderitem”

所以我把这个放在第一位

 cos.deleteOrderItem(cos.getOrderItem(id)) 

然后

co.deleteCustomerOrder(co.getCustomerOrder(id));

现在我遇到这样的错误:

HTTP 状态 500 - 请求处理失败；嵌套异常是 java.lang.IllegalArgumentException:尝试使用 null 实体创建删除事件

也许某些实体类存在问题:注册或其他。顺便说一句，当我使用 PgAdminIII orderItem 删除时，当然我不会收到第一个错误，我可以完成这行代码

   co.deleteCustomerOrder(co.getCustomerOrder(id));

正常情况下。

****如何解决第二个错误？我不想使用 PgAdmin xD****

我的一些代码。

package app.Spring.domain;



import java.util.Date;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.ManyToOne;
import javax.persistence.Table;

@Entity
@Table(name = "CustomerOrder")
public class CustomerOrder {

        @Id
        @Column(name = "orderID")
        private long orderID;

        @ManyToOne(cascade = { CascadeType.MERGE })
        private Register register;

        @Column(name = "status")
        private String status;

        @Column(name = "totalCost")
        private float totalCost;

        @Column(name = "orderDate")
        private Date orderDate;

        public CustomerOrder() {
        }

        public CustomerOrder(long orderID, Register register, String status,
                        float totalCost, Date orderDate) {
                this.orderID = orderID;
                this.register = register;
                this.status = status;
                this.totalCost = totalCost;
                this.orderDate = orderDate;
        }

        public long getOrderID() {
                return orderID;
        }

        public void setOrderID(long orderID) {
                this.orderID = orderID;
        }

        public Register getRegister() {
                return register;
        }

        public void setRegister(Register register) {
                this.register = register;
        }

        public String getStatus() {
                return status;
        }

        public void setStatus(String status) {
                this.status = status;
        }

        public float getTotalCost() {
                return totalCost;
        }

        public void setTotalCost(float totalCost) {
                this.totalCost = totalCost;
        }

        public Date getOrderDate() {
                return orderDate;
        }

        public void setOrderDate(Date orderDate) {
                this.orderDate = orderDate;
        }
}



package app.Spring.domain;
import java.util.List;
import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.Id;
import javax.persistence.ManyToMany;
import javax.persistence.ManyToOne;
import javax.persistence.Table;

@Entity
@Table(name = "OrderItem")
public class OrderItem {

        @Id
        @Column(name = "itemID")
        private long itemID;


        @ManyToOne(cascade = { CascadeType.MERGE })
        private Product product;

        @ManyToOne(cascade = { CascadeType.MERGE })
        private CustomerOrder customerOrder;

        @Column(name = "quantity")
        private int quantity;

        public OrderItem() {
        }

        public OrderItem(long itemID, Product product, CustomerOrder customerOrder,
                        int quantity) {
                this.itemID = itemID;
                this.product = product;
                this.customerOrder = customerOrder;
                this.quantity = quantity;
        }

        public long getItemID() {
                return itemID;
        }

        public void setItemID(long itemID) {
                this.itemID = itemID;
        }

        public Product getProduct() {
                return product;
        }

        public void setProduct(Product product) {
                this.product = product;
        }

        public CustomerOrder getCustomerOrder() {
                return customerOrder;
        }

        public void setCustomerOrder(CustomerOrder customerOrder) {
                this.customerOrder = customerOrder;
        }

        public long getQuantity() {
                return quantity;
        }

        public void setQuantity(int quantity) {
                this.quantity = quantity;
        }
}

Controller

@Controller
@RequestMapping("/admin/users")
public class AdminController {

    @Autowired
    LocalValidatorFactoryBean validator;

    @Autowired
    RegisterService reg;

    @Autowired    
CustomerOrderService co;
    @Autowired
OrderItemService cos;     

 @RequestMapping("/removecustomer/{id}")
    public String removeCustomerOrderById(@PathVariable Long id) {

        System.out.println("remove  customerOrderID "+id);
      cos.deleteOrderItem(cos.getOrderItem(id));
    co.deleteCustomerOrder(co.getCustomerOrder(id));


        return "redirect:/";
    }
}

客户订单服务

public class CustomerOrderImpl implements CustomerOrderService{


    protected final Logger log = LoggerFactory.getLogger(getClass());

    protected SessionFactory sessionFactory;

    public void setSessionFactory(SessionFactory sessionFactory) {
        this.sessionFactory = sessionFactory;
    }    

  @Override
      public CustomerOrder getCustomerOrder(Long id) {
          return (CustomerOrder)sessionFactory.getCurrentSession().get(CustomerOrder.class.getName(), id);
      }

 public void deleteCustomerOrder(CustomerOrder v) {
            sessionFactory.getCurrentSession().delete(v);
            sessionFactory.getCurrentSession().flush();
        }


}

订单服务

public class OrderItemImpl implements OrderItemService {



    protected final Logger log = LoggerFactory.getLogger(getClass());

    protected SessionFactory sessionFactory;

    public void setSessionFactory(SessionFactory sessionFactory) {
        this.sessionFactory = sessionFactory;
    }    



    public OrderItem getOrderItem(Long id) {
        return (OrderItem)sessionFactory.getCurrentSession().get(OrderItem.class.getName(), id);

    }
public void deleteOrderItem(OrderItem v) {
            sessionFactory.getCurrentSession().delete(v);
            sessionFactory.getCurrentSession().flush();
        }
}

Answer 1

cos.deleteOrderItem(cos.getOrderItem(id));
co.deleteCustomerOrder(co.getCustomerOrder(id));

表中的每条记录(行)都有一个键。您正在使用相同的键删除两个不同表中的项目。

除非它们碰巧处于一对一关系，否则记录的 ID 不会反射(reflect)这种关系。在本例中，您尝试使用未记录的 key 删除订单项。

说白了:

• 您想要删除 customerOrder 16；

• 您无法删除 customerOrder 16；因为由于外键，它具有与其相关的 orderItems 4 和 5。

• 您正在尝试删除订单项目 16；不存在(第二个错误)。

正确的解决方案是:

• 设置要级联删除的 customerOrder 和 orderItems 的关系(cascade = {CascadeType.DELETE, CascadeType.MERGE})

• 在删除customerOrder之前，找到相关的orderItems并将其删除(同样，不同元素的键不会相关)。

如果您在理解键、外键和级联的概念时遇到问题，您应该首先了解有关 SQL 工作原理的更多信息。