infrastructure

infrastructure

关注
发信
关注(28)粉丝(399)

使用Spring Batch从DB2提取数据并保存在MongoDB中，而无需元数据表

本文介绍了使用Spring Batch从DB2提取数据并保存在MongoDB中，而无需元数据表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 29岁程序员，3月因学历无情被辞！ 我需要从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 Item15: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 only15: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:2701715: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: 4260115: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.6015:17:41,347 WARN [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-2) SQL Error: -516, SQLState: 2650115: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.6015:17:41,347 WARN [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-2) SQL Error: -514, SQLState: 2650115: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.6015: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 readDB215: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 ResultSet15: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@EnableBatchProcessingpublic 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:@Configurationpublic 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}推荐答案根本原因是此错误：这会指出 OF 关键字是意外的。which points you to the OF keyword being unexpected. ORDER BY ORDER OF 在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中，而无需元数据表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！
09-06 03:00
Powered by LMLPHP ©2024 infrastructure 0.050918