LimitSql
来源:互联网 发布:海贼王的主题知乎 编辑:程序博客网 时间:2024/05/29 18:57
public class LimitSql {
/**
* 根据数据库类型组装分页sql
*
* @param dbName
* 数据库类型
* @param column
* 要查询的列
* @param tableName
* 表名
* @param offset
* @param limit
* @return
*/
public static String getLimitString(String dbName, String column, String tableName, long offset, long limit) {
String limitString = null;
if (dbName.toLowerCase().indexOf("mysql") != -1) {
limitString = getMysqlLimitString(column, tableName, offset, limit);
}
if (dbName.toLowerCase().indexOf("oracle") != -1) {
limitString = getOracleLimitString(column, tableName, offset, limit);
}
return limitString;
}
private static String getMysqlLimitString(String column, String tableName, long offset, long limit) {
StringBuffer sb = new StringBuffer();
sb.append("select ").append(column).append(" from ").append(tableName);
if (offset > 0) {
sb.append(" limit ").append(offset).append(',').append(limit);
} else {
sb.append(" limit ").append(limit);
}
return sb.toString();
}
private static String getOracleLimitString(String column, String tableName, long offset, long limit) {
StringBuffer sb = new StringBuffer();
if (offset > 0) {
sb.append("SELECT ").append(column).append(" FROM (")
.append(" SELECT A.*, ROWNUM RN FROM ( SELECT * FROM ").append(tableName)
.append(" ) A where ROWNUM <= ").append(offset + limit).append(") where RN > ").append(offset);
} else {
sb.append("SELECT ").append(column).append(" FROM ( select * FROM ").append(tableName)
.append(" ) WHERE ROWNUM <= ").append(limit);
}
return sb.toString();
}
}