Hibernate criteria restriction on join column

Question

My Hibernate bean ContentElementTypeProperty references another Hibernate Bean TestUnitType (Many to One).
TestUnitType is a field of ContentElementTypeProperty.
In the database, testunittypeid is a column in the table contentelementtypeproperty.

I am seeking to retrieve all rows from contentelementtypeproperty WHERE testunittypeid is null OR testunittypeid = [given Long value]

The method shown below returns 0 results.

However, if I delete: either

.add(Restrictions.isNull("testUnitType"));

or

cr.createCriteria("testUnitType")
                .add(Restrictions.eq("id", tutId));

I get results (but obviously not all the rows I need).

How do I combine these two so that they make an OR criteria given that each is created under a distinct createCriteria call?

    @Transactional(propagation = Propagation.MANDATORY)
public List<ContentElementTypeProperty> getContentElementTypePropertiesForTut(Long businessId, Long tutId)
        throws TestStructureException
{
    SS.getLogger().debug("getContentElementTypePropertiesForTut business id:"+businessId +" tutid: "+tutId);
    try
    {

        Session session = this.sessionFactory.getCurrentSession();

        Criteria cr = session.createCriteria(ContentElementTypeProperty.class)
                .add(Restrictions.isNull("testUnitType"));
        cr.createCriteria("testUnitType")
                .add(Restrictions.eq("id", tutId));

        cr.createCriteria("business").add(Restrictions.eq("id", businessId));

        List<ContentElementTypeProperty> result = cr.list();
        SS.getLogger().debug("getContentElementTypePropertiesForNullTutOnly result size:"+result.size());
        return result;
    }
    catch (Exception e)
    {
        SS.getLogger().error(e.getMessage(), e);

        throw new TestStructureException(e);
    }
}

UPDATE Following advice from Malagunna, I tried using criterion (below). However, this only returns rows for crAux2.

        Criteria cr = session.createCriteria(ContentElementTypeProperty.class);


    cr.createAlias("testUnitType", "tut");

    Criterion crAux1 = Restrictions.isNull("testUnitType");
    Criterion crAux2 = Restrictions.eq("tut.id", tutId);

    cr.add(Restrictions.or(crAux1, crAux2));

malaguna · Answer 1 · 2015-11-07T07:22:14.583

I believe your problem is that you are not using DISJUNCTION to combine both restrictions.

Try this:

....
Session session = this.sessionFactory.getCurrentSession();

//Here it starts my solution
Criteria cr = session.createCriteria(ContentElementTypeProperty.class);

Criterion crAux1 = Restrictions.isNull("testUnitType");
Criterion crAux2 = cr.createCriteria("testUnitType")
    .add(Restrictions.eq("id", tutId));

cr.add(Restrictions.or(crAux1, crAux2));
//Here it ends my solution

cr.createCriteria("business").add(Restrictions.eq("id", businessId));
....

I am not completely sure about crAux2 I would prefer to test it before, but if this doesn't work, then I would create first an alias to testUnitType and then rewrite crAux2 to

Criterion crAux2 = Restrictions.eq("alias.id", tutId);

Edit

I have been re-reading hibernate criteria doc and maybe this approach resolved your problem:

createAlias("mate", "mt", Criteria.LEFT_JOIN, Restrictions.like("mt.name", "good%") );

This sample has been taken directly from Hibernate docs and it states literally:

This will return all of the Cats with a mate whose name starts with "good" ordered by their mate's age, and all cats who do not have a mate.

So, that said, I think you could change your method this way:

....
Session session = this.sessionFactory.getCurrentSession();

//Here it starts my solution
Criteria cr = session.createCriteria(ContentElementTypeProperty.class);

cr.createCriteria("testUnitType", "tut", Criteria.LEFT_JOIN, Restrictions.eq("tut.id", tutId));    
//Here it ends my solution

cr.createCriteria("business").add(Restrictions.eq("id", businessId));
....

It is more straightforward and it means the same that disjunction sample.

Hope it helps!

Malaguna, this moves me in the right direcrtion, thanks a million. However, crAux2 has compilation error "Cannot convert from Criteria to Criterion" — Jake, Nov 06 '15 at 19:32
It did compile using the alias method but now only returns rows from crAux2 — Jake, Nov 06 '15 at 19:53

Hibernate criteria restriction on join column

1 Answers1

Edit