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]

Manuel Jordan · Accepted Answer · 2023-07-09 02:54:49Z

67

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 9, 2023 at 2:54

Manuel Jordan

16.5k26 gold badges113 silver badges187 bronze badges

answered May 28, 2021 at 19:49

ZitZit

9361 gold badge8 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Franklin M Gauer III Over a year ago

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!

anshulkatta Over a year ago

this is very important to know , was stuck for days

Amit Kumar Over a year ago

Was scratching my head after bumping my spring boot version

HARSHIT BAJPAI Over a year ago

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)

Janet Over a year ago

By default, data.sql and schema.sql both run before Hibernate. say, if you have a sequence start with 1000 in schema.sql, which is different from your default sequence start in entity class, without putting "spring.jpa.defer-datasource-initialization" in application.yml, sequence start with 1000 will be used.

|

mouad36 · Accepted Answer · 2022-01-08 20:44:42Z

14

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

answered Jan 8, 2022 at 20:44

mouad36

3362 silver badges11 bronze badges

Comments

Aikh · Accepted Answer · 2022-03-01 03:19:43Z

0

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

spring:
  sql:
    init:
      mode: always

answered Mar 1, 2022 at 3:19

Aikh

212 bronze badges

Comments

Dave · Accepted Answer · 2022-03-20 15:53:15Z

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, 2022 at 15:53

Dave

3,0917 gold badges22 silver badges33 bronze badges

answered Mar 20, 2022 at 8:11

Prajwal S M

2231 gold badge2 silver badges10 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

6 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

6 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related