/**
* 通過(guò)數(shù)據(jù)庫(kù)類型處理翻頁(yè)查詢語(yǔ)句
* 注釋：如果框架當(dāng)前不支持此種數(shù)據(jù)庫(kù)分頁(yè)查詢，則返回一個(gè)空值字符串。
* * @param strSql 待執(zhí)行SQL語(yǔ)句
* @param start 開始行數(shù)
* @param pageCount 每頁(yè)行數(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ù)庫(kù)采用limit關(guān)鍵字進(jìn)行分頁(yè)
int startItem = start -1;if(startItem<0)startItem = 0;
retSql = strSql + " limit " + startItem + "," + pageCount;
}
else if(dbType.equals("oracle"))
{
//ORACLE數(shù)據(jù)庫(kù)采用ROWNUM控制分頁(yè)
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ù)庫(kù)采用ROWNUMBER() OVER()函數(shù)進(jìn)行分頁(yè)
//其中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ù)進(jìn)行分頁(yè)
//其中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"))
{
//通過(guò)OFFSET等關(guān)鍵字進(jìn)行翻頁(yè)（尚未經(jīng)過(guò)測(cè)試）
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;
}