You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@openjpa.apache.org by Jacek Laskowski <ja...@laskowski.net.pl> on 2007/11/28 16:57:51 UTC
Trouble understanding JOINs
Hi,
I've got two entities: Osoba (Whole) and Konto (Part) in OneToMany
association. Each contains id and name attributes. I decided to test
inner and left fetch joins.
I created 2 Osoba's, but only one got 2 Konto's.
When I run
SELECT o FROM Osoba o
it returns 2 entities which is fine. However, when I run
SELECT o FROM Osoba o JOIN FETCH o.konta
it returns 1 entity which is still fine but the number of Konta
entities is...4 (!).
When I changed the type of Osoba.konta (plural of konto in Polish)
from List<Konto> to Set<Konto> the above query returned 2 which was
what I had first expected.
Could anyone explain it to me? I seem to be missing some SQL classes
on how joins are supposed to work so any pointers to documentation
would be fine too.
Here the entities go (with Set<Konto>):
package pl.jaceklaskowski.jpa;
import java.io.Serializable;
import java.util.HashSet;
import java.util.Set;
import javax.persistence.CascadeType;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;
import javax.persistence.OneToMany;
@Entity
@NamedQueries({
@NamedQuery(name = "Osoba.poImieniu", query = "SELECT o FROM Osoba
o WHERE o.imie = :imie"),
@NamedQuery(name = "Osoba.wszystkieOsoby", query = "SELECT o FROM Osoba o"),
@NamedQuery(name = "Osoba.wszystkieOsoby.JOIN_FETCH", query =
"SELECT o FROM Osoba o JOIN FETCH o.konta"),
@NamedQuery(name = "Osoba.wszystkieOsoby.LEFT_JOIN_FETCH", query =
"SELECT o FROM Osoba o LEFT JOIN FETCH o.konta")
})
public class Osoba implements Serializable {
private static final long serialVersionUID = 1L;
private int id;
private String imie;
private Set<Konto> konta = new HashSet<Konto>();
protected Osoba() {
}
public Osoba(String imie) {
this.imie = imie;
}
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
@OneToMany(cascade=CascadeType.PERSIST)
public Set<Konto> getKonta() {
return konta;
}
public void setKonta(Set<Konto> konta) {
this.konta = konta;
}
public void addKonto(Konto konto) {
getKonta().add(konto);
}
public String getImie() {
return imie;
}
public void setImie(String imie) {
this.imie = imie;
}
@Override
public String toString() {
return "Osoba[id=" + getId() + "]";
}
}
package pl.jaceklaskowski.jpa;
import java.io.Serializable;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
@Entity
public class Konto implements Serializable {
private static final long serialVersionUID = 1L;
private int id;
private String numer;
protected Konto() {
}
public Konto(String numer) {
this.numer = numer;
}
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getNumer() {
return numer;
}
public void setNumer(String numer) {
this.numer = numer;
}
@Override
public String toString() {
return "Konto[id=" + getId() + "]";
}
}
Jacek
--
Jacek Laskowski
http://www.JacekLaskowski.pl
Re: Trouble understanding JOINs
Posted by Jacek Laskowski <ja...@laskowski.net.pl>.
On Nov 29, 2007 11:36 AM, <pl...@gmail.com> wrote:
> Based on that description, it sounds like a bug in OpenJPA's list
> handling somewhere.
>
> Do the SQL statements issued differ?
What SQLs are you asking about? Unless I'm mistaken I think they do. I
created a sample application - a maven project - so it's easy to
reproduce. Just extract the archive [1] and run mvn test.
Jacek
[1] http://www.jaceklaskowski.pl/aplikacje/jpa-joins.zip
--
Jacek Laskowski
http://www.JacekLaskowski.pl
Re: Trouble understanding JOINs
Posted by catalina wei <ca...@gmail.com>.
This a bug related to OPENJPA-135. That issue reported that OpenJPA
implementation not conforming to JPA spec where duplicate is expected but
OpenJPA is not
returning duplicates (for JOIN FETCH queries without DISTINCT keyword).
By SELECT *DISTINCT *.. JOIN Fetch, it is supposed to eliminate duplicates
while OpenJPA is doing exactly the opposite (not eliminating the
duplicates).
I will look into OPENJPA-135 (meanwhile as been assigned to me by Kevin
Sutter) as soon as I have a chance to work on it.
Catalina
On 11/29/07, plinskey@gmail.com <pl...@gmail.com> wrote:
>
> Hi Jacek,
>
> Based on that description, it sounds like a bug in OpenJPA's list
> handling somewhere.
>
> Do the SQL statements issued differ?
>
> -Patrick
>
> On 11/28/07, Jacek Laskowski <ja...@laskowski.net.pl> wrote:
> > Hi,
> >
> > I've got two entities: Osoba (Whole) and Konto (Part) in OneToMany
> > association. Each contains id and name attributes. I decided to test
> > inner and left fetch joins.
> >
> > I created 2 Osoba's, but only one got 2 Konto's.
> >
> > When I run
> >
> > SELECT o FROM Osoba o
> >
> > it returns 2 entities which is fine. However, when I run
> >
> > SELECT o FROM Osoba o JOIN FETCH o.konta
> >
> > it returns 1 entity which is still fine but the number of Konta
> > entities is...4 (!).
> >
> > When I changed the type of Osoba.konta (plural of konto in Polish)
> > from List<Konto> to Set<Konto> the above query returned 2 which was
> > what I had first expected.
> >
> > Could anyone explain it to me? I seem to be missing some SQL classes
> > on how joins are supposed to work so any pointers to documentation
> > would be fine too.
> >
> > Here the entities go (with Set<Konto>):
> >
> > package pl.jaceklaskowski.jpa;
> >
> > import java.io.Serializable;
> > import java.util.HashSet;
> > import java.util.Set;
> > import javax.persistence.CascadeType;
> > import javax.persistence.Entity;
> > import javax.persistence.GeneratedValue;
> > import javax.persistence.GenerationType;
> > import javax.persistence.Id;
> > import javax.persistence.NamedQueries;
> > import javax.persistence.NamedQuery;
> > import javax.persistence.OneToMany;
> >
> > @Entity
> > @NamedQueries({
> > @NamedQuery(name = "Osoba.poImieniu", query = "SELECT o FROM Osoba
> > o WHERE o.imie = :imie"),
> > @NamedQuery(name = "Osoba.wszystkieOsoby", query = "SELECT o FROM
> Osoba
> > o"),
> > @NamedQuery(name = "Osoba.wszystkieOsoby.JOIN_FETCH", query =
> > "SELECT o FROM Osoba o JOIN FETCH o.konta"),
> > @NamedQuery(name = "Osoba.wszystkieOsoby.LEFT_JOIN_FETCH", query =
> > "SELECT o FROM Osoba o LEFT JOIN FETCH o.konta")
> > })
> > public class Osoba implements Serializable {
> >
> > private static final long serialVersionUID = 1L;
> > private int id;
> > private String imie;
> > private Set<Konto> konta = new HashSet<Konto>();
> >
> > protected Osoba() {
> > }
> >
> > public Osoba(String imie) {
> > this.imie = imie;
> > }
> >
> > @Id
> > @GeneratedValue(strategy = GenerationType.AUTO)
> > public int getId() {
> > return id;
> > }
> >
> > public void setId(int id) {
> > this.id = id;
> > }
> >
> > @OneToMany(cascade=CascadeType.PERSIST)
> > public Set<Konto> getKonta() {
> > return konta;
> > }
> >
> > public void setKonta(Set<Konto> konta) {
> > this.konta = konta;
> > }
> >
> > public void addKonto(Konto konto) {
> > getKonta().add(konto);
> > }
> >
> > public String getImie() {
> > return imie;
> > }
> >
> > public void setImie(String imie) {
> > this.imie = imie;
> > }
> >
> > @Override
> > public String toString() {
> > return "Osoba[id=" + getId() + "]";
> > }
> > }
> >
> > package pl.jaceklaskowski.jpa;
> >
> > import java.io.Serializable;
> > import javax.persistence.Entity;
> > import javax.persistence.GeneratedValue;
> > import javax.persistence.GenerationType;
> > import javax.persistence.Id;
> >
> > @Entity
> > public class Konto implements Serializable {
> >
> > private static final long serialVersionUID = 1L;
> > private int id;
> > private String numer;
> >
> > protected Konto() {
> > }
> >
> > public Konto(String numer) {
> > this.numer = numer;
> > }
> >
> > @Id
> > @GeneratedValue(strategy = GenerationType.AUTO)
> > public int getId() {
> > return id;
> > }
> >
> > public void setId(int id) {
> > this.id = id;
> > }
> >
> > public String getNumer() {
> > return numer;
> > }
> >
> > public void setNumer(String numer) {
> > this.numer = numer;
> > }
> >
> > @Override
> > public String toString() {
> > return "Konto[id=" + getId() + "]";
> > }
> > }
> >
> > Jacek
> >
> > --
> > Jacek Laskowski
> > http://www.JacekLaskowski.pl
> >
>
>
> --
> Patrick Linskey
> 202 669 5907
>
Re: Trouble understanding JOINs
Posted by pl...@gmail.com.
Hi Jacek,
Based on that description, it sounds like a bug in OpenJPA's list
handling somewhere.
Do the SQL statements issued differ?
-Patrick
On 11/28/07, Jacek Laskowski <ja...@laskowski.net.pl> wrote:
> Hi,
>
> I've got two entities: Osoba (Whole) and Konto (Part) in OneToMany
> association. Each contains id and name attributes. I decided to test
> inner and left fetch joins.
>
> I created 2 Osoba's, but only one got 2 Konto's.
>
> When I run
>
> SELECT o FROM Osoba o
>
> it returns 2 entities which is fine. However, when I run
>
> SELECT o FROM Osoba o JOIN FETCH o.konta
>
> it returns 1 entity which is still fine but the number of Konta
> entities is...4 (!).
>
> When I changed the type of Osoba.konta (plural of konto in Polish)
> from List<Konto> to Set<Konto> the above query returned 2 which was
> what I had first expected.
>
> Could anyone explain it to me? I seem to be missing some SQL classes
> on how joins are supposed to work so any pointers to documentation
> would be fine too.
>
> Here the entities go (with Set<Konto>):
>
> package pl.jaceklaskowski.jpa;
>
> import java.io.Serializable;
> import java.util.HashSet;
> import java.util.Set;
> import javax.persistence.CascadeType;
> import javax.persistence.Entity;
> import javax.persistence.GeneratedValue;
> import javax.persistence.GenerationType;
> import javax.persistence.Id;
> import javax.persistence.NamedQueries;
> import javax.persistence.NamedQuery;
> import javax.persistence.OneToMany;
>
> @Entity
> @NamedQueries({
> @NamedQuery(name = "Osoba.poImieniu", query = "SELECT o FROM Osoba
> o WHERE o.imie = :imie"),
> @NamedQuery(name = "Osoba.wszystkieOsoby", query = "SELECT o FROM Osoba
> o"),
> @NamedQuery(name = "Osoba.wszystkieOsoby.JOIN_FETCH", query =
> "SELECT o FROM Osoba o JOIN FETCH o.konta"),
> @NamedQuery(name = "Osoba.wszystkieOsoby.LEFT_JOIN_FETCH", query =
> "SELECT o FROM Osoba o LEFT JOIN FETCH o.konta")
> })
> public class Osoba implements Serializable {
>
> private static final long serialVersionUID = 1L;
> private int id;
> private String imie;
> private Set<Konto> konta = new HashSet<Konto>();
>
> protected Osoba() {
> }
>
> public Osoba(String imie) {
> this.imie = imie;
> }
>
> @Id
> @GeneratedValue(strategy = GenerationType.AUTO)
> public int getId() {
> return id;
> }
>
> public void setId(int id) {
> this.id = id;
> }
>
> @OneToMany(cascade=CascadeType.PERSIST)
> public Set<Konto> getKonta() {
> return konta;
> }
>
> public void setKonta(Set<Konto> konta) {
> this.konta = konta;
> }
>
> public void addKonto(Konto konto) {
> getKonta().add(konto);
> }
>
> public String getImie() {
> return imie;
> }
>
> public void setImie(String imie) {
> this.imie = imie;
> }
>
> @Override
> public String toString() {
> return "Osoba[id=" + getId() + "]";
> }
> }
>
> package pl.jaceklaskowski.jpa;
>
> import java.io.Serializable;
> import javax.persistence.Entity;
> import javax.persistence.GeneratedValue;
> import javax.persistence.GenerationType;
> import javax.persistence.Id;
>
> @Entity
> public class Konto implements Serializable {
>
> private static final long serialVersionUID = 1L;
> private int id;
> private String numer;
>
> protected Konto() {
> }
>
> public Konto(String numer) {
> this.numer = numer;
> }
>
> @Id
> @GeneratedValue(strategy = GenerationType.AUTO)
> public int getId() {
> return id;
> }
>
> public void setId(int id) {
> this.id = id;
> }
>
> public String getNumer() {
> return numer;
> }
>
> public void setNumer(String numer) {
> this.numer = numer;
> }
>
> @Override
> public String toString() {
> return "Konto[id=" + getId() + "]";
> }
> }
>
> Jacek
>
> --
> Jacek Laskowski
> http://www.JacekLaskowski.pl
>
--
Patrick Linskey
202 669 5907