/**
* 通過數(shù)據(jù)庫類型處理翻頁查詢語句
* 注釋：如果框架當前不支持此種數(shù)據(jù)庫分頁查詢，則返回一個空值字符串。
* * @param strSql 待執(zhí)行SQL語句
* @param start 開始行數(shù)
* @param pageCount 每頁行數(shù)
* */
private static String dealSqlByDBType(String dbType,String strSql,int start,int pageCount)
{
if(dbType==null)return "";
String retSql = "";
if(dbType.equals("mysql"))
{
//MYSQL數(shù)據(jù)庫采用limit關鍵字進行分頁
int startItem = start -1;if(startItem<0)startItem = 0;
retSql = strSql + " limit " + startItem + "," + pageCount;
}
else if(dbType.equals("oracle"))
{
//ORACLE數(shù)據(jù)庫采用ROWNUM控制分頁
int startItem = start;
if(startItem<=0){startItem = 0;}
int endItem = startItem + pageCount;
retSql = "SELECT * FROM ("
+ "SELECT A.*,ROWNUM RN FROM("
+ strSql
+ ") A WHERE ROWNUM<="+ endItem +") WHERE RN >"+startItem;
}
else if(dbType.equals("db2"))
{
//DB2數(shù)據(jù)庫采用ROWNUMBER() OVER()函數(shù)進行分頁
//其中OVER()函數(shù)中必須包含排序字段，此處用“1”代替
int startItem = start -1;if(startItem<0)startItem = 0;
int endItem = startItem + pageCount;
retSql = "SELECT * FROM ("
+ "SELECT B.*, ROWNUMBER() OVER(1) AS RN FROM ("
+ strSql
+ ") AS B )AS A WHERE A.RN <= "+ endItem +" AND A.RN >= "+ startItem;
}
else if(dbType.equals("sqlserver2005"))
{
//SQLSERVER2005采用ROW_NUMBER()函數(shù)進行分頁
//其中OVER()函數(shù)中必須包含排序字段，此處用“1”代替
int startItem = start -1;if(startItem<0)startItem = 0;
int endItem = startItem + pageCount;
retSql = "SELECT * FROM ("
+ "SELECT B.*, ROW_NUMBER() OVER(1) RN FROM ("
+ strSql
+ ") B )A WHERE A.RN <= "+ endItem +" AND A.RN >= "+ startItem;
}
else if(dbType.equals("derby"))
{
//通過OFFSET等關鍵字進行翻頁（尚未經(jīng)過測試）
int startItem = start -1;if(startItem<0)startItem = 0;
//int endItem = startItem + pageCount;
retSql = strSql
+ " OFFSET "+ startItem +" ROWS"
+ " FETCH NEXT "+ pageCount +" ROWS ONLY";
}
return retSql;
}