/** * 分页语句 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; }