重写SQLServer2005Dialect,使Hibernate支持真正分页

最近项目要用到sqlserver数据库的数据，之前基本上没有真正用到sqlserver数据库做实际开发，因此碰到了不少的问题，下面的是我在分页方面遇到的问题，记录下来

org.hibernate.dialect.SQLServerDialect类里面的方法

public String getLimitString(String querySelect, int offset, int limit) {
               if (offset > 0)
                     throw new UnsupportedOperationException("sql server has no offset");

                     return new StringBuffer(querySelect.length() + 8).append(querySelect).insert(getAfterSelectInsertPoint(querySelect), " top " + limit).toString();
 

 }

可以看出，hibernate对其分页是采用的top......分页的,数据量很大的时候，速度.性能可想而知，sql2005出现了ROW_NUMBER()函数，大大提高的查询速度.

以下继承该Dialect,重写该方法:

import java.sql.Types;

import org.hibernate.dialect.SQLServerDialect;

/**
 * Author: Wesley Wu / Jo�o Vasconcellos
 * [email protected] / [email protected]
 * Date: 2006-4-29 / 2008-02-12
 * Time: 14:24:03 / 18:11:00
 */
public class SQLServer2005Dialect extends SQLServerDialect {
	public SQLServer2005Dialect() {
		super();

		registerColumnType(Types.VARCHAR, 1073741823, "NVARCHAR(MAX)");
		registerColumnType(Types.VARCHAR, 2147483647, "VARCHAR(MAX)");
		registerColumnType(Types.VARBINARY, 2147483647, "VARBINARY(MAX)");
	}

	/**
	 * Add a LIMIT clause to the given SQL SELECT
	 *
	 * The LIMIT SQL will look like:
	 *
	 * WITH query AS
	 * (SELECT TOP 100 percent ROW_NUMBER() OVER (ORDER BY orderby) as __hibernate_row_nr__, ... original_query)
	 * SELECT *
	 * FROM query
	 * WHERE __hibernate_row_nr__ > offset
	 * ORDER BY __hibernate_row_nr__
	 *
	 * @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 last           Maximum number of rows to be returned by the query
	 * @return A new SQL statement with the LIMIT clause applied.
	 */
	@Override
	public String getLimitString(String querySqlString, int offset, int last) {
		/*
				 * WITH query AS
				 *     (SELECT TOP 100 percent ROW_NUMBER() OVER (ORDER BY orderby) as __hibernate_row_nr__, ... original_query)
				 * SELECT *
				 * FROM query
				 * WHERE __hibernate_row_nr__ > offset
				 * ORDER BY __hibernate_row_nr__
				 */
		StringBuffer pagingBuilder = new StringBuffer();
		String orderby = getOrderByPart(querySqlString);
		String distinctStr = "";

		String loweredString = querySqlString.toLowerCase();
		String sqlPartString = querySqlString;
		if (loweredString.trim().startsWith("select")) {
			int index = 6;
			if (loweredString.startsWith("select distinct")) {
				distinctStr = "DISTINCT ";
				index = 15;
			}
			sqlPartString = sqlPartString.substring(index);
		}
		pagingBuilder.append(sqlPartString);

		// if no ORDER BY is specified use fake ORDER BY field to avoid errors
		if (orderby == null || orderby.length() == 0) {
			orderby = "ORDER BY CURRENT_TIMESTAMP";
		}

		StringBuffer result = new StringBuffer();
		result.append("WITH query AS (SELECT ")
				.append(distinctStr)
				.append("TOP 100 PERCENT ")
				.append(" ROW_NUMBER() OVER (")
				.append(orderby)
				.append(") as __hibernate_row_nr__, ")
				.append(pagingBuilder)
				.append(") SELECT * FROM query WHERE __hibernate_row_nr__ > ")
				.append(offset)
				.append(" ORDER BY __hibernate_row_nr__");

		return result.toString();
	}

	@Override
	public boolean supportsLimit() {
		return true;
	}

	@Override
	public boolean supportsLimitOffset() {
		return true;
	}

	@Override
	public boolean useMaxForLimit() {
		return false;
	}

	static String getOrderByPart(String sql) {
		String loweredString = sql.toLowerCase();
		int orderByIndex = loweredString.indexOf("order by");
		if (orderByIndex != -1) {
			// if we find a new "order by" then we need to ignore
			// the previous one since it was probably used for a subquery
			return sql.substring(orderByIndex);
		} else {
			return "";
		}
	}
}

以上内容均来自互连网,今天测试可用.具体内容未解,欢迎一起探讨.