Hibernate cirteria, integer and like

Question

Hi there,

I'm migrating some of my hql-statements to Criterias now I'm figuring one problem: The entity property is type Integer but I need a like with wildcards search, so in hql I do

session.createQuery("from P1 where id like :id").setString("id", "%"+s+"%")

No problem at all, Hibernate casts String to Integer.

If I try this in Criteria, I only get a ClassCastException

String cannot be cast to Integer

Criteria crit = sessionFactory.getCurrentSession().createCriteria(P1.class);
crit.add(Restrictions.like("id",s)).addOrder(Order.asc("id")).setMaxResults(maxResults);

Why hibernate handles this both situations differently?

Answer 1

In SQL it makes no sense to do LIKE on numbers. Rather use the IN or BETWEEN clause or the < and/or > operators.

Answer 2

You could use the str expression conversion. If this makes any sense.

str() for converting numeric or temporal values to a readable string

session.createQuery("from P1 where str(id) like :id").setString("id", "%"+s+"%")

This will be quite slow if you do not have a function based index on the column.