Using the IF construct in Hibernate

Question

I use the if construct 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 reorganize this piece of code using HQL. How can i do this? Thank.

Answer 1

Option 1.

Replace IF CASE:

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

See the HQL documentation for more information .

Option 2

HQL supports native functions. If you encapsulate the calculations in a 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

You do not have an environment to check, but think that each record of the result will be represented as Object []. Where the first element will contain your essence, and the second will contain a tord.

Option 3

, . 'name' . .