Compatible with Hibernate 2.1.8, easily adaptable to 3.

Written on June, 2005, by Pietro Polsinelli.

Consider this requirement: on a case sensitive db, say named "Elcaro", have case in-sensitive queries. So if I search

name like :param  (and :param = 'Mario')

I should find objects where the property is 'MARIO', 'mario', 'Mario'.

By default, on Elcaro, you'll find only "Mario". As a workaround, you could use hql's lower (or upper), and use as query

lower(name) like lower(:param)  (and :param = 'Mario')

now this "solution" is a fragile hack:

• 1. you have to remember to always apply lower on all queries
• 2. on Elcaro, you are destroying index advantages, as Elcaro's indexes will be case-sensitive
• 3. if you are deploying to multiple db's, you have this lower everywhere even when it is entirely useless

so I propose a different way: save all data in a fixed case, either in lower or in upper, modifying your calls to Session.save, and extend QueryImpl so that in searches all string primitive data values is converted either to lower or upper. This way you have got 1 and 2; if you make this behavious configurable, you get also 3.

What's bad is that all data gets saved to Upper; but that is the price to pay in choosing a case-sensitive db from the start ! Developers are used to case-sensitivity, but the real world isn't: just imagine nightmares like a case sensitive encyclopedia; or a case sensitive version of Google, or even a case sensitive search on Hibernate' Wiki :-D. As an apparent alternative, you can always hack the database, and by hand make it performant anyway, but this is even worse, as not only you have to remember to be careful on every single query, but also to keep track of your indexing hacks; more generally, you are breaking Hibernate's transparency (thanks to the first poster below for a stimulating different opinion on this). Actually my collegue Roberto Bicchierai points out that the situation with the indices "hack" is even worse: if your query is complex and hence the db is occasionally unable to use the "hacked" index, in these occasion (and only these) the search results will be different from usual; it would work if you knew a-priori that the index is always applied: but who knows which queries will be performed ?

If you have updated information, please feed this page (not the remarks below) or send me (mailto:ppolsinelli (at) open-lab (dot) com) infos to put here.

The Code

This code is sample, you must adapt it to your environment.

public class PlatformHibernateQuery extends QueryImpl {
  public PlatformHibernateQuery(String oql, SessionImplementor sessionImplementor) {
    super(oql, sessionImplementor);
  }

  public Query setParameter(String name, Object val, Type type) {
    if ((ApplicationState.platformConfiguration.searchStringParametersToLower || 
ApplicationState.platformConfiguration.searchStringParametersToUpper) &&
            type.equals(Hibernate.STRING)) {
      if (ApplicationState.platformConfiguration.searchStringParametersToLower)
        val = ((String) val).toLowerCase();
      else
        val = ((String) val).toUpperCase();
    }
    return super.setParameter(name, val, type);
  }


  public Query setParameter(int position, Object val, Type type) {

    if ((ApplicationState.platformConfiguration.searchStringParametersToLower || 
ApplicationState.platformConfiguration.searchStringParametersToUpper) &&
            type.equals(Hibernate.STRING)) {
      if (ApplicationState.platformConfiguration.searchStringParametersToLower)
        val = ((String) val).toLowerCase();
      else
        val = ((String) val).toUpperCase();
    }
    return super.setParameter(position, val, type);
  }

  public Query setParameterList(String name, Collection vals, Type type) throws HibernateException {
    if ((ApplicationState.platformConfiguration.searchStringParametersToLower || 
ApplicationState.platformConfiguration.searchStringParametersToUpper) &&
            type.equals(Hibernate.STRING)) {
      if (ApplicationState.platformConfiguration.searchStringParametersToLower)
        for (Object val : vals) {
          val = ((String) val).toLowerCase();
        }
      else
        for (Object val : vals) {
          val = ((String) val).toUpperCase();
        }
    }
    return super.setParameterList(name, vals, type);
  }


}

public class MyHibernateFactory {

  ...

  private static void save(Session s, Identifiable i) throws HibernateException {

    if (ApplicationState.platformConfiguration.storeStringPrimitivesToLower || 
ApplicationState.platformConfiguration.storeStringPrimitivesToUpper) {

      Map<String, Field> dif = ReflectionUtilities.getDeclaredInheritedFields(i.getClass());
      for (Field f : dif.values()) {
        if (f.getType().equals(String.class)) {

          try {
            String value = (String) ReflectionUtilities.getFieldValue(f.getName(), i);
            if (value != null) {
              if (ApplicationState.platformConfiguration.storeStringPrimitivesToLower)
                value = value.toLowerCase();
              else
                value = value.toUpperCase();
              ReflectionUtilities.setFieldValue(f.getName(), i, value);
            }
          } catch (IllegalAccessException e) {
            throw new PlatformRuntimeException(e);
          } catch (FindByPrimaryKeyException e) {
            throw new PlatformRuntimeException(e);
          } catch (NoSuchMethodException e) {
            throw new PlatformRuntimeException(e);
          } catch (InvocationTargetException e) {
            throw new PlatformRuntimeException(e);
          } catch (PersistenceException e) {
            throw new PlatformRuntimeException(e);
          } catch (InstantiationException e) {
            throw new PlatformRuntimeException(e);
          }

        }
      }
    }

    s.save(i);
  }
}