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.

Skep

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.

Skep

I'm getting the usual error: java.lang.IllegalArgumentException: Unable to locate Attribute with the the given name [inheritedTags] on this ManagedType [...]. What am I doing wrong? (My subclass is abstract) I really need the answer please help! – Ombre

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 ?
    )

Jillayne

You are accessing Customer_.name, which is the same as Contact_.name. it will not work if you use a property defined on Customer and results in compilation error The method get(SingularAttribute<? super Contact,Y>) in the type Path<Contact> is not applicable for the arguments (SingularAttribute<Customer,String>) – Skep

See my update, if you ommit the type of root that problem does not occur. – Jillayne

You're messing with something that has little to do with metamodel query translation... but your update pointed me in the right direction. Though not worth the bounty or acceptance, still it's worth a couple of +1. Thanks. – Skep