openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Boblitz John <John.Bobl...@BERTSCHI.com>
Subject AW: AW: Unnecessary Join when using the Type construct in JPA Query
Date Mon, 02 Apr 2012 08:15:48 GMT
 

> -----Urspr√ľngliche Nachricht-----
> Von: Georg Nozicka [mailto:georg.nozicka@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

Mime
View raw message