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配置内容中。

 

		<property name="entityInterceptor">
			<bean id="myNullsFirstInterceptor"
				class="cn.com.timekey.commons.dao.MyNullsFirstInterceptor" />
		</property>

 

详细如：

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	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">

	<bean id="dataSource"
		class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property name="driverClassName" value="${jdbc.driver}" />
		<property name="url" value="${jdbc.url}" />
		<property name="username" value="${jdbc.username}" />
		<property name="password" value="${jdbc.password}" />
	</bean>
	<bean id="sessionFactory"
		class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
		<property name="dataSource">
			<ref bean="dataSource" />
		</property>
		<property name="hibernateProperties">
			<props>
				<!-- c3p0 ConnectPool Config begin -->
				<prop key="hibernate.connection.provider_class">
					org.hibernate.connection.C3P0ConnectionProvider
				</prop>
				<prop key="hibernate.c3p0.minPoolSize">5</prop>
				<prop key="hibernate.c3p0.maxPoolSize">20</prop>
				<prop key="hibernate.c3p0.timeout">600</prop>
				<prop key="hibernate.c3p0.max_statement">100</prop>
				<prop key="hibernate.c3p0.acquire_increment">2</prop>
				<prop key="hibernate.c3p0.idle_test_period">120</prop>
				<prop key="hibernate.c3p0.validate">false</prop>
				<prop key="hibernate.c3p0.testConnectionOnCheckout">
					false
				</prop>
				<prop key="hibernate.dialect">${jdbc.dialect}</prop>
				<!-- c3p0 ConnectionPool Config end -->
				<prop key="hibernate.show_sql">${hibernate.show_sql}</prop>

				<!-- Take care of this config, affect to DB. -->
				<prop key="hibernate.hbm2ddl.auto">${hibernate.hbm2ddl.auto}</prop>
				<prop key="hibernate.format_sql">${hibernate.format_sql}</prop>
				<prop key="hibernate.jdbc.fetch_size">50</prop>
				<prop key="hibernate.jdbc.batch_size">30</prop>
				<prop key="hibernate.statement_cache.size">25</prop>
			</props>
		</property>
		<property name="mappingLocations">
			<list>
				<value>
					classpath:/cn/com/timekey/project/po/**.hbm.xml
				</value>
			</list>
		</property>
		<property name="entityInterceptor">
			<bean id="myNullsFirstInterceptor"
				class="cn.com.timekey.commons.dao.MyNullsFirstInterceptor" />
		</property>
	</bean>
</beans>

 

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