Esempio n. 1
0
  /**
   * 分页语句 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;
 }