使用Spring Batch从DB2提取数据并保存在MongoDB中，而无需元数据表 [英] Fetch the Data from DB2 and save in MongoDB using Spring Batch without metadata tables
本文介绍了使用Spring Batch从DB2提取数据并保存在MongoDB中，而无需元数据表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我需要从Db2表中获取所有数据并将其复制到mongoDB。我正在使用没有元数据表的Spring批处理。我正在使用JpaPagingItemReader从Db2获取数据。我能够从第一个获取的Db2中获取数据，该获取基于PageSize属性并成功保存在mongoDB中，但对于下一组数据，在使用分页获取数据时出错。
I need to fetch all the data from Db2 table and copy to mongoDB. I am using Spring batch without metadata tables. I am using JpaPagingItemReader for fetching data from Db2. I am able to get the data from Db2 for the first fetch which based on the PageSize attribute and save in mongoDB successfully but for the next set of data, getting error in fetching the data with the pagination.
出现以下错误：
15:17:36,085 INFO [stdout] (default task-2) 2017-12-06 15:17:36.084 INFO 7088 --- [ default task-2] c.t.controller.ItemController : About to copy All Item
15:17:36,135 INFO [stdout] (default task-2) 2017-12-06 15:17:36.135 INFO 7088 --- [ default task-2] o.s.b.c.l.support.SimpleJobLauncher : Job: [SimpleJob: [name=readDB2]] launched with the following parameters: [{}]
15:17:36,180 INFO [stdout] (default task-2) 2017-12-06 15:17:36.180 INFO 7088 --- [ default task-2] o.s.batch.core.job.SimpleStepHandler : Executing step: [step1]
15:17:36,215 INFO [stdout] (default task-2) Hibernate: select itm0_.itm_nbr as t1_4_, itm0_.itm_des_txt as itm_des13_4_ from itm itm0_ fetch first 10 rows only
15:17:36,884 INFO [stdout] (default task-2) 2017-12-06 15:17:36.884 DEBUG 7088 --- [ default task-2] o.s.data.mongodb.core.MongoTemplate : Saving DBObject containing fields: [_class, itmDesTxt, itemId]
15:17:38,621 INFO [stdout] (default task-2) 2017-12-06 15:17:38.621 INFO 7088 --- [ default task-2] org.mongodb.driver.connection : Opened connection [connectionId{localValue:3, serverValue:1998}] to lxmdbpmdmdev001:27017
15:17:38,894 INFO [stdout] (default task-2) 2017-12-06 15:17:38.894 DEBUG 7088 --- [ default task-2] o.s.data.mongodb.core.MongoTemplate : Saving DBObject containing fields: [_class, itmDesTxt, itemId]
15:17:39,139 INFO [stdout] (default task-2) 2017-12-06 15:17:39.139 DEBUG 7088 --- [ default task-2] o.s.data.mongodb.core.MongoTemplate : Saving DBObject containing fields: [_class, itmDesTxt, itemId]
15:17:39,386 INFO [stdout] (default task-2) 2017-12-06 15:17:39.386 DEBUG 7088 --- [ default task-2] o.s.data.mongodb.core.MongoTemplate : Saving DBObject containing fields: [_class, itmDesTxt, itemId]
15:17:39,632 INFO [stdout] (default task-2) 2017-12-06 15:17:39.632 DEBUG 7088 --- [ default task-2] o.s.data.mongodb.core.MongoTemplate : Saving DBObject containing fields: [_class, itmDesTxt, itemId]
15:17:39,881 INFO [stdout] (default task-2) 2017-12-06 15:17:39.881 DEBUG 7088 --- [ default task-2] o.s.data.mongodb.core.MongoTemplate : Saving DBObject containing fields: [_class, itmDesTxt, itemId]
15:17:40,123 INFO [stdout] (default task-2) 2017-12-06 15:17:40.123 DEBUG 7088 --- [ default task-2] o.s.data.mongodb.core.MongoTemplate : Saving DBObject containing fields: [_class, itmDesTxt, itemId]
15:17:40,377 INFO [stdout] (default task-2) 2017-12-06 15:17:40.377 DEBUG 7088 --- [ default task-2] o.s.data.mongodb.core.MongoTemplate : Saving DBObject containing fields: [_class, itmDesTxt, itemId]
15:17:40,619 INFO [stdout] (default task-2) 2017-12-06 15:17:40.619 DEBUG 7088 --- [ default task-2] o.s.data.mongodb.core.MongoTemplate : Saving DBObject containing fields: [_class, itmDesTxt, itemId]
15:17:40,865 INFO [stdout] (default task-2) 2017-12-06 15:17:40.865 DEBUG 7088 --- [ default task-2] o.s.data.mongodb.core.MongoTemplate : Saving DBObject containing fields: [_class, itmDesTxt, itemId]
15:17:41,109 INFO [stdout] (default task-2) Hibernate: select * from ( select inner2_.*, rownumber() over(order by order of inner2_) as rownumber_ from ( select itm0_.itm_nbr as t1_4_, itm0_.itm_des_txt as itm_des13_4_ from itm itm0_ fetch first 20 rows only ) as inner2_ ) as inner1_ where rownumber_ > 10 order by rownumber_
15:17:41,346 WARN [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-2) SQL Error: -199, SQLState: 42601
15:17:41,347 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-2) DB2 SQL Error: SQLCODE=-199, SQLSTATE=42601, SQLERRMC=OF;;??( [ DESC ASC NULLS RANGE CONCAT || / MICROSECONDS MICROSECOND, DRIVER=4.18.60
15:17:41,347 WARN [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-2) SQL Error: -516, SQLState: 26501
15:17:41,347 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-2) DB2 SQL Error: SQLCODE=-516, SQLSTATE=26501, SQLERRMC=null, DRIVER=4.18.60
15:17:41,347 WARN [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-2) SQL Error: -514, SQLState: 26501
15:17:41,347 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-2) DB2 SQL Error: SQLCODE=-514, SQLSTATE=26501, SQLERRMC=SQL_CURLN200C1, DRIVER=4.18.60
15:17:41,362 INFO [stdout] (default task-2) 2017-12-06 15:17:41.362 ERROR 7088 --- [ default task-2] o.s.batch.core.step.AbstractStep : Encountered an error executing step step1 in job readDB2
15:17:41,362 INFO [stdout] (default task-2)
15:17:41,362 INFO [stdout] (default task-2) javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
15:17:41,362 INFO [stdout] (default task-2) at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1692) ~[hibernate-entitymanager-5.0.12.Final.jar:5.0.12.Final]
15:17:41,362 INFO [stdout] (default task-2) at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1602) ~[hibernate-entitymanager-5.0.12.Final.jar:5.0.12.Final]
15:17:41,362 INFO [stdout] (default task-2) at org.hibernate.jpa.internal.QueryImpl.getResultList(QueryImpl.java:492) ~[hibernate-entitymanager-5.0.12.Final.jar:5.0.12.Final]
15:17:41,362 INFO [stdout] (default task-2) at org.springframework.batch.item.database.JpaPagingItemReader.doReadPage(JpaPagingItemReader.java:225) ~[spring-batch-infrastructure-3.0.8.RELEASE.jar:3.0.8.RELEASE]
15:17:41,362 INFO [stdout] (default task-2) at org.springframework.batch.item.database.AbstractPagingItemReader.doRead(AbstractPagingItemReader.java:108) ~[spring-batch-infrastructure-3.0.8.RELEASE.jar:3.0.8.RELEASE]
15:17:41,362 INFO [stdout] (default task-2) at org.springframework.batch.item.support.AbstractItemCountingItemStreamItemReader.read(AbstractItemCountingItemStreamItemReader.java:88) ~[spring-batch-infrastructure-3.0.8.RELEASE.jar:3.0.8.RELEASE]
15:17:41,362 INFO [stdout] (default task-2) at org.springframework.batch.core.step.item.SimpleChunkProvider.doRead(SimpleChunkProvider.java:91) ~[spring-batch-core-3.0.8.RELEASE.jar:3.0.8.RELEASE]
15:17:41,362 INFO [stdout] (default task-2) at org.springframework.batch.core.step.item.SimpleChunkProvider.read(SimpleChunkProvider.java:157) ~[spring-batch-core-3.0.8.RELEASE.jar:3.0.8.RELEASE]
15:17:41,363 INFO [stdout] (default task-2) at org.springframework.batch.core.step.item.SimpleChunkProvider$1.doInIteration(SimpleChunkProvider.java:116) ~[spring-batch-core-3.0.8.RELEASE.jar:3.0.8.RELEASE]
15:17:41,363 INFO [stdout] (default task-2) at org.springframework.batch.repeat.support.RepeatTemplate.getNextResult(RepeatTemplate.java:374) ~[spring-batch-infrastructure-3.0.8.RELEASE.jar:3.0.8.RELEASE]
15:17:41,363 INFO [stdout] (default task-2) at org.springframework.batch.repeat.support.RepeatTemplate.executeInternal(RepeatTemplate.java:215) ~[spring-batch-infrastructure-3.0.8.RELEASE.jar:3.0.8.RELEASE]
15:17:41,363 INFO [stdout] (default task-2) at org.springframework.batch.repeat.support.RepeatTemplate.iterate(RepeatTemplate.java:144) ~[spring-batch-infrastructure-3.0.8.RELEASE.jar:3.0.8.RELEASE]
15:17:41,363 INFO [stdout] (default task-2) at org.springframework.batch.core.step.item.SimpleChunkProvider.provide(SimpleChunkProvider.java:110) ~[spring-batch-core-3.0.8.RELEASE.jar:3.0.8.RELEASE]
15:17:41,363 INFO [stdout] (default task-2) at org.springframework.batch.core.step.item.ChunkOrientedTasklet.execute(ChunkOrientedTasklet.java:69) ~[spring-batch-core-3.0.8.RELEASE.jar:3.0.8.RELEASE]
15:17:41,363 INFO [stdout] (default task-2) at org.springframework.batch.core.step.tasklet.TaskletStep$ChunkTransactionCallback.doInTransaction(TaskletStep.java:406) ~[spring-batch-core-3.0.8.RELEASE.jar:3.0.8.RELEASE]
15:17:41,363 INFO [stdout] (default task-2) at org.springframework.batch.core.step.tasklet.TaskletStep$ChunkTransactionCallback.doInTransaction(TaskletStep.java:330) ~[spring-batch-core-3.0.8.RELEASE.jar:3.0.8.RELEASE]
15:17:41,363 INFO [stdout] (default task-2) at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:133) ~[spring-tx-4.3.12.RELEASE.jar:4.3.12.RELEASE]
15:17:41,363 INFO [stdout] (default task-2) at org.springframework.batch.core.step.tasklet.TaskletStep$2.doInChunkContext(TaskletStep.java:272) ~[spring-batch-core-3.0.8.RELEASE.jar:3.0.8.RELEASE]
15:17:41,363 INFO [stdout] (default task-2) at org.springframework.batch.core.scope.context.StepContextRepeatCallback.doInIteration(StepContextRepeatCallback.java:81) ~[spring-batch-core-3.0.8.RELEASE.jar:3.0.8.RELEASE]
15:17:41,376 INFO [stdout] (default task-2) at org.springframework.batch.repeat.support.RepeatTemplate.getNextResult(RepeatTemplate.java:374) ~[spring-batch-infrastructure-3.0.8.RELEASE.jar:3.0.8.RELEASE]
15:17:41,376 INFO [stdout] (default task-2) at org.springframework.batch.repeat.support.RepeatTemplate.executeInternal(RepeatTemplate.java:215) ~[spring-batch-infrastructure-3.0.8.RELEASE.jar:3.0.8.RELEASE]
15:17:41,376 INFO [stdout] (default task-2) at org.springframework.batch.repeat.support.RepeatTemplate.iterate(RepeatTemplate.java:144) ~[spring-batch-infrastructure-3.0.8.RELEASE.jar:3.0.8.RELEASE]
15:17:41,376 INFO [stdout] (default task-2) at org.springframework.batch.core.step.tasklet.TaskletStep.doExecute(TaskletStep.java:257) ~[spring-batch-core-3.0.8.RELEASE.jar:3.0.8.RELEASE]
15:17:41,376 INFO [stdout] (default task-2) at org.springframework.batch.core.step.AbstractStep.execute(AbstractStep.java:200) ~[spring-batch-core-3.0.8.RELEASE.jar:3.0.8.RELEASE]
15:17:41,376 INFO [stdout] (default task-2) at org.springframework.batch.core.job.SimpleStepHandler.handleStep(SimpleStepHandler.java:148) [spring-batch-core-3.0.8.RELEASE.jar:3.0.8.RELEASE]
15:17:41,376 INFO [stdout] (default task-2) at org.springframework.batch.core.job.AbstractJob.handleStep(AbstractJob.java:392) [spring-batch-core-3.0.8.RELEASE.jar:3.0.8.RELEASE]
15:17:41,376 INFO [stdout] (default task-2) at org.springframework.batch.core.job.SimpleJob.doExecute(SimpleJob.java:135) [spring-batch-core-3.0.8.RELEASE.jar:3.0.8.RELEASE]
15:17:41,376 INFO [stdout] (default task-2) at org.springframework.batch.core.job.AbstractJob.execute(AbstractJob.java:306) [spring-batch-core-3.0.8.RELEASE.jar:3.0.8.RELEASE]
15:17:41,376 INFO [stdout] (default task-2) at org.springframework.batch.core.launch.support.SimpleJobLauncher$1.run(SimpleJobLauncher.java:135) [spring-batch-core-3.0.8.RELEASE.jar:3.0.8.RELEASE]
15:17:41,376 INFO [stdout] (default task-2) at org.springframework.core.task.SyncTaskExecutor.execute(SyncTaskExecutor.java:50) [spring-core-4.3.12.RELEASE.jar:4.3.12.RELEASE]
15:17:41,376 INFO [stdout] (default task-2) at org.springframework.batch.core.launch.support.SimpleJobLauncher.run(SimpleJobLauncher.java:128) [spring-batch-core-3.0.8.RELEASE.jar:3.0.8.RELEASE]
15:17:41,376 INFO [stdout] (default task-2) at com.test.service.impl.ItemServiceImpl.itemCopy(ItemServiceImpl.java:266) [classes:na]
15:17:41,376 INFO [stdout] (default task-2) at com.test.controller.ItemController.allItemCopy(ItemController.java:48) [classes:na]
....
15:17:41,410 INFO [stdout] (default task-2) 2017-12-06 15:17:41.410 INFO 7088 --- [ default task-2] o.s.b.c.l.support.SimpleJobLauncher : Job: [SimpleJob: [name=readDB2]] completed with the following parameters: [{}] and the following status: [FAILED]
批处理配置：
@Configuration
@EnableBatchProcessing
public class ItemBatch {
@Autowired
private JobBuilderFactory jobBuilderFactory;
@Autowired
private StepBuilderFactory stepBuilderFactory;
@Autowired
EntityManagerFactory entityManagerFactory;
@Autowired
MongoTemplate mongoTemplate;
@Bean
public Job readDB2() {
return jobBuilderFactory.get("readDB2").start(step1()).build();
}
@Bean
public Step step1() {
return stepBuilderFactory.get("step1")
.<Itm, com.test.model.mongodb.Itm> chunk(5)
.reader(reader())
.writer(writer())
.build();
}
@Bean
public ItemStreamReader<Itm> reader() {
JpaPagingItemReader<Itm> databaseReader = new JpaPagingItemReader<>();
try {
databaseReader.setEntityManagerFactory(entityManagerFactory);
JpaQueryProviderImpl<Itm> jpaQueryProvider = new JpaQueryProviderImpl<>();
jpaQueryProvider.setQuery("Itm.findAll");
databaseReader.setQueryProvider(jpaQueryProvider);
databaseReader.setPageSize(10);
databaseReader.afterPropertiesSet();
} catch (Exception e) {
System.err.println("Error :" + e);
}
return databaseReader;
}
@Bean
public MongoItemWriter<com.test.model.mongodb.Itm> writer() {
MongoItemWriter<com.test.model.mongodb.Itm> writer = new MongoItemWriter<>();
try {
writer.setTemplate(mongoTemplate);
} catch (Exception e) {
e.printStackTrace();
}
writer.setCollection("itm");
return writer;
}
}
内存配置，而不是元数据：
In-Memory Configuration instead of metadata:
@Configuration
public class SpringBatchTestConfiguration {
@Bean
public ResourcelessTransactionManager transactionManager() {
return new ResourcelessTransactionManager();
}
@Bean
public MapJobRepositoryFactoryBean mapJobRepositoryFactory(ResourcelessTransactionManager txManager)
throws Exception {
MapJobRepositoryFactoryBean factory = new MapJobRepositoryFactoryBean(txManager);
factory.afterPropertiesSet();
return factory;
}
@Bean
public JobRepository jobRepository(MapJobRepositoryFactoryBean factory) throws Exception {
return factory.getObject();
}
@Bean
public JobExplorer jobExplorer(MapJobRepositoryFactoryBean factory) {
return new SimpleJobExplorer(factory.getJobInstanceDao(), factory.getJobExecutionDao(),
factory.getStepExecutionDao(), factory.getExecutionContextDao());
}
@Bean
public SimpleJobLauncher jobLauncher(JobRepository jobRepository) {
SimpleJobLauncher launcher = new SimpleJobLauncher();
launcher.setJobRepository(jobRepository);
return launcher;
}
}
QueryProvider：
QueryProvider:
public class JpaQueryProviderImpl<E> extends AbstractJpaQueryProvider {
private Class<E> entityClass;
private String query;
@Override
public Query createQuery() {
return getEntityManager().createNamedQuery(query, entityClass);
}
public void setQuery(String query) {
this.query = query;
}
public void setEntityClass(Class<E> entityClazz) {
this.entityClass = entityClazz;
}
@Override
public void afterPropertiesSet() throws Exception {
Assert.isTrue(StringUtils.hasText(query), "Query cannot be empty");
Assert.notNull(entityClass, "Entity class cannot be NULL");
}
}
实体：
@Entity
@Table(name="ITM")
@NamedQueries({ @NamedQuery(name = "Itm.findAll", query = "select i from Itm i") })
public class Itm implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@Column(name="ITEM_NBR")
private int itemId;
@Column(name="ITM_DES_TXT")
private String itmDesTxt;
//getter and setter
}
推荐答案
根本原因是此错误：
DB2 SQL错误：SQLCODE = -199，SQLSTATE = 42601，SQLERRMC = OF ;; ??（[DESC ASC NULLS RANGE CONCAT || / MICROSECONDS MICROSECOND，DRIVER = 4.18.60
DB2 SQL Error: SQLCODE=-199, SQLSTATE=42601, SQLERRMC=OF;;??( [ DESC ASC NULLS RANGE CONCAT || / MICROSECONDS MICROSECOND, DRIVER=4.18.60
这会指出 OF 关键字是意外的。
which points you to the OF keyword being unexpected.
ORDER BY ORDER OF < OLAP规范中的/ code>在Db2 for z / OS v11中无效，因为可以在手册中看到。您需要通过一些显式表达式重写语句以进行排序-或跳过 ORDER完全由BY 来完成，因为您始终没有订购 inner2 _ 子选择。
ORDER BY ORDER OF in the OLAP specification is not valid in Db2 for z/OS v11, as can be seen in the manual. You'll need to rewrite your statement to order by some explicit expression -- or skip ORDER BY altogether since you're not ordering the inner2_ subselect anyway.
这篇关于使用Spring Batch从DB2提取数据并保存在MongoDB中，而无需元数据表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
