protected String getOverClause() { StringBuilder sql = new StringBuilder(); sql.append(" ORDER BY ").append(buildSortClause(this)); return sql.toString(); }
@Override public String generateJumpToItemQuery(int itemIndex, int pageSize) { int page = itemIndex / pageSize; int lastRowNum = (page * pageSize); if (lastRowNum <= 0) { lastRowNum = 1; } StringBuilder sql = new StringBuilder(); sql.append("SELECT "); buildSortKeySelect(sql, getSortKeysReplaced(extractTableAlias())); sql.append(" FROM ( "); sql.append("SELECT "); buildSortKeySelect(sql); sql.append(", ROW_NUMBER() OVER (").append(getOverClause()); sql.append(") AS ROW_NUMBER"); sql.append(getOverSubstituteClauseStart()); sql.append(" FROM ").append(getFromClause()); sql.append(getWhereClause() == null ? "" : " WHERE " + getWhereClause()); sql.append(getGroupClause() == null ? "" : " GROUP BY " + getGroupClause()); sql.append(getOverSubstituteClauseEnd()); sql.append(") ").append(getSubQueryAlias()).append("WHERE ").append(extractTableAlias()).append( "ROW_NUMBER = ").append(lastRowNum); sql.append(" ORDER BY ").append(SqlPagingQueryUtils.buildSortClause(getSortKeysReplaced(extractTableAlias()))); return sql.toString(); }
@Override public String generateFirstPageQuery(int pageSize) { StringBuilder sql = new StringBuilder(); sql.append("SELECT * FROM ( "); sql.append("SELECT ").append(StringUtils.hasText(getOrderedQueryAlias()) ? getOrderedQueryAlias() + ".*, " : "*, "); sql.append("ROW_NUMBER() OVER (").append(getOverClause()); sql.append(") AS ROW_NUMBER"); sql.append(getOverSubstituteClauseStart()); sql.append(" FROM ").append(getFromClause()).append( getWhereClause() == null ? "" : " WHERE " + getWhereClause()); sql.append(getGroupClause() == null ? "" : " GROUP BY " + getGroupClause()); sql.append(getOverSubstituteClauseEnd()); sql.append(") ").append(getSubQueryAlias()).append("WHERE ").append(extractTableAlias()).append( "ROW_NUMBER <= ").append(pageSize); sql.append(" ORDER BY ").append(SqlPagingQueryUtils.buildSortClause(this)); return sql.toString(); }
private void buildSortKeySelect(StringBuilder sql, Map<String, Order> sortKeys) { String prefix = ""; if (sortKeys == null) { sortKeys = getSortKeys(); } for (Map.Entry<String, Order> sortKey : sortKeys.entrySet()) { sql.append(prefix); prefix = ", "; sql.append(sortKey.getKey()); } }
private void buildSortKeySelect(StringBuilder sql) { buildSortKeySelect(sql, null); }
private Map<String, Order> getSortKeysReplaced(Object qualifierReplacement) { final String newQualifier = "" + qualifierReplacement; final Map<String, Order> sortKeys = new LinkedHashMap<>(); for (Map.Entry<String, Order> sortKey : getSortKeys().entrySet()) { sortKeys.put(sortKey.getKey().replaceFirst("^.*\\.", newQualifier), sortKey.getValue()); } return sortKeys; }
private void buildSortKeySelect(StringBuilder sql) { buildSortKeySelect(sql, null); }
@Override public String generateRemainingPagesQuery(int pageSize) { StringBuilder sql = new StringBuilder(); sql.append("SELECT * FROM ( "); sql.append("SELECT ").append(StringUtils.hasText(getOrderedQueryAlias()) ? getOrderedQueryAlias() + ".*, " : "*, "); sql.append("ROW_NUMBER() OVER (").append(getOverClause()); sql.append(") AS ROW_NUMBER"); sql.append(getOverSubstituteClauseStart()); sql.append(" FROM ").append(getFromClause()); if (getWhereClause() != null) { sql.append(" WHERE "); sql.append(getWhereClause()); } sql.append(getGroupClause() == null ? "" : " GROUP BY " + getGroupClause()); sql.append(getOverSubstituteClauseEnd()); sql.append(") ").append(getSubQueryAlias()).append("WHERE ").append(extractTableAlias()).append( "ROW_NUMBER <= ").append(pageSize); sql.append(" AND "); SqlPagingQueryUtils.buildSortConditions(this, sql); sql.append(" ORDER BY ").append(SqlPagingQueryUtils.buildSortClause(this)); return sql.toString(); }
@Override public String generateJumpToItemQuery(int itemIndex, int pageSize) { int page = itemIndex / pageSize; int lastRowNum = (page * pageSize); if (lastRowNum <= 0) { lastRowNum = 1; } StringBuilder sql = new StringBuilder(); sql.append("SELECT "); buildSortKeySelect(sql, getSortKeysReplaced(extractTableAlias())); sql.append(" FROM ( "); sql.append("SELECT "); buildSortKeySelect(sql); sql.append(", ROW_NUMBER() OVER (").append(getOverClause()); sql.append(") AS ROW_NUMBER"); sql.append(getOverSubstituteClauseStart()); sql.append(" FROM ").append(getFromClause()); sql.append(getWhereClause() == null ? "" : " WHERE " + getWhereClause()); sql.append(getGroupClause() == null ? "" : " GROUP BY " + getGroupClause()); sql.append(getOverSubstituteClauseEnd()); sql.append(") ").append(getSubQueryAlias()).append("WHERE ").append(extractTableAlias()).append( "ROW_NUMBER = ").append(lastRowNum); sql.append(" ORDER BY ").append(SqlPagingQueryUtils.buildSortClause(getSortKeysReplaced(extractTableAlias()))); return sql.toString(); }
private void buildSortKeySelect(StringBuilder sql, Map<String, Order> sortKeys) { String prefix = ""; if (sortKeys == null) { sortKeys = getSortKeys(); } for (Map.Entry<String, Order> sortKey : sortKeys.entrySet()) { sql.append(prefix); prefix = ", "; sql.append(sortKey.getKey()); } }
protected String getOverClause() { StringBuilder sql = new StringBuilder(); sql.append(" ORDER BY ").append(buildSortClause(this)); return sql.toString(); }
@Override public String generateFirstPageQuery(int pageSize) { StringBuilder sql = new StringBuilder(); sql.append("SELECT * FROM ( "); sql.append("SELECT ").append(StringUtils.hasText(getOrderedQueryAlias()) ? getOrderedQueryAlias() + ".*, " : "*, "); sql.append("ROW_NUMBER() OVER (").append(getOverClause()); sql.append(") AS ROW_NUMBER"); sql.append(getOverSubstituteClauseStart()); sql.append(" FROM ").append(getFromClause()).append( getWhereClause() == null ? "" : " WHERE " + getWhereClause()); sql.append(getGroupClause() == null ? "" : " GROUP BY " + getGroupClause()); sql.append(getOverSubstituteClauseEnd()); sql.append(") ").append(getSubQueryAlias()).append("WHERE ").append(extractTableAlias()).append( "ROW_NUMBER <= ").append(pageSize); sql.append(" ORDER BY ").append(SqlPagingQueryUtils.buildSortClause(this)); return sql.toString(); }
private Map<String, Order> getSortKeysReplaced(Object qualifierReplacement) { final String newQualifier = "" + qualifierReplacement; final Map<String, Order> sortKeys = new LinkedHashMap<String, Order>(); for (Map.Entry<String, Order> sortKey : getSortKeys().entrySet()) { sortKeys.put(sortKey.getKey().replaceFirst("^.*\\.", newQualifier), sortKey.getValue()); } return sortKeys; }
@Override public String generateRemainingPagesQuery(int pageSize) { StringBuilder sql = new StringBuilder(); sql.append("SELECT * FROM ( "); sql.append("SELECT ").append(StringUtils.hasText(getOrderedQueryAlias()) ? getOrderedQueryAlias() + ".*, " : "*, "); sql.append("ROW_NUMBER() OVER (").append(getOverClause()); sql.append(") AS ROW_NUMBER"); sql.append(getOverSubstituteClauseStart()); sql.append(" FROM ").append(getFromClause()); if (getWhereClause() != null) { sql.append(" WHERE "); sql.append(getWhereClause()); } sql.append(getGroupClause() == null ? "" : " GROUP BY " + getGroupClause()); sql.append(getOverSubstituteClauseEnd()); sql.append(") ").append(getSubQueryAlias()).append("WHERE ").append(extractTableAlias()).append( "ROW_NUMBER <= ").append(pageSize); sql.append(" AND "); SqlPagingQueryUtils.buildSortConditions(this, sql); sql.append(" ORDER BY ").append(SqlPagingQueryUtils.buildSortClause(this)); return sql.toString(); }