CODEWITHSUNDEEP
javamysqljpaintellij-idea
driima
driima

Reputation: 643

JPA Foreign Key Constraint Fails on EntityManager flush

I've been following the NetBeans AffableBean e-commerce tutorial, but using IntelliJ as my IDE instead. I have gotten everything working up until the point where I need to add Customers to the database, as well as a CustomerOrder relating to the order they are placing. The section of the tutorial is visible here:

https://netbeans.org/kb/docs/javaee/ecommerce/transaction.html#placeOrder

When I place an order, I receive the following error:

com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`larrys`.`customer_order`, CONSTRAINT `fk_customer_order_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION)

When flushing the EntityManager here:

private void addOrderedItems(CustomerOrder order, ShoppingCart cart) {

    em.flush(); //Here

    List<ShoppingCartItem> items = cart.getItems();

    for (ShoppingCartItem scItem : items) {

        int productId = scItem.getProduct().getId();

        OrderedProductPK orderedProductPK = new OrderedProductPK();
        orderedProductPK.setCustomerOrderId(order.getId());
        orderedProductPK.setProductId(productId);

        OrderedProduct orderedItem = new OrderedProduct(orderedProductPK);

        orderedItem.setQuantity(scItem.getQuantity());

        em.persist(orderedItem);
    }
}

My full OrderManager code is visible here:

http://pastebin.com/jRintrTS

Please correct me otherwise, but from what I can understand about the error, I am trying to insert a row in the customer_order table but cannot do this as the foreign key reference to customer_id does not exist in the customer table. However, I have created the customer and persisted it before the order, so I don't understand why it isn't committing the data to the database before attempting to create a CustomerOrder.

This is the schema sql provided by netbeans for the e-commerce project:

https://netbeans.org/projects/samples/sources/samples-source-code/content/samples/javaee/AffableBean/setup/schemaCreation.sql

And my persistence.xml file, identical to the one provided in the tutorial:

<?xml version="1.0" encoding="UTF-8"?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence" version="2.0">

    <persistence-unit name="LarrysPU" transaction-type="JTA">
        <jta-data-source>jdbc/larrys</jta-data-source>
        <properties/>
    </persistence-unit>
</persistence>

The only thing different about my project and the NetBeans project is that in my customer table, I have changed cityRegion to town, added postcode and county, and my project uses a different name to AffableBean.

Has anyone got any ideas as to where I might be going wrong?

EDIT - GlassFish Log when encountering the error:

[2015-06-11T21:18:57.799+0100] [glassfish 4.1] [FINE] [] [org.eclipse.persistence.session.file:/OBFUSCATED/Larrys/out/artifacts/Larrys/WEB-INF/classes/_LarrysPU.sql] [tid: _ThreadID=26 _ThreadName=http-listener-1(2)] [timeMillis: 1434053937799] [levelValue: 500] [[
  INSERT INTO larrys.customer (id, address, cc_number, county, email, name, phone, postcode, town) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
    bind => [9 parameters bound]]]

[2015-06-11T21:18:57.800+0100] [glassfish 4.1] [FINEST] [] [org.eclipse.persistence.session.file:/OBFUSCATED/Larrys/out/artifacts/Larrys/WEB-INF/classes/_LarrysPU.query] [tid: _ThreadID=26 _ThreadName=http-listener-1(2)] [timeMillis: 1434053937800] [levelValue: 300] [[
  Execute query InsertObjectQuery(entity.CustomerOrder@1f3cec0)]]

[2015-06-11T21:18:57.801+0100] [glassfish 4.1] [FINE] [] [org.eclipse.persistence.session.file:/OBFUSCATED/Larrys/out/artifacts/Larrys/WEB-INF/classes/_LarrysPU.sql] [tid: _ThreadID=26 _ThreadName=http-listener-1(2)] [timeMillis: 1434053937801] [levelValue: 500] [[
  INSERT INTO larrys.customer_order (id, amount, confirmation_number, date_created, customer_id) VALUES (?, ?, ?, ?, ?)
    bind => [5 parameters bound]]]

[2015-06-11T21:18:57.802+0100] [glassfish 4.1] [FINE] [] [org.eclipse.persistence.session.file:/OBFUSCATED/Larrys/out/artifacts/Larrys/WEB-INF/classes/_LarrysPU.sql] [tid: _ThreadID=26 _ThreadName=http-listener-1(2)] [timeMillis: 1434053937802] [levelValue: 500] [[
  SELECT 1]]

[2015-06-11T21:18:57.802+0100] [glassfish 4.1] [WARNING] [] [org.eclipse.persistence.session.file:/OBFUSCATED/Larrys/out/artifacts/Larrys/WEB-INF/classes/_LarrysPU] [tid: _ThreadID=26 _ThreadName=http-listener-1(2)] [timeMillis: 1434053937802] [levelValue: 900] [[

Local Exception Stack: 
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`larrys`.`customer_order`, CONSTRAINT `fk_customer_order_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION)
Error Code: 1452

Upvotes: 0

Views: 1572

Answers (2)

driima
driima

Reputation: 643

It turns out that the issue I encountered was caused by the fact my Id accessors had no @GeneratedValue(strategy = GenerationType.IDENTITY), so I added this for each Entity.

As for fixing the Unknown column 'SOMETHING' in 'field list' error I received, it turns out I only received this for the OrderedProduct class. Instead of using the @Annotations on fields, I chose to use them on their accessor methods, which could allow for customization in the future; but I forgot about this:

@EmbeddedId
protected OrderedProductPK orderedProductPK;

where the @EmbeddedId annotation should have been present on an accessor for the OrderedProductPK.

Upvotes: 2

John
John

Reputation: 4006

I'm guessing there is a customerId property on OrderedProduct and you need to call orderedItem.setCustomerId() or orderedItem.setCustomer() with a valid customer id or valid customer object depending on how you've created the entity classes. These seem to be the only two lines that set values on the orderedItem object. 

// order id
orderedProductPK.setCustomerOrderId(order.getId());
// product id
orderedProductPK.setProductId(productId);
// object created with order and product associations
OrderedProduct orderedItem = new OrderedProduct(orderedProductPK);
// quantity set
orderedItem.setQuantity(scItem.getQuantity());
// customer is not associated

A customer needs to be associated with the orderedItem.

EDIT: Looking at the tutorial it looks like customer is associated with the order object. What do you get for order.getCustomer()? If it is null, that's your problem.

Upvotes: 0

Related Questions