Using IF construction in Hibernate

Question

I'm using the construction "if" in HSQL:

String q = "SELECT id, name, " +
                "IF (ABS(name) > 0, LPAD(ABS(name), 4, '0'), name) AS tord " +
                "FROM table where city= " + cityId + " order by tord";

    Query query = session.createSQLQuery(q);
    List<Object[]> list = query.list();
    session.getTransaction().commit();
    session.close();

And now i want to refactor this piece of code with HQL. How can i do this? Thanks.

Answer 1

Option 1.

Replace IF with CASE:

(case when ABS(name) > 0 then LPAD(ABS(name), 4, '0' else name end) AS tord

See HQL documentation for details.

Option 2.

HQL supports native functions. If you encapsulate calculations in custom SQL function you can write something like:

select t.*, paddNumber(t.name, 4) as tord from TableEntity t 
where t.city = :city order by tord

Have no environment to check but think each result record will be represented as Object[]. Where first element will contain your entity and second will contain tord.

Option 3.

If possible, rewrite application logic. There are different ways to add required padding to field 'name' before saving record. This will remove unnecessary calculation in DB during sorting.