You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openjpa.apache.org by Tobias Trelle <to...@codecentric.de> on 2011/10/17 14:42:22 UTC
Unnecessary Join on bidirectional OneToOne relation
Hi,
our DBAs detected a strange SQL statement in conjunction w/ a bidirectional
one-to-one relationship between entities. I compiled a minimal example that
demonstrates the case:
When querying an entity of type A the following SQL is executed:
The select starts at table A and performs a join to table B (fine). Then
there's another join back to A again (strange). We are using OpenJPA 2.0.1.
The double join happens (at least) w/ dictionairies for Derby and DB2.
During my research, I came upon this ticket:
https://issues.apache.org/jira/browse/OPENJPA-292 which seems to be
unresolved. Can I assume that this error still exists? Did the patch go into
the 2.x versions?
A possible workaround (using lazy loading and fetching the relationships
with fetch join queries) is not applicable in our situation since the
relationships are not necessarily fetched by queries.
I am also aware that a bidirectional one-to-one relationship is not the best
design, but the data is spread on two tables and some legacy COBOL
transaction are accessing these tables. A schema change is not possible.
Any ideas?
TIA,
Tobias
--
View this message in context: http://openjpa.208410.n2.nabble.com/Unnecessary-Join-on-bidirectional-OneToOne-relation-tp6900378p6900378.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: Unnecessary Join on bidirectional OneToOne relation
Posted by Rick Curtis <cu...@gmail.com>.
Tobias -
Hmm, I thought for sure that this worked.... but I stand corrected. You are
correc that when selecting an Entity that is the owner of a bidirectional,
eager 1-1 (perhaps others) we issue an extra join. You probably already
found this out.... but if you were to call em.find(B.class, [id]), we do
issue the correct SQL.
Unfortunately, I don't have a very good solution for you at this time... You
could mark A->B as lazy and every time you call em.find(A.class, [id]), you
could reference the B field. I'm not sure if this would be better or worse
than the extra join.
Thanks,
Rick
On Tue, Oct 18, 2011 at 9:32 AM, Tobias Trelle <tobias.trelle@codecentric.de
> wrote:
>
> Rick Curtis wrote:
> >
> > Sorry I didn't ask this in my initial email.... what version of OpenJPA
> > are
> > you using, and how are you enhancing your Entities?
> >
>
> I'm using OpenJPA 2.0.1.
>
> The double join happens both with runtime and build time enhanced classes.
> The build time enhancement is done with an ant task:
>
> openjpa-enhance:
> [openjpac] INFO 16:29:51 - No targets were given. Running on all classes
> in your persistent classes list, or all metadata files in classpath
> directories if you have not listed your persistent classes. Use -help to
> display tool usage information.
> [openjpac] TRACE 16:29:51 - Enhancer running on type
> "com.xyz.framework.util.db.jpa.oto.A".
> [openjpac] TRACE 16:29:52 - Enhancer running on type
> "com.xyz.framework.util.db.jpa.oto.B".
> BUILD SUCCESSFUL
> Total time: 2 seconds
>
> Cheers,
> Tobias
>
>
> --
>
--
*Rick Curtis*
Re: Unnecessary Join on bidirectional OneToOne relation
Posted by Tobias Trelle <to...@codecentric.de>.
Rick Curtis wrote:
>
> Sorry I didn't ask this in my initial email.... what version of OpenJPA
> are
> you using, and how are you enhancing your Entities?
>
I'm using OpenJPA 2.0.1.
The double join happens both with runtime and build time enhanced classes.
The build time enhancement is done with an ant task:
openjpa-enhance:
[openjpac] INFO 16:29:51 - No targets were given. Running on all classes
in your persistent classes list, or all metadata files in classpath
directories if you have not listed your persistent classes. Use -help to
display tool usage information.
[openjpac] TRACE 16:29:51 - Enhancer running on type
"com.xyz.framework.util.db.jpa.oto.A".
[openjpac] TRACE 16:29:52 - Enhancer running on type
"com.xyz.framework.util.db.jpa.oto.B".
BUILD SUCCESSFUL
Total time: 2 seconds
Cheers,
Tobias
--
View this message in context: http://openjpa.208410.n2.nabble.com/Unnecessary-Join-on-bidirectional-OneToOne-relation-tp6900378p6904837.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: Unnecessary Join on bidirectional OneToOne relation
Posted by Rick Curtis <cu...@gmail.com>.
Sorry I didn't ask this in my initial email.... what version of OpenJPA are
you using, and how are you enhancing your Entities?
On Tue, Oct 18, 2011 at 3:02 AM, Tobias Trelle <tobias.trelle@codecentric.de
> wrote:
> Dear Rick,
>
>
> Rick Curtis wrote:
> >
> > How are you mapping this relationship? Can you post code snippets from
> > both
> > sides of the relationship?
> >
>
> Here's the full code of my minial example:
>
> @Entity
> public class A {
>
> @Id
> int id;
>
> @OneToOne(cascade = CascadeType.ALL)
> @JoinColumn(name = "id")
> private B b;
>
> public int getId() {
> return id;
> }
>
> public void setId(int id) {
> this.id = id;
> }
>
> public B getB() {
> return b;
> }
>
> public void setB(B b) {
> this.b = b;
> }
>
> }
>
> @Entity
> public class B {
>
> @Id
> private int id;
>
> @OneToOne(mappedBy = "b")
> private A a;
>
> public int getId() {
> return id;
> }
>
> public void setId(int id) {
> this.id = id;
> }
>
> public A getA() {
> return a;
> }
>
> public void setA(A a) {
> this.a = a;
> }
>
> }
>
> My test code looks like this:
>
> A a;
> B b;
> EntityManager em = getEntityManager();
> EntityTransaction tx = em.getTransaction();
>
> a = new A();
> a.setId(1);
>
> b = new B();
> b.setId(1);
>
> a.setB(b);
> b.setA(a);
>
> tx.begin();
> em.persist(a);
> tx.commit();
>
> em.clear();
> a = em.find(A.class, 1);
>
> which produces the following SQL select statement:
>
> TRACE 09:59:19 - <t 13741320, conn 11915355> executing prepstmnt 10188622
> SELECT t1.id, t2.id
> FROM A t0 INNER JOIN B t1 ON t0.id = t1.id LEFT OUTER JOIN A t2 ON
> t1.id
> = t2.id
> WHERE t0.id = ?
> [params=(int) 1]
> TRACE 09:59:19 - <t 13741320, conn 11915355> [15 ms] spent
>
> TIA,
> Tobias
> --
>
*Rick Curtis*
Re: Unnecessary Join on bidirectional OneToOne relation
Posted by Tobias Trelle <to...@codecentric.de>.
Dear Rick,
Rick Curtis wrote:
>
> How are you mapping this relationship? Can you post code snippets from
> both
> sides of the relationship?
>
Here's the full code of my minial example:
@Entity
public class A {
@Id
int id;
@OneToOne(cascade = CascadeType.ALL)
@JoinColumn(name = "id")
private B b;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public B getB() {
return b;
}
public void setB(B b) {
this.b = b;
}
}
@Entity
public class B {
@Id
private int id;
@OneToOne(mappedBy = "b")
private A a;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public A getA() {
return a;
}
public void setA(A a) {
this.a = a;
}
}
My test code looks like this:
A a;
B b;
EntityManager em = getEntityManager();
EntityTransaction tx = em.getTransaction();
a = new A();
a.setId(1);
b = new B();
b.setId(1);
a.setB(b);
b.setA(a);
tx.begin();
em.persist(a);
tx.commit();
em.clear();
a = em.find(A.class, 1);
which produces the following SQL select statement:
TRACE 09:59:19 - <t 13741320, conn 11915355> executing prepstmnt 10188622
SELECT t1.id, t2.id
FROM A t0 INNER JOIN B t1 ON t0.id = t1.id LEFT OUTER JOIN A t2 ON t1.id
= t2.id
WHERE t0.id = ?
[params=(int) 1]
TRACE 09:59:19 - <t 13741320, conn 11915355> [15 ms] spent
TIA,
Tobias
--
View this message in context: http://openjpa.208410.n2.nabble.com/Unnecessary-Join-on-bidirectional-OneToOne-relation-tp6900378p6903642.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: Unnecessary Join on bidirectional OneToOne relation
Posted by Rick Curtis <cu...@gmail.com>.
> https://issues.apache.org/jira/browse/OPENJPA-292 which seems to be
unresolved.
You are correct that the JIRA is unresolved... but but it appears that some
code was committed. By looking at the unit tests, it looks like the issue
may have been resolved.
How are you mapping this relationship? Can you post code snippets from both
sides of the relationship?
On Mon, Oct 17, 2011 at 7:42 AM, Tobias Trelle <tobias.trelle@codecentric.de
> wrote:
> Hi,
>
> our DBAs detected a strange SQL statement in conjunction w/ a bidirectional
> one-to-one relationship between entities. I compiled a minimal example that
> demonstrates the case:
>
>
>
> When querying an entity of type A the following SQL is executed:
>
>
>
> The select starts at table A and performs a join to table B (fine). Then
> there's another join back to A again (strange). We are using OpenJPA 2.0.1.
> The double join happens (at least) w/ dictionairies for Derby and DB2.
>
> During my research, I came upon this ticket:
> https://issues.apache.org/jira/browse/OPENJPA-292 which seems to be
> unresolved. Can I assume that this error still exists? Did the patch go
> into
> the 2.x versions?
>
> A possible workaround (using lazy loading and fetching the relationships
> with fetch join queries) is not applicable in our situation since the
> relationships are not necessarily fetched by queries.
>
> I am also aware that a bidirectional one-to-one relationship is not the
> best
> design, but the data is spread on two tables and some legacy COBOL
> transaction are accessing these tables. A schema change is not possible.
>
> Any ideas?
>
> TIA,
> Tobias
>
>
> --
*Rick Curtis*