Casting the string column as int in hql query or criteria

Question

Can anyone guide me where i am going wrong? Its giving zero result but in db values are existing as for condition appear in the below query.

  Str = QueryImpl(from ArcZipCodeRange where cast(fromZip as int) >='12345' and cast(toZip as int)<='12345')
        arcZipCodeRangeList = 0

Answer 1

Are you sure your conditions are correct?

cast(fromZip as int) >='12345' and cast(toZip as int)<='12345')

Would give all results with from > 12345 and to < 12345.

Should it be other way around: from < 12345 and to > 12345?

Answer 2

you are casting fromZip and toZip to an int and then compare it with a String. That is asking for trouble.

Use the same data type on both sides of the comparison.

Also as @Fazovsky notes, your condition seems to be the wrong way round.

Answer 3

You can do this by Hibernate Criteria easily. For solution you must create your own Hibernate Formula for these(fromZip and toZip). The following must be your pojo mapping.

@Column
private String fromZip;

@Formula(value="to_number(fromZip)")
private double newfromZip;

@Column
private String toZip;

@Formula(value="to_number(toZip)")
private double newtoZip;

Following is your criteria for select:

Criteria criteria = session.createCriteria(ArcZipCodeRange.class);
criteria.add(Restrictions.le("newfromZip", yourIntegerParameter));
criteria.add(Restrictions.ge("newtoZip", yourIntegerParameter));
List<ArcZipCodeRange> list = criteria.list();

I hope this will help you.