0

We've having a problem doing an insert into two tables in a single transaction in our H2 database using EclipseLink JPA.

The database schema looks something like ... Table A {..., IP_ADDRESS Varchar2( 15 ) not null }

Table B {..., IP_ADDRESS Varchar2( 15 ) not null, constraint MY_FK foreign key (IP_ADDRESS) references A
}

The problem we are having is that the foreign key constraint on B is making the transaction fail.

We're doing this:

entityManager.getTransaction().begin();

entityManager.merge ( Save to A transaction ); entityManager.merge ( Save to B transaction );

entityManager.getTransaction().commit();

It looks like JPA is trying to do the save to B first and failing. Or perhaps it is checking the constraint on B first?

At any rate, what we'd like to do ideally is something like what I recall from many years ago, using PL/SQL on oracle:

BEGIN
   Do the insert on A;
   Do the insert on B;
   commit;
End;

I don't think Oracle looked at the constraint until the transaction was complete. Or allowed it complete because the uncommitted version of the transaction did not violate the constraint.

Any thoughts? I looked at the documentation and have not found anything helpful yet.

Thanks

Warren
  • 179
  • 2
  • 7

1 Answers1

0

I believe I have found the problem, although I would love a confirmation from somehow who knows JPA better than I do. The issue here was that there was no definition of the foreign key constraint in the entity classes representing the two tables.

As a result, the EntityManager knew nothing about the relationship between the two tables and picked an arbitrary insert order. The transaction then failed because JPA tried to insert the table B row before the the row for table A.

I fixed this by adding a @ManyToOne relationship in table B, and a @OneToMany relationship table A. I set the fetch type to lazy as the relationship definition as there is no need to use any fetch results.

In the Table A entity class:

// bi-directional one-to-many association to PositionHostPermission
@OneToMany( mappedBy = "the a entity", fetch=FetchType.LAZY )
private Set<B> bInstances;

In the Table B entity class:

//bi-directional many-to-one association to ipAddress
@ManyToOne( fetch= FetchType.LAZY )
@JoinColumn(name="IP_ADDRESS",insertable=false, updatable=false )
private A aTableInstance;
Warren
  • 179
  • 2
  • 7