Using Hibernate DetachedCriteria for calling aggregate functions

I have a DetachedCriteria which I am using to search a table based on a name field. I want to make the search case-insensitive, and am wondering if there is a way to do this without using HQL. Something like:

private void searchByFullName(DetachedCriteria criteria, String searchCriteria) {
        criteria.add(Restrictions.like("fullName", "%" + searchCriteria.toLowerCase() + "%"));
        criteria.addOrder(Order.asc("fullName"));
}

But I want to make sure that it will ignore the case when it does the search (it has to search for both the upper and lower case), so the SQL it generates sh开发者_StackOverflowould look something like:

SELECT * FROM Student WHERE ? LIKE toLower(FULL_NAME);

What database are you using? MySQL LIKE is case-insensitive for CHAR, VARCHAR, and TEXT columns (I believe the same is true for SQL Server).

http://dev.mysql.com/doc/refman/5.5/en/case-sensitivity.html

If you're using PostgreSQL, you'll want to use the ILIKE operator, so you'll want to use Restrictions.ilike("fullName", name).

I see two options,

Option 1:

private void searchByFullName(DetachedCriteria criteria, String searchCriteria) {
            criteria.add(Restrictions.like("toLower(fullName)", "%" + searchCriteria.toLowerCase() + "%"));
            criteria.addOrder(Order.asc("fullName"));
    }

Option 2:

private void searchByFullName(DetachedCriteria criteria, String searchCriteria) {
                criteria.add(Restrictions.sqlRestriction("toLower({alias}.fullName) LIKE '%" + searchCriteria.toLowerCase() + "%'"));
                criteria.addOrder(Order.asc("fullName"));
        }

I am not very optimistic about Option 1. Option 2 should work for sure. {alias} is placeholder to let Hibernate know that it needs to add the appropriate alias for the table when it creates the SQL. More info http://docs.jboss.org/hibernate/core/3.5/api/org/hibernate/criterion/Restrictions.html#sqlRestriction%28java.lang.String%29