- 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"));
- }
- }
来源: http://www.phpxs.com/code/1001558/