Query by Example on associations

Question

It's very frustrating that you cannot use QBE on associations.

I have a large datatable with about 8 many-to-one columns. There is a a drop-down list for every column to filter the table.

Let's assume the following:

Table User

User { id, UserStatus, UserAuthorization }

I want to use this code:

Criteria crit = getSession().createCriteria(class);
crit.add(Example.create(userObject));

This does not work on the following example userObject:

User id=1 { UserStatus=Active, UserAuthorization=Admin }

because QBE doesn't support collections.

One way to solve this is to use it this way:

crit.createCriteria("UserStatus").add(Example.create(userStatusObject));
crit.createCriteria("UserAuthorization").add(Example.create(userAuthorizationObject));

My question is how this can be programmed dynamically just with the given User object. Is there another way than using QBE?

I try to avoid that... the whole point behind QBE is to not do things by hand — rotsch, Feb 16 '12 at 15:47
Ah sorry :D That's my solution now, but I thought of a more sophisticated one ;) — rotsch, Feb 16 '12 at 17:14

score 3 · Accepted Answer · answered Feb 16 '12 at 17:24

3

you can combine QBE and normal Expressions to handle the parts QBE doesnt support

Criteria crit = getSession().createCriteria(class);
    .add(Example.create(userObject));
    .add(Expression.eq("UserStatus", userObject.getUserStatus()));

answered Feb 16 '12 at 17:24

Firo

30,626
4
55
94

Thanks, are there any ways to make this dynamic? – rotsch Feb 17 '12 at 06:42

score 1 · Answer 2 · edited Mar 10 '15 at 21:41

Here's a generic answer that I found worked for me inside of my repository base, using reflection:

protected T GetByExample(T example)
{
    var c = DetachedCriteria.For<T>().Add(Example.Create(example).ExcludeNone());
    var props = typeof (T).GetProperties()
        .Where(p => p.PropertyType.GetInterfaces().Contains(typeof(IEntityBase)));
    foreach (var pInfo in props)
    {
        c.Add(Restrictions.Eq(pInfo.Name, pInfo.GetValue(example)));
    }
    return Query(c);
}

Note that all of my entities inherit from IEntityBase, which allowed me to find just those foreign key references from the object properties so I could add them to the criteria. You'll have to provide some way to execute the query (i.e. c.GetExecutableCriteria(Session))

score 0 · Answer 3 · answered Dec 10 '16 at 05:18

Here is the code which can be used for every entity to use the query by example in hibernate.

  /**
                 * This method will use for query by example with association 
                 * @param exampleInstance the persistent class(T) object
                 * @param restrictPropertyName the string object contains the field name of the association
                 * @param restrictPropertyValue the association object 
                 * @return list the persistent class list
                 */
public List<T> queryByExampleWithRestriction(T exampleInstance, String restrictPropertyName, Object restrictPropertyValue) {
            log.info("Inside queryByExampleWithRestriction method of GenericHibernateDAO");
            List<T> list = null;
            try {
                Criteria criteria = getSession().createCriteria(exampleInstance.getClass());  
                Example example =  Example.create(exampleInstance);
                criteria.add(example);
                criteria.add(Restrictions.eq(restrictPropertyName, restrictPropertyValue));
                list = criteria.list();
                log.info("Executed the queryByExampleWithRestriction query with criteria successfully!");
            } catch(HibernateException e){
                throw (e);
            }
            finally{
                this.closeSession();
            }
            return list;  
        }

Query by Example on associations

3 Answers3