Hibernate criteria: how to order by two columns concatenated?

user_464956 0
Viewed 47578

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?

hibernate-criteria hql hibernate java
2 Answers

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.

user_88992780
Related