org.springframework.batch.item.database.Order Java Examples

/**
 * Generates ORDER BY attributes based on the sort keys.
 * @param sortKeys generates order by clause from map
 * @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();
}
 

/**
 * Create a pagingQueryProvider specific database type with a query containing a where
 * clause.
 * @param databaseProductName of the database.
 * @param whereClause to be applied to the query.
 * @return a PagingQueryProvider that will return the requested information.
 * @throws Exception exception thrown if error occurs creating
 * {@link PagingQueryProvider}.
 */
public static PagingQueryProvider getPagingQueryProvider(String databaseProductName,
		String whereClause) throws Exception {
	DataSource dataSource = getMockDataSource(databaseProductName);
	Map<String, Order> orderMap = new TreeMap<>();
	orderMap.put("START_TIME", Order.DESCENDING);
	orderMap.put("TASK_EXECUTION_ID", Order.DESCENDING);
	SqlPagingQueryProviderFactoryBean factoryBean = new SqlPagingQueryProviderFactoryBean();
	factoryBean.setSelectClause(JdbcTaskExecutionDao.SELECT_CLAUSE);
	factoryBean.setFromClause(JdbcTaskExecutionDao.FROM_CLAUSE);
	if (whereClause != null) {
		factoryBean.setWhereClause(whereClause);
	}
	factoryBean.setSortKeys(orderMap);
	factoryBean.setDataSource(dataSource);
	PagingQueryProvider pagingQueryProvider = null;
	try {
		pagingQueryProvider = factoryBean.getObject();
		pagingQueryProvider.init(dataSource);
	}
	catch (Exception e) {
		throw new IllegalStateException(e);
	}
	return pagingQueryProvider;
}
 

private ItemReader<TestData> dataSourceItemReader() throws Exception {
    JdbcPagingItemReader<TestData> reader = new JdbcPagingItemReader<>();
    reader.setDataSource(dataSource); // 设置数据源
    reader.setFetchSize(5); // 每次取多少条记录
    reader.setPageSize(5); // 设置每页数据量

    // 指定sql查询语句 select id,field1,field2,field3 from TEST
    MySqlPagingQueryProvider provider = new MySqlPagingQueryProvider();
    provider.setSelectClause("id,field1,field2,field3"); //设置查询字段
    provider.setFromClause("from TEST"); // 设置从哪张表查询

    // 将读取到的数据转换为TestData对象
    reader.setRowMapper((resultSet, rowNum) -> {
        TestData data = new TestData();
        data.setId(resultSet.getInt(1));
        data.setField1(resultSet.getString(2)); // 读取第一个字段，类型为String
        data.setField2(resultSet.getString(3));
        data.setField3(resultSet.getString(4));
        return data;
    });

    Map<String, Order> sort = new HashMap<>(1);
    sort.put("id", Order.ASCENDING);
    provider.setSortKeys(sort); // 设置排序,通过id 升序

    reader.setQueryProvider(provider);

    // 设置namedParameterJdbcTemplate等属性
    reader.afterPropertiesSet();
    return reader;
}
 

/**
 * @return a {@link PagingQueryProvider} with a where clause to narrow the
 * query
 * @throws Exception
 */
private PagingQueryProvider getPagingQueryProvider(String whereClause) {
	SqlPagingQueryProviderFactoryBean factory = new SqlPagingQueryProviderFactoryBean();
	factory.setDataSource(dataSource);
	factory.setFromClause(getQuery("%PREFIX%STEP_EXECUTION S, %PREFIX%JOB_EXECUTION J, %PREFIX%JOB_INSTANCE I"));
	factory.setSelectClause(FIELDS);
	Map<String, Order> sortKeys = new HashMap<String, Order>();
	sortKeys.put("STEP_EXECUTION_ID", Order.DESCENDING);
	factory.setSortKeys(sortKeys);
	if (whereClause != null) {
		factory.setWhereClause(whereClause
				+ " AND S.JOB_EXECUTION_ID = J.JOB_EXECUTION_ID AND J.JOB_INSTANCE_ID = I.JOB_INSTANCE_ID");
	}
	try {
		return (PagingQueryProvider) factory.getObject();
	}
	catch (Exception e) {
		throw new IllegalStateException("Unexpected exception creating paging query provide", e);
	}
}
 

/**
 * @return a {@link PagingQueryProvider} with a where clause to narrow the
 * query
 * @throws Exception if page provider is not created.
 */
