CODEWITHSUNDEEP
hibernatecriteria
user1016403
user1016403

Reputation: 12621

How to apply to_number for a column in Hibernate criteria?

I am using hibernate detached criteria to get the results from a table (Oracle database). I need to fetch the results order by ascending some column. The column contains numbers but data type is varchar. If we don't apply to_number() for a column then Oracle does ASCII comparison to order by. So, I need to apply to_number() for ordering the results as column contains all numbers but data type is varchar. Please find below query that I am using

public List<EntityClass> getResults(final String someinput) throws Exception {
    DetachedCriteria criteria = DetachedCriteria.forClass(EntityClass.class);
    criteria.add(Restrictions.eq("input", someinput));
    criteria.addOrder(Order.asc("someId")); // **here id column is of varchar type**. 

    List<EntityClass> results= this.getHibernateTemplate().findByCriteria(criteria);
    return results;
}

criteria.addOrder(Order.asc("someId")); - here I need to apply to_number. We can achieve easily using SQL queries, but I am supposed to use only criteria queries.
Please help me how to achieve this?

Upvotes: 3

Views: 4724

Answers (1)

Stijn Geukens
Stijn Geukens

Reputation: 15628

I know this is possible with NHibernate using Projections.cast but AFAIK what you want is not possible this way with Hibernate. The only solution I see using only Criteria is by using a @Formula (although I have never done this myself):

@Column
private String someId;

@Formula(value="to_number(someId)")
private Long someIdNumber;

...

criteria.addOrder(Order.asc("someIdNumber"));

Personally I would consider ordering your results in Java (with a comparator) because whatever solution you choose your queries will depend on a specific DBMS.

Upvotes: 2

Related Questions