mysql order by null last_Hibernate查询中实现order by的 NULLS LAST 和 NULLS FIRST

在不同数据库中对null字段的order by优先级有所不同，造成在一些情况下应用的排序有误。解决办法其实可以使用NULLS LAST和 NULLS FIRST来声明给数据库对NULL字段的排序，但在使用hibernate的条件查询(criteria)不支持这一特性。不过直到4.2.0.CR1，hibernate官方解决了这个问题。详细见：https://hibernate.onjira.com/browse/HHH-465

虽然官方已经解决了这个问题，但之前对此问题的一个的解决方案很值得参考，可能在日后解决问题的时候得到一定的启发。这个方案简单来说，就是在生成的SQL后，添加为SQL增加自定义的修改，所以十分日后也许十分有用。

参考 http://stackoverflow.com/questions/3683174/hibernate-order-by-with-nulls-last

1.创建一个自己的Interceptor，MyNullsFirstInterceptor

public class MyNullsFirstInterceptor extends EmptyInterceptor {

private static final long serialVersionUID = -8690066766867444573L;

private final Log logger = LogFactory.getLog(getClass());

private static final String ORDER_BY_TOKEN = "order by";

/*

* (non-Javadoc)

*

* @see org.hibernate.EmptyInterceptor#onPrepareStatement(java.lang.String)

*/

//FIXME replace来解决不完美。并且如果没写ASC或者DESC的情况下，也会有默认的排序。

public String onPrepareStatement(String sql) {

int orderByStart = sql.toLowerCase().indexOf(ORDER_BY_TOKEN);

boolean isNeedFixNullFirst = orderByStart > -1;

if (!isNeedFixNullFirst) {

return super.onPrepareStatement(sql);

}

sql = StringUtils.replace(sql, " DESC ", " DESC NULLS LAST ");

sql = StringUtils.replace(sql, " DESC)", " DESC NULLS LAST)");

sql = StringUtils.replace(sql, " ASC ", " ASC NULLS FIRST ");

sql = StringUtils.replace(sql, " ASC)", " ASC NULLS FIRST)");

// orderByStart += ORDER_BY_TOKEN.length() + 1;

// int orderByEnd = sql.indexOf(")", orderByStart);

// if (orderByEnd == -1) {

// orderByEnd = sql.indexOf(" UNION ", orderByStart);

// if (orderByEnd == -1) {

// orderByEnd = sql.length();

// }

// }

// String orderByContent = sql.substring(orderByStart, orderByEnd);

// String[] orderByNames = orderByContent.split("\\,");

// for (int i = 0; i < orderByNames.length; i++) {

// if (orderByNames[i].trim().length() > 0) {

// if (orderByNames[i].trim().toLowerCase().endsWith("desc")) {

// orderByNames[i] += " NULLS LAST";

// } else {

// orderByNames[i] += " NULLS FIRST";

// }

// }

// }

// orderByContent = StringUtils.join(orderByNames, ",");

// sql = sql.substring(0, orderByStart) + orderByContent

// + sql.substring(orderByEnd);

logger.debug("--------------------");

logger.debug(sql);

logger.debug("--------------------");

return super.onPrepareStatement(sql);

}

注释掉的部分为原帖的解决方案，但使用后发现会报错，没详细的排查，而是采取了简单的处理方案，即看到DESC的时候直接后面添加NULLS LAST，而遇到ASC的时候反之。当然replace的那代码块一段完全可以更好的正则来实现。

也可以看到FIXME的那个注释，如果没写ASC和DESC的话，这个就完全起效没用了，但如果用criteria条件查询的话，一定会有的咯。

二，添加MyNullsFirstInterceptor到sessionFactory的xml配置内容中。

class="cn.com.timekey.commons.dao.MyNullsFirstInterceptor" />

详细如：

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context"

xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx"

xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd

http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd

http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.0.xsd

http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.0.xsd">

class="org.springframework.jdbc.datasource.DriverManagerDataSource">

class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">

org.hibernate.connection.C3P0ConnectionProvider

5

20

600

100

2

120

false

false

${jdbc.dialect}

${hibernate.show_sql}

${hibernate.hbm2ddl.auto}

${hibernate.format_sql}

50

30

25

classpath:/cn/com/timekey/project/po/**.hbm.xml

class="cn.com.timekey.commons.dao.MyNullsFirstInterceptor" />

由于这个问题已经在hibernate4.2.0cr1中被修复了。所以也不打算进一步的优化了，至少目前是这么想。这套东西可以作为oracle和db2环境下的临时应急方案吧。(注，因为下mysql，mssql的null字段优先级默认就是最低)