mysql bitxor_Hibernate中SQLServer2005方言(支持真分页) | 学步园

由于Hibernate中默认的SQL Server方言不支持真分页，在两者结合的时候总会觉得有那么一点不和谐。Oracle提供了rownum，MySQL也有limit和offset，但是SQL Server只有top，所以Hibernate采用的策略是将每页条数*页码的数据使用top全部加载进来再在内存中进行假分页的处理。不过SQL Server 2005以上版本微软已经提供了ROWNUMBER()的支持，所以有必要对方言进行改进以提升性能，另外也简单地加了几个函数。

/**

*

SQL Server 2005方言。

*

* 1.支持SQL Server 2005版本，不支持SQL Server 2000及以前版本

* 2.支持真分页，第一页采用top查询，其余采用ROW_NUMBER()实现。第一页以上查询要求sql中必须含有排序子句

*

* @author Reker

*

2008-6-11

*/

public class SQLServer2005Dialect extends CustomSQLDialect {

/**

* 构造函数。

*/

public SQLServer2005Dialect() {

super();

registerFunction("bitand", new BitAndFunction());

registerFunction("bitxor", new BitXorFunction());

registerFunction("bitor", new BitOrFunction());

setSupportsVariableLimit(false);

}

/**

* 是否需要绑定limit参数?

* 在SQL Server中使用top时不能使用参数表示top条数,而使用ROW_NUMBER()则需要提供limit参数

*/

private ThreadLocal supportsVariableLimit = new ThreadLocal();

/**

*

设置是否先绑定limit参数。

* @author Reker

* @param first

*/

private void setSupportsVariableLimit(boolean first) {

supportsVariableLimit.set(first);

}

/**

*

获取sql中select子句位置。

* @author Reker

* @param sql

* @return int

*/

protected static int getSqlAfterSelectInsertPoint(String sql) {

int selectIndex = sql.toLowerCase().indexOf("select");

final int selectDistinctIndex = sql.toLowerCase().indexOf("select distinct");

return selectIndex + (selectDistinctIndex == selectIndex ? 15 : 6);

}

/*

* @author Reker

* SQLServerDialect中supportsLimit为true，

* 但supportLimitOffset和supportsVariableLimit为false

*/

public boolean supportsLimitOffset() {

return true;

}

/*

* @author Reker

* @see org.hibernate.loader.Loader#bindLimitParameters(PreparedStatement ,int ,RowSelection)

* Hibernate在获得Limit String(已添加了limit子句)后，如果此方法返回true，

* 则会添加额外的参数值(ROW_NUMBER()范围)(策略可能是这样：有offset设置两个参数值，没有设置一个参数值)

*/

public boolean supportsVariableLimit() {

return supportsVariableLimit.get();

}

/*

* @author Reker

* @see org.hibernate.loader.Loader#getMaxOrLimit(RowSelection,Dialect)

*/

public boolean useMaxForLimit() {

return true;

}

/*

* @author Reker

*/

public String getLimitString(String query, int offset, int limit) {

setSupportsVariableLimit(offset > 0);

if (offset == 0) { //no offset , use top , 这时不能支持limit参数绑定

return new StringBuffer(query.length() + 8).append(query).insert(getSqlAfterSelectInsertPoint(query),

" top " + limit).toString();

}

return getLimitString(query, offset > 0);

}

/*

* @author Reker

*/

public String getLimitString(String sql, boolean hasOffset) {

int orderByIndex = sql.toLowerCase().lastIndexOf("order by");

if (orderByIndex <= 0) {

throw new UnsupportedOperationException(

"must specify 'order by' statement to support limit operation with offset in sql server 2005");

}

String sqlOrderBy = sql.substring(orderByIndex + 8);

String sqlRemoveOrderBy = sql.substring(0, orderByIndex);

int insertPoint = getSqlAfterSelectInsertPoint(sql);

return new StringBuffer(sql.length() + 100).append("with tempPagination as(").append(sqlRemoveOrderBy).insert(

insertPoint + 23, " ROW_NUMBER() OVER(ORDER BY " + sqlOrderBy + ") as RowNumber,").append(

") select * from tempPagination where RowNumber between ? and ?").toString();

}

函数类：

public class BitAndFunction implements SQLFunction {

public Type getReturnType(Type type, Mapping mapping) {

return Hibernate.INTEGER;

}

public boolean hasArguments() {

return true;

}

public boolean hasParenthesesIfNoArguments() {

return true;

}

@SuppressWarnings("unchecked")

public String render(List args, SessionFactoryImplementor factory) throws QueryException {

if (args.size() != 2) {

throw new IllegalArgumentException("BitAndFunction requires 2 arguments!");

}

return args.get(0).toString() + " & " + args.get(1).toString();

}

}

public class BitOrFunction implements SQLFunction {

public Type getReturnType(Type type, Mapping mapping) {

return Hibernate.INTEGER;

}

public boolean hasArguments() {

return true;

}

public boolean hasParenthesesIfNoArguments() {

return true;

}

@SuppressWarnings("unchecked")

public String render(List args, SessionFactoryImplementor factory) throws QueryException {

if (args.size() != 2) {

throw new IllegalArgumentException("BitOrFunction requires 2 arguments!");

}

return args.get(0).toString() + " | " + args.get(1).toString();

}

}

public class BitXorFunction implements SQLFunction {

public Type getReturnType(Type type, Mapping mapping) {

return Hibernate.INTEGER;

}

public boolean hasArguments() {

return true;

}

public boolean hasParenthesesIfNoArguments() {

return true;

}

@SuppressWarnings("unchecked")

public String render(List args, SessionFactoryImplementor factory) throws QueryException {

if (args.size() != 2) {

throw new IllegalArgumentException("BitXorFunction requires 2 arguments!");

}

return args.get(0).toString() + " ^ " + args.get(1).toString();

}

}