private PagingQueryProvider getPagingQueryProvider(String fields, String fromClause, String whereClause) throws Exception {
	SqlPagingQueryProviderFactoryBean factory = new SqlPagingQueryProviderFactoryBean();
	factory.setDataSource(dataSource);
	fromClause = "%PREFIX%JOB_EXECUTION E, %PREFIX%JOB_INSTANCE I" + (fromClause == null ? "" : ", " + fromClause);
	factory.setFromClause(getQuery(fromClause));
	if(fields == null) {
		fields = FIELDS;
	}
	factory.setSelectClause(getQuery(fields));
	Map<String, Order> sortKeys = new HashMap<>();
	sortKeys.put("JOB_EXECUTION_ID", Order.DESCENDING);
	factory.setSortKeys(sortKeys);
	whereClause = "E.JOB_INSTANCE_ID=I.JOB_INSTANCE_ID" + (whereClause == null ? "" : " and " + whereClause);
	factory.setWhereClause(whereClause);

	return factory.getObject();
}
 

/**
 * @return a {@link PagingQueryProvider} with a where clause to narrow the
 * query
 * @throws Exception
 */
private PagingQueryProvider getPagingQueryProvider(String fromClause, String whereClause) throws Exception {
    final SqlPagingQueryProviderFactoryBean factory = new SqlPagingQueryProviderFactoryBean();
    factory.setDataSource(this.dataSource);
    fromClause = "%PREFIX%JOB_EXECUTION E, %PREFIX%JOB_INSTANCE I" + (fromClause == null ? "" : ", " + fromClause);
    factory.setFromClause(this.getQuery(fromClause));
    factory.setSelectClause(FIELDS);
    final Map<String, Order> sortKeys = new HashMap<>();
    sortKeys.put("JOB_EXECUTION_ID", Order.DESCENDING);
    factory.setSortKeys(sortKeys);
    whereClause = "E.JOB_INSTANCE_ID=I.JOB_INSTANCE_ID" + (whereClause == null ? "" : " and " + whereClause);
    factory.setWhereClause(whereClause);

    return factory.getObject();
}
 

private PagingQueryProvider getPagingQueryProviderForQueryService(String fromClause, String whereClause, final Boolean withJobName) throws Exception {
    final SqlPagingQueryProviderFactoryBean factory = new SqlPagingQueryProviderFactoryBean();
    factory.setDataSource(this.dataSource);
    fromClause = "%PREFIX%JOB_EXECUTION E, %PREFIX%JOB_INSTANCE I" + (fromClause == null ? "" : ", " + fromClause);
    factory.setFromClause(this.getQuery(fromClause));
    factory.setSelectClause(FIELDS);
    final Map<String, Order> sortKeys = new HashMap<>();
    sortKeys.put("JOB_EXECUTION_ID", Order.DESCENDING);
    factory.setSortKeys(sortKeys);
    whereClause = "%s E.JOB_INSTANCE_ID=I.JOB_INSTANCE_ID" + (whereClause == null ? "" : whereClause);
    if (withJobName) {
        whereClause = String.format(whereClause, " I.JOB_NAME=? AND ");
    } else {
        whereClause = String.format(whereClause, "");
    }
    factory.setWhereClause(whereClause);
    return factory.getObject();
}
 

private PagingQueryProvider getPagingQueryProvider(String fromClause, String whereClause) throws Exception {
    final SqlPagingQueryProviderFactoryBean factory = new SqlPagingQueryProviderFactoryBean();
    factory.setDataSource(this.jdbcTemplate.getDataSource());
    fromClause = "%s S" + (fromClause == null ? "" : ", " + fromClause);
    factory.setFromClause(String.format(fromClause, this.tableName));
    factory.setSelectClause(FIELDS);
    final Map<String, Order> sortKeys = new HashMap<>();
    sortKeys.put("id", Order.ASCENDING);
    factory.setSortKeys(sortKeys);
    whereClause = whereClause == null ? "" : whereClause;
    factory.setWhereClause(whereClause);
    return factory.getObject();
}
 

