hibernate 3.2.7 支持SQL SERVER 2005+的分页

由于hibernate 3.2.7是用top处理mssql 查询分页，效率不高。由于在 3.6.x 版本中已经有改进的代码，因此从最近的版本hibernate-distribution-3.6.10.Final-dist.tar.gz中拷贝出SQLServer2005Dialect.java、SQLServer2008Dialect.java

具体目录是

hibernate-distribution-3.6.10.Final-dist.tar.gz\hibernate-distribution-3.6.10.Final\project\hibernate-core\src\main\java\org\hibernate\dialect

SQLServer2005Dialect.java 稍作修改：

/* * Hibernate, Relational Persistence for Idiomatic Java * * Copyright (c) 2010, Red Hat Inc. or third-party contributors as * indicated by the @author tags or express copyright attribution * statements applied by the authors.  All third-party contributions are * distributed under license by Red Hat Inc. * * This copyrighted material is made available to anyone wishing to use, modify, * copy, or redistribute it subject to the terms and conditions of the GNU * Lesser General Public License, as published by the Free Software Foundation. * * This program is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY * or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU Lesser General Public License * for more details. * * You should have received a copy of the GNU Lesser General Public License * along with this distribution; if not, write to: * Free Software Foundation, Inc. * 51 Franklin Street, Fifth Floor * Boston, MA  02110-1301  USA */package org.hibernate.dialect;import java.sql.Types;import org.hibernate.Hibernate;import org.hibernate.dialect.function.NoArgSQLFunction;//import org.hibernate.type.StandardBasicTypes;/** * A dialect for Microsoft SQL 2005. (HHH-3936 fix) *  * @author Yoryos Valotasios */public class MySQLServer2005Dialect extends SQLServerDialect {private static final String SELECT = "select";private static final String FROM = "from";private static final String DISTINCT = "distinct";private static final int MAX_LENGTH = 8000;@SuppressWarnings("deprecation")public MySQLServer2005Dialect() {// HHH-3965 fix// As per http://www.sql-server-helper.com/faq/sql-server-2005-varchar-max-p01.aspx// use varchar(max) and varbinary(max) instead of TEXT and IMAGE typesregisterColumnType( Types.BLOB, "varbinary(MAX)" );registerColumnType( Types.VARBINARY, "varbinary(MAX)" );registerColumnType( Types.VARBINARY, MAX_LENGTH, "varbinary($l)" );registerColumnType( Types.LONGVARBINARY, "varbinary(MAX)" );registerColumnType( Types.CLOB, "varchar(MAX)" );registerColumnType( Types.LONGVARCHAR, "varchar(MAX)" );registerColumnType( Types.VARCHAR, "varchar(MAX)" );registerColumnType( Types.VARCHAR, MAX_LENGTH, "varchar($l)" );registerColumnType( Types.BIGINT, "bigint" );registerColumnType( Types.BIT, "bit" );registerColumnType( Types.BOOLEAN, "bit" );//registerFunction("row_number", new NoArgSQLFunction("row_number", StandardBasicTypes.INTEGER, true));registerFunction("row_number", new NoArgSQLFunction("row_number", Hibernate.INTEGER, true));        registerHibernateType(Types.CHAR, Hibernate.STRING.getName());           registerHibernateType(Types.NVARCHAR, Hibernate.STRING.getName());           registerHibernateType(Types.LONGNVARCHAR, Hibernate.STRING.getName());           registerHibernateType(Types.DECIMAL, Hibernate.DOUBLE.getName()); }@Overridepublic boolean supportsLimitOffset() {return true;}@Overridepublic boolean bindLimitParametersFirst() {return false;}@Overridepublic boolean supportsVariableLimit() {return true;}//@Overridepublic int convertToFirstRowValue(int zeroBasedFirstResult) {// Our dialect paginated results aren't zero based. The first row should get the number 1 and so onreturn zeroBasedFirstResult + 1;}@Overridepublic String getLimitString(String query, int offset, int limit) {// We transform the query to one with an offset and limit if we have an offset and limit to bindif (offset > 1 || limit > 1)  {return getLimitString(query, true);}return query;}/** * Add a LIMIT clause to the given SQL SELECT (HHH-2655: ROW_NUMBER for Paging) *  * The LIMIT SQL will look like: *  * <pre> * WITH query AS ( *   SELECT ROW_NUMBER() OVER (ORDER BY orderby) as __hibernate_row_nr__,  *   original_query_without_orderby * ) * SELECT * FROM query WHERE __hibernate_row_nr__ BEETWIN offset AND offset + last * </pre> *  *  * @param querySqlString *            The SQL statement to base the limit query off of. * @param offset *            Offset of the first row to be returned by the query (zero-based) * @param limit *            Maximum number of rows to be returned by the query *  * @return A new SQL statement with the LIMIT clause applied. */@Overridepublic String getLimitString(String querySqlString, boolean hasOffset) {StringBuilder sb = new StringBuilder(querySqlString.trim().toLowerCase());int orderByIndex = sb.indexOf("order by");CharSequence orderby = orderByIndex > 0 ? sb.subSequence(orderByIndex, sb.length()): "ORDER BY CURRENT_TIMESTAMP";// Delete the order by clause at the end of the queryif (orderByIndex > 0) {sb.delete(orderByIndex, orderByIndex + orderby.length());}// HHH-5715 bug fixreplaceDistinctWithGroupBy(sb);insertRowNumberFunction(sb, orderby);// Wrap the query within a with statement:sb.insert(0, ";WITH query AS (").append(") SELECT * FROM query ");sb.append("WHERE __hibernate_row_nr__ BETWEEN ? AND ?");return sb.toString();}/** * Utility method that checks if the given sql query is a select distinct one and if so replaces the distinct select * with an equivalent simple select with a group by clause. See * {@link SQLServer2005DialectTestCase#testReplaceDistinctWithGroupBy()} *  * @param sql an sql query */protected static void replaceDistinctWithGroupBy(StringBuilder sql) {int distinctIndex = sql.indexOf(DISTINCT);if (distinctIndex > 0) {sql.delete(distinctIndex, distinctIndex + DISTINCT.length() + 1);sql.append(" group by").append(getSelectFieldsWithoutAliases(sql));}}/** * This utility method searches the given sql query for the fields of the select statement and returns them without * the aliases. See {@link SQLServer2005DialectTestCase#testGetSelectFieldsWithoutAliases()} *  * @param an *            sql query * @return the fields of the select statement without their alias */protected static CharSequence getSelectFieldsWithoutAliases(StringBuilder sql) {String select = sql.substring(sql.indexOf(SELECT) + SELECT.length(), sql.indexOf(FROM));// Strip the as clausesreturn stripAliases(select);}/** * Utility method that strips the aliases. See {@link SQLServer2005DialectTestCase#testStripAliases()} *  * @param a *            string to replace the as statements * @return a string without the as statements */protected static String stripAliases(String str) {return str.replaceAll("\\sas[^,]+(,?)", "$1");}/** * Right after the select statement of a given query we must place the row_number function *  * @param sql *            the initial sql query without the order by clause * @param orderby *            the order by clause of the query */protected static void insertRowNumberFunction(StringBuilder sql, CharSequence orderby) {// Find the end of the select statementint selectEndIndex = sql.indexOf(SELECT) + SELECT.length();// Insert after the select statement the row_number() function:sql.insert(selectEndIndex, " ROW_NUMBER() OVER (" + orderby + ") as __hibernate_row_nr__,");}}

