一个针对不同数据库的分页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")); 
    } 
}