| 1 | /* |
| 2 | * Copyright 2006-2008 the original author or authors. |
| 3 | * |
| 4 | * Licensed under the Apache License, Version 2.0 (the "License"); |
| 5 | * you may not use this file except in compliance with the License. |
| 6 | * You may obtain a copy of the License at |
| 7 | * |
| 8 | * http://www.apache.org/licenses/LICENSE-2.0 |
| 9 | * |
| 10 | * Unless required by applicable law or agreed to in writing, software |
| 11 | * distributed under the License is distributed on an "AS IS" BASIS, |
| 12 | * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. |
| 13 | * See the License for the specific language governing permissions and |
| 14 | * limitations under the License. |
| 15 | */ |
| 16 | |
| 17 | package org.springframework.batch.item.database.support; |
| 18 | |
| 19 | /** |
| 20 | * Utility class that generates the actual SQL statements used by query |
| 21 | * providers. |
| 22 | * |
| 23 | * @author Thomas Risberg |
| 24 | * @author Dave Syer |
| 25 | * @since 2.0 |
| 26 | */ |
| 27 | public class SqlPagingQueryUtils { |
| 28 | |
| 29 | /** |
| 30 | * Generate SQL query string using a LIMIT clause |
| 31 | * |
| 32 | * @param provider {@link AbstractSqlPagingQueryProvider} providing the |
| 33 | * implementation specifics |
| 34 | * @param remainingPageQuery is this query for the ramining pages (true) as |
| 35 | * opposed to the first page (false) |
| 36 | * @param limitClause the implementation specific limit clause to be used |
| 37 | * @return the generated query |
| 38 | */ |
| 39 | public static String generateLimitSqlQuery(AbstractSqlPagingQueryProvider provider, boolean remainingPageQuery, |
| 40 | String limitClause) { |
| 41 | StringBuilder sql = new StringBuilder(); |
| 42 | sql.append("SELECT ").append(provider.getSelectClause()); |
| 43 | sql.append(" FROM ").append(provider.getFromClause()); |
| 44 | buildWhereClause(provider, remainingPageQuery, sql); |
| 45 | sql.append(" ORDER BY ").append(provider.getSortKeyWithoutAlias()); |
| 46 | buildAscendingClause(provider, sql); |
| 47 | sql.append(" " + limitClause); |
| 48 | |
| 49 | return sql.toString(); |
| 50 | } |
| 51 | |
| 52 | /** |
| 53 | * Generate SQL query string using a TOP clause |
| 54 | * |
| 55 | * @param provider {@link AbstractSqlPagingQueryProvider} providing the |
| 56 | * implementation specifics |
| 57 | * @param remainingPageQuery is this query for the ramining pages (true) as |
| 58 | * opposed to the first page (false) |
| 59 | * @param topClause the implementation specific top clause to be used |
| 60 | * @return the generated query |
| 61 | */ |
| 62 | public static String generateTopSqlQuery(AbstractSqlPagingQueryProvider provider, boolean remainingPageQuery, |
| 63 | String topClause) { |
| 64 | StringBuilder sql = new StringBuilder(); |
| 65 | sql.append("SELECT ").append(topClause).append(" ").append(provider.getSelectClause()); |
| 66 | sql.append(" FROM ").append(provider.getFromClause()); |
| 67 | buildWhereClause(provider, remainingPageQuery, sql); |
| 68 | sql.append(" ORDER BY ").append(provider.getSortKeyWithoutAlias()); |
| 69 | buildAscendingClause(provider, sql); |
| 70 | |
| 71 | return sql.toString(); |
| 72 | } |
| 73 | |
| 74 | /** |
| 75 | * Generate SQL query string using a ROW_NUM condition |
| 76 | * |
| 77 | * @param provider {@link AbstractSqlPagingQueryProvider} providing the |
| 78 | * implementation specifics |
| 79 | * @param remainingPageQuery is this query for the remaining pages (true) as |
| 80 | * opposed to the first page (false) |
| 81 | * @param rowNumClause the implementation specific row num clause to be used |
| 82 | * @return the generated query |
| 83 | */ |
| 84 | public static String generateRowNumSqlQuery(AbstractSqlPagingQueryProvider provider, boolean remainingPageQuery, |
| 85 | String rowNumClause) { |
| 86 | |
| 87 | return generateRowNumSqlQuery(provider, provider.getSelectClause(), remainingPageQuery, rowNumClause); |
| 88 | |
| 89 | } |
| 90 | |
| 91 | /** |
| 92 | * Generate SQL query string using a ROW_NUM condition |
| 93 | * |
| 94 | * @param provider {@link AbstractSqlPagingQueryProvider} providing the |
| 95 | * implementation specifics |
| 96 | * @param remainingPageQuery is this query for the remaining pages (true) as |
| 97 | * opposed to the first page (false) |
| 98 | * @param rowNumClause the implementation specific row num clause to be used |
| 99 | * @return the generated query |
| 100 | */ |
| 101 | public static String generateRowNumSqlQuery(AbstractSqlPagingQueryProvider provider, String selectClause, |
| 102 | boolean remainingPageQuery, String rowNumClause) { |
| 103 | StringBuilder sql = new StringBuilder(); |
| 104 | sql.append("SELECT * FROM (SELECT ").append(selectClause).append(", ROWNUM as TMP_ROW_NUM"); |
| 105 | sql.append(" FROM ").append(provider.getFromClause()); |
| 106 | buildWhereClause(provider, remainingPageQuery, sql); |
| 107 | sql.append(" ORDER BY ").append(provider.getSortKeyWithoutAlias()); |
| 108 | buildAscendingClause(provider, sql); |
| 109 | sql.append(") WHERE ").append(rowNumClause); |
| 110 | |
| 111 | return sql.toString(); |
| 112 | |
| 113 | } |
| 114 | |
| 115 | public static String generateRowNumSqlQueryWithNesting(AbstractSqlPagingQueryProvider provider, |
| 116 | String selectClause, boolean remainingPageQuery, String rowNumClause) { |
| 117 | return generateRowNumSqlQueryWithNesting(provider, selectClause, selectClause, remainingPageQuery, rowNumClause); |
| 118 | } |
| 119 | |
| 120 | public static String generateRowNumSqlQueryWithNesting(AbstractSqlPagingQueryProvider provider, |
| 121 | String innerSelectClause, String outerSelectClause, boolean remainingPageQuery, String rowNumClause) { |
| 122 | |
| 123 | StringBuilder sql = new StringBuilder(); |
| 124 | sql.append("SELECT ").append(outerSelectClause).append(" FROM (SELECT ").append(outerSelectClause) |
| 125 | .append(", ROWNUM as TMP_ROW_NUM"); |
| 126 | sql.append(" FROM (SELECT ").append(innerSelectClause).append(" FROM ").append(provider.getFromClause()); |
| 127 | buildWhereClause(provider, remainingPageQuery, sql); |
| 128 | sql.append(" ORDER BY ").append(provider.getSortKeyWithoutAlias()); |
| 129 | buildAscendingClause(provider, sql); |
| 130 | sql.append(")) WHERE ").append(rowNumClause); |
| 131 | |
| 132 | return sql.toString(); |
| 133 | |
| 134 | } |
| 135 | |
| 136 | /** |
| 137 | * Generate SQL query string using a LIMIT clause |
| 138 | * |
| 139 | * @param provider {@link AbstractSqlPagingQueryProvider} providing the |
| 140 | * implementation specifics |
| 141 | * @param limitClause the implementation specific top clause to be used |
| 142 | * @return the generated query |
| 143 | */ |
| 144 | public static String generateLimitJumpToQuery(AbstractSqlPagingQueryProvider provider, String limitClause) { |
| 145 | StringBuilder sql = new StringBuilder(); |
| 146 | sql.append("SELECT ").append(provider.getSortKey()).append(" AS SORT_KEY"); |
| 147 | sql.append(" FROM ").append(provider.getFromClause()); |
| 148 | sql.append(provider.getWhereClause() == null ? "" : " WHERE " + provider.getWhereClause()); |
| 149 | sql.append(" ORDER BY ").append(provider.getSortKeyWithoutAlias()); |
| 150 | buildAscendingClause(provider, sql); |
| 151 | sql.append(" " + limitClause); |
| 152 | |
| 153 | return sql.toString(); |
| 154 | } |
| 155 | |
| 156 | /** |
| 157 | * Generate SQL query string using a TOP clause |
| 158 | * |
| 159 | * @param provider {@link AbstractSqlPagingQueryProvider} providing the |
| 160 | * implementation specifics |
| 161 | * @param topClause the implementation specific top clause to be used |
| 162 | * @return the generated query |
| 163 | */ |
| 164 | public static String generateTopJumpToQuery(AbstractSqlPagingQueryProvider provider, String topClause) { |
| 165 | StringBuilder sql = new StringBuilder(); |
| 166 | sql.append("SELECT ").append(topClause).append(" ").append(provider.getSortKey()).append(" AS SORT_KEY"); |
| 167 | sql.append(" FROM ").append(provider.getFromClause()); |
| 168 | sql.append(provider.getWhereClause() == null ? "" : " WHERE " + provider.getWhereClause()); |
| 169 | sql.append(" ORDER BY ").append(provider.getSortKeyWithoutAlias()); |
| 170 | buildAscendingClause(provider, sql); |
| 171 | |
| 172 | return sql.toString(); |
| 173 | } |
| 174 | |
| 175 | private static void buildAscendingClause(AbstractSqlPagingQueryProvider provider, StringBuilder sql) { |
| 176 | if (provider.isAscending()) { |
| 177 | sql.append(" ASC"); |
| 178 | } |
| 179 | else { |
| 180 | sql.append(" DESC"); |
| 181 | } |
| 182 | } |
| 183 | |
| 184 | private static void buildWhereClause(AbstractSqlPagingQueryProvider provider, boolean remainingPageQuery, |
| 185 | StringBuilder sql) { |
| 186 | if (remainingPageQuery) { |
| 187 | sql.append(" WHERE "); |
| 188 | if (provider.getWhereClause() != null) { |
| 189 | sql.append(provider.getWhereClause()); |
| 190 | sql.append(" AND "); |
| 191 | } |
| 192 | sql.append(provider.getSortKey()); |
| 193 | if (provider.isAscending()) { |
| 194 | sql.append(" > "); |
| 195 | } |
| 196 | else { |
| 197 | sql.append(" < "); |
| 198 | } |
| 199 | sql.append(provider.getSortKeyPlaceHolder()); |
| 200 | } |
| 201 | else { |
| 202 | sql.append(provider.getWhereClause() == null ? "" : " WHERE " + provider.getWhereClause()); |
| 203 | } |
| 204 | } |
| 205 | |
| 206 | } |