JAVA 后台拼接分页查询SQL
/**
* 拼接分页查询sql<br/>
* ORACLE,SQLSERVER,DB2,DM通用
*
* @param field
* @param from
* @param page
* @param size
* @param orderBy
* @return
*/
public static String joinPageQuerySql(final String field, final String from, final String page, final String size,
final String orderBy) {
int pageNum = 1;// 页数从1开始计数
int sizeNum = SystemConstant.PAGE_SIZE;
if (StringUtils.isNotBlank(page)) {
pageNum = Integer.parseInt(page);
if (pageNum < 1) {
pageNum = 1;
}
}
if (StringUtils.isNotBlank(size)) {
sizeNum = Integer.parseInt(size);
if (sizeNum < 0) {
sizeNum = SystemConstant.PAGE_SIZE;
}
}
StringBuilder buff = new StringBuilder("SELECT ");
buff.append(field);
buff.append(" FROM (SELECT ROW_NUMBER() OVER (ORDER BY ");
buff.append(StringUtils.isNotBlank(orderBy) ? orderBy : field);
buff.append(") AS RN,PAGE1.* FROM(");
buff.append(from);
buff.append(") PAGE1 ) PAGE2 WHERE RN <= ");
buff.append(pageNum * sizeNum);
buff.append(" AND RN > ");
buff.append((pageNum - 1) * sizeNum);
return buff.toString();
}
/**
* 示例
* @param args
*/
public static void main(String[] args) {
String page = "1";
String size = "15";
String field = "USERID,USERNAME,SEX,EMAIL";
String from = "SELECT USERID, USERNAME, SEX, EMAIL FROM SYS_USER ";
String orderBy = "USERNAME";
String sql = joinPageQuerySql(field, from.toString(), page, size, orderBy);
System.out.println(sql);
}