分享一个java针对不同数据库的分页代码

分享一个java针对不同数据库的分页代码

package org.rwl.utils.db;
import org.apache.log4j.Logger;
import org.rwl.utils.db.dialect.ExtendDialect;
import org.rwl.utils.db.dialect.impl.ExtendDB2Dialect;
import org.rwl.utils.exception.RwlDialectException;
/**
 * 数据库SQL规则处理
 * @author polarbear 2009-5-9
 *
 */
public class RwlDBDialectUtil {
	
	private volatile static RwlDBDialectUtil instance = null;
	
	private RwlDBDialectUtil.dbtype currentDialect = RwlDBDialectUtil.dbtype.mysql;
	
	private String SQL_SERVER_VERSION = "2005";
	
	/**
	 * 是否支持分页: 1:支持分页(缺省)  0:不支持分页
	 */
	private int SQL_SUPPORT_PAGING = 1;
	
	/**
	 * 分页处理程序
	 */
	public static final ExtendDialect db2Dialect = new ExtendDB2Dialect();
	
	private static Logger log = Logger.getLogger(RwlDBDialectUtil.class);
	
	private RwlDBDialectUtil() {
		_init();
	}
	private void _init() {
		
	}
	public static RwlDBDialectUtil getInstance() {
		if (instance == null) {
			synchronized (RwlDBDialectUtil.class) {
				if (instance == null) {
					instance = new RwlDBDialectUtil();
				}
			}
		}
		return instance;
	}
	
