openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Georg Nozicka <georg.nozi...@nozicka.at>
Subject Re: AW: Unnecessary Join when using the Type construct in JPA Query
Date Mon, 02 Apr 2012 07:44:36 GMT
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.

Mime
View raw message