| 注册
请输入搜索内容

热门搜索

Java Linux MySQL PHP JavaScript Hibernate jQuery Nginx
pc688
9年前发布

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