order by nulls first / last
Description
Attachments
Activity

Former user December 16, 2018 at 8:42 PM
I see this had been implemented for the Criteria API which has now been deprecated. Is it likely that we’ll see this feature return in CriteriaBuilder?

Stephane Eybert December 6, 2012 at 11:15 AM
Here is what a minor issue makes me do:
@Override
@SuppressWarnings("unchecked")
public List<NavlinkItem> findWithNavlink(Navlink navlink) {
String statement = "select id, version, name, description, image, image_over as imageOver, url, blank_target as blankTarget, language_code as languageCode from navlink_item where navlink_id = :navlinkId order by language_code nulls first";
Query query = getSession().createSQLQuery(statement)
.addScalar("id", StandardBasicTypes.INTEGER)
.addScalar("version", StandardBasicTypes.INTEGER)
.addScalar("name")
.addScalar("description")
.addScalar("image")
.addScalar("imageOver")
.addScalar("url")
.addScalar("blankTarget", StandardBasicTypes.BOOLEAN)
.addScalar("languageCode");
query.setInteger("navlinkId", navlink.getId());
return query.setResultTransformer(Transformers.aliasToBean(NavlinkItem.class)).list();
}
instead of:
public List<NavlinkItem> findWithNavlink(Navlink navlink) {
Criteria criteria = getSession().createCriteria(getPersistentClass());
criteria.add(Restrictions.eq("navlink", navlink)).addOrder(Order.asc("languageCode").nullsFirst());
return criteria.list();
}

Stephane Eybert November 22, 2012 at 11:10 AMEdited
It's too bad this issue is still in the way, for all my integration tests developed against a MySql database now fail against Oracle 10g XE.
It would be very helpful to have Hibernate offering something like:
Order.Asc("myproperty").Nulls.First()

Grégory OLIVER September 20, 2012 at 12:01 PMEdited
I think you should change this priority, it's not a minor changed, since we have 59 votes for this.

Eric Jain May 23, 2011 at 9:05 PM
This would be quite helpful for making applications behave more consistently across databases; I ran into this when I noticed that H2 (which we use for testing) sorts nulls lower, whereas PostgreSQL (which we use in production) sorts nulls higher...
Details
Assignee
Lukasz AntoniakLukasz AntoniakReporter
Anthony PatricioAnthony PatricioComponents
Fix versions
Priority
Minor
Details
Details
Assignee

Reporter

support of nulls first / last in order clause
"NULLS LAST" is part of the SQL 99 standard.
The syntax is as follows:
ORDER BY [COLUMN NAME] [ASC | DESC] [NULLS FIRST | NULLS LAST]
In different DBs, the sorting of nulls relative to other values is handled differently.
PostgreSQL - Nulls are considered HIGHER than non-nulls.
DB2 - Higher
MSSQL - Lower
MySQL - Lower
Oracle - Higher
The following DBs have supported this functionality:
DB2 V7
Oracle 9i
PostgreSQL, MySQL, SQLServer do not appear to support this from what I can gather.
see http://forum.hibernate.org/viewtopic.php?t=942176&start=0&postdays=0&postorder=asc&highlight=