Example usage for org.springframework.batch.item.database.support SqlPagingQueryProviderFactoryBean setSortKey

Introduction

In this page you can find the example usage for org.springframework.batch.item.database.support SqlPagingQueryProviderFactoryBean setSortKey.

Prototype

public void setSortKey(String key) 

Source Link

Usage

From source file:nu.yona.server.batch.jobs.SendSystemMessageBatchJob.java

private SqlPagingQueryProviderFactoryBean createQueryProviderFactory() {
    final SqlPagingQueryProviderFactoryBean sqlPagingQueryProviderFactoryBean = new SqlPagingQueryProviderFactoryBean();
    sqlPagingQueryProviderFactoryBean.setDataSource(dataSource);
    sqlPagingQueryProviderFactoryBean.setSelectClause("select id");
    sqlPagingQueryProviderFactoryBean.setFromClause("from users_anonymized");
    sqlPagingQueryProviderFactoryBean.setSortKey("id");
    return sqlPagingQueryProviderFactoryBean;
}

---

From source file:de.langmi.spring.batch.examples.readers.jdbc.JdbcPagingItemReaderTests.java

@Test
public void testWithFactory() throws Exception {
    // setup queryProviderFactory
    SqlPagingQueryProviderFactoryBean factory = new SqlPagingQueryProviderFactoryBean();
    factory.setDataSource(dataSource);//  w  ww. j  a v  a2 s. c  o m
    factory.setDatabaseType("HSQL");
    factory.setSelectClause("select ID, NAME");
    factory.setFromClause("from TEST");
    factory.setWhereClause("where NAME <> 'foo'");
    factory.setSortKey("ID");
    PagingQueryProvider queryProvider = (PagingQueryProvider) factory.getObject();
    // call init to imitate spring context startup behaviour        
    queryProvider.init(dataSource);

    // setup reader
    JdbcPagingItemReader<String> reader = new JdbcPagingItemReader<String>();
    reader.setDataSource(dataSource);
    reader.setQueryProvider(queryProvider);
    reader.setRowMapper(new ParameterizedRowMapper<String>() {

        @Override
        public String mapRow(ResultSet rs, int rowNum) throws SQLException {
            return rs.getString("NAME");
        }
    });
    reader.setPageSize(2);

    // needed call, normally done at spring application context startup
    reader.afterPropertiesSet();

    reader.open(MetaDataInstanceFactory.createStepExecution().getExecutionContext());
    // read
    try {
        int count = 0;
        String line;
        while ((line = reader.read()) != null) {
            assertEquals(String.valueOf(count), line);
            count++;
        }
        assertEquals(EXPECTED_COUNT, count);
    } catch (Exception e) {
        throw e;
    } finally {
        reader.close();
    }
}

---

From source file:uk.ac.kcl.batch.JobConfiguration.java

@Bean
@StepScope/*from   w  w w.java 2s.co  m*/
@Qualifier("documentItemReader")
@Profile("jdbc_in")
public ItemReader<Document> documentItemReader(@Value("#{stepExecutionContext[minValue]}") String minValue,
        @Value("#{stepExecutionContext[maxValue]}") String maxValue,
        @Value("#{stepExecutionContext[min_time_stamp]}") String minTimeStamp,
        @Value("#{stepExecutionContext[max_time_stamp]}") String maxTimeStamp,
        @Qualifier("documentRowMapper") RowMapper<Document> documentRowmapper,
        @Qualifier("sourceDataSource") DataSource jdbcDocumentSource) throws Exception {

    JdbcPagingItemReader<Document> reader = new JdbcPagingItemReader<>();
    reader.setDataSource(jdbcDocumentSource);
    SqlPagingQueryProviderFactoryBean qp = new SqlPagingQueryProviderFactoryBean();
    qp.setSelectClause(env.getProperty("source.selectClause"));
    qp.setFromClause(env.getProperty("source.fromClause"));
    qp.setSortKey(env.getProperty("source.sortKey"));
    qp.setWhereClause(stepPartitioner.getPartitioningLogic(minValue, maxValue, minTimeStamp, maxTimeStamp));
    qp.setDataSource(jdbcDocumentSource);
    reader.setPageSize(Integer.parseInt(env.getProperty("source.pageSize")));
    reader.setQueryProvider(qp.getObject());
    reader.setRowMapper(documentRowmapper);
    return reader;
}

