I am trying to build an IPL-Dashboard as per a tutorial. The first phase involves using Spring batch to read from a csv file and populate an in-memory database created using JPA. However, I encounter the issue:
org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [INSERT INTO "match" (id, city, date, player_of_match, venue, team1, team2, toss_winner, toss_decision, match_winner, result, result_margin, umpire1, umpire2) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]; nested exception is java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: match in statement [INSERT INTO "match" (id, city, date, player_of_match, venue, team1, team2, toss_winner, toss_decision, match_winner, result, result_margin, umpire1, umpire2) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]
caused by:
java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: match in statement...
The issue arises from lack of access to the database created. Or whether the database was created at all. I’ve found this stackoverflow question particularly aiding and this second, but not able to solve the issue. Any help would be appreciated.
Here is my code:
MatchDataProcessor.java
... A bunch of imports here...
import model.Match;
@Configuration
@EnableBatchProcessing
public class BatchConfig {
private final String[] FIELD_NAMES = new String[] { "id", "city", "date", "player_of_match", "venue",
"neutral_venue", "team1", "team2", "toss_winner", "toss_decision", "winner", "result", "result_margin",
"eliminator", "method", "umpire1", "umpire2" };
@Autowired
public JobBuilderFactory jobBuilderFactory;
@Autowired
public StepBuilderFactory stepBuilderFactory;
@Bean
public FlatFileItemReader<MatchInput> reader() {
return new FlatFileItemReaderBuilder<MatchInput>().name("MatchItemReader")
.resource(new ClassPathResource("match-data.csv")).delimited().names(FIELD_NAMES)
.fieldSetMapper(new BeanWrapperFieldSetMapper<MatchInput>() {
{
setTargetType(MatchInput.class);
}
}).build();
}
@Bean
public MatchDataProcessor processor() {
return new MatchDataProcessor();
}
@Bean
public JdbcBatchItemWriter<Match> writer(DataSource dataSource) {
return new JdbcBatchItemWriterBuilder<Match>()
.itemSqlParameterSourceProvider(new BeanPropertyItemSqlParameterSourceProvider<>())
.sql(
"INSERT INTO \"match\" (id, city, date, player_of_match, venue, team1, team2, toss_winner, toss_decision, match_winner, result, result_margin, umpire1, umpire2) "
+ " VALUES (:id, :city, :date, :playerOfMatch, :venue, :team1, :team2, :tossWinner, :tossDecision, :matchWinner, :result, :resultMargin, :umpire1, :umpire2)")
.dataSource(dataSource).build();
}
@Bean
public Job importUserJob(JobCompletionNotificationListener listener, Step step1) {
return jobBuilderFactory.get("importUserJob")
.incrementer(new RunIdIncrementer())
.listener(listener)
.flow(step1)
.end()
.build();
}
@Bean
public Step step1(JdbcBatchItemWriter<Match> writer) {
return stepBuilderFactory.get("step1")
.<MatchInput, Match>chunk(10)
.reader(reader())
.processor(processor())
.writer(writer)
.build();
}
}
MatchInput.java
public class MatchInput {
private String id;
private String city;
private String date;
private String player_of_match;
private String venue;
private String neutral_venue;
private String team1;
private String team2;
private String toss_winner;
private String toss_decision;
private String winner;
private String result;
private String result_margin;
private String eliminator;
private String method;
private String umpire1;
private String umpire2;
...Getters and Setters...
model\Match.java
import java.time.LocalDate;
import javax.persistence.Entity;
import javax.persistence.Id;
@Entity
public class Match {
@Id
private long id;
private String city;
private LocalDate date;
private String playerOfMatch;
private String venue;
private String team1;
private String team2;
private String tossWinner;
private String tossDecision;
private String matchWinner;
private String result;
private String resultMargin;
private String umpire1;
private String umpire2;
...Getters and Setters...
BatchConfig.java
...A bunch of imports here...
@Configuration
@EnableBatchProcessing
public class BatchConfig {
private final String[] FIELD_NAMES = new String[] { "id", "city", "date", "player_of_match", "venue",
"neutral_venue", "team1", "team2", "toss_winner", "toss_decision", "winner", "result", "result_margin",
"eliminator", "method", "umpire1", "umpire2" };
@Autowired
public JobBuilderFactory jobBuilderFactory;
@Autowired
public StepBuilderFactory stepBuilderFactory;
@Bean
public FlatFileItemReader<MatchInput> reader() {
return new FlatFileItemReaderBuilder<MatchInput>().name("MatchItemReader")
.resource(new ClassPathResource("match-data.csv")).delimited().names(FIELD_NAMES)
.fieldSetMapper(new BeanWrapperFieldSetMapper<MatchInput>() {
{
setTargetType(MatchInput.class);
}
}).build();
}
@Bean
public MatchDataProcessor processor() {
return new MatchDataProcessor();
}
@Bean
public JdbcBatchItemWriter<Match> writer(DataSource dataSource) {
return new JdbcBatchItemWriterBuilder<Match>()
.itemSqlParameterSourceProvider(new BeanPropertyItemSqlParameterSourceProvider<>())
.sql(
"INSERT INTO \"match\" (id, city, date, player_of_match, venue, team1, team2, toss_winner, toss_decision, match_winner, result, result_margin, umpire1, umpire2) "
+ " VALUES (:id, :city, :date, :playerOfMatch, :venue, :team1, :team2, :tossWinner, :tossDecision, :matchWinner, :result, :resultMargin, :umpire1, :umpire2)")
.dataSource(dataSource).build();
}
@Bean
public Job importUserJob(JobCompletionNotificationListener listener, Step step1) {
return jobBuilderFactory.get("importUserJob")
.incrementer(new RunIdIncrementer())
.listener(listener)
.flow(step1)
.end()
.build();
}
@Bean
public Step step1(JdbcBatchItemWriter<Match> writer) {
return stepBuilderFactory.get("step1")
.<MatchInput, Match>chunk(10)
.reader(reader())
.processor(processor())
.writer(writer)
.build();
}
}
BatchCompletionListener.java
...A bunch of imports here...
@Component
public class JobCompletionNotificationListener extends JobExecutionListenerSupport {
private static final Logger log = LoggerFactory.getLogger(JobCompletionNotificationListener.class);
private final JdbcTemplate jdbcTemplate;
@Autowired
public JobCompletionNotificationListener(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
@Override
public void afterJob(JobExecution jobExecution) {
if (jobExecution.getStatus() == BatchStatus.COMPLETED) {
log.info("!!! JOB FINISHED! Time to verify the results");
jdbcTemplate.query("SELECT team1, team2, date FROM match",
(rs, row) -> "Team 1: " + rs.getString(1) + " Team 2: " + rs.getString(2) + " Date: " + rs.getString(3))
.forEach(str -> System.out.println(str));
}
}
}
pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.6.2</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.webapps</groupId>
<artifactId>ipl-dashboard</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>ipl-dashboard</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>17</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-batch</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
<scope>runtime</scope>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.hsqldb</groupId>
<artifactId>hsqldb</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework.batch</groupId>
<artifactId>spring-batch-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
