I am trying to run a simple Spring Boot 2.3.1.RELEASE web application (which uses an H2 database) that includes a data.sql file. The data.sql creates the database schema and inserts 3 records in the specified table. When the CommandLineRunner runs it uses a JDBC/DAO Bean to retrieve all the records in the table. However, I'm getting an error:
Table "PERSON" not found
The PERSON table is suppose to be created when the data.sql is executed. What I have found is that the data.sql is being executed after the application is started. Therefore, it doesn't exist when the CommandLineRunner runs. Here's some of the log info:
- Application Starts:
2020-06-29 17:09:09.456 INFO 19588 --- [ main] c.i.c.DataCreateIssueApplication : Started DataCreateIssueApplication in 3.016 seconds (JVM running for 3.461) 2020-06-29 17:09:09.467 DEBUG 19588 --- [ main] o.s.jdbc.core.JdbcTemplate : Executing SQL query [select * from person] 2020-06-29 17:09:09.468 DEBUG 19588 --- [ main] o.s.jdbc.datasource.DataSourceUtils : Fetching JDBC Connection from DataSource
...
- Error:
Caused by: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [select * from person]; nested exception is org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "PERSON" not found; SQL statement: select * from person [42102-200]
...
- data.sql executed: person table created, 3 records inserted.
2020-06-29 17:09:09.867 DEBUG 19588 --- [ task-2] o.s.jdbc.datasource.init.ScriptUtils : Executing SQL script from URL [file:/C:/Users/garyj/Documents/STS-4.6.0-Work-Area/workspace-jpa-with-spring-boot/data-create-issue/target/classes/data.sql] 2020-06-29 17:09:09.877 DEBUG 19588 --- [ task-2] o.s.jdbc.datasource.init.ScriptUtils : 0 returned as update count for SQL: create table person ( id integer not null, name varchar(255), location varchar(255), birth_date timestamp, constraint pk_person_id primary key (id) ) 2020-06-29 17:09:09.897 DEBUG 19588 --- [ task-2] o.s.jdbc.datasource.init.ScriptUtils : 1 returned as update count for SQL: INSERT INTO PERSON (ID, NAME, LOCATION, BIRTH_DATE ) VALUES(10001, 'Ranga', 'Hyderabad',sysdate()) 2020-06-29 17:09:09.897 DEBUG 19588 --- [ task-2] o.s.jdbc.datasource.init.ScriptUtils : 1 returned as update count for SQL: INSERT INTO PERSON (ID, NAME, LOCATION, BIRTH_DATE ) VALUES(10002, 'James', 'New York',sysdate()) 2020-06-29 17:09:09.897 DEBUG 19588 --- [ task-2] o.s.jdbc.datasource.init.ScriptUtils : 1 returned as update count for SQL: INSERT INTO PERSON (ID, NAME, LOCATION, BIRTH_DATE ) VALUES(10003, 'Pieter', 'Amsterdam',sysdate()) 2020-06-29 17:09:09.898 DEBUG 19588 --- [ task-2] o.s.jdbc.datasource.init.ScriptUtils : Executed SQL script from URL [file:/C:/Users/garyj/Documents/STS-4.6.0-Work-Area/workspace-jpa-with-spring-boot/data-create-issue/target/classes/data.sql] in 31 ms.
This doesn't happen in Spring Boot 2.2.4.RELEASE - in the pom.xml file, I changed the version:
From:
To:
I have attached a Zip file that has the Maven src and pom.xml.
Comment From: snicoll
Thanks for the sample and sorry for the late reply. In Spring Boot 2.3, the bootstrap of the JPA container switched to deferred. We make sure that the database is created before any JPA repository but your sample uses plain JDBC. Relying on JPA to create the container and access it with JDBC is a bit unusual.
That said, you can get back the behaviour of 2.2 with a single property. I've added the following and your app works:
spring.data.jpa.repositories.bootstrap-mode=default
If you haven't done so yet, please review the release notes while upgrading. It has a dedicated section on this topic.
Comment From: garyjolly
@snicoll Thank you very much. I agree on "Relying on JPA to create the container and access it with JDBC is a bit unusual." That example is from an online course that starts with JDBC and then teaches JPA. Thanks again. - Gary