在不同数据库中对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字段优先级默认就是最低)
相关推荐
适用情况: oracle 对查询结果进行排序时,被排序的栏位存在null值,且要指定NULL值排在最前面或者最后面 关键字:Nulls First;Nulls Last 默认情况:null 默认为最大值(即:asc 升序<小–>大>,null值排在最后...
将字符串排序为 SQL 这是一个 rust crate,用于将“排序表达式”转换为可在“ORDER BY”中使用的 SQL 表达式。 例如, -date,id将转换为date DESC NULLS LAST, id ASC NULLS LAST 。 它可用于将 HTTP 查询参数转换...
nulls_for_goals
ArcEngine二次开发代码,调试可运行,可实现属性查询、空间查询和统计功能!供初学者练习使用.........
Special version of lists, where end of list is not a NULL pointer, but a nulls marker, which can have many different values.
抽象精品ppt模板nulls_for_goals166
SQL Order By SQL insert SQL update SQL delete SQL 高级 SQL Top SQL Like SQL 通配符 SQL In SQL Between SQL Aliases SQL Join SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL ...
iterate over a hlist continuing current point.
主要介绍了Java函数式编程(九):Comparator,本文是系列文章的第9篇,其它文章请参阅本文底部的相关文章,需要的朋友可以参考下
A clustered index is like a telephone directory in which all of the rows for customers with the same last name are clustered together in the same part of the book. Just as the organization of a ...
在oracle中,有rank,dense_rank,row_number,以及分组排名partition。 说明: rank:排名会出现并列第n名,它...row_number() over(partition by bb.channel_name order by sum(aa.dk_serv_num) desc nulls last) p1_ra
select * from dy_info t order by t.degree nulls first 1.8.4.2 说明 控制null显示行位置 1.8.5 Nvl 1.8.5.1 语法 select nvl(t.sap,'空') from dy_info t; 1.8.5.2 说明 如果sap号为空,则返回‘空’,否则返回...
SET ANSI_NULLS ON GO ALTER PROCEDURE test @From varchar(100)="zhang.zhanling@itgemini.net" , @To varchar(100)="zhang.zhanling@itgemini.net" ,@eng varchar(1000)=" ", @Bcc varchar(500)="xu.ming;...
jquery_aja_plsql_xml_hibernate、
Consistent Query Answering Based on Repairing Inconsistent Attributes with Nulls
NULL 博文链接:https://caohong286.iteye.com/blog/1275437
替换_white_space_with_nulls_in_excel 用NULL替换空白和空字符串的代码
校友录系统(ASP.NET 3.5实现)USE [friends] GO /****** 对象: Table [dbo].[diaryclass] 脚本日期: 10/22/2008 22:49:24 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo]....
The method used is a matter of preference, but *it will be very difficult to use this library without first understanding the JSON syntax for the API*, so it is recommended to look at the [APIs ...
留言本系统(ASP.NET 3.5实现)USE [guestbook] GO /****** 对象: Table [dbo].[gbook_class] 脚本日期: 10/22/2008 22:51:16 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[gbook...