/**
* 通过数据库类型处理翻页查询语句
* 注释：如果框架当前不支持此种数据库分页查询，则返回一个空值字符串。
* * @param strSql 待执行SQL语句
* @param start 开始行数
* @param pageCount 每页行数
* */
private static String dealSqlByDBType(String dbType,String strSql,int start,int pageCount)
{
if(dbType==null)return "";
String retSql = "";
if(dbType.equals("mysql"))
{
//MYSQL数据库采用limit关键字进行分页
int startItem = start -1;if(startItem<0)startItem = 0;
retSql = strSql + " limit " + startItem + "," + pageCount;
}
else if(dbType.equals("oracle"))
{
//ORACLE数据库采用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数据库采用ROWNUMBER() OVER()函数进行分页
//其中OVER()函数中必须包含排序字段，此处用“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()函数进行分页
//其中OVER()函数中必须包含排序字段，此处用“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等关键字进行翻页（尚未经过测试）
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;
}