You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openjpa.apache.org by "M. Walter" <ma...@sbb.ch> on 2011/11/09 15:57:22 UTC
Constraint violation using OneToOne relationship
We have an entity A having a OneToOne unidirectional relationship to entity B
configured like this:
In entity A:
@OneToOne(cascade = CascadeType.ALL)
@JoinColumn(name = "B_ID")
private B b;
Now we create new entities A and B and would like to persist them:
final A a = new A();
final B b = new B();
a.setB(b);
em.persist(a);
We get a PersistenceException because OpenJPA tries to insert the entity
with the foreign key first:
<openjpa-1.2.3-SNAPSHOT-r422266:1053401 nonfatal general error>
org.apache.openjpa.persistence.PersistenceException: ORA-02291: integrity
constraint violated - parent key not found
{prepstmnt 1708156368
INSERT INTO A (ID, ROW_ERF_TSTAMP, ROW_ERF_USER, ROW_MUT_VERSION, B_ID)
VALUES (?, ?, ?, ?, ?)
[params=(long) 396, (Timestamp) 2011-11-09 15:38:12.048, (String) TEST,
(int) 1, (long) 2772]} [code=2291, state=23000]
Why does OpenJPA first try to insert entity A and not B? An how can we force
OpenJPA to execute statements in the right order?
Interestingly this works on our OneToMany relationships.
In the internet I found https://issues.apache.org/jira/browse/OPENJPA-1961
this and http://www-01.ibm.com/support/docview.wss?uid=swg1PK74266 this .
But these issues are for OneToMany relationships. IBM provides a solution
using the OpenJPA property openjpa.jdbc.SchemaFactory. But we would like to
understand why persisting does not work in the OneToOne case above. So has
somebody an explanation for our issue? Thank you in advance!
We use WebSphere server 7.0.0.19 and OpenJPA 1.2.3.
--
View this message in context: http://openjpa.208410.n2.nabble.com/Constraint-violation-using-OneToOne-relationship-tp6978223p6978223.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: Constraint violation using OneToOne relationship
Posted by "M. Walter" <ma...@sbb.ch>.
Hi Kevin,
thank you VERY much for your answers. I tested your second proposal for
solution and it works like a charm! But one drawback here: we lose
portability because as you said the annotation is OpenJPA specific.
Could some OpenJPA official please update the FAQ
(http://openjpa.apache.org/faq.html) "Can OpenJPA reorder SQL statements to
satisfy database foreign key constraints?" Maybe the @ForeignKey solution
would be helpful for other desperate developers as well...
--
View this message in context: http://openjpa.208410.n2.nabble.com/Constraint-violation-using-OneToOne-relationship-tp6978223p6981835.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: Constraint violation using OneToOne relationship
Posted by Kevin Sutter <kw...@gmail.com>.
Hi Walter,
Unfortunately, I think you have hit upon a short-coming of OpenJPA's
foreign key detection mechanism. OpenJPA has attempted to detect and
process foreign keys according to the definition and usage of the entities,
but it still has a couple of holes. There are two means of forcing OpenJPA
to do the right thing...
1) The use of the SchemaFactory property. But, as you have found out,
there may be some side effects if OpenJPA determines that the metadata for
your database schema doesn't match exactly what's defined in your object
model.
2) The use of the OpenJPA-specific ForeignKey annotation [1]. Although
this requires a change to your entity code, this explicit declaration will
tell OpenJPA that a foreign key exists for this relationship and
appropriate ordering of the SQL will take place to avoid the constraint
violations you are experiencing.
In your scenario, I would suggest trying the ForeignKey approach and see if
that doesn't get you up and running.
Also, since it sounds like you are a WebSphere customer, you might want to
follow up with a PMR. Even though it might be thought of as a "feature
request", at least it would be logged for future consideration.
Thanks and Good Luck,
Kevin
[1]
http://openjpa.apache.org/builds/apache-openjpa-1.2.3-SNAPSHOT/docs/manual/manual.html#ref_guide_mapping_jpa_fk
On Thu, Nov 10, 2011 at 2:57 AM, M. Walter <ma...@sbb.ch> wrote:
> This is just unbelievable! As I expected using this property has side
> effects.
>
> We have boolean fields defined in our database as number(1,0) because
> Oracle
> does not know boolean type. Now we get ArgumentExceptions saying that we
> declare columns that are not compatible with the expected type "bit". These
> errors never showed up before we added the property
> "openjpa.jdbc.SchemaFactory".
>
> Now what?
>
> Remove the property and use OneToMany instead of OneToOne relationships
> with
> programmatically limit the number of child elements to one only? This would
> be very awful.
>
> Are there any other options we have got?
>
> --
> View this message in context:
> http://openjpa.208410.n2.nabble.com/Constraint-violation-using-OneToOne-relationship-tp6978223p6980990.html
> Sent from the OpenJPA Users mailing list archive at Nabble.com.
>
Re: AW: Constraint violation using OneToOne relationship
Posted by "M. Walter" <ma...@sbb.ch>.
Boblitz John wrote:
>
> I might be wrong here - but it would seem to me that you are trying
> to persist the child, not the parent.
>
> In your definition "A" has a foreign key to "B" which means "B" must
> exist prior to "A" and is thus the parent in the relationship. This is
> also what the error message said "parent key not found".
>
> Have you tried persisting "B" instead of "A"?
>
I agree that B must exist prior to A. But in my understanding the parent is
referenced by the mappedBy attribute which we have defined in entity B, so A
is the parent. I had expected JPA to recognize this relationship and execute
the statements in the correct order. But as Kevin explained OpenJPA has
tried and failed to detect the relationship unfortunately. I don't think
that persisting B instead of A is the right way (well I haven't tried it).
IMHO one advantage to use JPA is that it simplifies the way to work with
data and takes the burden to generate the proper SQL statements. Otherwise
developers could use plain JDBC and pay attention to all relationships on
their own. There would be no reason to use JPA.
--
View this message in context: http://openjpa.208410.n2.nabble.com/Constraint-violation-using-OneToOne-relationship-tp6978223p6996720.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
AW: Constraint violation using OneToOne relationship
Posted by Boblitz John <Jo...@BERTSCHI.com>.
> -----Ursprüngliche Nachricht-----
> Von: M. Walter [mailto:marc.walter@sbb.ch]
> Gesendet: Donnerstag, 10. November 2011 09:57
> An: users@openjpa.apache.org
> Betreff: Re: Constraint violation using OneToOne relationship
>
> This is just unbelievable! As I expected using this property
> has side effects.
>
> We have boolean fields defined in our database as number(1,0)
> because Oracle does not know boolean type. Now we get
> ArgumentExceptions saying that we declare columns that are
> not compatible with the expected type "bit". These errors
> never showed up before we added the property
> "openjpa.jdbc.SchemaFactory".
>
> Now what?
>
> Remove the property and use OneToMany instead of OneToOne
> relationships with programmatically limit the number of child
> elements to one only? This would be very awful.
>
> Are there any other options we have got?
>
> --
> View this message in context:
> http://openjpa.208410.n2.nabble.com/Constraint-violation-using
> -OneToOne-relationship-tp6978223p6980990.html
> Sent from the OpenJPA Users mailing list archive at Nabble.com.
>
I might be wrong here - but it would seem to me that you are trying
to persist the child, not the parent.
In your definition "A" has a foreign key to "B" which means "B" must
exist prior to "A" and is thus the parent in the relationship. This is
also what the error message said "parent key not found".
Have you tried persisting "B" instead of "A"?
Just my 2 cents ...

John
----
Who is General Failure, and why is he reading my hard disk?
Re: Constraint violation using OneToOne relationship
Posted by "M. Walter" <ma...@sbb.ch>.
This is just unbelievable! As I expected using this property has side
effects.
We have boolean fields defined in our database as number(1,0) because Oracle
does not know boolean type. Now we get ArgumentExceptions saying that we
declare columns that are not compatible with the expected type "bit". These
errors never showed up before we added the property
"openjpa.jdbc.SchemaFactory".
Now what?
Remove the property and use OneToMany instead of OneToOne relationships with
programmatically limit the number of child elements to one only? This would
be very awful.
Are there any other options we have got?
--
View this message in context: http://openjpa.208410.n2.nabble.com/Constraint-violation-using-OneToOne-relationship-tp6978223p6980990.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: Constraint violation using OneToOne relationship
Posted by "M. Walter" <ma...@sbb.ch>.
Hi Rick,
thank you for the fast responses!
Yes I successfully tried the solution IBM proposed.
But why do I have to add this property just for OneToOne relationships in
order to make them work properly? For all other relationships this is not
necessary. I really would like to understand this difference.
Are there any side effects for the persistence mappings by adding the
openjpa.jdbc.SchemaFactory property to the PU? I'm suspicious of adding
properties to get things to work which in my opinion should work without
further configuration. I wonder if creating both sides of a OneToOne
relationship and persisting them by saving the parent entity is a special
case.
--
View this message in context: http://openjpa.208410.n2.nabble.com/Constraint-violation-using-OneToOne-relationship-tp6978223p6978516.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: Constraint violation using OneToOne relationship
Posted by Rick Curtis <cu...@gmail.com>.
Have you tried setting the following property ? It seemed to fix my
recreate of your scenario (on trunk anyway).
<property name="openjpa.jdbcSchemaFactory"
value="native(ForeignKeys=true)"/>
Thanks,
Rick
On Wed, Nov 9, 2011 at 9:23 AM, M. Walter <ma...@sbb.ch> wrote:
> Hi Rick,
>
> these are the properties for the unit test PU:
>
> <properties>
> <property name="openjpa.Log" value="DefaultLevel=INFO, SQL=WARN,
> JDBC=WARN, Query=TRACE, Schema=ERROR, Runtime=WARN" />
> <property name="openjpa.ConnectionFactoryProperties"
> value="PrintParameters=true, PrettyPrint=true" />
> <property name="openjpa.LockManager" value="version" />
> <property name="openjpa.jdbc.TransactionIsolation"
> value="read-committed" />
> <property name="openjpa.DetachState"
> value="fetch-groups(DetachedStateField=true)" />
> </properties>
>
> And these properties are for production environment:
>
> <properties>
> <property name="openjpa.ConnectionFactoryProperties"
> value="PrintParameters=true" />
> <property name="openjpa.LockManager" value="version" />
> <property name="openjpa.jdbc.TransactionIsolation"
> value="read-committed" />
> <property name="openjpa.DetachState"
> value="fetch-groups(DetachedStateField=true)" />
> </properties>
>
> --
>
--
*Rick Curtis*
Re: Constraint violation using OneToOne relationship
Posted by "M. Walter" <ma...@sbb.ch>.
Hi Rick,
these are the properties for the unit test PU:
<properties>
<property name="openjpa.Log" value="DefaultLevel=INFO, SQL=WARN,
JDBC=WARN, Query=TRACE, Schema=ERROR, Runtime=WARN" />
<property name="openjpa.ConnectionFactoryProperties"
value="PrintParameters=true, PrettyPrint=true" />
<property name="openjpa.LockManager" value="version" />
<property name="openjpa.jdbc.TransactionIsolation"
value="read-committed" />
<property name="openjpa.DetachState"
value="fetch-groups(DetachedStateField=true)" />
</properties>
And these properties are for production environment:
<properties>
<property name="openjpa.ConnectionFactoryProperties"
value="PrintParameters=true" />
<property name="openjpa.LockManager" value="version" />
<property name="openjpa.jdbc.TransactionIsolation"
value="read-committed" />
<property name="openjpa.DetachState"
value="fetch-groups(DetachedStateField=true)" />
</properties>
--
View this message in context: http://openjpa.208410.n2.nabble.com/Constraint-violation-using-OneToOne-relationship-tp6978223p6978287.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: Constraint violation using OneToOne relationship
Posted by Rick Curtis <cu...@gmail.com>.
What do you have set for properties in your persistence.xml ?
Thanks,
Rick
On Wed, Nov 9, 2011 at 8:57 AM, M. Walter <ma...@sbb.ch> wrote:
> We have an entity A having a OneToOne unidirectional relationship to
> entity B
> configured like this:
>
> In entity A:
> @OneToOne(cascade = CascadeType.ALL)
> @JoinColumn(name = "B_ID")
> private B b;
>
> Now we create new entities A and B and would like to persist them:
>
> final A a = new A();
> final B b = new B();
> a.setB(b);
>
> em.persist(a);
>
> We get a PersistenceException because OpenJPA tries to insert the entity
> with the foreign key first:
>
> <openjpa-1.2.3-SNAPSHOT-r422266:1053401 nonfatal general error>
> org.apache.openjpa.persistence.PersistenceException: ORA-02291: integrity
> constraint violated - parent key not found
> {prepstmnt 1708156368
> INSERT INTO A (ID, ROW_ERF_TSTAMP, ROW_ERF_USER, ROW_MUT_VERSION, B_ID)
> VALUES (?, ?, ?, ?, ?)
> [params=(long) 396, (Timestamp) 2011-11-09 15:38:12.048, (String) TEST,
> (int) 1, (long) 2772]} [code=2291, state=23000]
>
> Why does OpenJPA first try to insert entity A and not B? An how can we
> force
> OpenJPA to execute statements in the right order?
>
> Interestingly this works on our OneToMany relationships.
>
> In the internet I found
> https://issues.apache.org/jira/browse/OPENJPA-1961
> this and http://www-01.ibm.com/support/docview.wss?uid=swg1PK74266 this
> .
> But these issues are for OneToMany relationships. IBM provides a solution
> using the OpenJPA property openjpa.jdbc.SchemaFactory. But we would like to
> understand why persisting does not work in the OneToOne case above. So has
> somebody an explanation for our issue? Thank you in advance!
>
> We use WebSphere server 7.0.0.19 and OpenJPA 1.2.3.
>
> --
> View this message in context:
> http://openjpa.208410.n2.nabble.com/Constraint-violation-using-OneToOne-relationship-tp6978223p6978223.html
> Sent from the OpenJPA Users mailing list archive at Nabble.com.
>
--
*Rick Curtis*