	/**
	 * 获取分页的SQL语句
	 * @param _sql 基础语句
	 * @param hasOffset 是否限定数量(一般都是true)
	 * @param _start 起始数
	 * @param _limit 限定的数量
	 * @return 返回设定好分页的SQL语句
	 * @throws RwlDialectException
	 */
	public String getSqlLimit(String _sql, boolean hasOffset, int _start, int _limit) throws RwlDialectException {
		
	    if(log.isDebugEnabled()) {
	        log.debug(">>RwlDBDialect-start:" + _sql);
	    }
	    
		//add by polarbear , 2009-6-4, 不支持分页的方式
		if(SQL_SUPPORT_PAGING == 0) {
			throw new RwlDialectException("Not Support Paging!");
		}
		
		/**
		 * #############Oracle/kingbase分页方式###############
		 */
		if(currentDialect == dbtype.oracle || currentDialect == dbtype.kingbase) {
			_sql = _sql.trim();
			boolean isForUpdate = false;
			if ( _sql.toLowerCase().endsWith(" for update") ) {
				_sql = _sql.substring( 0, _sql.length()-11 );
				isForUpdate = true;
			}
			
			StringBuffer pagingSelect = new StringBuffer(_sql.length()+100 );
			if (hasOffset) {
				pagingSelect.append("select * from ( select row_.*, rownum rownum_ from ( ");
			}
			else {
				pagingSelect.append("select * from ( ");
			}
			pagingSelect.append(_sql);
			if (hasOffset) {
				pagingSelect.append(" ) row_ where rownum <= " + (_start + _limit) + ") where rownum_ > " + _start);
			}
			else {
				pagingSelect.append(" ) where rownum <= " + (_start + _limit));
			}
			if (isForUpdate) pagingSelect.append(" for update");
			
			//结束
			if(log.isDebugEnabled()) {
			    log.debug(">>RwlDBDialect-end(oracle):" + pagingSelect.toString());
			}
			
			
			return pagingSelect.toString();
		} 
		/**
		 * ############## HSQL方式 ###############
		 */
		else if(currentDialect == dbtype.hsql) {
			return new StringBuffer( _sql.length() + 10 )
			.append( _sql )
			.insert( _sql.toLowerCase().indexOf( "select" ) + 6, hasOffset ? " limit " + _start + " " + _limit : " top "+_start)
			.toString();
		}
		
		//缺省使用的是mysql的分页方式
		else if(currentDialect == dbtype.mysql) {
			String result = new StringBuffer(_sql.length()+20 )
			.append(_sql)
			.append( hasOffset ? " limit " + _start + ", " + _limit : " limit " + _start)
			.toString();
			
			//结束
            if(log.isDebugEnabled()) {
                log.debug(">>RwlDBDialect-end(mysql):" + result);
            }
            
            return result;
		} 
		
		/**
		 * ############## SQLServer分页方式 ################
		 */
		else if(currentDialect == dbtype.sqlserver) {
			
			if(!SQL_SERVER_VERSION.equals("2005")) {
				throw new RwlDialectException("Not Support Paging!");
			}
			
			StringBuffer pagingBuilder = new StringBuffer();  
			String orderby = getOrderByPart(_sql);  
			String distinctStr = "";  
			String loweredString = _sql.toLowerCase();  
			String sqlPartString = _sql.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("SELECT * FROM (")   
			.append("SELECT ")  
			.append(distinctStr)  
			.append(" TOP 100 PERCENT ROW_NUMBER() OVER (") //使用TOP 100 PERCENT可以提高性能  
			.append(orderby)  
			.append(") AS __hibernate_row_nr__, ")  
			.append(pagingBuilder)  
			.append(") as ucstarTempTable WHERE __hibernate_row_nr__ >")
			.append(_start)  
			.append(" AND __hibernate_row_nr__ <=")  
			.append(_start + _limit)  
			.append(" ORDER BY __hibernate_row_nr__");  
			
			//结束
            if(log.isDebugEnabled()) {
                log.debug(">>RwlDBDialect-end(sqlserver):" + result.toString());
            }
			
			return result.toString();
		}
		
		//IBM的DB2的分页方式
		else if(currentDialect == dbtype.db2) {
		    String resultSql = db2Dialect.getLimitString(_sql, _start, _limit);
			//结束
            if(log.isDebugEnabled()) {
                log.debug(">>RwlDBDialect-end(db2):" + resultSql);
            }
			return resultSql;
		}
		
		/**
		 * ############# 不支持的分页 ##############
		 */
		else {
			log.error("No support Paging!");
			return _sql;
		}
	}
	
	
	/**
	 * SQLServer的处理
	 * polarbear 2009-5-9 
	 * @param sql
	 * @return
	 */
	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 "";
		}
	}
	
	private static boolean hasDistinct(String sql) {
		return sql.toLowerCase().indexOf("select distinct")>=0;
	}
	
	private static 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();
	}
	
	/**
	 * 专门针对DB2处理的SQL代码
	 * polarbear 2009-8-31 
	 * @param _sql
	 * @return
	 */
	private static String genReturnField(String _sql) {
		int startOfSelect = _sql.toLowerCase().indexOf("select");
		int startOfFrom = _sql.toLowerCase().indexOf("from");
		int startOfWhere = _sql.toLowerCase().indexOf("where");
		int startOfOrderBy = _sql.toLowerCase().indexOf("order by");
		int startOfGroupBy = _sql.toLowerCase().indexOf("group by");
		
		String returnField = "";
		if(startOfFrom >= 0) {
			String fromTableStr = "";
			if(startOfWhere >= 0) {
				fromTableStr = _sql.substring(startOfFrom + "from".length(), startOfWhere);
			} else if(startOfOrderBy >= 0) {
				fromTableStr = _sql.substring(startOfFrom + "from".length(), startOfOrderBy);
			} else if(startOfGroupBy >= 0) {
				fromTableStr = _sql.substring(startOfFrom + "from".length(), startOfGroupBy);
			} else {
				fromTableStr = _sql.substring(startOfFrom + "from".length());
			}
			if(fromTableStr.length() > 0) {
				String[] fromTableStrArr = fromTableStr.split(",");
				for(String fromTable : fromTableStrArr) {
					if(fromTable != null && fromTable.length() > 0) {
						String fromTable2 = fromTable.trim();
						int startTableName = fromTable2.indexOf(" ");
						String tableNick = "";
						if(startTableName > 0) {
							tableNick = fromTable2.substring(startTableName);
						} else {
							tableNick = fromTable2;
						}
						tableNick = tableNick.trim();
						returnField += tableNick + ".*" + ",";
					}
					
				}
			}
			if(returnField.length() > 0) {
				returnField = returnField.substring(0, returnField.length() - 1);
			}
			
		}
		
		if(startOfSelect >= 0 && startOfFrom >= 0) {
			String selectFromStr = _sql.substring(startOfSelect + "select".length(), startOfFrom);
			String fromEndStr = _sql.substring(startOfFrom + "from".length(), _sql.length());
			selectFromStr = selectFromStr.trim();
			if(selectFromStr.length() > 0) {
				String selectField = "";
				String[] tempSqlArr = selectFromStr.split(",");
				for(String tempStr : tempSqlArr) {
					if(tempStr != null && tempStr.length() > 0) {
						if(tempStr.equalsIgnoreCase("*")) {
							selectField += returnField + ",";
						} else {
							selectField += tempStr + ",";
						}
					}
				}
				if(selectField.length() > 0) {
					selectField = selectField.substring(0,selectField.length() - 1);
					return "select" + " " + selectField + " from " + fromEndStr; 
				}
			}
		}
		
		return _sql;
	}
	
	/**
	 * 数据库类型
	 * @author polarrwl
	 */
	public enum dbtype {
		oracle,
		mysql,
		sqlserver,
		db2,
		hsql, 
		kingbase
	}
	
	/**
	 * 根据驱动得到对应的数据库类型
	 * @param _driver
	 * @return
	 */
	public static dbtype getDbtypeByDriver(String _driver) {
		if(_driver != null) {
			if(_driver.toLowerCase().indexOf("oracle") >= 0) {
				return dbtype.oracle;
			} else if(_driver.toLowerCase().indexOf("kingbase") >= 0) {
				return dbtype.kingbase;
			} else if(_driver.toLowerCase().indexOf("mysql") >= 0) {
				return dbtype.mysql;
			} else if(_driver.toLowerCase().indexOf("sqlserver") >= 0) {
				return dbtype.sqlserver;
			} else if(_driver.toLowerCase().indexOf("hsql") >= 0) {
				return dbtype.hsql;
			} else if(_driver.toLowerCase().indexOf("db2") >= 0) {
				return dbtype.db2;
			}
		}
		return null;
	}
	
	/**
	 * 设定当前的数据库类型
	 * @param _dbtype
	 */
	public void setCurrentDialect(dbtype _dbtype) {
		
		log.info("设定当前的数据库类型(currentDialect):" + _dbtype);
		
		if(_dbtype != null) {
			currentDialect = _dbtype;
		}
	}
	
	public static void main(String[] args) {
		System.out.println(genReturnField("select * from user order by type"));
	}
}