Spring Boot Data JPA with H2 and data.sql - Table not Found

Question

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]

score 57 · Answer 1 · edited Jul 09 '23 at 02:54

57

spring.jpa.defer-datasource-initialization=true

By default, data.sql scripts are now run before Hibernate is initialized. This aligns the behavior of basic script-based initialization with that of Flyway and Liquibase.

If you want to use data.sql to populate a schema created by Hibernate, set spring.jpa.defer-datasource-initialization to true. While mixing database initialization technologies is not recommended, this will also allow you to use a schema.sql script to build upon a Hibernate-created schema before it’s populated via data.sql.

you'll have to convert spring.jpa.defer-datasource-initialization to yml.

edited Jul 09 '23 at 02:54

Manuel Jordan

15,253
21
95
158

answered May 28 '21 at 19:49

ZitZit

816
1
5
10

2

Good grief. I literally spent 2 solid weeks on this. I finally gave up in disgust and starting writing @BeforeEach code blocks on all unit tests to populate the H2 database with data because loading data.sql always resulted in 'TABLE <> NOT FOUND' errors. Hours and hours of frustration. This was the ticket: spring.jpa.defer-datasource-initialization=true Thank you so much! – Franklin M Gauer III Jun 21 '21 at 16:49
this is very important to know , was stuck for days – anshulkatta Jun 22 '21 at 05:28
Was scratching my head after bumping my spring boot version – Amit Kumar Jul 11 '21 at 15:03
2

This works perfectly fine. What also worked for me was to rename data.sql to import.sql (no idea why even without adding spring.jpa.defer-datasource-initialization) – HARSHIT BAJPAI Jul 29 '21 at 06:43

score 12 · Accepted Answer · answered Jan 08 '22 at 20:44

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

score 0 · Answer 3 · answered Mar 01 '22 at 03:19

0

in addition to defer-datasource-initialization: true, you may also need

spring:
  sql:
    init:
      mode: always

answered Mar 01 '22 at 03:19

Aikh

21
2

score 0 · Answer 4 · edited Mar 20 '22 at 15:53

0

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

edited Mar 20 '22 at 15:53

Dave

3,073
7
20
33

answered Mar 20 '22 at 08:11

Prajwal S M

91
1
1
6

Spring Boot Data JPA with H2 and data.sql - Table not Found

4 Answers4

Linked