| 1 | /* |
| 2 | * Copyright 2006-2012 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 | import java.util.ArrayList; |
| 20 | import java.util.List; |
| 21 | import java.util.Map; |
| 22 | import java.util.Map.Entry; |
| 23 | |
| 24 | import org.springframework.batch.item.database.Order; |
| 25 | import org.springframework.util.StringUtils; |
| 26 | |
| 27 | /** |
| 28 | * Utility class that generates the actual SQL statements used by query |
| 29 | * providers. |
| 30 | * |
| 31 | * @author Thomas Risberg |
| 32 | * @author Dave Syer |
| 33 | * @author Michael Minella |
| 34 | * @since 2.0 |
| 35 | */ |
| 36 | public class SqlPagingQueryUtils { |
| 37 | |
| 38 | /** |
| 39 | * Generate SQL query string using a LIMIT clause |
| 40 | * |
| 41 | * @param provider {@link AbstractSqlPagingQueryProvider} providing the |
| 42 | * implementation specifics |
| 43 | * @param remainingPageQuery is this query for the ramining pages (true) as |
| 44 | * opposed to the first page (false) |
| 45 | * @param limitClause the implementation specific limit clause to be used |
| 46 | * @return the generated query |
| 47 | */ |
| 48 | public static String generateLimitSqlQuery(AbstractSqlPagingQueryProvider provider, boolean remainingPageQuery, |
| 49 | String limitClause) { |
| 50 | StringBuilder sql = new StringBuilder(); |
| 51 | sql.append("SELECT ").append(provider.getSelectClause()); |
| 52 | sql.append(" FROM ").append(provider.getFromClause()); |
| 53 | buildWhereClause(provider, remainingPageQuery, sql); |
| 54 | buildGroupByClause(provider, sql); |
| 55 | sql.append(" ORDER BY ").append(buildSortClause(provider)); |
| 56 | sql.append(" " + limitClause); |
| 57 | |
| 58 | return sql.toString(); |
| 59 | } |
| 60 | |
| 61 | /** |
| 62 | * Generate SQL query string using a LIMIT clause |
| 63 | * |
| 64 | * @param provider {@link AbstractSqlPagingQueryProvider} providing the |
| 65 | * implementation specifics |
| 66 | * @param remainingPageQuery is this query for the ramining pages (true) as |
| 67 | * opposed to the first page (false) |
| 68 | * @param limitClause the implementation specific limit clause to be used |
| 69 | * @return the generated query |
| 70 | */ |
| 71 | public static String generateLimitGroupedSqlQuery(AbstractSqlPagingQueryProvider provider, boolean remainingPageQuery, |
| 72 | String limitClause) { |
| 73 | StringBuilder sql = new StringBuilder(); |
| 74 | sql.append("SELECT * "); |
| 75 | sql.append(" FROM ("); |
| 76 | sql.append("SELECT ").append(provider.getSelectClause()); |
| 77 | sql.append(" FROM ").append(provider.getFromClause()); |
| 78 | sql.append(provider.getWhereClause() == null ? "" : " WHERE " + provider.getWhereClause()); |
| 79 | buildGroupByClause(provider, sql); |
| 80 | sql.append(") AS MAIN_QRY "); |
| 81 | sql.append("WHERE "); |
| 82 | buildSortConditions(provider, sql); |
| 83 | sql.append(" ORDER BY ").append(buildSortClause(provider)); |
| 84 | sql.append(" " + limitClause); |
| 85 | |
| 86 | return sql.toString(); |
| 87 | } |
| 88 | |
| 89 | /** |
| 90 | * Generate SQL query string using a TOP clause |
| 91 | * |
| 92 | * @param provider {@link AbstractSqlPagingQueryProvider} providing the |
| 93 | * implementation specifics |
| 94 | * @param remainingPageQuery is this query for the ramining pages (true) as |
| 95 | * opposed to the first page (false) |
| 96 | * @param topClause the implementation specific top clause to be used |
| 97 | * @return the generated query |
| 98 | */ |
| 99 | public static String generateTopSqlQuery(AbstractSqlPagingQueryProvider provider, boolean remainingPageQuery, |
| 100 | String topClause) { |
| 101 | StringBuilder sql = new StringBuilder(); |
| 102 | sql.append("SELECT ").append(topClause).append(" ").append(provider.getSelectClause()); |
| 103 | sql.append(" FROM ").append(provider.getFromClause()); |
| 104 | buildWhereClause(provider, remainingPageQuery, sql); |
| 105 | buildGroupByClause(provider, sql); |
| 106 | sql.append(" ORDER BY ").append(buildSortClause(provider)); |
| 107 | |
| 108 | return sql.toString(); |
| 109 | } |
| 110 | |
| 111 | /** |
| 112 | * Generate SQL query string using a TOP clause |
| 113 | * |
| 114 | * @param provider {@link AbstractSqlPagingQueryProvider} providing the |
| 115 | * implementation specifics |
| 116 | * @param remainingPageQuery is this query for the ramining pages (true) as |
| 117 | * opposed to the first page (false) |
| 118 | * @param topClause the implementation specific top clause to be used |
| 119 | * @return the generated query |
| 120 | */ |
| 121 | public static String generateGroupedTopSqlQuery(AbstractSqlPagingQueryProvider provider, boolean remainingPageQuery, |
| 122 | String topClause) { |
| 123 | StringBuilder sql = new StringBuilder(); |
| 124 | sql.append("SELECT ").append(topClause).append(" * FROM ("); |
| 125 | sql.append("SELECT ").append(provider.getSelectClause()); |
| 126 | sql.append(" FROM ").append(provider.getFromClause()); |
| 127 | sql.append(provider.getWhereClause() == null ? "" : " WHERE " + provider.getWhereClause()); |
| 128 | buildGroupByClause(provider, sql); |
| 129 | sql.append(") AS MAIN_QRY "); |
| 130 | sql.append("WHERE "); |
| 131 | buildSortConditions(provider, sql); |
| 132 | sql.append(" ORDER BY ").append(buildSortClause(provider)); |
| 133 | |
| 134 | return sql.toString(); |
| 135 | } |
| 136 | |
| 137 | /** |
| 138 | * Generate SQL query string using a ROW_NUM condition |
| 139 | * |
| 140 | * @param provider {@link AbstractSqlPagingQueryProvider} providing the |
| 141 | * implementation specifics |
| 142 | * @param remainingPageQuery is this query for the remaining pages (true) as |
| 143 | * opposed to the first page (false) |
| 144 | * @param rowNumClause the implementation specific row num clause to be used |
| 145 | * @return the generated query |
| 146 | */ |
| 147 | public static String generateRowNumSqlQuery(AbstractSqlPagingQueryProvider provider, boolean remainingPageQuery, |
| 148 | String rowNumClause) { |
| 149 | |
| 150 | return generateRowNumSqlQuery(provider, provider.getSelectClause(), remainingPageQuery, rowNumClause); |
| 151 | |
| 152 | } |
| 153 | |
| 154 | /** |
| 155 | * Generate SQL query string using a ROW_NUM condition |
| 156 | * |
| 157 | * @param provider {@link AbstractSqlPagingQueryProvider} providing the |
| 158 | * implementation specifics |
| 159 | * @param remainingPageQuery is this query for the remaining pages (true) as |
| 160 | * opposed to the first page (false) |
| 161 | * @param rowNumClause the implementation specific row num clause to be used |
| 162 | * @return the generated query |
| 163 | */ |
| 164 | public static String generateRowNumSqlQuery(AbstractSqlPagingQueryProvider provider, String selectClause, |
| 165 | boolean remainingPageQuery, String rowNumClause) { |
| 166 | StringBuilder sql = new StringBuilder(); |
| 167 | sql.append("SELECT * FROM (SELECT ").append(selectClause); |
| 168 | sql.append(" FROM ").append(provider.getFromClause()); |
| 169 | sql.append(provider.getWhereClause() == null ? "" : " WHERE " + provider.getWhereClause()); |
| 170 | buildGroupByClause(provider, sql); |
| 171 | sql.append(" ORDER BY ").append(buildSortClause(provider)); |
| 172 | sql.append(") WHERE ").append(rowNumClause); |
| 173 | if(remainingPageQuery) { |
| 174 | sql.append(" AND "); |
| 175 | buildSortConditions(provider, sql); |
| 176 | } |
| 177 | |
| 178 | return sql.toString(); |
| 179 | |
| 180 | } |
| 181 | |
| 182 | public static String generateRowNumSqlQueryWithNesting(AbstractSqlPagingQueryProvider provider, |
| 183 | String selectClause, boolean remainingPageQuery, String rowNumClause) { |
| 184 | return generateRowNumSqlQueryWithNesting(provider, selectClause, selectClause, remainingPageQuery, rowNumClause); |
| 185 | } |
| 186 | |
| 187 | public static String generateRowNumSqlQueryWithNesting(AbstractSqlPagingQueryProvider provider, |
| 188 | String innerSelectClause, String outerSelectClause, boolean remainingPageQuery, String rowNumClause) { |
| 189 | |
| 190 | StringBuilder sql = new StringBuilder(); |
| 191 | sql.append("SELECT ").append(outerSelectClause).append(" FROM (SELECT ").append(outerSelectClause) |
| 192 | .append(", ").append(StringUtils.hasText(provider.getGroupClause()) ? "MIN(ROWNUM) as TMP_ROW_NUM" : "ROWNUM as TMP_ROW_NUM"); |
| 193 | sql.append(" FROM (SELECT ").append(innerSelectClause).append(" FROM ").append(provider.getFromClause()); |
| 194 | buildWhereClause(provider, remainingPageQuery, sql); |
| 195 | buildGroupByClause(provider, sql); |
| 196 | sql.append(" ORDER BY ").append(buildSortClause(provider)); |
| 197 | sql.append(")) WHERE ").append(rowNumClause); |
| 198 | |
| 199 | return sql.toString(); |
| 200 | |
| 201 | } |
| 202 | |
| 203 | /** |
| 204 | * Generate SQL query string using a LIMIT clause |
| 205 | * |
| 206 | * @param provider {@link AbstractSqlPagingQueryProvider} providing the |
| 207 | * implementation specifics |
| 208 | * @param limitClause the implementation specific top clause to be used |
| 209 | * @return the generated query |
| 210 | */ |
| 211 | public static String generateLimitJumpToQuery(AbstractSqlPagingQueryProvider provider, String limitClause) { |
| 212 | StringBuilder sql = new StringBuilder(); |
| 213 | sql.append("SELECT ").append(buildSortKeySelect(provider)); |
| 214 | sql.append(" FROM ").append(provider.getFromClause()); |
| 215 | sql.append(provider.getWhereClause() == null ? "" : " WHERE " + provider.getWhereClause()); |
| 216 | buildGroupByClause(provider, sql); |
| 217 | sql.append(" ORDER BY ").append(buildSortClause(provider)); |
| 218 | sql.append(" " + limitClause); |
| 219 | |
| 220 | return sql.toString(); |
| 221 | } |
| 222 | |
| 223 | /** |
| 224 | * Generate SQL query string using a TOP clause |
| 225 | * |
| 226 | * @param provider {@link AbstractSqlPagingQueryProvider} providing the |
| 227 | * implementation specifics |
| 228 | * @param topClause the implementation specific top clause to be used |
| 229 | * @return the generated query |
| 230 | */ |
| 231 | public static String generateTopJumpToQuery(AbstractSqlPagingQueryProvider provider, String topClause) { |
| 232 | StringBuilder sql = new StringBuilder(); |
| 233 | sql.append("SELECT ").append(topClause).append(" ").append(buildSortKeySelect(provider)); |
| 234 | sql.append(" FROM ").append(provider.getFromClause()); |
| 235 | sql.append(provider.getWhereClause() == null ? "" : " WHERE " + provider.getWhereClause()); |
| 236 | buildGroupByClause(provider, sql); |
| 237 | sql.append(" ORDER BY ").append(buildSortClause(provider)); |
| 238 | |
| 239 | return sql.toString(); |
| 240 | } |
| 241 | |
| 242 | /** |
| 243 | * Generates ORDER BY attributes based on the sort keys. |
| 244 | * |
| 245 | * @param provider |
| 246 | * @return a String that can be appended to an ORDER BY clause. |
| 247 | */ |
| 248 | public static String buildSortClause(AbstractSqlPagingQueryProvider provider) { |
| 249 | return buildSortClause(provider.getSortKeys()); |
| 250 | } |
| 251 | |
| 252 | /** |
| 253 | * Generates ORDER BY attributes based on the sort keys. |
| 254 | * |
| 255 | * @param sortKeys |
| 256 | * @return a String that can be appended to an ORDER BY clause. |
| 257 | */ |
| 258 | public static String buildSortClause(Map<String, Order> sortKeys) { |
| 259 | StringBuilder builder = new StringBuilder(); |
| 260 | String prefix = ""; |
| 261 | |
| 262 | for (Map.Entry<String, Order> sortKey : sortKeys.entrySet()) { |
| 263 | builder.append(prefix); |
| 264 | |
| 265 | prefix = ", "; |
| 266 | |
| 267 | builder.append(sortKey.getKey()); |
| 268 | |
| 269 | if(sortKey.getValue() != null && sortKey.getValue() == Order.DESCENDING) { |
| 270 | builder.append(" DESC"); |
| 271 | } |
| 272 | else { |
| 273 | builder.append(" ASC"); |
| 274 | } |
| 275 | } |
| 276 | |
| 277 | return builder.toString(); |
| 278 | } |
| 279 | |
| 280 | /** |
| 281 | * Appends the where conditions required to query for the subsequent pages. |
| 282 | * |
| 283 | * @param provider |
| 284 | * @param sql |
| 285 | */ |
| 286 | public static void buildSortConditions( |
| 287 | AbstractSqlPagingQueryProvider provider, StringBuilder sql) { |
| 288 | List<Map.Entry<String, Order>> keys = new ArrayList<Map.Entry<String,Order>>(provider.getSortKeys().entrySet()); |
| 289 | List<String> clauses = new ArrayList<String>(); |
| 290 | |
| 291 | for(int i = 0; i < keys.size(); i++) { |
| 292 | StringBuilder clause = new StringBuilder(); |
| 293 | |
| 294 | String prefix = ""; |
| 295 | for(int j = 0; j < i; j++) { |
| 296 | clause.append(prefix); |
| 297 | prefix = " AND "; |
| 298 | Entry<String, Order> entry = keys.get(j); |
| 299 | clause.append(entry.getKey()); |
| 300 | clause.append(" = "); |
| 301 | clause.append(provider.getSortKeyPlaceHolder(entry.getKey())); |
| 302 | } |
| 303 | |
| 304 | if(clause.length() > 0) { |
| 305 | clause.append(" AND "); |
| 306 | } |
| 307 | clause.append(keys.get(i).getKey()); |
| 308 | |
| 309 | if(keys.get(i).getValue() != null && keys.get(i).getValue() == Order.DESCENDING) { |
| 310 | clause.append(" < "); |
| 311 | } |
| 312 | else { |
| 313 | clause.append(" > "); |
| 314 | } |
| 315 | |
| 316 | clause.append(provider.getSortKeyPlaceHolder(keys.get(i).getKey())); |
| 317 | |
| 318 | clauses.add(clause.toString()); |
| 319 | } |
| 320 | |
| 321 | sql.append("("); |
| 322 | String prefix = ""; |
| 323 | |
| 324 | for (String curClause : clauses) { |
| 325 | sql.append(prefix); |
| 326 | prefix = " OR "; |
| 327 | sql.append("("); |
| 328 | sql.append(curClause); |
| 329 | sql.append(")"); |
| 330 | } |
| 331 | sql.append(")"); |
| 332 | } |
| 333 | |
| 334 | private static String buildSortKeySelect(AbstractSqlPagingQueryProvider provider) { |
| 335 | StringBuilder select = new StringBuilder(); |
| 336 | |
| 337 | String prefix = ""; |
| 338 | |
| 339 | for (Map.Entry<String, Order> sortKey : provider.getSortKeys().entrySet()) { |
| 340 | select.append(prefix); |
| 341 | |
| 342 | prefix = ", "; |
| 343 | |
| 344 | select.append(sortKey.getKey()); |
| 345 | } |
| 346 | |
| 347 | return select.toString(); |
| 348 | } |
| 349 | |
| 350 | private static void buildWhereClause(AbstractSqlPagingQueryProvider provider, boolean remainingPageQuery, |
| 351 | StringBuilder sql) { |
| 352 | if (remainingPageQuery) { |
| 353 | sql.append(" WHERE "); |
| 354 | if (provider.getWhereClause() != null) { |
| 355 | sql.append(provider.getWhereClause()); |
| 356 | sql.append(" AND "); |
| 357 | } |
| 358 | |
| 359 | buildSortConditions(provider, sql); |
| 360 | } |
| 361 | else { |
| 362 | sql.append(provider.getWhereClause() == null ? "" : " WHERE " + provider.getWhereClause()); |
| 363 | } |
| 364 | } |
| 365 | |
| 366 | private static void buildGroupByClause(AbstractSqlPagingQueryProvider provider, StringBuilder sql) { |
| 367 | if(StringUtils.hasText(provider.getGroupClause())) { |
| 368 | sql.append(" GROUP BY "); |
| 369 | sql.append(provider.getGroupClause()); |
| 370 | } |
| 371 | } |
| 372 | |
| 373 | } |