Using a Restriction.like criteria on a numeric property

juanformoso · **Joined:** Thu May 27, 2010 4:10 pm **Posts:** 2

I need to create a criteria using a like restriction on a numeric column.
Users need to filter a transactions list, and sometimes all they know is the last 4 digits of its id.

I created the DetachedCriteria and tried a few approaches with no luck

Code:

// (...)
DetachedCriteria c = DetachedCriteria.forClass(getDomainClass());;

c.add(Restrictions.like("transactionId", 42));
// or
c.add(Restrictions.like("transactionId", "%42%"));
// or
c.add(Restrictions.like("str(transactionId)", "%42%"));
// (...)
List l = getHibernateTemplate().findByCriteria(c);
// (...)

Any idea how I can achieve that using Criteria (not HQL)

Thanks for your time

Quaternion · **Joined:** Mon May 10, 2010 1:41 pm **Posts:** 15

I've never done this with hibernate, just an idea....

can you use an expression in the restriction?

Simply take the modulus of the numeric field against the search string length and compare the values (both numeric). [Edit it should read 10^(power of the search string length), read between the lines =P, same goes for later]
This will find if the string matches at the end.
Then add an "or" condition with criteria to use integer division to truncate the end of the fields string and compare, to search the start.

If it is possible it isn't beautiful but it is more likely that people want the start or end and not the middle.

juanformoso · **Joined:** Thu May 27, 2010 4:10 pm **Posts:** 2

Thanks Quaternion, but that's too complex. Besides, I could need to match in the middle.

I temporarily fixed it using a sqlRestriction:

Code:

c.add(Restrictions.sqlRestriction("transactionId like '%42%'"));

I don't really like this solution, please let me know if there's a better way to do it