db2dialect.java

jsf+spring+ibatis 的一个程序,主要用来介绍JSF程序.

package com.wxd.common.paging;

/**
 * 分页查询方言支持DB2实现 （没有在db2中进行测试，请根据测试结果进行调整）
 * 参考hibernate 实现
 * @author <a href="mailto:[e-mail]">zhuwei </a>
 * @version 1.0
 */
public class DB2Dialect implements Dialect {

	protected static final String SQL_END_DELIMITER = ";";

	public String getLimitString(String sql, boolean hasOffset) {
		int startOfSelect = sql.toLowerCase().indexOf("select");
		StringBuffer pagingSelect = (new StringBuffer(sql.length() + 100))
				.append(sql.substring(0, startOfSelect)).append(
						"select * from ( select ").append(getRowNumber(sql));
		if (hasDistinct(sql))
			pagingSelect.append(" row_.* from ( ").append(
					sql.substring(startOfSelect)).append(" ) as row_");
		else
			pagingSelect.append(sql.substring(startOfSelect + 6));
		pagingSelect.append(" ) as temp_ where rownumber_ ");
		if (hasOffset)
			pagingSelect.append("between ?+1 and ?");
		else
			pagingSelect.append("<= ?");
		return pagingSelect.toString();
	}

	public String getLimitString(String sql, int pageIndex, int pageSize) {
		sql = sql.trim();
		int m = pageIndex * pageSize;
		int n = m + pageSize;

		int startOfSelect = sql.toLowerCase().indexOf("select");
		StringBuffer pagingSelect = (new StringBuffer(sql.length() + 100))
				.append(sql.substring(0, startOfSelect)).append(
						"select * from ( select ").append(getRowNumber(sql));
		if (hasDistinct(sql))
			pagingSelect.append(" row_.* from ( ").append(
					sql.substring(startOfSelect)).append(" ) as row_");
		else
			pagingSelect.append(sql.substring(startOfSelect + 6));
		pagingSelect.append(" ) as temp_ where rownumber_ ");

		pagingSelect.append("between " + m + "+1 and " + n);

		return pagingSelect.toString();
	}

	private static boolean hasDistinct(String sql) {
		return sql.toLowerCase().indexOf("select distinct") >= 0;
	}

	private String getRowNumber(String sql) {
		StringBuffer rownumber = (new StringBuffer(50))
				.append("rownumber() over(");
		int orderByIndex = sql.toLowerCase().indexOf("order by");
		if (orderByIndex > 0 && !hasDistinct(sql))
			rownumber.append(sql.substring(orderByIndex));
		rownumber.append(") as rownumber_,");
		return rownumber.toString();
	}

	public boolean supportsLimit() {
		return true;
	}

	private String trim(String sql) {
		sql = sql.trim();
		if (sql.endsWith(SQL_END_DELIMITER)) {
			sql = sql.substring(0, sql.length() - 1
					- SQL_END_DELIMITER.length());
		}
		return sql;
	}
}