---

From source file:nu.yona.server.batch.jobs.ActivityAggregationBatchJob.java

private SqlPagingQueryProviderFactoryBean createQueryProviderFactory(Class<?> activityClass) {
    SqlPagingQueryProviderFactoryBean sqlPagingQueryProviderFactoryBean = new SqlPagingQueryProviderFactoryBean();
    sqlPagingQueryProviderFactoryBean.setDataSource(dataSource);
    sqlPagingQueryProviderFactoryBean.setSelectClause("select id");
    sqlPagingQueryProviderFactoryBean.setFromClause("from interval_activities");
    sqlPagingQueryProviderFactoryBean.setWhereClause("where dtype = '" + activityClass.getSimpleName()
            + "' and aggregates_computed = 0 and start_date <= :cutOffDate");
    sqlPagingQueryProviderFactoryBean.setSortKey("id");
    return sqlPagingQueryProviderFactoryBean;
}

---

From source file:org.copperengine.spring.audit.AuditTrailQueryEngine.java

@Override
public List<AuditTrailInfo> getAuditTrails(String transactionId, String conversationId, String correlationId,
        Integer level, int maxResult) {

    SqlPagingQueryProviderFactoryBean factory = new SqlPagingQueryProviderFactoryBean();

    String sortClause = "SEQ_ID";
    String whereClause = "where 1=1 ";
    List<Object> args = new ArrayList<Object>();

    if (level != null) {
        whereClause += " and LOGLEVEL <= ? ";
        sortClause = "LOGLEVEL";
        args.add(level);/*from   w w  w. j ava 2 s. c  o m*/
    }
    if (StringUtils.hasText(correlationId)) {
        whereClause += " and CORRELATION_ID = ? ";
        sortClause = "CORRELATION_ID";
        args.add(correlationId);
    }

    if (StringUtils.hasText(conversationId)) {
        whereClause += " and CONVERSATION_ID = ? ";
        sortClause = "CONVERSATION_ID";
        args.add(conversationId);
    }

    if (StringUtils.hasText(transactionId)) {
        whereClause += " and TRANSACTION_ID = ? ";
        sortClause = "TRANSACTION_ID";
        args.add(transactionId);
    }

    String selectClause = "select " + "SEQ_ID," + "TRANSACTION_ID," + "CONVERSATION_ID," + "CORRELATION_ID,"
            + "OCCURRENCE," + "LOGLEVEL," + "CONTEXT," + "INSTANCE_ID," + "MESSAGE_TYPE";

    factory.setDataSource(getDataSource());
    factory.setFromClause("from COP_AUDIT_TRAIL_EVENT ");

    factory.setSelectClause(selectClause);

    factory.setWhereClause(whereClause);
    factory.setSortKey(sortClause);

    PagingQueryProvider queryProvider = null;
    try {
        queryProvider = (PagingQueryProvider) factory.getObject();
    } catch (Exception e) {
        logger.error(e.getMessage(), e);
        return null;
    }

    String query = queryProvider.generateFirstPageQuery(maxResult);

    // this.getJdbcTemplate().setQueryTimeout(1000);

    long start = System.currentTimeMillis();
    RowMapper<AuditTrailInfo> rowMapper = new RowMapper<AuditTrailInfo>() {

        public AuditTrailInfo mapRow(ResultSet rs, int arg1) throws SQLException {

            return new AuditTrailInfo(rs.getLong("SEQ_ID"), rs.getString("TRANSACTION_ID"),
                    rs.getString("CONVERSATION_ID"), rs.getString("CORRELATION_ID"),
                    rs.getTimestamp("OCCURRENCE").getTime(), rs.getInt("LOGLEVEL"), rs.getString("CONTEXT"),
                    rs.getString("INSTANCE_ID"), rs.getString("MESSAGE_TYPE"));
        }

    };
    List<AuditTrailInfo> res = this.getJdbcTemplate().query(query, rowMapper, args.toArray());

    long end = System.currentTimeMillis();

    logger.info("query took: " + (end - start) + " ms : " + query);

    return res;
}

---