How to use LIKE in hibernate detached criteria for integer data type?

Question

I have to do Restrictions.like("sequenceNo", "%" + Integer.valueOf(sequenceNo.trim()) + "%").

The field sequenceNo is integer type but the sequenceNo param value is string. My problem is I get an exception java.lang.ClassCastException: java.lang.String cannot be cast to java.lang.Integer. For some reasons I really have to make my param a string data type. When I tried it in SQL to LIKE an integer it works.

Please help. Thanks.

You cannot use `LIKE` with integer columns. If you absolutely want to do this you must cast the integer to a string before the `LIKE` is applied. This would be easy if you were writing your own SQL, but right now I'm too lazy to look up how to tell Hibernate to do that :-) — Jim Garrison, Mar 20 '13 at 03:36

score 9 · Accepted Answer · answered Mar 29 '13 at 10:32

9

You cannot add Criteria`s property restrictions for the purpose, as during fetching, property value specifiedwould be casted according to the 'field type' specified in Entity class.

However, a solution would be using SQLRestriction of criteria, to by pass casting. I have tested and this works.

yourDetachedCriteriaObj.add(Restrictions.sqlRestriction(" sequenceNo LIKE '%"+yourSequenceNumToSearch+"%' "));

To get list you would do like below

List ls = yourDetachedCriteriaObj.getExecutableCriteria(yourSession).list();
// iterate over list or do whatever.

answered Mar 29 '13 at 10:32

maimoona

617
8
16

without sqlRestriction is this possible. ? – Yogesh Prajapati Sep 25 '14 at 09:25
another issue is i have join of tables and both table have "sequenceNo" column, in this scenario again it throws error. – Yogesh Prajapati Sep 25 '14 at 15:12
this doesn't work if you want to use a alias / different table then the criteria table – C_B Nov 28 '16 at 12:52

score 2 · Answer 2 · answered Feb 28 '14 at 14:03

2

i'm ashamed, but i did following workaround with postgres

crit.add(Restrictions.sqlRestriction(entry.getKey()+"::text like '%"+entry.getValue().replace("'", "''")+"%'"));

answered Feb 28 '14 at 14:03

wutzebaer

14,365
19
99
170

score 0 · Answer 3 · answered May 26 '16 at 09:58

0

My postgres modification

cr34.add(Restrictions.sqlRestriction(cr34.getAlias()+"_.field::text like '%"+ fieldVal+"%'"));

answered May 26 '16 at 09:58

sherif

2,282
19
21

Bhagrav K. · Answer 4 · 2017-08-29T08:13:06.000

0

for Integer search parameter

criteria = session.createCriteria(demo.class).add(Restrictions.sqlRestriction("sequenceNo LIKE '%"+searchParameter+"%' "));

Try this...

edited Aug 29 '17 at 08:13

answered Jun 10 '16 at 12:41

Bhagrav K.

1
2

score 0 · Answer 5 · answered Jul 31 '16 at 13:08

yourDetachedCriteriaObj.add(Restrictions.sqlRestriction(" sequenceNo LIKE '%"+yourSequenceNumToSearch+"%' "));

return error for unabling type cast:

operator does not exit : integer ~~ unknown HINT: No operator matches the given name and argument tyeps(s). you might need to add explicitr type casts.

so, we can use this code:

yourDetachedCriteriaObj.add(Restrictions.sqlRestriction(" sequenceNo ::text LIKE '%"+yourSequenceNumToSearch+"%' "));

How to use LIKE in hibernate detached criteria for integer data type?

5 Answers5