I'm following Getting Started Guide|Creating a Batch Service.
It uses HyperSQL database as given in the guide.
I have followed the guide as is, but getting the following error:
org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [INSERT INTO people(first_name,last_name) VALUES (?, ?)]
at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:101) ~[spring-jdbc-6.0.4.jar:6.0.4]
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70) ~[spring-jdbc-6.0.4.jar:6.0.4]
at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1538) ~[spring-jdbc-6.0.4.jar:6.0.4]
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:667) ~[spring-jdbc-6.0.4.jar:6.0.4]
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:691) ~[spring-jdbc-6.0.4.jar:6.0.4]
at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:1034) ~[spring-jdbc-6.0.4.jar:6.0.4]
at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.batchUpdate(NamedParameterJdbcTemplate.java:373) ~[spring-jdbc-6.0.4.jar:6.0.4]
at org.springframework.batch.item.database.JdbcBatchItemWriter.write(JdbcBatchItemWriter.java:190) ~[spring-batch-infrastructure-5.0.0.jar:5.0.0]
at org.springframework.batch.core.step.item.SimpleChunkProcessor.writeItems(SimpleChunkProcessor.java:203) ~[spring-batch-core-5.0.0.jar:5.0.0]
at org.springframework.batch.core.step.item.SimpleChunkProcessor.doWrite(SimpleChunkProcessor.java:170) ~[spring-batch-core-5.0.0.jar:5.0.0]
at org.springframework.batch.core.step.item.SimpleChunkProcessor.write(SimpleChunkProcessor.java:297) ~[spring-batch-core-5.0.0.jar:5.0.0]
at org.springframework.batch.core.step.item.SimpleChunkProcessor.process(SimpleChunkProcessor.java:227) ~[spring-batch-core-5.0.0.jar:5.0.0]
at org.springframework.batch.core.step.item.ChunkOrientedTasklet.execute(ChunkOrientedTasklet.java:75) ~[spring-batch-core-5.0.0.jar:5.0.0]
at org.springframework.batch.core.step.tasklet.TaskletStep$ChunkTransactionCallback.doInTransaction(TaskletStep.java:389) ~[spring-batch-core-5.0.0.jar:5.0.0]
at org.springframework.batch.core.step.tasklet.TaskletStep$ChunkTransactionCallback.doInTransaction(TaskletStep.java:313) ~[spring-batch-core-5.0.0.jar:5.0.0]
at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140) ~[spring-tx-6.0.4.jar:6.0.4]
at org.springframework.batch.core.step.tasklet.TaskletStep$2.doInChunkContext(TaskletStep.java:256) ~[spring-batch-core-5.0.0.jar:5.0.0]
at org.springframework.batch.core.scope.context.StepContextRepeatCallback.doInIteration(StepContextRepeatCallback.java:82) ~[spring-batch-core-5.0.0.jar:5.0.0]
at org.springframework.batch.repeat.support.RepeatTemplate.getNextResult(RepeatTemplate.java:362) ~[spring-batch-infrastructure-5.0.0.jar:5.0.0]
at org.springframework.batch.repeat.support.RepeatTemplate.executeInternal(RepeatTemplate.java:206) ~[spring-batch-infrastructure-5.0.0.jar:5.0.0]
at org.springframework.batch.repeat.support.RepeatTemplate.iterate(RepeatTemplate.java:139) ~[spring-batch-infrastructure-5.0.0.jar:5.0.0]
at org.springframework.batch.core.step.tasklet.TaskletStep.doExecute(TaskletStep.java:241) ~[spring-batch-core-5.0.0.jar:5.0.0]
at org.springframework.batch.core.step.AbstractStep.execute(AbstractStep.java:227) ~[spring-batch-core-5.0.0.jar:5.0.0]
at org.springframework.batch.core.job.SimpleStepHandler.handleStep(SimpleStepHandler.java:153) ~[spring-batch-core-5.0.0.jar:5.0.0]
at org.springframework.batch.core.job.flow.JobFlowExecutor.executeStep(JobFlowExecutor.java:68) ~[spring-batch-core-5.0.0.jar:5.0.0]
at org.springframework.batch.core.job.flow.support.state.StepState.handle(StepState.java:68) ~[spring-batch-core-5.0.0.jar:5.0.0]
at org.springframework.batch.core.job.flow.support.SimpleFlow.resume(SimpleFlow.java:167) ~[spring-batch-core-5.0.0.jar:5.0.0]
at org.springframework.batch.core.job.flow.support.SimpleFlow.start(SimpleFlow.java:142) ~[spring-batch-core-5.0.0.jar:5.0.0]
at org.springframework.batch.core.job.flow.FlowJob.doExecute(FlowJob.java:137) ~[spring-batch-core-5.0.0.jar:5.0.0]
at org.springframework.batch.core.job.AbstractJob.execute(AbstractJob.java:316) ~[spring-batch-core-5.0.0.jar:5.0.0]
at org.springframework.batch.core.launch.support.SimpleJobLauncher$1.run(SimpleJobLauncher.java:157) ~[spring-batch-core-5.0.0.jar:5.0.0]
at org.springframework.core.task.SyncTaskExecutor.execute(SyncTaskExecutor.java:50) ~[spring-core-6.0.4.jar:6.0.4]
at org.springframework.batch.core.launch.support.SimpleJobLauncher.run(SimpleJobLauncher.java:148) ~[spring-batch-core-5.0.0.jar:5.0.0]
at org.springframework.batch.core.launch.support.TaskExecutorJobLauncher.run(TaskExecutorJobLauncher.java:70) ~[spring-batch-core-5.0.0.jar:5.0.0]
at org.springframework.boot.autoconfigure.batch.JobLauncherApplicationRunner.execute(JobLauncherApplicationRunner.java:199) ~[spring-boot-autoconfigure-3.0.2.jar:3.0.2]
at org.springframework.boot.autoconfigure.batch.JobLauncherApplicationRunner.executeLocalJobs(JobLauncherApplicationRunner.java:178) ~[spring-boot-autoconfigure-3.0.2.jar:3.0.2]
at org.springframework.boot.autoconfigure.batch.JobLauncherApplicationRunner.launchJobFromProperties(JobLauncherApplicationRunner.java:166) ~[spring-boot-autoconfigure-3.0.2.jar:3.0.2]
at org.springframework.boot.autoconfigure.batch.JobLauncherApplicationRunner.run(JobLauncherApplicationRunner.java:161) ~[spring-boot-autoconfigure-3.0.2.jar:3.0.2]
at org.springframework.boot.autoconfigure.batch.JobLauncherApplicationRunner.run(JobLauncherApplicationRunner.java:156) ~[spring-boot-autoconfigure-3.0.2.jar:3.0.2]
at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:758) ~[spring-boot-3.0.2.jar:3.0.2]
at org.springframework.boot.SpringApplication.callRunners(SpringApplication.java:748) ~[spring-boot-3.0.2.jar:3.0.2]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:315) ~[spring-boot-3.0.2.jar:3.0.2]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1302) ~[spring-boot-3.0.2.jar:3.0.2]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1291) ~[spring-boot-3.0.2.jar:3.0.2]
at com.example.batchprocessing.DemoApplication.main(DemoApplication.java:10) ~[classes/:na]
Caused by: java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: PEOPLE in statement [INSERT INTO people(first_name,last_name) VALUES (?, ?)]
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source) ~[hsqldb-2.7.1.jar:2.7.1]
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source) ~[hsqldb-2.7.1.jar:2.7.1]
at org.hsqldb.jdbc.JDBCPreparedStatement.<init>(Unknown Source) ~[hsqldb-2.7.1.jar:2.7.1]
at org.hsqldb.jdbc.JDBCConnection.prepareStatement(Unknown Source) ~[hsqldb-2.7.1.jar:2.7.1]
at com.zaxxer.hikari.pool.ProxyConnection.prepareStatement(ProxyConnection.java:327) ~[HikariCP-5.0.1.jar:na]
at com.zaxxer.hikari.pool.HikariProxyConnection.prepareStatement(HikariProxyConnection.java) ~[HikariCP-5.0.1.jar:na]
at org.springframework.jdbc.core.JdbcTemplate$SimplePreparedStatementCreator.createPreparedStatement(JdbcTemplate.java:1637) ~[spring-jdbc-6.0.4.jar:6.0.4]
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:649) ~[spring-jdbc-6.0.4.jar:6.0.4]
... 41 common frames omitted
Caused by: org.hsqldb.HsqlException: user lacks privilege or object not found: PEOPLE
at org.hsqldb.error.Error.error(Unknown Source) ~[hsqldb-2.7.1.jar:2.7.1]
at org.hsqldb.error.Error.error(Unknown Source) ~[hsqldb-2.7.1.jar:2.7.1]
at org.hsqldb.ParserDQL.readTableName(Unknown Source) ~[hsqldb-2.7.1.jar:2.7.1]
at org.hsqldb.ParserDQL.readRangeVariableForDataChange(Unknown Source) ~[hsqldb-2.7.1.jar:2.7.1]
at org.hsqldb.ParserDML.compileInsertStatement(Unknown Source) ~[hsqldb-2.7.1.jar:2.7.1]
at org.hsqldb.ParserCommand.compilePart(Unknown Source) ~[hsqldb-2.7.1.jar:2.7.1]
at org.hsqldb.ParserCommand.compileStatement(Unknown Source) ~[hsqldb-2.7.1.jar:2.7.1]
at org.hsqldb.Session.compileStatement(Unknown Source) ~[hsqldb-2.7.1.jar:2.7.1]
at org.hsqldb.StatementManager.compile(Unknown Source) ~[hsqldb-2.7.1.jar:2.7.1]
at org.hsqldb.Session.execute(Unknown Source) ~[hsqldb-2.7.1.jar:2.7.1]
... 47 common frames omitted
This is the BatchConfiguration class.
package com.example.batchprocessing;
import org.springframework.batch.core.Job;
import org.springframework.batch.core.Step;
import org.springframework.batch.core.job.builder.JobBuilder;
import org.springframework.batch.core.launch.support.RunIdIncrementer;
import org.springframework.batch.core.repository.JobRepository;
import org.springframework.batch.core.step.builder.StepBuilder;
import org.springframework.batch.item.database.BeanPropertyItemSqlParameterSourceProvider;
import org.springframework.batch.item.database.JdbcBatchItemWriter;
import org.springframework.batch.item.database.builder.JdbcBatchItemWriterBuilder;
import org.springframework.batch.item.file.FlatFileItemReader;
import org.springframework.batch.item.file.builder.FlatFileItemReaderBuilder;
import org.springframework.batch.item.file.mapping.BeanWrapperFieldSetMapper;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.ClassPathResource;
import org.springframework.transaction.PlatformTransactionManager;
import javax.sql.DataSource;
@Configuration
public class BatchConfiguration {
@Bean
public FlatFileItemReader<Person> reader() {
return new FlatFileItemReaderBuilder<Person>()
.name("personItemReader")
.resource(new ClassPathResource("sample-data.csv"))
.delimited()
.names(new String[]{"firstName", "lastName"})
.fieldSetMapper(new BeanWrapperFieldSetMapper<Person>() {{
setTargetType(Person.class);
}})
.build();
}
@Bean
public PersonItemProcessor processor() {
return new PersonItemProcessor();
}
@Bean
public JdbcBatchItemWriter<Person> writer(DataSource dataSource) {
return new JdbcBatchItemWriterBuilder<Person>()
.itemSqlParameterSourceProvider(new BeanPropertyItemSqlParameterSourceProvider<>())
.sql("INSERT INTO people(first_name,last_name) VALUES (:firstName, :lastName)")//This is showing problem
.dataSource(dataSource)
.build();
}
@Bean
public Job importUserJob(JobRepository jobRepository,
JobCompletionNotificationListener listener, Step step1) {
return new JobBuilder("importUserJob", jobRepository)
.incrementer(new RunIdIncrementer())
.listener(listener)
.flow(step1)
.end()
.build();
}
@Bean
public Step step1(JobRepository jobRepository,
PlatformTransactionManager transactionManager, JdbcBatchItemWriter<Person> writer) {
return new StepBuilder("step1", jobRepository)
.<Person, Person> chunk(10, transactionManager)
.reader(reader())
.processor(processor())
.writer(writer)
.build();
}
}
I'm a beginner and have tried searching for related questions regarding BadSQLGrammarException, but haven't been able to find a solution to my problem.
There is nothing wrong with your code. If you look at the bottom of the stack trace it points that either the table PEOPLE doesn't exist or your user doesn't have permissions to access it.
org.hsqldb.HsqlException: user lacks privilege or object not found: PEOPLE