SQLServer2008Dialect.java 保留原样

/* * Hibernate, Relational Persistence for Idiomatic Java * * Copyright (c) 2010, Red Hat Inc. or third-party contributors as * indicated by the @author tags or express copyright attribution * statements applied by the authors.  All third-party contributions are * distributed under license by Red Hat Inc. * * This copyrighted material is made available to anyone wishing to use, modify, * copy, or redistribute it subject to the terms and conditions of the GNU * Lesser General Public License, as published by the Free Software Foundation. * * This program is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY * or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU Lesser General Public License * for more details. * * You should have received a copy of the GNU Lesser General Public License * along with this distribution; if not, write to: * Free Software Foundation, Inc. * 51 Franklin Street, Fifth Floor * Boston, MA  02110-1301  USA */package org.hibernate.dialect;import java.sql.Types;import org.hibernate.Hibernate;import org.hibernate.dialect.function.NoArgSQLFunction;/** * A dialect for Microsoft SQL Server 2008 with JDBC Driver 3.0 and above * * @author Gavin King */public class SQLServer2008Dialect extends SQLServer2005Dialect {public SQLServer2008Dialect(){registerColumnType( Types.DATE, "date" );registerColumnType( Types.TIME, "time" );registerColumnType( Types.TIMESTAMP, "datetime2" );registerFunction( "current_timestamp", new NoArgSQLFunction("current_timestamp", Hibernate.TIMESTAMP,false) );}}

默认的是SQL Server 2000

hibernate.dialect=org.hibernate.dialect.SQLServerDialect

对于SQL Server 2005 修改配置

hibernate.dialect=org.hibernate.dialect.SQLServer2005Dialect

对于 SQL Server 2008 修改配置

hibernate.dialect=org.hibernate.dialect.SQLServer2008Dialect