首页 > 代码库 > 解决hibernate对Sql Server分页慢的问题

解决hibernate对Sql Server分页慢的问题

2024-11-22 22:36:39   203人阅读

 
一、hibernate分页 hibernate对MsSql的伪分页 
分页是web项目中比不可少的一个功能，数据量大的时候不能全部展示必然要用到分页技术。相信大家对hibernate中的分页都不陌生： 
Java代码  技术分享
  1. public Query setMaxResults(int maxResults);  
  2. public Query setFirstResult(int firstResult);  

只要调用了这两个方法并设置好参数，hibernate自动分页完全屏蔽了底层数据库分页技术，这也是众多开发者喜欢hibernate的原因之一。 
项目开发中遇到一个奇怪的问题。数据库采用的是Sql Server 2005，也设置了上面两个参数，可是每次发送到数据库端的SQL语句都是select top ....语句。即便是查询第10w条，也只有一个select top 语句，不免引起对hibernate实现sql server分页的怀疑。hibernate针对不同数据库实现的分页方法封装在对应数据库的方言里，通过getLimitString方法转化成对应数据库的分页算法。 
以常见的Mysql数据库的方言MySQLDialect为例： 
Java代码  技术分享
  1. public String getLimitString(String sql, boolean hasOffset) {  
  2.         return new StringBuffer( sql.length() + 20 )  
  3.                 .append( sql )  
  4.                 .append( hasOffset ? " limit ?, ?" : " limit ?" )  
  5.                 .toString();  
  6.     }  

采用了大家熟悉的mysql的limit进行分页。 
Oracle数据库的方言Oracle9iDialect： 
Java代码  技术分享
  1. StringBuffer pagingSelect = new StringBuffer( sql.length()+100 );  
  2.         if (hasOffset) {  
  3.             pagingSelect.append("select * from ( select row_.*, rownum rownum_ from ( ");  
  4.         }  
  5.         else {  
  6.             pagingSelect.append("select * from ( ");  
  7.         }  
  8.         pagingSelect.append(sql);  
  9.         if (hasOffset) {  
  10.             pagingSelect.append(" ) row_ where rownum <= ?) where rownum_ > ?");  
  11.         }  
  12.         else {  
  13.             pagingSelect.append(" ) where rownum <= ?");  
  14.         }  

利用Oracle的rownum 结合三层嵌套查询完成分页。这个三层是Oracle最经典高效的分页算法。 
可是针对Sql Server的方言SQLServerDialect： 
Java代码  技术分享
  1. public String getLimitString(String querySelect, int offset, int limit) {  
  2.         if ( offset > 0 ) {  
  3.             throw new UnsupportedOperationException( "query result offset is not supported" );  
  4.         }  
  5.         return new StringBuffer( querySelect.length() + 8 )  
  6.                 .append( querySelect )  
  7.                 .insert( getAfterSelectInsertPoint( querySelect ), " top " + limit )  
  8.                 .toString();  
  9.     }  

