How to compare using HIbernate criteria on a string column treating it as a long column

Question

I am working on a web project written in java that uses Hibernate for data access from a oracle database.

I have a column name serial in my database which is defined as VARCHAR(12) even though it contains only values that can be cast into long. My intention is get all serials (treating them as numbers) between numbers fromNo and toNo. I cannot use Restrictions.between because the serial column is not a number in DB. It is defined as String in the associated object as well. Right now I achieve the requirement (in an ugly way) by converting the number range to a list of strings and do a Restricitons.in on the column.

long fromNO = 10;
long toNo = 100;
List<String> listNos = null;
for (long k = fromNo; k <=toNO; k++) {
    listNos.add(k.toString());
}

Criteria criteria = getMyHibernateSession().createCriteria(MyObject.class);
criteria.add(Restrictions.in('serialNo',listNos));
List<MyObject> results = criteria.list();

Though I get the desired results, the problem happens when the toNo is not defined by the user and the tool need to get all the serial from fromNo

My question is how can write a hibernate criteria where I can overcome all this problem, by specifying Criteria to treat serial column as long and not String? (Some sort of casting process may be?)

Answer 1

You can add new 'long' field in your entity annotated as

    @Formula("cast(serialNo as NUMBER(10,0))")
    private int lSerialNo;

Then use the new field lSerialNo in your Restrictions