spring batch with spring boot using RepositoryItemReader nativeQuery

Question

Presently i am using Spring boot + Spring batch + Spring data jpa.

i am using RepositoryItemReader. with normal

    Page<QuoteOfferFulfillment> findByStatus(String status,
                    Pageable pageable);

its working fine and working as expected. but when i have use Native queries i am not getting any error but always results are coming 0. i have tried both ways:

    1. 

    @Query(value = "SELECT * FROM QUOTE_OFFER_FULFILLMENT QOF WHERE QOF.STATUS= (:status) OR QOF.STATUS=(:status1) AND QOF.RETRY_COUNT < 3 \n#pageable\n", countQuery = "SELECT count(*) FROM QUOTE_OFFER_FULFILLMENT QOF WHERE QOF.STATUS=(:status) OR QOF.STATUS=(:status1) AND QOF.RETRY_COUNT < 3 \n#pageable\n", nativeQuery = true)
        Page<QuoteOfferFulfillment> findByStatusAndRetryCount(
                @Param("status") String status,
                @Param("status1") String status1, Pageable pageable);

and second way

    2. Page<QuoteOfferFulfillment> findByStatusAndRetryCount(
                @Param("status") QuoteOfferFulfillmentStatus status,
                @Param("status1") QuoteOfferFulfillmentStatus status1, Pageable pageable);

