JPA criteria API query subclass property

Question

I want to execute a query matching a specific subclass property, so I'm trying to use treat().

In this example I want:

all subjects with name starting with 'a',
or all subjects, which are persons, with last name starting with 'a'

private List<Subject> q1()
{
    CriteriaBuilder b = em.getCriteriaBuilder();

    CriteriaQuery<Subject> q = b.createQuery(Subject.class);
    Root<Subject> r = q.from(Subject.class);
    q.select(r);
    q.distinct(true);
    q.where(
        b.or(
            b.like(r.get(Subject_.name), "a%"),
            b.like(b.treat(r, Person.class).get(Person_.lastName), "a%")));

    return em.createQuery(q).getResultList();
}

Obviously, Person extends Subject, Subject is abstract, inheritance is SINGLE_TABLE, and Subject has @DiscriminatorOptions(force = true) for other reasons (non influent).

But the generated SQL is this:

select distinct subject0_.ID as ID2_71_, subject0_.CODE as CODE3_71_, ...
from SUBJECT subject0_ 
where subject0_.DTYPE='Person' and (subject0_.name like 'a%' or subject0_.lastName like 'a%')

while I'm expecting:

select distinct subject0_.ID as ID2_71_, subject0_.CODE as CODE3_71_, ...
from SUBJECT subject0_ 
where subject0_.name like 'a%' or (subject0_.DTYPE='Person' and subject0_.lastName like 'a%')

Is there a way to produce the expected query using criteria builder?

Note that

• using another Root - q.from(Person.class)
• using subqueries - q.subquery(Person.class)
• moving lastName field up to Subject
• using Native Queries
• using Entity Graphs

are not acceptable.

I'm interested in something which can be declared and used directly in WHERE clause (produced only from CriteriaBuilder and/or the single Root, just like the treat() clause), if it does exist.

Answer 1

The solution is, with Hibernate and in this specific scenario, very simple:

private List<Subject> q1()
{
    CriteriaBuilder b = em.getCriteriaBuilder();

    CriteriaQuery<Subject> q = b.createQuery(Subject.class);
    Root<Subject> r = q.from(Subject.class);
    q.select(r);
    q.distinct(true);
    q.where(
        b.or(
            b.like(r.get(Subject_.name), "a%"),
            b.and(
                b.equal(r.type(), Person.class),
                b.like(((Root<Person>) (Root<?>) r).get(Person_.lastName), "a%"))));

    return em.createQuery(q).getResultList();
}

Note the double cast, which avoids compilation error, and allows to perform the query clause on the same table. This generates:

select distinct subject0_.ID as ID2_71_, subject0_.CODE as CODE3_71_, ...
from SUBJECT subject0_ 
where subject0_.DTYPE in ('Office', 'Team', 'Role', 'Person', ...) 
    and (subject0_.name like 'a%' 
        or subject0_.DTYPE='Person' and (subject0_.lastName like 'a%'))

There's no need to change the model or anything else.

Answer 2

Update:

Your expecting sql - while it can be genereated with pure jpa crtieria api - it won't work and will cast an exception becasue you (hibernate) cannot Instantiate the abstract class subject.

Caused by: org.hibernate.InstantiationException: Cannot instantiate abstract class or interface:

If the class is not abstract it works. like this:

CriteriaBuilder b = getEntityManager().getCriteriaBuilder();
CriteriaQuery<Contact> q;
q = b.createQuery(Contact.class);
Root r = q.from(Contact.class);

   q.select(r);
   q.distinct(true);
q.where(
        b.or(
                b.like(r.get(Contact_.name),"t%"),
                b.and(
                        b.equal(r.get(Contact_.contact_type),"customer"),
                        b.like(r.get(Customer_.lastName),"t%")
                )
        )
);

return getEntityManager().createQuery(q).getResultList();

(My Customer is your person, and my subject class is the contact class)

---

Accessing the discriminator column as a readonly might be a working workaround for you. if discriminator_column is contact_type do:

@Column(name = "contact_type",insertable = false,updatable = false)
@XmlTransient
private String contact_type;

Then Contact being an abstract class with customer as subclass the following :

CriteriaBuilder b = getEntityManager().getCriteriaBuilder();
        CriteriaQuery<Contact> q = b.createQuery(Contact.class);
           Root<Contact>  r = q.from(Contact.class);
           q.distinct(true);

           q.where(
                b.or(
                        b.like(r.get(Contact_.name), "t%"),
                        b.and(
                                b.equal(r.get(Customer_.contact_type), "customer"),
                                b.like(r.get(Customer_.name), "%t")
                        )
                )
                );
           return getEntityManager().createQuery(q).getResultList();

Produces

select

distinct contact0_.id as id2_1_,
    contact0_.contact_type as contact_1_1_,
    contact0_.name as name3_1_ 
from
    Contact contact0_ 
where
    contact0_.name like ? 
    or contact0_.contact_type=? 
    and (
        contact0_.name like ?
    )