Configure Hibernate to escape underscores in LIKE clause using SQL Server dialect

Question

I have a SQL SELECT query which has a LIKE clause containing an underscore, which should specifically look for an underscore, not treat it as a wildcard:

SELECT * FROM my_table WHERE name LIKE '_H9%';

I understand that I can change the actual clause to '[_]H9%' for this to work as I expect, but the problem is that this clause is being generated by Hibernate.

Is there a way to configure Hibernate to escape all underscores in all queries in this way? Failing that, is there a way to configure SQL Server (2008 in my case) to not treat underscores as wildcards?

Answer 1

If you're using Criteria to create the query, you can create your own expression which subclasses org.hibernate.criterion.LikeExpression, using one of the protected constructors that takes in 'Character escapeChar', and does substitution in the value for you. Assuming that '!' is a known value that won't be in any search strings (you can pick any you like, I guess), you can just do:

public class EscapingLikeExpression extends LikeExpression {
  public EscapingLikeExpression(String propertyName, String value) {
      super(propertyName, escapeString(value), '!', false);
  }

  static String escapeString(String inputString) {
    inputString = inputString.replace("_", "!_");
    return inputString;
  }

}

if there ARE no such characters (that won't appear in your search value as literals) then add the following as the first line of escapeString() to escape those too:

    inputString = inputString.replace("!", "!!");