Spring Batch JdbcPagingItemReader missing parameter

1
votes

I have a Spring Batch application in which i have to export a partition based on a config table, using Oracle Data Dump. In the config table I have information like days to run the export, name of the table to export and so on. This is my query provider

private PagingQueryProvider queryProviderStep1() throws Exception {
        SqlPagingQueryProviderFactoryBean queryProvider = new SqlPagingQueryProviderFactoryBean();
        queryProvider.setDataSource(infraConfig.dataSourceLocal());
        queryProvider.setDatabaseType("ORACLE");
        queryProvider
                .setSelectClause("SELECT part.partition_name, config.ID_CONFIG_DATA_EXPORT, config.SERVIDOR_ORIGEM, "
                        + "config.SERVIDOR_DESTINO, config.CAMINHO_DESTINO, config.NOME_TABELA, config.TEMPO_RETENCAO_BD, "
                        + "config.TEMPO_RETENCAO_TAPELIBRARY, config.TEMPO_DELAY, config.FREQUENCIA_EXECUCAO ");
        queryProvider.setFromClause("FROM user_tab_partitions@CDRONE_RAC part "
                + "INNER JOIN CONFIG_DATA_EXPORT config ON config.NOME_TABELA = part.TABLE_NAME "
                + "LEFT JOIN CONFIG_DATA_EXPORT_LOG exlog ON config.ID_CONFIG_DATA_EXPORT = exlog.ID_CONFIG_DATA_EXPORT ");
        queryProvider.setWhereClause(" WHERE exlog.ID_CONFIG_DATA_EXPORT IS NULL");
        queryProvider.setSortKey("config.id_config_data_export");
        return queryProvider.getObject();

The problem I have is when Spring generates the 'remainingPagesSql' which it includes ' AND ((config.id_config_data_export > ?))' at the end of the query. I was expecting that Spring would automatically use the sortKey as the parameter, but it does not and gives me the error:

org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [SELECT * FROM (SELECT part.partition_name, config.ID_CONFIG_DATA_EXPORT, config.SERVIDOR_ORIGEM, config.SERVIDOR_DESTINO, config.CAMINHO_DESTINO, config.NOME_TABELA, config.TEMPO_RETENCAO_BD, config.TEMPO_RETENCAO_TAPELIBRARY, config.TEMPO_DELAY, config.FREQUENCIA_EXECUCAO FROM user_tab_partitions@CDRONE_RAC part INNER JOIN CONFIG_DATA_EXPORT config ON config.NOME_TABELA = part.TABLE_NAME LEFT JOIN CONFIG_DATA_EXPORT_LOG exlog ON config.ID_CONFIG_DATA_EXPORT = exlog.ID_CONFIG_DATA_EXPORT WHERE exlog.ID_CONFIG_DATA_EXPORT IS NULL ORDER BY config.id_config_data_export ASC) WHERE ROWNUM <= 10 AND ((config.id_config_data_export > ?))]; SQL state [99999]; error code [17041];  Missing IN or OUT parameter at index:: 1; nested exception is java.sql.SQLException:  Missing IN or OUT parameter at index:: 1
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:84) ~[spring-jdbc-4.3.10.RELEASE.jar:4.3.10.RELEASE]
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-4.3.10.RELEASE.jar:4.3.10.RELEASE]
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-4.3.10.RELEASE.jar:4.3.10.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:649) ~[spring-jdbc-4.3.10.RELEASE.jar:4.3.10.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:684) ~[spring-jdbc-4.3.10.RELEASE.jar:4.3.10.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:716) ~[spring-jdbc-4.3.10.RELEASE.jar:4.3.10.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:726) ~[spring-jdbc-4.3.10.RELEASE.jar:4.3.10.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:776) ~[spring-jdbc-4.3.10.RELEASE.jar:4.3.10.RELEASE]
    at org.springframework.batch.item.database.JdbcPagingItemReader.doReadPage(JdbcPagingItemReader.java:222) ~[spring-batch-infrastructure-3.0.8.RELEASE.jar:3.0.8.RELEASE]
    at org.springframework.batch.item.database.AbstractPagingItemReader.doRead(AbstractPagingItemReader.java:108) ~[spring-batch-infrastructure-3.0.8.RELEASE.jar:3.0.8.RELEASE]
    at org.springframework.batch.item.support.AbstractItemCountingItemStreamItemReader.read(AbstractItemCountingItemStreamItemReader.java:88) ~[spring-batch-infrastructure-3.0.8.RELEASE.jar:3.0.8.RELEASE]
    at org.springframework.batch.core.step.item.SimpleChunkProvider.doRead(SimpleChunkProvider.java:91) ~[spring-batch-core-3.0.8.RELEASE.jar:3.0.8.RELEASE]
    at org.springframework.batch.core.step.item.SimpleChunkProvider.read(SimpleChunkProvider.java:157) ~[spring-batch-core-3.0.8.RELEASE.jar:3.0.8.RELEASE]
    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]
    at org.springframework.batch.repeat.support.RepeatTemplate.getNextResult(RepeatTemplate.java:374) ~[spring-batch-infrastructure-3.0.8.RELEASE.jar:3.0.8.RELEASE]
    at org.springframework.batch.repeat.support.RepeatTemplate.executeInternal(RepeatTemplate.java:215) ~[spring-batch-infrastructure-3.0.8.RELEASE.jar:3.0.8.RELEASE]
    at org.springframework.batch.repeat.support.RepeatTemplate.iterate(RepeatTemplate.java:144) ~[spring-batch-infrastructure-3.0.8.RELEASE.jar:3.0.8.RELEASE]
    at org.springframework.batch.core.step.item.SimpleChunkProvider.provide(SimpleChunkProvider.java:110) ~[spring-batch-core-3.0.8.RELEASE.jar:3.0.8.RELEASE]
    at org.springframework.batch.core.step.item.ChunkOrientedTasklet.execute(ChunkOrientedTasklet.java:69) ~[spring-batch-core-3.0.8.RELEASE.jar:3.0.8.RELEASE]
    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]
    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]
    at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:133) ~[spring-tx-4.3.10.RELEASE.jar:4.3.10.RELEASE]
    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]
    at org.springframework.batch.core.scope.context.StepContextRepeatCallback.doInIteration(StepContextRepeatCallback.java:81) ~[spring-batch-core-3.0.8.RELEASE.jar:3.0.8.RELEASE]
    at org.springframework.batch.repeat.support.TaskExecutorRepeatTemplate$ExecutingRunnable.run(TaskExecutorRepeatTemplate.java:262) ~[spring-batch-infrastructure-3.0.8.RELEASE.jar:3.0.8.RELEASE]
    at java.lang.Thread.run(Thread.java:745) ~[na:1.8.0_121]
