bad SQL grammar [SELECT unique_tx_id, created_time FROM Transaction WHERE created_time BETWEEN ? AND ? ORDER BY unique_tx_id ASC LIMIT 10]

I am developing the Spring Boot Batch example. In this example I am getting the below error. Using spring-boot-starter-parent is 3.4.3.RELEASE.

I am trying to fetch records from mysql db which get inserted between today 10pm to yesterday 10pm when it running to fetch the records I getting below error.

org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [SELECT unique_tx_id, created_time FROM Transaction WHERE created_time BETWEEN ? AND ? ORDER BY unique_tx_id ASC LIMIT 10], 

I have tried multiple ways but still getting same.

Any suggestion or guidance to fix this issue:

BatchConfig:

@Configuration
@EnableBatchProcessing
public class BatchConfig {
    private final DataSource dataSource;
    private final ExcelWriter excelWriter;

    public BatchConfig(DataSource dataSource, ExcelWriter excelWriter) {
        this.dataSource = dataSource;
        this.excelWriter = excelWriter;
    }

    @Bean
    public Job transactionExportJob(
        JobRepository jobRepository, Step exportStep) {
        return new JobBuilder("transactionExportJob", jobRepository)
            .incrementer(new RunIdIncrementer())
            .start(exportStep)
            .build();
    }

    @Bean
    public Step exportStep(JobRepository jobRepository,
        PlatformTransactionManager transactionManager,
        ItemReader<Transaction> reader, ExcelWriter excelWriter) {
        return new StepBuilder("exportStep", jobRepository)
            .<Transaction, Transaction>chunk(5000, transactionManager)
            .reader(reader)
            .processor(new TransactionProcessor())
            .writer(excelWriter)
            .faultTolerant()
            .retryLimit(3)
            .retry(Exception.class)
            .skip(Exception.class)
            .skipLimit(100)
            .listener(new CustomSkipListener())
            .build();
    }

    @Bean
    public JdbcPagingItemReader<Transaction> reader(
        PagingQueryProvider transactionQueryProvider) {
        JdbcPagingItemReader<Transaction> reader = new JdbcPagingItemReader<>();
        reader.setDataSource(dataSource);
        reader.setFetchSize(5000);
        reader.setRowMapper(new TransactionRowMapper());
        reader.setQueryProvider(transactionQueryProvider);
        return reader;
    }
}

BatchScheduler:

@Component
public class BatchScheduler {
    private final JobLauncher jobLauncher;
    private final Job transactionExportJob;

    public BatchScheduler(JobLauncher jobLauncher, Job transactionExportJob) {
        this.jobLauncher = jobLauncher;
        this.transactionExportJob = transactionExportJob;
    }

    @Scheduled(cron = "0 0 22 * * ?", zone = "Asia/Kolkata")
    public void runBatchJob() {
        try {
            LocalDateTime startTime =
                LocalDateTime.now().minusDays(1).withHour(22);
            LocalDateTime endTime = LocalDateTime.now().withHour(22);
            JobParameters jobParameters =
                new JobParametersBuilder()
                    .addDate("startTime",
                        Date.from(startTime.atZone(ZoneId.of("Asia/Kolkata"))
                                      .toInstant()))
                    .addDate("endTime",
                        Date.from(endTime.atZone(ZoneId.of("Asia/Kolkata"))
                                      .toInstant()))
                    .toJobParameters();

            System.out.println("Batch Job started");
            JobExecution execution =
                jobLauncher.run(transactionExportJob, jobParameters);
            System.out.println(
                "Batch job completed with status: {}" + execution.getStatus());

        } catch (Exception e) {
            System.out.println(
                "Batch job execution failed: {}" + e.getMessage() + e);
        }
    }
}

TransactionQueryConfig class:

@Configuration
public class TransactionQueryConfig {
    @Bean
    public PagingQueryProvider transactionQueryProvider(DataSource dataSource) {
        SqlPagingQueryProviderFactoryBean provider =
            new SqlPagingQueryProviderFactoryBean();
        provider.setDataSource(dataSource);
        provider.setSelectClause("SELECT unique_tx_id, created_time");
        provider.setFromClause("FROM Transaction");
        provider.setWhereClause("WHERE created_time BETWEEN ? AND ?");
        provider.setSortKey("unique_tx_id");

        try {
            return provider.getObject(); // Convert to PagingQueryProvider
        } catch (Exception e) {
            throw new RuntimeException(
                "Error creating PagingQueryProvider: " + e.getMessage(), e);
        }
    }
}

TransactionRowMapper:

public class TransactionRowMapper implements RowMapper<Transaction> {
    @Override
    public Transaction mapRow(ResultSet rs, int rowNum) throws SQLException {
        return new Transaction(rs.getString("unique_tx_id"),
            rs.getTimestamp("created_time").toLocalDateTime());
    }
}

I am expecting code to run successfully and fetch the record from db.

You are using the wrong syntax ?, Change the setWhereClause clause to : annotation

provider.setWhereClause("WHERE created_time BETWEEN :from AND :to");