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

List of usage examples for org.springframework.batch.item.database.support SqlPagingQueryProviderFactoryBean SqlPagingQueryProviderFactoryBean

  1. HOME
  2. Java
  3. org.springframework
  4. org.springframework.batch.item.database.support.*
  5. SqlPagingQueryProviderFactoryBean
  6. SqlPagingQueryProviderFactoryBean

Introduction

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

Prototype

SqlPagingQueryProviderFactoryBean

Source Link

Usage

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   ww w  .ja va  2s. 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;
}

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  w w  . j av a  2 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:admin.service.JdbcSearchableJobExecutionDao.java

/**
 * @return a {@link PagingQueryProvider} with a where clause to narrow the
 * query//from w w w.  java2  s .c  o  m
 * @throws Exception
 */
private PagingQueryProvider getPagingQueryProvider(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));
    factory.setSelectClause(FIELDS);
    Map<String, Order> sortKeys = new HashMap<String, Order>();
    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 (PagingQueryProvider) factory.getObject();
}

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:admin.service.JdbcSearchableStepExecutionDao.java

/**
 * @return a {@link PagingQueryProvider} with a where clause to narrow the
 * query/* w  w w .j  a  v  a 2  s . c om*/
 * @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);
    }
}

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:uk.ac.kcl.batch.JobConfiguration.java

@Bean
@StepScope//from   w  w  w  .j a v a  2  s.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:org.springframework.batch.item.database.JdbcPagingItemReaderAsyncTests.java

protected ItemReader<Foo> getItemReader() throws Exception {

    JdbcPagingItemReader<Foo> reader = new JdbcPagingItemReader<Foo>();
    reader.setDataSource(dataSource);/*from  ww w . j  a v  a 2s . c om*/
    SqlPagingQueryProviderFactoryBean factory = new SqlPagingQueryProviderFactoryBean();
    factory.setDataSource(dataSource);
    factory.setSelectClause("select ID, NAME, VALUE");
    factory.setFromClause("from T_FOOS");
    Map<String, Order> sortKeys = new LinkedHashMap<String, Order>();
    sortKeys.put("VALUE", Order.ASCENDING);
    factory.setSortKeys(sortKeys);
    reader.setQueryProvider(factory.getObject());
    reader.setRowMapper(new RowMapper<Foo>() {
        @Override
        public Foo mapRow(ResultSet rs, int i) throws SQLException {
            Foo foo = new Foo();
            foo.setId(rs.getInt(1));
            foo.setName(rs.getString(2));
            foo.setValue(rs.getInt(3));
            return foo;
        }
    });
    reader.setPageSize(PAGE_SIZE);
    reader.afterPropertiesSet();
    reader.setSaveState(false);

    return reader;
}

From source file:org.springframework.batch.item.database.JdbcPagingQueryIntegrationTests.java

protected PagingQueryProvider getPagingQueryProvider() throws Exception {

    SqlPagingQueryProviderFactoryBean factory = new SqlPagingQueryProviderFactoryBean();
    factory.setDataSource(dataSource);//from   w ww  .j a  v  a 2  s  . com
    factory.setSelectClause("select ID, NAME, VALUE");
    factory.setFromClause("from T_FOOS");
    Map<String, Order> sortKeys = new LinkedHashMap<String, Order>();
    sortKeys.put("VALUE", Order.ASCENDING);
    factory.setSortKeys(sortKeys);
    return factory.getObject();

}

From source file:org.springframework.batch.item.database.JdbcPagingRestartIntegrationTests.java

protected ItemReader<Foo> getItemReader() throws Exception {

    JdbcPagingItemReader<Foo> reader = new JdbcPagingItemReader<Foo>();
    reader.setDataSource(dataSource);/*  ww w  .  j a  v  a 2 s  .co  m*/
    SqlPagingQueryProviderFactoryBean factory = new SqlPagingQueryProviderFactoryBean();
    factory.setDataSource(dataSource);
    factory.setSelectClause("select ID, NAME, VALUE");
    factory.setFromClause("from T_FOOS");
    Map<String, Order> sortKeys = new LinkedHashMap<String, Order>();
    sortKeys.put("VALUE", Order.ASCENDING);
    factory.setSortKeys(sortKeys);
    reader.setQueryProvider(factory.getObject());
    reader.setRowMapper(new RowMapper<Foo>() {
        @Override
        public Foo mapRow(ResultSet rs, int i) throws SQLException {
            Foo foo = new Foo();
            foo.setId(rs.getInt(1));
            foo.setName(rs.getString(2));
            foo.setValue(rs.getInt(3));
            return foo;
        }
    });
    reader.setPageSize(pageSize);
    reader.afterPropertiesSet();
    reader.setSaveState(true);

    return reader;
}