package org.rwl.utils.db;
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;
}
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) {
.append( _sql )
.insert( _sql.toLowerCase().indexOf( "select" ) + 6, hasOffset ? " limit " + _start + " " + _limit : " top "+_start)
.toString();
}
//缺省使用的是mysql的分页方式
else if(currentDialect == dbtype.mysql) {
.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!");
}
String orderby
= getOrderByPart
(_sql
);
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";
}
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
*/
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;
}
.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
*/
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");
if(startOfFrom >= 0) {
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(" ");
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[] 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"));
}
}
//java/6244