一個針對不同數據庫的分頁java代碼

pc688 9年前發布 | 2K 次閱讀 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"));
}

}</pre>
本文由用戶 pc688 自行上傳分享，僅供網友學習交流。所有權歸原作者，若您的權利被侵害，請聯系管理員。
轉載本站原創文章，請注明出處，并保留原始鏈接、圖片水印。
本站是一個以用戶分享為主的開源技術平臺，歡迎各類分享！
本文地址：http://www.baiduhome.net/code/view/1430808274226
Java
一個針對不同數據庫的分頁java代碼

相關代碼

相關文檔

相關經驗

目錄
