Case insensitive text search - Postgresql ILIKE

[msanitariusz]

When i try to do case insensitive search using PostgresSQL ILIKE
(PostgreSQL 8.4 and proper JDBC drivers are used) I get Java
exception:
IllegalArgumentException occured :
org.hibernate.hql.ast.QuerySyntaxException: unexpected token:
ILIKE ...
Any solution or smart (large datasets so LOWER is not an option)
workaround for this problem?

[msanitariusz]

~* operator doesn't work either: IllegalArgumentException occured :
org.hibernate.QueryException: unexpected char: '~' ...

I forgot to mention, I use it as: Book.find("title ILIKe ?",
param1).fetch(); // when I use LIKE instead of ILIKE everything works
fine

[Philippe Gauthier]

Hi,
ILIKE doesn't exist in JPQL.

Maybe you can try
Book.find("lower(title)",param1.toLowerCase())

Or U can create a native query, then u can use ILIKE.
JPA.em().createNativeQuery("SELECT title FROM TABLE WHERE title
ILIKE ?").setParameter(1,
param1);

[The Dave]

The difficulty you are running into is that ILIKE is not an expression
recognized by JPQL/HQL, which is the query language in use here.
Since ILIKE is specific to PostgreSQL, it's not supported in JPQL.

I think you really have two options here:

1) use a native query (JPA.em().createNativeQuery("....")), which
allows you specify exactly the query parameters that you want. The
main difficulty of this is that you basically have to perform the
result mapping yourself (the result of the query will be a
List<Object[]>....you'll have to pull out each row and translate it
into the object you want.)

2) create a custom dialect of org.hibernate.dialect.PostgreSQLDialect,
adding an "ilike" function into it. This is actually a lot easier
than it sounds, here's some (untested) code, which I think might do
the job:

public class CustomPostgreSQLDialect extends
org.hibernate.dialect.PostgreSQLDialect {
public CustomPostgreSQLDialect() {
super();
registerFunction("ilike", new
VarArgsSQLFunction(Hibernate.BOOLEAN, ""," ilike ", ""));
}
}

Basically, option #1 is a good option if you aren't going to need to
use ILIKE in a lot of queries and if you don't mind not getting back
managed entities as query results, whereas option #2 is better for the
query you appear to be using.

One note on using this dialect, the proper syntax in your query would
be: Book.find("where ilike(title,?)",param1).fetch(); The dialect
extension above essentially registers "ilike" as a custom function,
which Hibernate will then know how to recognize and parse. However,
all Hibernate functions are of the form
"functionName(parm1,...parmN)". The registerFunction command tells
the dialect to translate calls like "ilike(x,y)" into "x ilike y".

Finally, be sure to specify that you're going to use this dialect in
your application.conf file. You may have to have entries for both
jpa.dialect and hibernate.dialect (e.g.,
jpa.dialect=util.CustomPostgreSQLDialect and
hibernate.dialect=util.CustomPostgreSQLDialect might need to be
listed).

--Dave