一个针对不同数据库的分页java代码详解编程语言

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")); 
} 
}