> -----Ursprüngliche Nachricht----- > Von: Georg Nozicka [mailto:georg.nozi...@nozicka.at] > Gesendet: Montag, 2. April 2012 09:45 > An: users@openjpa.apache.org > Betreff: Re: AW: Unnecessary Join when using the Type > construct in JPA Query > > John, > > I have no idea why all the code (and also the sqls) is not > shown in the mail. When you browse the mailing list directly, > you can see them. > > Here the direct link: > http://openjpa.208410.n2.nabble.com/Unnecessary-Join-when-usin > g-the-Type-construct-in-JPA-Query-td7424993.html > http://openjpa.208410.n2.nabble.com/Unnecessary-Join-when-usin > g-the-Type-construct-in-JPA-Query-td7424993.html > > For all, who want to use just email, I post the content here > again, this time not formatted but hopefully with all content. > > Setup > Imagine the following entities: A Company with a 1:Many to > AddressLocation and another 1:Many to AddressPostal. > AddressLocation and AddressPostal are two different kind of > addresses. They are both subclasses of Address. > @Entity > public class Company { > > @Id > private long id; > > private String name; > > @OneToMany(fetch = FetchType.LAZY, mappedBy = > "company", cascade = { > CascadeType.MERGE, CascadeType.PERSIST }) > private List<AddressLocation> addressesLocation = new > ArrayList<AddressLocation>(); > > @OneToMany(fetch = FetchType.LAZY, mappedBy = > "company", cascade = { > CascadeType.MERGE, CascadeType.PERSIST }) > private List<AddressPostal> addressesPostal = new > ArrayList<AddressPostal>(); } > > @Entity > @Inheritance(strategy = InheritanceType.SINGLE_TABLE) > @DiscriminatorColumn(name = "KIND", columnDefinition = > "CHAR(2) NOT NULL", length = 2, discriminatorType = > DiscriminatorType.STRING) public class Address { > > @Id > @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = > ShopConstants.SEQ_GEN_SHOP) > private long id; > > private String street; > } > > @Entity > @DiscriminatorValue("AL") > public class AddressLocation extends Address { > > private int localAttr; > > @ManyToOne(fetch = FetchType.LAZY, cascade = { > CascadeType.MERGE, > CascadeType.PERSIST }) > @JoinColumn(name = "COMPOID") > private Company company; > > } > > @Entity > @DiscriminatorValue("AP") > public class AddressPostal extends Address { > > private int postalAttr; > > @ManyToOne(fetch = FetchType.LAZY, cascade = { > CascadeType.MERGE, > CascadeType.PERSIST }) > @JoinColumn(name = "COMPOID") > private Company company; > > } > > The goal > The goal is to have to following two queries which both have the same > result: > - Match just by the name of the company. > - Match by the name of the company and street of the location address. > The result is always the name of the company and the street > of the location address. > > Version1: Hand written native queries > Query by company name only > SELECT t0.name, t1.street > FROM Company t0 LEFT JOIN Address t1 ON t0.id = t1.compoid > WHERE (t0.name = 'c1' AND t1.kind = 'AL') > > Query by company name and street of location address SELECT > t0.name, t1.street > FROM Company t0 LEFT JOIN Address t1 ON t0.id = t1.compoid > WHERE (t0.name = 'c1' AND t1.kind = 'AL' and t1.street = > 'location1_1') > > The question is if it is possible to formulate these queries > with JPA Query Language in such a way, that also just one > join is used. So I tried the versions below. > > Version 2: JPA Query > Query by company name only > > select new CompanyList(c.name, al.street) from Company c left > join c.addressesLocation al where c.name = :name > > Results is the following SQL: > SELECT t0.name, t1.street > FROM Company t0 LEFT OUTER JOIN Address t1 ON t0.id = t1.compoid > WHERE (t0.name = ?) > > Query by company name and street of location address select > new CompanyList(c.name, al.street) from Company c left join > c.addressesLocation al where c.name = :name and al.street = :street > > Results is the following SQL: > SELECT t0.name, t1.street > FROM Company t0 LEFT OUTER JOIN Address t1 ON t0.id = t1.compoid > WHERE (t0.name = ? AND t1.street = ?) > > Well, both queries do almost what I original wanted, i.e. > just two joins, except, that they do not limit the queries to > the location address. > Therefore the queries are not semantically correct. > > Version 3: JPA Query with Type construct To limit the query > to a specific type, in my case the type AddressLocation, the > type construct must be used. > > Query by company name only > select new CompanyList(c.name, al.street) from Company c left > join c.addressesLocation al where c.name = :name and type(al) > = AddressLocation > > Results is the following SQL: > SELECT t2.id, t0.name, t1.street > FROM Company t0 LEFT OUTER JOIN Address t1 ON t0.id = t1.compoid, > Address t2 > WHERE (t0.name = ? AND t1.kind = 'AL') > > Query by company name and street of location address select > new CompanyList(c.name, al.street) from Company c left join > c.addressesLocation al where c.name = :name and al.street = > :street and > type(al) = AddressLocation > > Results is the following SQL: > SELECT t2.id, t0.name, t1.street > FROM Company t0 LEFT OUTER JOIN Address t1 ON t0.id = t1.compoid, > Address t2 > WHERE (t0.name = ? AND t1.street = ? AND t1.kind = 'AL') > > From a conceptional point of view these queries do exactly > what I want, except, that two joins are used. Actually I have > no idea why in both cases the second join to Address is done, > the alias t2 is only used in the select > (t2.id) where I do not need it. > > Conclusion and Question > The question is, if there is another way to express the JPA > Query so that only one join is used and that only addresses > of type AddressLocation are taken into account. Or is the > only solution, to step back to native queries which would > really be a pity because we would like to use higher level > APIs like the Criteria API or Querydsl which rely on JPA > Queries. I.e. as long as at the JPA Query level these kind of > problems are not solved, we can not use Criteria API or Querydsl. > > > -- > View this message in context: > http://openjpa.208410.n2.nabble.com/Unnecessary-Join-when-usin > g-the-Type-construct-in-JPA-Query-tp7424993p7428607.html > Sent from the OpenJPA Users mailing list archive at Nabble.com. >
Just an idea ... Why not join directly to the AdressLocation Entity? John
