关于 hibernate 查询中实现order by的 NULLS LAST 和 NULLS FIRST
1:创建一个CustomNullsFirstInterceptor类
public class CustomNullsFirstInterceptor extends EmptyInterceptor {
private static final long serialVersionUID = -3156853534261313031L;
private static final String ORDER_BY_TOKEN = "order by";
public String onPrepareStatement(String sql) {
int orderByStart = sql.toLowerCase().indexOf(ORDER_BY_TOKEN);
if (orderByStart == -1) {
return super.onPrepareStatement(sql);
}
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);
return super.onPrepareStatement(sql);
}
}
2:加入配置
<bean id="sessionFactory"
class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
<property name="dataSource" ref="dataSource" />
<property name="packagesToScan">
<list>
<value>com.skywin.law.workflow.domain</value>
<!--<value>com.skywin.common.fileupload.domain</value>-->
<value>com.skywin.common.domain</value>
<value>com.skywin.chat.domain</value>
<value>com.skywin.law.assistflow.domain</value>
</list>
</property>
<property name="mappingResources" ref="sessionFactoryMappingResources"></property>
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">${hibernate.dialect}</prop>
<prop key="hibernate.cache.use_second_level_cache">true</prop>
<!-- <prop key="hibernate.cache.provider_configuration_file_resource_path">diss-override-ehcache.xml</prop> -->
<prop key="hibernate.cache.region.factory_class">net.sf.ehcache.hibernate.EhCacheRegionFactory</prop>
<!-- <prop key="hibernate.cache.provider_class">org.hibernate.cache.EhCacheProvider</prop> -->
<prop key="hibernate.cache.use_query_cache">true</prop>
<prop key="hibernate.show_sql">true</prop>
<!-- <prop key="hibernate.hbm2ddl.auto">update</prop> -->
<prop key="hibernate.format_sql">true</prop>
<prop key="hibernate.jdbc.batch_size">0</prop>
<prop key="hibernate.hbm2ddl.import_files">import-install.sql</prop>
</props>
</property>
<property name="entityCacheStrategies">
<props>
<prop key="org.jbpm.pvm.internal.task.TaskImpl">read-write</prop>
</props>
</property>
<property name="entityInterceptor">
<bean id ="customNullsFirstInterceptor" class="com.skywin.common.hibernate3.CustomNullsFirstInterceptor" />
</property>
</bean>