/**
* 分页语句 SELECT * FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY OrderDate) RowNum, * FROM Orders WHERE
* OrderDate >= '1980-01-01' ) RowConstrainedResult WHERE RowNum >= 1 AND RowNum < 20 ORDER BY
* RowNum
*/
@Override
public StatementWrapper pageStatement(
String orderCol, String direction, int start, int limit, String strSQL, Object... values) {
int indexFrom = StringUtils.indexOfIgnoreCase(strSQL, " FROM ");
if (orderCol.contains(".")) {
orderCol = orderCol.replaceAll("\\.", "\".\"");
}
orderCol = orderCol.toUpperCase();
StringBuffer sbSQL =
new StringBuffer("SELECT * FROM (")
.append(strSQL.substring(0, indexFrom))
.append(", ROW_NUMBER() OVER (ORDER BY \"")
.append(orderCol)
.append("\" ")
.append(direction)
.append(") ")
.append(COLUMN_ROW_NUMBER)
.append(strSQL.substring(indexFrom))
.append(") WHERE ")
.append(COLUMN_ROW_NUMBER)
.append(" >= ? ");
if (limit > 0) {
sbSQL.append(" AND ").append(COLUMN_ROW_NUMBER).append(" <= ? ");
values = ArrayUtils.addAll(values, new Object[] {start, start + limit});
} else {
values = ArrayUtils.add(values, start);
}
return new StatementWrapper(sbSQL.toString(), values);
}
private String where(String select) {
int i = StringUtils.indexOfIgnoreCase(select, " WHERE ");
if (i < 0) {
return null;
}
int j = StringUtils.indexOfIgnoreCase(select, " GROUP BY ");
if (j < 0) {
j = StringUtils.indexOfIgnoreCase(select, " ORDER BY ");
}
if (j < 0) {
j = select.length();
}
if (j < i) {
return null;
}
String substring = select.substring(i, j);
return substring;
}