You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openjpa.apache.org by Boblitz John <Jo...@BERTSCHI.com> on 2012/04/02 08:20:59 UTC
AW: Unnecessary Join when using the Type construct in JPA Query
> -----Ursprüngliche Nachricht-----
> Von: Georg Nozicka [mailto:georg.nozicka@nozicka.at]
> Gesendet: Samstag, 31. März 2012 17:25
> An: users@openjpa.apache.org
> Betreff: Unnecessary Join when using the Type construct in JPA Query
>
> 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.
>
>
>
> 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
>
>
>
> Query by company name and street of location address
>
>
>
> 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
>
>
>
>
> Results is the following SQL:
>
>
> Query by company name and street of location address
>
>
>
>
> Results is the following SQL:
>
>
> 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
>
>
>
> Results is the following SQL:
>
>
> Query by company name and street of location address
>
>
>
> Results is the following SQL:
>
>
> 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-tp7424993p7424993.html
> Sent from the OpenJPA Users mailing list archive at Nabble.com.
Hello Georg,
It would seem that your SQL Statements are missing in the original mail, which makes
it difficult to visualize where your problem may lie.
Can you provide them?

John
----
Who is General Failure, and why is he reading my hard disk?
Re: AW: AW: Unnecessary Join when using the Type construct in JPA
Query
Posted by Georg Nozicka <ge...@nozicka.at>.
Boblitz John wrote
>
>
> Just an idea ...
>
> Why not join directly to the AdressLocation Entity?
>
> John
>
John,
I’m not sure what you mean with „join directly to the AdressLocation
Entity”.
For both (i.e. filter by name or filter by name and street) samples (Version
3) I do the join to AddressLocation by specifying “… left join
c.addressesLocation al …”.
Maybe you can give a sample?
Georg
--
View this message in context: http://openjpa.208410.n2.nabble.com/Unnecessary-Join-when-using-the-Type-construct-in-JPA-Query-tp7424993p7429117.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
AW: AW: Unnecessary Join when using the Type construct in JPA Query
Posted by Boblitz John <Jo...@BERTSCHI.com>.
> -----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
Re: AW: Unnecessary Join when using the Type construct in JPA Query
Posted by Georg Nozicka <ge...@nozicka.at>.
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-using-the-Type-construct-in-JPA-Query-td7424993.html
http://openjpa.208410.n2.nabble.com/Unnecessary-Join-when-using-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-using-the-Type-construct-in-JPA-Query-tp7424993p7428607.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.