Reputation: 591
I have a Spring Boot
2.5.0 project. I'm using Spring Data JPA
with the H2
in-memory database. I want to populate data on startup with a data.sql
file but I'm getting a table not found exception. If I remove the data.sql
file, I can see that a table for my entity does get created automatically. But if I include the data.sql
file, I get the error saying the table doesn't exist. Maybe it is an error with my sql syntax of I have misconfigured the H2
database?
applicaltion.yml
spring:
datasource:
url: jdbc:h2:mem:test
driverClassName: org.h2.Driver
username: sa
password: sa
jpa:
database-platform: org.hibernate.dialect.H2Dialect
debug: true
data.sql
INSERT INTO BUSINESS_SUMMARY VALUES (1, "ALM470", "B48", 3);
BusinessSummary.java entity
@NoArgsConstructor(access = AccessLevel.PROTECTED)
@Getter
@Entity
public class BusinessSummary {
@Id
private Long id;
private String businessId;
private String businessDomainId;
private Integer cityCode;
}
BusinessSummaryRepository.java
@Repository
public interface BusinessSummaryRepository extends JpaRepository<BusinessSummary, Long> {
}
Exception:
Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "BUSINESS_SUMMARY" not found; SQL statement:
INSERT INTO BUSINESS_SUMMARY VALUES(1, "ALM470", "B48", 3) [42102-200]
Upvotes: 31
Views: 26807
Reputation: 926
spring.jpa.defer-datasource-initialization=true
By default,
data.sql
scripts are now run beforeHibernate
is initialized. This aligns the behavior of basic script-based initialization with that ofFlyway
andLiquibase
.
If you want to use
data.sql
to populate a schema created byHibernate
, set spring.jpa.defer-datasource-initialization to true. While mixing database initialization technologies is not recommended, this will also allow you to use aschema.sql
script to build upon aHibernate
-created schema before it’s populated viadata.sql
.
you'll have to convert spring.jpa.defer-datasource-initialization
to yml.
Upvotes: 66
Reputation: 203
spring.jpa.defer-datasource-initialization = true
spring.sql.init.mode = always
if still doesn`t work try renaming the file from data.sql to import.sql
Upvotes: 0
Reputation: 21
in addition to defer-datasource-initialization: true, you may also need
spring:
sql:
init:
mode: always
Upvotes: 0
Reputation: 336
If you're using hibernate as a JPA implementation, the best way I think is by using the file import.sql instead of data.sql for Database Initialization.
for more information on database initialization see the official Spring Boot documentation Database Initialization
Upvotes: 14