private PagingQueryProvider getPagingQueryProvider(String fromClause, String whereClause) throws Exception {
    final SqlPagingQueryProviderFactoryBean factory = new SqlPagingQueryProviderFactoryBean();
    factory.setDataSource(this.jdbcTemplate.getDataSource());
    fromClause = "%s S" + (fromClause == null ? "" : ", " + fromClause);
    factory.setFromClause(String.format(fromClause, this.tableName));
    factory.setSelectClause(FIELDS);
    final Map<String, Order> sortKeys = new HashMap<>();
    sortKeys.put("id", Order.DESCENDING);
    factory.setSortKeys(sortKeys);
    whereClause = whereClause == null ? "" : whereClause;
    factory.setWhereClause(whereClause);
    return factory.getObject();
}
 

/**
 * Initializes the JdbTaskExecutionDao and defaults the table prefix to
 * {@link TaskProperties#DEFAULT_TABLE_PREFIX}.
 * @param dataSource used by the dao to execute queries and update the tables.
 */
public JdbcTaskExecutionDao(DataSource dataSource) {
	Assert.notNull(dataSource, "The dataSource must not be null.");
	this.jdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
	this.dataSource = dataSource;
	this.orderMap = new LinkedHashMap<>();
	this.orderMap.put("START_TIME", Order.DESCENDING);
	this.orderMap.put("TASK_EXECUTION_ID", Order.DESCENDING);
}
 

private Page<TaskExecution> queryForPageableResults(Pageable pageable,
		String selectClause, String fromClause, String whereClause,
		MapSqlParameterSource queryParameters, long totalCount) {
	SqlPagingQueryProviderFactoryBean factoryBean = new SqlPagingQueryProviderFactoryBean();
	factoryBean.setSelectClause(selectClause);
	factoryBean.setFromClause(fromClause);
	if (StringUtils.hasText(whereClause)) {
		factoryBean.setWhereClause(whereClause);
	}
	final Sort sort = pageable.getSort();
	final LinkedHashMap<String, Order> sortOrderMap = new LinkedHashMap<>();

	if (sort != null) {
		for (Sort.Order sortOrder : sort) {
			sortOrderMap.put(sortOrder.getProperty(),
					sortOrder.isAscending() ? Order.ASCENDING : Order.DESCENDING);
		}
	}

	if (!CollectionUtils.isEmpty(sortOrderMap)) {
		factoryBean.setSortKeys(sortOrderMap);
	}
	else {
		factoryBean.setSortKeys(this.orderMap);
	}

	factoryBean.setDataSource(this.dataSource);
	PagingQueryProvider pagingQueryProvider;
	try {
		pagingQueryProvider = factoryBean.getObject();
		pagingQueryProvider.init(this.dataSource);
	}
	catch (Exception e) {
		throw new IllegalStateException(e);
	}
	String query = pagingQueryProvider.getPageQuery(pageable);
	List<TaskExecution> resultList = this.jdbcTemplate.query(getQuery(query),
			queryParameters, new TaskExecutionRowMapper());
	return new PageImpl<>(resultList, pageable, totalCount);
}
 

@Before
public void setup() throws Exception {
	this.factoryBean = new SqlPagingQueryProviderFactoryBean();
	this.factoryBean.setDataSource(TestDBUtils.getMockDataSource("MySQL"));
	this.factoryBean.setDatabaseType("Oracle");
	this.factoryBean.setSelectClause(JdbcTaskExecutionDao.SELECT_CLAUSE);
	this.factoryBean.setFromClause(JdbcTaskExecutionDao.FROM_CLAUSE);
	Map<String, Order> orderMap = new TreeMap<>();
	orderMap.put("START_TIME", Order.DESCENDING);
	orderMap.put("TASK_EXECUTION_ID", Order.DESCENDING);
	this.factoryBean.setSortKeys(orderMap);

}
 

/**
 * @param sortKeys key to use to sort and limit page content
 */
public void setSortKeys(Map<String, Order> sortKeys) {
	this.sortKeys = sortKeys;
}
 

/**
 * @param sortKeys the sortKeys to set
 */
public void setSortKeys(Map<String, Order> sortKeys) {
	this.sortKeys = sortKeys;
}
 

/**
 * A Map<String, Order> of sort columns as the key and {@link Order} for
 * ascending/descending.
 * @return sortKey key to use to sort and limit page content
 */
@Override
public Map<String, Order> getSortKeys() {
	return this.sortKeys;
}
 

/**
 * The sort keys. A Map of the columns that make up the key and a Boolean indicating
 * ascending or descending (ascending = true).
 * @return the sort keys used to order the query
 */
Map<String, Order> getSortKeys();