一个针对不同数据库的分页java代码，针对不同分页java,import org.a
分享于 点击 36116 次 点评：104
一个针对不同数据库的分页java代码，针对不同分页java,import org.a
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")); }}
用户点评