19

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?

Georgie Porgie
  • 2,100
  • 3
  • 24
  • 25
  • 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 Jul 19 '11 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 Jul 19 '11 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 Jul 19 '11 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 Jul 19 '11 at 22:44

2 Answers2

27

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.

adam_0
  • 6,920
  • 6
  • 40
  • 52
toto2
  • 5,306
  • 21
  • 24
  • In this case, the order columns are not concatenated. I want to achieve something like "order by first_name || ' ' || last_name" – Georgie Porgie Jul 19 '11 at 21:47
  • 1
    As I wrote in the comments to your question above, I fail to see the difference. – toto2 Jul 19 '11 at 22:45
  • 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. – Georgie Porgie Jul 22 '11 at 19:31
  • 1
    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. – toto2 Jul 22 '11 at 20:10
  • 1
    hql is fine with this kind of operation but criteria api seems to fail. – Georgie Porgie Jul 25 '11 at 16:13
0

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.

KamilJ
  • 1