Search code examples
javasqlpostgresqlhibernatehibernate-criteria

Hibernate - unexpected token: lower

I have to get all registers in my DB (PostgreSQL) with case-insesitive. I have tried with criteria but the ignoreCase() is not working for me (I'm using Hibernate 3.6).

criteria.add(Restrictions.eq(TABLECODEID, tableCodeID).ignoreCase());

I have also tried to use the ilike method but still doesn't work.

criteria.add(Restrictions.ilike(TABLECODEID, tableCodeID));

And this version too:

criteria.add(Restrictions.ilike(TABLECODEID, tableCodeID, MatchMode.ANYWHERE));

So now I'm getting this error when I try to create a query in Hibernate with HQL:

unexpected token: lower near line 1, column 81

My code looks like this:

StringBuffer queryString = new StringBuffer()
                    .append("from ListItem li ")
                    .append("where lower(li.tableCodeId) like :tableCodeId");

Query query = session.createQuery(queryString.toString());
query.setParameter("tableCodeId", tableCodeID.toLowerCase());

List<ListItem> listItemListAux = query.list();

What am I doing wrong?

Solution

  • At the end I used HQL and the problem was I was attacking the entity in the lower(li.tableCodeId) function, I had to use the DB column name lower(tablecodeid)