List of usage examples for org.springframework.batch.item.database.support SqlPagingQueryProviderFactoryBean setSelectClause
public void setSelectClause(String selectClause)
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: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:de.langmi.spring.batch.examples.readers.jdbc.JdbcPagingItemReaderTests.java
@Test public void testWithFactory() throws Exception { // setup queryProviderFactory SqlPagingQueryProviderFactoryBean factory = new SqlPagingQueryProviderFactoryBean(); factory.setDataSource(dataSource);//from w w w.j a v a2 s .co 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.JdbcSearchableStepExecutionDao.java
/** * @return a {@link PagingQueryProvider} with a where clause to narrow the * query/*from ww w .jav a2 s . c o m*/ * @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:uk.ac.kcl.batch.JobConfiguration.java
@Bean @StepScope/* w w w . jav a 2 s .c o 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:admin.service.JdbcSearchableJobExecutionDao.java
/** * @return a {@link PagingQueryProvider} with a where clause to narrow the * query//from w w w . j ava 2s . 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: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);//w ww. j a v a2 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; }
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 2 s . 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);/*from w w w .ja 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; }