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

The following examples show how to use org.springframework.batch.item.database.Order. You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example #1
Source File: SqlPagingQueryUtils.java    From spring-cloud-task with Apache License 2.0 6 votes vote down vote up
/**
 * 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();
}
 
Example #2
Source File: TestDBUtils.java    From spring-cloud-task with Apache License 2.0 6 votes vote down vote up
/**
 * 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;
}
 
Example #3
Source File: DataSourceItemReaderDemo.java    From SpringAll with MIT License 5 votes vote down vote up
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;
}
 
Example #4
Source File: JdbcSearchableStepExecutionDao.java    From spring-cloud-dataflow with Apache License 2.0 5 votes vote down vote up
/**
 * @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);
	}
}
 
Example #5
Source File: JdbcSearchableJobExecutionDao.java    From spring-cloud-dataflow with Apache License 2.0 5 votes vote down vote up
/**
 * @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();
}
 
Example #6
Source File: JdbcLightminJobExecutionDao.java    From spring-batch-lightmin with Apache License 2.0 5 votes vote down vote up
/**
 * @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();
}
 
Example #7
Source File: JdbcLightminJobExecutionDao.java    From spring-batch-lightmin with Apache License 2.0 5 votes vote down vote up
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();
}
 
Example #8
Source File: JdbcSchedulerConfigurationRepository.java    From spring-batch-lightmin with Apache License 2.0 5 votes vote down vote up
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();
}
 
Example #9
Source File: JdbcSchedulerExecutionRepository.java    From spring-batch-lightmin with Apache License 2.0 5 votes vote down vote up
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();
}
 
Example #10
Source File: JdbcTaskExecutionDao.java    From spring-cloud-task with Apache License 2.0 5 votes vote down vote up
/**
 * 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);
}
 
Example #11
Source File: JdbcTaskExecutionDao.java    From spring-cloud-task with Apache License 2.0 5 votes vote down vote up
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);
}
 
Example #12
Source File: SqlPagingQueryProviderFactoryBeanTests.java    From spring-cloud-task with Apache License 2.0 5 votes vote down vote up
@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);

}
 
Example #13
Source File: AbstractSqlPagingQueryProvider.java    From spring-cloud-task with Apache License 2.0 4 votes vote down vote up
/**
 * @param sortKeys key to use to sort and limit page content
 */
public void setSortKeys(Map<String, Order> sortKeys) {
	this.sortKeys = sortKeys;
}
 
Example #14
Source File: SqlPagingQueryProviderFactoryBean.java    From spring-cloud-task with Apache License 2.0 4 votes vote down vote up
/**
 * @param sortKeys the sortKeys to set
 */
public void setSortKeys(Map<String, Order> sortKeys) {
	this.sortKeys = sortKeys;
}
 
Example #15
Source File: AbstractSqlPagingQueryProvider.java    From spring-cloud-task with Apache License 2.0 2 votes vote down vote up
/**
 * A Map&lt;String, Order&gt; 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;
}
 
Example #16
Source File: PagingQueryProvider.java    From spring-cloud-task with Apache License 2.0 2 votes vote down vote up
/**
 * 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();