How to use a nested property as a named param in a hibernate query?

Question

I really want to use nested property as a named param in a hibernate query. So I can maintenance my param bean more elegantly.

For example, I want write my HQL like:

"......
where
    employee.age >= :age.min
  and
    employee.age <= :age.max
  and
    employee.name = :name"

and put all the params into ONE paramBean, and in this paramBean, there is a nested bean (named "age"), and the nested bean have 2 properties： min and max.

But the problem is:

1. The HQL syntax does not support nested property as named param: Hibernate will throw a exception, because it does not allow using "." in the param name.

2. In the org.hibernate.internal.AbstractQueryImpl.setProperties(Object) method, the implementation code is:

       Getter getter = ReflectHelper.getGetter( clazz, namedParam );
       Class retType = getter.getReturnType();
       final Object object = getter.get( bean );
   

It use the getter method on the param bean, so it can not retrieve the nested property.

I had to create lots of delegate method in the param bean, to access the nested property:

public int getAgeMin() {
    return this.age.getMin();
}


public int getAgeMax() {
    return this.age.getMax();
}

and write the HQL like:

"......
where
    employee.age >= :ageMin
  and
    employee.age <= :ageMax
  and
    employee.name = :name"

This problem bothered me for years.

And I finally find a way to fix it.

Here is the solution:

1. For problem (1): In HQL: use "_" as a escape char for the "."

HQL likes:

"......
where
    employee.age >= :age_min
  and
    employee.age <= :age_max
  and
    employee.name = :name"

2. For problem (2): write some helper methods, to set all param values for the HQL.

The code of the helper methods are:

private void setParameters(final Query query, final Object paramBean) {
    for (String namedParam : query.getNamedParameters()) {
        try {
            // !!! Fix problem (1) !!!
            // unescape the param name into nested property name
            String nestedPropName = StringUtils.replace(namedParam, "_",
                    ".");

            // !!! Fix problem (2) !!!
            // retrieve the nested property, using Apache Commons BeanUtils
            // see: http://commons.apache.org/proper/commons-beanutils/
            Object paramValue = PropertyUtils.getNestedProperty(paramBean,
                    nestedPropName);

            Class<?> paramType = null;
            if (paramValue != null) {
                paramType = paramValue.getClass();
            }

            if ((paramType != null)
                    && Collection.class.isAssignableFrom(paramType)) {
                query.setParameterList(namedParam,
                        (Collection<?>) paramValue);
            } else if ((paramType != null) && paramType.isArray()) {
                query.setParameterList(namedParam, (Object[]) paramValue);
            } else {
                Type type = this.guessType(paramType);
                query.setParameter(namedParam, paramValue, type);
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
}


private Type guessType(final Class<?> clazz) throws HibernateException {
    SessionFactoryImplementor sessionFactoryImplementor = (SessionFactoryImplementor) this.sessionFactory;

    String typeName = clazz.getName();
    Type type = sessionFactoryImplementor.getTypeResolver().heuristicType(
            typeName);
    boolean serializable = type != null && type instanceof SerializableType;
    if (type == null || serializable) {
        try {
            sessionFactoryImplementor.getEntityPersister(clazz.getName());
        } catch (MappingException me) {
            if (serializable) {
                return type;
            } else {
                throw new HibernateException(
                        "Could not determine a type for class: " + typeName);
            }
        }
        return this.getSession().getTypeHelper().entity(clazz);
    } else {
        return type;
    }
}

The important points are [!!! Fix problem (1) !!!] and [!!! Fix problem (2) !!!],

all the other code are simply copied from org.hibernate.internal.AbstractQueryImpl

Answer 1

How about using an example query Criteria instead:

Age age = ...;
Employee employee = new Employee();
employee.setAge(age);
Example example = Example.create(employee);             
List results = session.createCriteria(Employee.class)
    .add(example)
    .list();

This is close as possible to having nested filtering parameters.

Answer 2

Why I need this?

1. This is not only a problem for "SELECT", but also happen to "DELETE", "UPDATE"

2. If I need to write a very complex query, I prefer HQL, but not Criteria

3. Nested Param Bean/Nested Property make me able to maintenance my param beans more elegantly. For example, I have a class: [ValueRange>], which has 4 properties:

public class ValueRange<T extends Comparable<T>> {
  private T min;

  private boolean includeMin;

  private T max;

  private boolean includeMax;

.....
}

I like to use this container Bean to present all the ValueRange condition, likes:

where
....
    employee.birthDate >= :dateRange.min
  and
    employee.birthDate <= :dateRange.max
....
    employee.salary >= :salaryRange.min
  and
    employee.salary <= :salaryRange.max

4. And even more complex mode: I like to:

(4-1)use a DepartmentCondition bean to present a search condition for Department.

(4-2)use a EmployeeCondition bean to present a search condition for Employee.

(4-3)use DepartmentCondition as a Nested Bean of EmployeeCondition to present a search condition for the Employee's Department

(4-4)I like to write HQL like this:

where
....
    employee.birthDate >= :dateRange.min
  and
    employee.birthDate <= :dateRange.max
....
    employee.salary >= :salaryRange.min
  and
    employee.salary <= :salaryRange.max
....
    employee.department.code = :department.code
and
    employee.department.name = :department.name

by using this design pattern, the DepartmentCondition bean can be a common bean, used for both Department query, and Employee query.

So I can maintenance my param bean more elegantly.