JPA Criteria Query over an entity hierarchy using single table inheritance

Question

Say I have the following entities:

@Entity
@Inheritance(strategy = SINGLE_TABLE)
@DiscriminatorColumn(name = "type")
public abstract class BaseEntity { 
    private Date someDate;
    private Date otherDate;
    private boolean flag;
}

@Entity
@DiscriminatorValue("entity1")
public class Entity1 extends BaseEntity { 
    private String someProperty;
}

@Entity
@DiscriminatorValue("entity2")
public class Entity2 extends BaseEntity { 
    private String otherProperty;
}

I'm trying to build a criteria query that returns instances of BaseEntity based on properties in BaseEntity and both of the subclasses. So essentially I'm looking for a criteria query that corresponds to this pseudo-SQL:

SELECT * FROM <BaseEntity table name>
WHERE someDate < ? AND otherDate > ? AND flag = ?
AND someProperty = ? AND otherProperty = ?;

I'd rather not build two separate queries since they have so much overlap (i.e. most of the properties are in the base class). However, I haven't figured out a way to reference the subclass properties in the query if I declare BaseEntity as the root. Is it possible to build a criteria query like this?

UPDATE:

Maybe some code would clarify the question. I'd essentially like to do something like this:

CriteriaBuilder builder = ...;
CriteriaQuery<BaseEntity> query = ...;
Root<BaseEntity> root = ...;

query.select(root).where(builder.and(
        builder.lessThan(root.get(BaseEntity_.someDate), new Date()),
        builder.greaterThan(root.get(BaseEntity_.otherDate), new Date()),
        builder.isTrue(root.get(BaseEntity_.flag)),
        builder.equal(root.get(Entity1_.someProperty), "foo"),   <-- This won't work
        builder.equal(root.get(Entity2_.otherProperty), "bar")   <-- Neither will this
));

Now, I understand why the above code sample doesn't work, but I'd like to know if there's a way to get around it.

score 16 · Accepted Answer · answered Sep 05 '14 at 07:01

16

I managed to solve this by downcasting the BaseEntity root into new roots that correspond to the subclass types with CriteriaBuilder.treat() like this:

CriteriaBuilder builder = ...;
CriteriaQuery<BaseEntity> query = ...;
Root<BaseEntity> root = ...;
Root<Entity1> entity1 = builder.treat(root, Entity1.class);
Root<Entity2> entity2 = builder.treat(root, Entity2.class);

query.select(root).where(builder.and(
        builder.lessThan(root.get(BaseEntity_.someDate), new Date()),
        builder.greaterThan(root.get(BaseEntity_.otherDate), new Date()),
        builder.isTrue(root.get(BaseEntity_.flag)),
        builder.equal(entity1.get(Entity1_.someProperty), "foo"),
        builder.equal(entity2.get(Entity2_.otherProperty), "bar")
));

answered Sep 05 '14 at 07:01

Markus Yrjölä

859
1
12
25

not working it gives null.someDate and null.otherDate, illegal error – user1735921 Dec 28 '18 at 10:01
It worked now, I don't what was wrong, but it suddenly started working – user1735921 Jan 04 '19 at 07:24
What if you only want to select entities of those specific type and don't wanna enforce any other condition? – Amirhosein Al Jun 30 '21 at 18:25

score -2 · Answer 2 · answered Sep 02 '14 at 14:30

-2

Try Junction. You can always do something like this:

Criteria crit = session.createCriteria(YourClassName.class);
Criterion cr1 = Restrictions.lt("propertyname", propertyvalue);
Criterion cr2 = Restrictions.gt("propertyname", propertyvalue);
Criterion cr3 = Restrictions.eq("propertyname", propertyvalue);
Criterion cr4 = Restrictions.eq("propertyname", propertyvalue);
Junction cond = Restrictions.conjunction();
cond.add(cr1).add(cr2).add(cr3).add(cr4)
crit.add(cond);

I hope I got your question Correctly.

answered Sep 02 '14 at 14:30

Stanley Mungai

4,044
30
100
168

That solution is based purely on Hibernate, but I'm looking for one based on the JPA Criteria API. Also, I'd rather use the JPA2 metamodel for referencing the properties rather than the string identifiers. – Markus Yrjölä Sep 03 '14 at 05:40
The op asked question of JPA, not hibernate. – user1735921 Dec 28 '18 at 09:42

JPA Criteria Query over an entity hierarchy using single table inheritance

2 Answers2

Linked