I have a Person table which has two columns: first_name and last_name. The Person class has two corresponding fields: firstName and lastName. Now I'm using criteria api and trying to create an order by based on these two columns concatenated. Is it possible? Or it can only be achieved by hql?
4
- Sorting by concatenated columns will only make sense if you concatenate their lower (or upper) case representations. Otherwise, sorting is equvilaent to sorting by 2 columns i.e. order by first_name, last_name.Andrey Adamovich– Andrey Adamovich2011-07-19 20:28:20 +00:00Commented Jul 19, 2011 at 20:28
- @Andrey don't you get the same result for both methods? You have to be careful if you sort first with first/last name.toto2– toto22011-07-19 20:51:20 +00:00Commented Jul 19, 2011 at 20:51
- @toto, in most of the cases it will, but let's say you have these names to sort: (John Anderson, Johna Butcher). JohnAndreson < JohnaButcher, but johnanderson > johnabutcher, because A < a.Andrey Adamovich– Andrey Adamovich2011-07-19 21:07:50 +00:00Commented Jul 19, 2011 at 21:07
- @Andrey if you concatenate them with a space between first and last name you won't get this problem. You also avoid this problem if you do first a sort on last names and then sort this sorted data according to the first name.toto2– toto22011-07-19 22:44:24 +00:00Commented Jul 19, 2011 at 22:44
Add a comment |
2 Answers
Here an example for the JBoss hibernate site:
from DomesticCat cat order by cat.name asc, cat.weight desc, cat.birthdate Or from the same website, for the Criteria api:
List cats = sess.createCriteria(Cat.class) .add( Restrictions.like("name", "F%") .addOrder( Order.asc("name") ) .addOrder( Order.desc("age") ) .setMaxResults(50) .list(); They seem to be quite fond of cats at JBoss.
5 Comments
Georgie Porgie
In this case, the order columns are not concatenated. I want to achieve something like "order by first_name || ' ' || last_name"
toto2
As I wrote in the comments to your question above, I fail to see the difference.
Georgie Porgie
Oh, yes, right. Sorry I didn't read your comments above first. But what about scenarios when I need to order by field1 + field2? Both field1 and field2 are numeric.
toto2
You could try
order by (item.field1 + item.field2). I don't know if it will work, so please let me know. I found a SQL example where they used division, so my guess is that hql can also deal with operations for the ordering. If the mathematical operations do work, order by concat(item.lastName + " " + item.firstName) also probably works.Georgie Porgie
hql is fine with this kind of operation but criteria api seems to fail.
I had the same problem and criteria api orderBy method won't work with concatenated columns. I needed this to use with criteriaBuilder.construct() method. I solved this by extending Orale10gDialect class and registering custom function:
import org.hibernate.dialect.Oracle10gDialect; import org.hibernate.dialect.function.SQLFunctionTemplate; import org.hibernate.type.StandardBasicTypes; public class CustomOracle10gDialect extends Oracle10gDialect { public CustomOracle10gDialect() { super(); // This must be used due to bug in Hibernate (orderBy won't work with concat) registerFunction("concatwithspace", new SQLFunctionTemplate(StandardBasicTypes.STRING, "?1 || ' ' || ?2")); } } And then:
Expression<String> user = cb.function("concatwithspace", String.class, criteriaRoot.get("firstname"), criteriaRoot.get("lastname")); ...
criteriaQuery.orderBy(cb.asc(user)); Of course you must also select this concatenated columns.
