Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

jpa join query on a subclass

I have the following relationships in JPA (hibernate).

Object X has two subclasses, Y and Z.

Object A has a manyToOne relationship to object X. (Note, this is a one-sided relationship so object X cannot see object A).

Now, I want to get the max value of a column in object A, but only where the relationship is of a specific subtype, ie...Y.

So, that equates to...get the max value of column1 in object A, across all instances of A where they have a relationship with Y. Is this possible? I'm a bit lost as how to query it.

I was thinking of something like:

String query = "SELECT MAX(a.columnName) FROM A a join a.x;
Query query = super.entityManager.createQuery(query);
query.execute();

However that doesn't take account of the subclass of X...so I'm a bit lost.

Any help would be much appreciated.

like image 353
Brian Avatar asked Jun 02 '10 11:06

Brian


1 Answers

JPA 2.0 allows queries to restrict the classes returned from a polymorphic query with the TYPE operator. From the JPA 2.0 specification:

4.6.17.4 Entity Type Expressions

An entity type expression can be used to restrict query polymorphism. The TYPE operator returns the exact type of the argument.

The syntax of an entity type expression is as follows:

entity_type_expression ::=
         type_discriminator |
         entity_type_literal |
         input_parameter
type_discriminator ::=
         TYPE(identification_variable |
                single_valued_object_path_expression |
                input_parameter )

An entity_type_literal is designated by the entity name.

The Java class of the entity is used as an input parameter to specify the entity type.

Examples:

SELECT e
FROM Employee e
WHERE TYPE(e) IN (Exempt, Contractor)

SELECT e
FROM Employee e
WHERE TYPE(e) IN (:empType1, :empType2)

SELECT e
FROM Employee e
WHERE TYPE(e) IN :empTypes

SELECT TYPE(e)
FROM Employee e
WHERE TYPE(e) <> Exempt

JPA 1.0 doesn't offer such a mechanism so if you're using JPA 1.0, this won't be possible using standard JPQL.

If you don't mind using proprietary HQL, then you can use the special property class:

Likewise, the special property class accesses the discriminator value of an instance in the case of polymorphic persistence. A Java class name embedded in the where clause will be translated to its discriminator value.

from Cat cat where cat.class = DomesticCat

So in your case, I'd try something like that:

SELECT MAX(a.columnName) FROM A a where a.x.class = Y
like image 108
Pascal Thivent Avatar answered Sep 22 '22 16:09

Pascal Thivent