Hi

We need an SQL like this:

=======
select lpad(code, 5, "0") as paddedcode, * from cat_table order by paddedcode;

OR

select * from cat_table order by lpad(code, 5, "0");

=======

All it needs is an sql alias which is used in the order by field. Hibernate should still populate the object (cat in this example) as normal.

Is this possible using the Criteria API? It looks really simple, but I cannot see how it can be achieved.

Keith

I think it can be done using an sqlProjection (with alias). Will give it a go.

Keith

did you ever find out the correct syntax for this?

I didn't manage to find a way to ORDER BY a projection criteria query, nor did I find any postings about it. I gave up and am using HQL.

My code (below) causes the error: org.hibernate.QueryException: could not resolve property: rval of: com.skahasoftware.Ad. This seems to indicate that Order cannot be used with an SQL function.

Criteria crit = session.createCriteria(Ad.class);
crit.setProjection(
Projections.projectionList()
.add(Projections.sqlProjection("{alias}.id theObj",
new String[]{"theObj"},
new org.hibernate.type.Type[]{Hibernate.entity(Ad.class)}))
.add(
Projections.sqlProjection("rand() as rval",
new String[] {"rval"},
new org.hibernate.type.Type[] {new org.hibernate.type.FloatType()}
)
)
);

crit.addOrder(Order.asc("rval"));