xing00 2008-07-24
Hibernate一直没有提供针对SqlServer2005的Dialect,原有的SqlServerDialect不支持物理分页查询。最近经常使用SqlServer2005,所以就很想写一个使用SqlServer2005ROW_NUMBER()函数实现limit查询的Dialect类。
下面这个类是使用ROW_NUMBER()函数分页的Dialect,开始是从NHibernate移植的,后来又参考了Hibernate的Jira(http://opensource.atlassian.com/projects/hibernate/browse/HHH-2655)。不知道为什,Jira上的那个有BUG类可以很简单的修改,但是却没人理会。所以我感觉下面这个类可能也存在问题,有兴趣的朋友帮忙测测。
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 AND __hibernate_row_nr__ <= offset + limit * 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 limit Maximum number of rows to be returned by the query * @return A new SQL statement with the LIMIT clause applied. */ public String getLimitString(String querySqlString, int offset, int limit) { StringBuffer pagingBuilder = new StringBuffer(); String orderby = getOrderByPart(querySqlString); String distinctStr = ""; String loweredString = querySqlString.toLowerCase(); String sqlPartString = querySqlString.trim(); 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 ROW_NUMBER() OVER (") //使用TOP 100 PERCENT可以提高性能 .append(orderby) .append(") AS __hibernate_row_nr__, ") .append(pagingBuilder) .append(") SELECT * FROM query WHERE __hibernate_row_nr__ >") .append(offset) .append(" AND __hibernate_row_nr__ <=") .append(offset + limit) .append(" ORDER BY __hibernate_row_nr__"); return result.toString(); } public boolean supportsLimit() { return true; } 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 ""; } } }