in the Entity Class

    @SqlResultSetMappings({
            @SqlResultSetMapping(name = "SqlResultSetMapping.count", columns = @ColumnResult(name = "cnt")) })
    @NamedNativeQueries({
            @NamedNativeQuery(name = "QuoteOfferFulfillment.findByStatusAndRetryCount", resultClass = QuoteOfferFulfillment.class, query = "SELECT * FROM QUOTE_OFFER_FULFILLMENT WHERE STATUS= (:status) OR STATUS=(:status1) AND RETRY_COUNT < 3"),
            @NamedNativeQuery(name = "QuoteOfferFulfillment.findByStatusAndRetryCount.count", resultSetMapping = "SqlResultSetMapping.count", query = "SELECT count(*) as cnt FROM QUOTE_OFFER_FULFILLMENT WHERE STATUS= (:status) OR STATUS=(:status1) AND RETRY_COUNT < 3") })
    @Entity

    public class QuoteOfferFulfillment implements Serializable {

Other Batch Configuration

    @Configuration
    public class BatchScheduler {

        @Bean
        public ResourcelessTransactionManager resourcelessTransactionManager() {
            return new ResourcelessTransactionManager();
        }

        @Bean
        public JobRepository jobRepository() throws Exception {
            return new MapJobRepositoryFactoryBean(resourcelessTransactionManager()).getObject();
        }

        @Bean
        public ThreadPoolTaskExecutor taskExecutor() {
            ThreadPoolTaskExecutor taskExecutor = new ThreadPoolTaskExecutor();
            taskExecutor.setCorePoolSize(15);
            taskExecutor.setMaxPoolSize(20);
            taskExecutor.setQueueCapacity(30);
            return taskExecutor;
        }

        @Bean
        public SimpleJobLauncher jobLauncher(ThreadPoolTaskExecutor taskExecutor,
                JobRepository jobRepository) {
            SimpleJobLauncher launcher = new SimpleJobLauncher();
            launcher.setTaskExecutor(taskExecutor);
            launcher.setJobRepository(jobRepository);
            return launcher;
        }

    }

and

    @Configuration
    @EnableBatchProcessing
    @Import(BatchScheduler.class)
    public class BatchConfiguration {

    @Autowired
        public StepBuilderFactory stepBuilderFactory;

        @Autowired
        public JobBuilderFactory jobBuilderFactory;

        @Autowired
        public QuoteOfferFulfillmentRepository fulfillmentRepository;

        @Autowired
        private SimpleJobLauncher jobLauncher;

    @Scheduled(cron = "*/5 * * * * *")
        public void perform() throws Exception {

            System.out.println("Job Started at :" + new Date());

            JobParameters param = new JobParametersBuilder()
                    .addString("JobID", String.valueOf(System.currentTimeMillis())).toJobParameters();

            JobExecution execution = jobLauncher.run(job(), param);

            System.out.println("Job finished with status :" + execution.getStatus());
        }

     @Bean
        public RepositoryMetadata repositoryMetadata() {
            return new DefaultRepositoryMetadata(QuoteOfferFulfillmentRepository.class);
        }

        @Bean
        public RepositoryItemReader<QuoteOfferFulfillment> reader() {
            RepositoryItemReader<QuoteOfferFulfillment> fullfillment = new RepositoryItemReader<QuoteOfferFulfillment>();
            fullfillment.setRepository(fulfillmentRepository);
            fullfillment.setMethodName("findByStatusAndRetryCount");
            List<QuoteOfferFulfillmentStatus> list = new ArrayList<QuoteOfferFulfillmentStatus>();
            list.add("FULFILLMENT_READY");
            list.add("FAILED");
            fullfillment.setArguments(list);
            fullfillment.setPageSize(40);
            HashMap<String, Direction> sorts = new HashMap<>();
            sorts.put("id", Direction.DESC);
            fullfillment.setSort(sorts);
            return fullfillment;
        }

        @Bean
        public RepositoryItemWriter<QuoteOfferFulfillment> writer() {
            System.out.println("BatchConfiguration.writer()");
            RepositoryItemWriter<QuoteOfferFulfillment> itemWriter = new RepositoryItemWriter<QuoteOfferFulfillment>();
            itemWriter.setRepository(fulfillmentRepository);
            itemWriter.setMethodName("save");
            return itemWriter;
        }

        @Bean
        public Step step1() throws Exception {
            return this.stepBuilderFactory.get("step1")
                    .<QuoteOfferFulfillment, QuoteOfferFulfillment> chunk(1).reader(reader())
                    .processor(new SubmitionProcessor()).writer(writer()).build();
        }

        @Bean
        public Job job() throws Exception {
            System.out.println("BatchConfiguration2.job() =+> ");
            return this.jobBuilderFactory.get("job").incrementer(new RunIdIncrementer()).start(step1())
                    .listener(new JobCompletionListener()).build();

        }

    }

Where would be the wrong i have done. Kindly let me know your suggestions to go forward.

I have followed: https://stackoverflow.com/questions/38349930/spring-data-and-native-query-with-pagination — Balakrishna Tirumalasetti, Aug 14 '18 at 08:58
This is just a workaround as I can see. Have you tried to remove it? And do you have a unit test to see what SQL statements are generated? — Simon Martinelli, Aug 14 '18 at 10:47

score 1 · Answer 1 · answered Aug 14 '18 at 23:24

But i have resolved in other way:

@Bean
    public ItemReader<QuoteOfferFulfillment> reader() {
        JpaPagingItemReader<QuoteOfferFulfillment> fullfillment = new JpaPagingItemReader<>();
        try {
            String sqlQuery = "SELECT * FROM FULFILLMENT QOF WHERE QOF.STATUS='"
                    + FULFILLMENT_READY + "'" + " OR QOF.STATUS='"
                    + FAILED + "'" + " AND QOF.RETRY_COUNT < 3";
            JpaNativeQueryProvider<QuoteOfferFulfillment> queryProvider = new JpaNativeQueryProvider<QuoteOfferFulfillment>();
            queryProvider.setSqlQuery(sqlQuery);
            queryProvider.setEntityClass(QuoteOfferFulfillment.class);
            queryProvider.afterPropertiesSet();
            fullfillment.setEntityManagerFactory(entityManagerFactory);
            fullfillment.setPageSize(3);
            fullfillment.setQueryProvider(queryProvider);
            fullfillment.afterPropertiesSet();
            fullfillment.setSaveState(true);
        }
        catch (Exception e) {
            System.out.println("BatchConfiguration.reader() ==> error " + e.getMessage());
        }
        return fullfillment;
    }

Do we have any other way using RepositoryItemReader. the reason is using RepositoryItemReader we can follow standard nativequeries (@Query) using Repository interface itself.

We just did same setup with `nativeQuery=true` in `@Query` and `RepositoryItemReader`. Everything works great (Spring batch 3.0.6). But remember that you need to trim the values and columns when you are using char types in stead of varchar2 in the DB — chillworld, Jul 03 '19 at 09:01

spring batch with spring boot using RepositoryItemReader nativeQuery

1 Answers1