I been looking around to see if the hibernate API covers this...and I found nothing!!

So I had to create my own solution!!

Description

The application I am working on has a complex search where the user is able to search different entities and their properties. This can include dates, strings, and numbers. The search is like a Google search...using object and their properties.

Problem

In the API that I created I had to use Restrictions.sqlRestriction as I couldn't do certain things with the Criteria API. The problem that I found is that when dealing with criteria we are using properties to apply different restrictions...but in the sqlRestriction we have to use DB column name. So now I had a problem...how do I apply object properties to sqlRestriction.

Solution

I created my own Restriction class that uses the SQL and property Name.
I re used some of the code from different part of hibernate API.
So now you can use property name as so {properyName} in your sql (Very simlar how you use the {alias}...not you don't have to use the alias as the property name will get it for you.

public class CustomRestriction
{
public static Criterion sqlRestriction(String sql, String propertyName)
{
    return new CustomSQLCriterion(sql, propertyName, ArrayHelper.EMPTY_OBJECT_ARRAY, ArrayHelper.EMPTY_TYPE_ARRAY);
}

}

public class CustomSQLCriterion implements Criterion
{
private final String sql;
String propertyName;
private final TypedValue[] typedValues;

public String toSqlString(
        Criteria criteria,
        CriteriaQuery criteriaQuery)
        throws HibernateException
{
    //extract alis and colum name
    String[] columns = criteriaQuery.getColumnsUsingProjection(criteria, propertyName);
    StringBuffer fragment = new StringBuffer();

    //if we have multiple columns....which i will not
    if (columns.length > 1)
    {
        fragment.append('(');
    }

    //get session factory
    SessionFactoryImplementor factory = criteriaQuery.getFactory();

    //get column name
    for (int i = 0; i < columns.length; i++)         {             fragment.append(columns[i]);         }          if (columns.length > 1)
    {
        fragment.append(')');
    }

    // repleace propery with column name
    return StringHelper.replace(sql, "{propertyName}", fragment.toString());
}

public TypedValue[] getTypedValues(Criteria criteria, CriteriaQuery criteriaQuery)
        throws HibernateException
{
    return typedValues;
}

public String toString()
{
    return sql;
}

protected CustomSQLCriterion(String sql, String propertyName, Object[] values, Type[] types)
{
    this.sql = sql;
    this.propertyName = propertyName;

    typedValues = new TypedValue[values.length];
    for (int i = 0; i < typedValues.length; i++)
    {
        typedValues[i] = new TypedValue(types[i], values[i], EntityMode.POJO);
      }
     }
    }

table structure

lets say this is your table structure

Table Test
 first_name
 last_name

Class Test
{
 String fristName;
 Sting lastName;
}

Usage-

CustomRestriction.sqlRestriction("{propertyName} = " + "'test'", "firstName");

Output - which will be used to construct your query

first_name = 'test'