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?

2 Answers
2

You should resolve in this way, the MatchMode.ANYWHERE will do the trick:

MatchMode.ANYWHERE

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


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)

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.