Caused by: java.sql.SQLException:  Missing IN or OUT parameter at index:: 1

I've tried to map a parameter but I don't have or don't know how to use this parameter as it's based on the PAGE_SIZE, so in each time the query is executed, the value changes.

FIRST EDIT
I've updated the sortKey to "id_config_data_export", without the "config.". But the error still happens.

1
springspring-batch
Does the SQL run in a SQL client? - Michael Minella
It runs without the '((config.id_config_data_export > ?))', but with it I get ORA-00904 string: invalid identifier, but i don't understand why because this is my primary key, and it's working. - Ivan Arantes
I don't see it in the stack trace...are you getting the right type of SqlQueryProvider back from the factory? - Michael Minella
The error ORA-00904 was happening because I was using 'config.id_config_data_export' as sortKey, but i've already changed just to 'id_config_data_export' and now the query works on the client. But I still get the ' Missing IN or OUT parameter at index:: 1'. I'm using SqlPagingQueryProviderFactoryBean - Ivan Arantes
You didn't answer the question. That factory bean can provide any number of actual SqlPagingQueryProvider implementations. Which one is being returned for you? - Michael Minella

1 Answers

0
votes

Finally made it work! Instead of writing the query directly in Java, I created a view with the query and it worked. I still don't know why it was not working but that's the workaround. This is the queryProvider now:

private PagingQueryProvider queryProviderStep1() throws Exception {
        SqlPagingQueryProviderFactoryBean queryProvider = new SqlPagingQueryProviderFactoryBean();
        queryProvider.setDataSource(infraConfig.dataSourceLocal());
        queryProvider.setDatabaseType("ORACLE");
        queryProvider.setSelectClause("SELECT * ");
        queryProvider.setFromClause("FROM VW_CONFIG_EXPORT_PART_CDRONE ");
        queryProvider.setSortKey("id_config_data_export");
        return queryProvider.getObject();