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

标签： Mysql 代码 数据库

版权申明：本站文章部分自网络，如有侵权，请联系：west999com@outlook.com
特别注意：本站所有转载文章言论不代表本站观点！
本站所提供的图片等素材，版权归原作者所有，如需使用，请与原作者联系。