揉揉眼睛、再揉揉，没错，只出现了一个top语句。这就意味着如果查询第10w页的数据，需要把前10w页数据全部提取出来。hibernate针对sql server的分页是伪分页，所以随着数据量日益增加用户抱怨系统速度慢，程序员抱怨hibernate性能低，dba抱怨开发人员sql功底太浅。 
不知道hibernate开发组，出于什么目前或情况没有真正提供sql server的分页技术，那我们自己来实现。 
方言类： 
Java代码  技术分享
  1. public class SQLServer2005Dialect extends SQLServerDialect {  
  2.   
  3.     /** 
  4.      *  
  5.      * 是否需要绑定limit参数? 
  6.      *  
  7.      * 在SQL Server中使用top时不能使用参数表示top条数,而使用ROW_NUMBER()则需要提供limit参数 
  8.      */  
  9.   
  10.     private ThreadLocal<Boolean> supportsVariableLimit = new ThreadLocal<Boolean>();  
  11.     public SQLServer2005Dialect() {  
  12.         registerFunction("bitand", new BitAndFunction());  
  13.         registerFunction("bitxor", new BitXorFunction());  
  14.         registerFunction("bitor", new BitOrFunction());  
  15.         setSupportsVariableLimit(false);  
  16.     }  
  17.   
  18.     /** 
  19.      *  
  20.      * <p> 
  21.      * 设置是否先绑定limit参数。 
  22.      * </p> 
  23.      *  
  24.      * @param first 
  25.      */  
  26.   
  27.     private void setSupportsVariableLimit(boolean first) {  
  28.         this.supportsVariableLimit.set(Boolean.valueOf(first));  
  29.     }  
  30.   
  31.     /** 
  32.      *  
  33.      * <p> 
  34.      * 获取sql中select子句位置。 
  35.      * </p> 
  36.      *  
  37.      * @param sql 
  38.      *  
  39.      * @return int 
  40.      */  
  41.     protected static int getSqlAfterSelectInsertPoint(String sql) {  
  42.         int selectIndex = sql.toLowerCase().indexOf("select");  
  43.   
  44.         int selectDistinctIndex = sql.toLowerCase().indexOf("select distinct");  
  45.   
  46.         return selectIndex + ((selectDistinctIndex == selectIndex) ? 15 : 6);  
  47.     }  
  48.   
  49.     public boolean supportsLimitOffset() {  
  50.         return true;  
  51.     }  
  52.   
  53.     /* 
  54.      * Hibernate在获得Limit String(已添加了limit子句)后，如果此方法返回true， 
  55.      *  
  56.      * 则会添加额外的参数值(ROW_NUMBER()范围)(策略可能是这样：有offset设置两个参数值，没有设置一个参数值) 
  57.      */  
  58.     public boolean supportsVariableLimit() {  
  59.         return ((Boolean) this.supportsVariableLimit.get()).booleanValue();  
  60.     }  
  61.   
  62.     public boolean useMaxForLimit() {  
  63.         return true;  
  64.     }  
  65.     /** 
  66.      * 首页top，以后用ROW_NUMBER 
  67.      */  
  68.     public String getLimitString(String query, int offset, int limit) {  
  69.         setSupportsVariableLimit(offset > 0);  
  70.   
  71.         if (offset == 0) {  
  72.             return new StringBuffer(query.length() + 8).append(query).insert(  
  73.                     getSqlAfterSelectInsertPoint(query), " top " + limit)  
  74.                     .toString();  
  75.         }  
  76.   
  77.         return getLimitString(query, offset > 0);  
  78.     }  
  79.       
  80.     public String getLimitString(String sql, boolean hasOffset) {  
  81.         int orderByIndex = sql.toLowerCase().lastIndexOf("order by");  
  82.   
  83.         if (orderByIndex <= 0) {  
  84.             throw new UnsupportedOperationException(  
  85.                     "must specify ‘order by‘ statement to support limit operation with offset in sql server 2005");  
  86.         }  
  87.   
  88.         String sqlOrderBy = sql.substring(orderByIndex + 8);  
  89.   
  90.         String sqlRemoveOrderBy = sql.substring(0, orderByIndex);  
  91.   
  92.         int insertPoint = getSqlAfterSelectInsertPoint(sql);  
  93.         return new StringBuffer(sql.length() + 100)  
  94.                 .append("with tempPagination as(")  
  95.                 .append(sqlRemoveOrderBy)  
  96.                 .insert(  
  97.                         insertPoint + 23,  
  98.                         " ROW_NUMBER() OVER(ORDER BY " + sqlOrderBy  
  99.                                 + ") as RowNumber,")  
  100.                 .append(  
  101.                         ") select * from tempPagination where RowNumber>?  and RowNumber<=?")  
  102.                 .toString();  
  103.     }  
  104. }  


最后在配置hibernate的时候在 hibernate.cfg.xml 配置 dialect 使用自己的SQLServer2005Dialect类

 

<property name="dialect">
      com.common.SQLServer2005Dialect
</property>
或在与spring的集成配置文件中

 

 <prop key="hibernate.dialect">com.common.SQLServer2005Dialect</prop>

在分页时，就可以达到很好的效率了。


第一页采用top分页，以后采用ROW_NUMBER分页第一页以上查询要求sql中必须含有排序子句。 
这只是在项目过程中采用的sql server遇到的该问题，如果使用mysql，oracle则不会遇到这个问题。

解决hibernate对Sql Server分页慢的问题


联系
我们