/** * Generates ORDER BY attributes based on the sort keys. * * @param provider * @return a String that can be appended to an ORDER BY clause. */ private String buildSortClause(AbstractSqlPagingQueryProvider provider) { return SqlPagingQueryUtils.buildSortClause(provider.getSortKeysWithoutAliases()); }
/** * Generates ORDER BY attributes based on the sort keys. * * @param provider the {@link AbstractSqlPagingQueryProvider} to be used for * used for pagination. * @return a String that can be appended to an ORDER BY clause. */ public static String buildSortClause(AbstractSqlPagingQueryProvider provider) { return buildSortClause(provider.getSortKeys()); }
public static String generateRowNumSqlQueryWithNesting(AbstractSqlPagingQueryProvider provider, String innerSelectClause, String outerSelectClause, boolean remainingPageQuery, String rowNumClause) { StringBuilder sql = new StringBuilder(); sql.append("SELECT ").append(outerSelectClause).append(" FROM (SELECT ").append(outerSelectClause) .append(", ").append(StringUtils.hasText(provider.getGroupClause()) ? "MIN(ROWNUM) as TMP_ROW_NUM" : "ROWNUM as TMP_ROW_NUM"); sql.append(" FROM (SELECT ").append(innerSelectClause).append(" FROM ").append(provider.getFromClause()); buildWhereClause(provider, remainingPageQuery, sql); buildGroupByClause(provider, sql); sql.append(" ORDER BY ").append(buildSortClause(provider)); sql.append(")) WHERE ").append(rowNumClause); return sql.toString(); }
/** * Generate SQL query string using a TOP clause * * @param provider {@link AbstractSqlPagingQueryProvider} providing the * implementation specifics * @param topClause the implementation specific top clause to be used * @return the generated query */ public static String generateTopJumpToQuery(AbstractSqlPagingQueryProvider provider, String topClause) { StringBuilder sql = new StringBuilder(); sql.append("SELECT ").append(topClause).append(" ").append(buildSortKeySelect(provider)); sql.append(" FROM ").append(provider.getFromClause()); sql.append(provider.getWhereClause() == null ? "" : " WHERE " + provider.getWhereClause()); buildGroupByClause(provider, sql); sql.append(" ORDER BY ").append(buildSortClause(provider)); return sql.toString(); }
/** * Generate SQL query string using a TOP clause * * @param provider {@link AbstractSqlPagingQueryProvider} providing the * implementation specifics * @param remainingPageQuery is this query for the remaining pages (true) as * opposed to the first page (false) * @param topClause the implementation specific top clause to be used * @return the generated query */ public static String generateTopSqlQuery(AbstractSqlPagingQueryProvider provider, boolean remainingPageQuery, String topClause) { StringBuilder sql = new StringBuilder(); sql.append("SELECT ").append(topClause).append(" ").append(provider.getSelectClause()); sql.append(" FROM ").append(provider.getFromClause()); buildWhereClause(provider, remainingPageQuery, sql); buildGroupByClause(provider, sql); sql.append(" ORDER BY ").append(buildSortClause(provider)); return sql.toString(); }
/** * Generate SQL query string using a LIMIT clause * * @param provider {@link AbstractSqlPagingQueryProvider} providing the * implementation specifics * @param remainingPageQuery is this query for the remaining pages (true) as * opposed to the first page (false) * @param limitClause the implementation specific limit clause to be used * @return the generated query */ public static String generateLimitSqlQuery(AbstractSqlPagingQueryProvider provider, boolean remainingPageQuery, String limitClause) { StringBuilder sql = new StringBuilder(); sql.append("SELECT ").append(provider.getSelectClause()); sql.append(" FROM ").append(provider.getFromClause()); buildWhereClause(provider, remainingPageQuery, sql); buildGroupByClause(provider, sql); sql.append(" ORDER BY ").append(buildSortClause(provider)); sql.append(" " + limitClause); return sql.toString(); }
/** * Generate SQL query string using a LIMIT clause * * @param provider {@link AbstractSqlPagingQueryProvider} providing the * implementation specifics * @param limitClause the implementation specific top clause to be used * @return the generated query */ public static String generateLimitJumpToQuery(AbstractSqlPagingQueryProvider provider, String limitClause) { StringBuilder sql = new StringBuilder(); sql.append("SELECT ").append(buildSortKeySelect(provider)); sql.append(" FROM ").append(provider.getFromClause()); sql.append(provider.getWhereClause() == null ? "" : " WHERE " + provider.getWhereClause()); buildGroupByClause(provider, sql); sql.append(" ORDER BY ").append(buildSortClause(provider)); sql.append(" " + limitClause); return sql.toString(); }
/** * Generate SQL query string using a ROW_NUM condition * * @param provider {@link AbstractSqlPagingQueryProvider} providing the * implementation specifics * @param selectClause {@link String} containing the select portion of the query. * @param remainingPageQuery is this query for the remaining pages (true) as * opposed to the first page (false) * @param rowNumClause the implementation specific row num clause to be used * @return the generated query */ public static String generateRowNumSqlQuery(AbstractSqlPagingQueryProvider provider, String selectClause, boolean remainingPageQuery, String rowNumClause) { StringBuilder sql = new StringBuilder(); sql.append("SELECT * FROM (SELECT ").append(selectClause); sql.append(" FROM ").append(provider.getFromClause()); sql.append(provider.getWhereClause() == null ? "" : " WHERE " + provider.getWhereClause()); buildGroupByClause(provider, sql); sql.append(" ORDER BY ").append(buildSortClause(provider)); sql.append(") WHERE ").append(rowNumClause); if(remainingPageQuery) { sql.append(" AND "); buildSortConditions(provider, sql); } 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(); }
/** * Generate SQL query string using a TOP clause * * @param provider {@link AbstractSqlPagingQueryProvider} providing the * implementation specifics * @param remainingPageQuery is this query for the remaining pages (true) as * opposed to the first page (false) * @param topClause the implementation specific top clause to be used * @return the generated query */ public static String generateGroupedTopSqlQuery(AbstractSqlPagingQueryProvider provider, boolean remainingPageQuery, String topClause) { StringBuilder sql = new StringBuilder(); sql.append("SELECT ").append(topClause).append(" * FROM ("); sql.append("SELECT ").append(provider.getSelectClause()); sql.append(" FROM ").append(provider.getFromClause()); sql.append(provider.getWhereClause() == null ? "" : " WHERE " + provider.getWhereClause()); buildGroupByClause(provider, sql); sql.append(") AS MAIN_QRY "); sql.append("WHERE "); buildSortConditions(provider, sql); sql.append(" ORDER BY ").append(buildSortClause(provider)); return sql.toString(); }
/** * Generate SQL query string using a LIMIT clause * * @param provider {@link AbstractSqlPagingQueryProvider} providing the * implementation specifics * @param remainingPageQuery is this query for the remaining pages (true) as * opposed to the first page (false) * @param limitClause the implementation specific limit clause to be used * @return the generated query */ public static String generateLimitGroupedSqlQuery(AbstractSqlPagingQueryProvider provider, boolean remainingPageQuery, String limitClause) { StringBuilder sql = new StringBuilder(); sql.append("SELECT * "); sql.append(" FROM ("); sql.append("SELECT ").append(provider.getSelectClause()); sql.append(" FROM ").append(provider.getFromClause()); sql.append(provider.getWhereClause() == null ? "" : " WHERE " + provider.getWhereClause()); buildGroupByClause(provider, sql); sql.append(") AS MAIN_QRY "); sql.append("WHERE "); buildSortConditions(provider, sql); sql.append(" ORDER BY ").append(buildSortClause(provider)); sql.append(" " + limitClause); return sql.toString(); }
@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(); }
/** * Generates ORDER BY attributes based on the sort keys. * * @param provider the {@link AbstractSqlPagingQueryProvider} to be used for * used for pagination. * @return a String that can be appended to an ORDER BY clause. */ public static String buildSortClause(AbstractSqlPagingQueryProvider provider) { return buildSortClause(provider.getSortKeys()); }
/** * Generates ORDER BY attributes based on the sort keys. * * @param provider * @return a String that can be appended to an ORDER BY clause. */ private String buildSortClause(AbstractSqlPagingQueryProvider provider) { return SqlPagingQueryUtils.buildSortClause(provider.getSortKeysWithoutAliases()); }
public static String generateRowNumSqlQueryWithNesting(AbstractSqlPagingQueryProvider provider, String innerSelectClause, String outerSelectClause, boolean remainingPageQuery, String rowNumClause) { StringBuilder sql = new StringBuilder(); sql.append("SELECT ").append(outerSelectClause).append(" FROM (SELECT ").append(outerSelectClause) .append(", ").append(StringUtils.hasText(provider.getGroupClause()) ? "MIN(ROWNUM) as TMP_ROW_NUM" : "ROWNUM as TMP_ROW_NUM"); sql.append(" FROM (SELECT ").append(innerSelectClause).append(" FROM ").append(provider.getFromClause()); buildWhereClause(provider, remainingPageQuery, sql); buildGroupByClause(provider, sql); sql.append(" ORDER BY ").append(buildSortClause(provider)); sql.append(")) WHERE ").append(rowNumClause); return sql.toString(); }
/** * Generate SQL query string using a TOP clause * * @param provider {@link AbstractSqlPagingQueryProvider} providing the * implementation specifics * @param topClause the implementation specific top clause to be used * @return the generated query */ public static String generateTopJumpToQuery(AbstractSqlPagingQueryProvider provider, String topClause) { StringBuilder sql = new StringBuilder(); sql.append("SELECT ").append(topClause).append(" ").append(buildSortKeySelect(provider)); sql.append(" FROM ").append(provider.getFromClause()); sql.append(provider.getWhereClause() == null ? "" : " WHERE " + provider.getWhereClause()); buildGroupByClause(provider, sql); sql.append(" ORDER BY ").append(buildSortClause(provider)); return sql.toString(); }
/** * Generate SQL query string using a LIMIT clause * * @param provider {@link AbstractSqlPagingQueryProvider} providing the * implementation specifics * @param limitClause the implementation specific top clause to be used * @return the generated query */ public static String generateLimitJumpToQuery(AbstractSqlPagingQueryProvider provider, String limitClause) { StringBuilder sql = new StringBuilder(); sql.append("SELECT ").append(buildSortKeySelect(provider)); sql.append(" FROM ").append(provider.getFromClause()); sql.append(provider.getWhereClause() == null ? "" : " WHERE " + provider.getWhereClause()); buildGroupByClause(provider, sql); sql.append(" ORDER BY ").append(buildSortClause(provider)); sql.append(" " + limitClause); return sql.toString(); }
/** * Generate SQL query string using a LIMIT clause * * @param provider {@link AbstractSqlPagingQueryProvider} providing the * implementation specifics * @param remainingPageQuery is this query for the remaining pages (true) as * opposed to the first page (false) * @param limitClause the implementation specific limit clause to be used * @return the generated query */ public static String generateLimitSqlQuery(AbstractSqlPagingQueryProvider provider, boolean remainingPageQuery, String limitClause) { StringBuilder sql = new StringBuilder(); sql.append("SELECT ").append(provider.getSelectClause()); sql.append(" FROM ").append(provider.getFromClause()); buildWhereClause(provider, remainingPageQuery, sql); buildGroupByClause(provider, sql); sql.append(" ORDER BY ").append(buildSortClause(provider)); sql.append(" " + limitClause); 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(); }