spring-batch SqlPagingQueryUtils 源码

spring-batch SqlPagingQueryUtils 代码

文件路径：/spring-batch-infrastructure/src/main/java/org/springframework/batch/item/database/support/SqlPagingQueryUtils.java

/*
 * Copyright 2006-2021 the original author or authors.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      https://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

package org.springframework.batch.item.database.support;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.springframework.batch.item.database.Order;
import org.springframework.util.StringUtils;

/**
 * Utility class that generates the actual SQL statements used by query providers.
 *
 * @author Thomas Risberg
 * @author Dave Syer
 * @author Michael Minella
 * @author Mahmoud Ben Hassine
 * @since 2.0
 */
public class SqlPagingQueryUtils {

	/**
	 * 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 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
	 * @deprecated as of v5.0 in favor of
	 * {@link #generateLimitGroupedSqlQuery(AbstractSqlPagingQueryProvider, java.lang.String)}
	 */
	@Deprecated
	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();
	}

	/**
	 * Generate SQL query string using a LIMIT clause
	 * @param provider {@link AbstractSqlPagingQueryProvider} providing the implementation
	 * specifics
	 * @param limitClause the implementation specific limit clause to be used
	 * @return the generated query
	 */
	public static String generateLimitGroupedSqlQuery(AbstractSqlPagingQueryProvider provider, 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();
	}

	/**
	 * 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(128);
		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 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 ROW_NUM condition
	 * @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 rowNumClause the implementation specific row num clause to be used
	 * @return the generated query
	 */
	public static String generateRowNumSqlQuery(AbstractSqlPagingQueryProvider provider, boolean remainingPageQuery,
			String rowNumClause) {

		return generateRowNumSqlQuery(provider, provider.getSelectClause(), remainingPageQuery, rowNumClause);

	}

	/**
	 * 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();

	}

	public static String generateRowNumSqlQueryWithNesting(AbstractSqlPagingQueryProvider provider, String selectClause,
			boolean remainingPageQuery, String rowNumClause) {
		return generateRowNumSqlQueryWithNesting(provider, selectClause, selectClause, remainingPageQuery,
				rowNumClause);
	}

	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 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 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();
	}

	/**
	 * 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 sortKeys {@link Map} where the key is the name of the column to be sorted
	 * and the value contains the {@link Order}.
	 * @return a String that can be appended to an ORDER BY clause.
	 */
	public static String buildSortClause(Map<String, Order> sortKeys) {
		StringBuilder builder = new StringBuilder();
		String prefix = "";

		for (Map.Entry<String, Order> sortKey : sortKeys.entrySet()) {
			builder.append(prefix);

			prefix = ", ";

			builder.append(sortKey.getKey());

			if (sortKey.getValue() != null && sortKey.getValue() == Order.DESCENDING) {
				builder.append(" DESC");
			}
			else {
				builder.append(" ASC");
			}
		}

		return builder.toString();
	}

	/**
	 * Appends the where conditions required to query for the subsequent pages.
	 * @param provider the {@link AbstractSqlPagingQueryProvider} to be used for
	 * pagination.
	 * @param sql {@link StringBuilder} containing the sql to be used for the query.
	 */
	public static void buildSortConditions(AbstractSqlPagingQueryProvider provider, StringBuilder sql) {
		List<Map.Entry<String, Order>> keys = new ArrayList<>(provider.getSortKeys().entrySet());
		List<String> clauses = new ArrayList<>();

		for (int i = 0; i < keys.size(); i++) {
			StringBuilder clause = new StringBuilder();

			String prefix = "";
			for (int j = 0; j < i; j++) {
				clause.append(prefix);
				prefix = " AND ";
				Entry<String, Order> entry = keys.get(j);
				clause.append(entry.getKey());
				clause.append(" = ");
				clause.append(provider.getSortKeyPlaceHolder(entry.getKey()));
			}

			if (clause.length() > 0) {
				clause.append(" AND ");
			}
			clause.append(keys.get(i).getKey());

			if (keys.get(i).getValue() != null && keys.get(i).getValue() == Order.DESCENDING) {
				clause.append(" < ");
			}
			else {
				clause.append(" > ");
			}

			clause.append(provider.getSortKeyPlaceHolder(keys.get(i).getKey()));

			clauses.add(clause.toString());
		}

		sql.append("(");
		String prefix = "";

		for (String curClause : clauses) {
			sql.append(prefix);
			prefix = " OR ";
			sql.append("(");
			sql.append(curClause);
			sql.append(")");
		}
		sql.append(")");
	}

	private static String buildSortKeySelect(AbstractSqlPagingQueryProvider provider) {
		StringBuilder select = new StringBuilder();

		String prefix = "";

		for (Map.Entry<String, Order> sortKey : provider.getSortKeys().entrySet()) {
			select.append(prefix);

			prefix = ", ";

			select.append(sortKey.getKey());
		}

		return select.toString();
	}

	private static void buildWhereClause(AbstractSqlPagingQueryProvider provider, boolean remainingPageQuery,
			StringBuilder sql) {
		if (remainingPageQuery) {
			sql.append(" WHERE ");
			if (provider.getWhereClause() != null) {
				sql.append("(");
				sql.append(provider.getWhereClause());
				sql.append(") AND ");
			}

			buildSortConditions(provider, sql);
		}
		else {
			sql.append(provider.getWhereClause() == null ? "" : " WHERE " + provider.getWhereClause());
		}
	}

	private static void buildGroupByClause(AbstractSqlPagingQueryProvider provider, StringBuilder sql) {
		if (StringUtils.hasText(provider.getGroupClause())) {
			sql.append(" GROUP BY ");
			sql.append(provider.getGroupClause());
		}
	}

}

相关信息

spring-batch 源码目录

相关文章

spring-batch AbstractSqlPagingQueryProvider 源码

spring-batch ColumnMapItemPreparedStatementSetter 源码

spring-batch DataFieldMaxValueIncrementerFactory 源码

spring-batch Db2PagingQueryProvider 源码

spring-batch DefaultDataFieldMaxValueIncrementerFactory 源码

spring-batch DerbyPagingQueryProvider 源码

spring-batch H2PagingQueryProvider 源码

spring-batch HanaPagingQueryProvider 源码

spring-batch HsqlPagingQueryProvider 源码

spring-batch MySqlPagingQueryProvider 源码