Search code examples
spring-bootspring-data-jpah2

Data from sql file doesn't insert into H2 database

I'm trying to insert data into a H2 database in Spring Boot 3.2.0 (JDK 17) but I can't seem to get it working. I was able to create the table but it doesn't have any rows inserted.

Note.java :

@Entity
@Data
@Table(name = "notes")
public class Note implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    @Column(name="note")
    private String note;
}

NoteRepository.java:

public interface NoteRepository extends JpaRepository\<Note, Long\> {

}

application.properties:

spring.datasource.url=jdbc:h2:file:./database
spring.datasource.driverClassName=org.h2.Driver
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect

spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=update

src/main/ressources/data.sql:

INSERT INTO notes (note) VALUES ('First note');
INSERT INTO notes (note) VALUES ('Second note');
INSERT INTO notes (note) VALUES ('Third note');

Start log:

2023-12-04T17:41:34.603+02:00  INFO 19268 --- [  restartedMain] c.e.h.HarjoitustyoApplication            : No active profile set, falling back to 1 default profile: "default"
2023-12-04T17:41:34.642+02:00  INFO 19268 --- [  restartedMain] .e.DevToolsPropertyDefaultsPostProcessor : Devtools property defaults active! Set 'spring.devtools.add-properties' to 'false' to disable
2023-12-04T17:41:34.642+02:00  INFO 19268 --- [  restartedMain] .e.DevToolsPropertyDefaultsPostProcessor : For additional web related logging consider setting the 'logging.level.web' property to 'DEBUG'
2023-12-04T17:41:35.038+02:00  INFO 19268 --- [  restartedMain] .s.d.r.c.RepositoryConfigurationDelegate : Bootstrapping Spring Data JPA repositories in DEFAULT mode.
2023-12-04T17:41:35.073+02:00  INFO 19268 --- [  restartedMain] .s.d.r.c.RepositoryConfigurationDelegate : Finished Spring Data repository scanning in 31 ms. Found 1 JPA repository interface.
2023-12-04T17:41:35.473+02:00  INFO 19268 --- [  restartedMain] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat initialized with port 8080 (http)
2023-12-04T17:41:35.482+02:00  INFO 19268 --- [  restartedMain] o.apache.catalina.core.StandardService   : Starting service [Tomcat]
2023-12-04T17:41:35.483+02:00  INFO 19268 --- [  restartedMain] o.apache.catalina.core.StandardEngine    : Starting Servlet engine: [Apache Tomcat/10.1.16]
2023-12-04T17:41:35.527+02:00  INFO 19268 --- [  restartedMain] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring embedded WebApplicationContext
2023-12-04T17:41:35.528+02:00  INFO 19268 --- [  restartedMain] w.s.c.ServletWebServerApplicationContext : Root WebApplicationContext: initialization completed in 886 ms
2023-12-04T17:41:35.555+02:00  INFO 19268 --- [  restartedMain] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
2023-12-04T17:41:35.706+02:00  INFO 19268 --- [  restartedMain] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - Added connection conn0: url=jdbc:h2:file:./database user=
2023-12-04T17:41:35.708+02:00  INFO 19268 --- [  restartedMain] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.     
2023-12-04T17:41:35.714+02:00  INFO 19268 --- [  restartedMain] o.s.b.a.h2.H2ConsoleAutoConfiguration    : H2 console available at '/h2-console'. Database available at 'jdbc:h2:file:./database'
2023-12-04T17:41:35.800+02:00  INFO 19268 --- [  restartedMain] o.hibernate.jpa.internal.util.LogHelper  : HHH000204: Processing PersistenceUnitInfo [name: default]
2023-12-04T17:41:35.840+02:00  INFO 19268 --- [  restartedMain] org.hibernate.Version                    : HHH000412: Hibernate ORM core version 6.3.1.Final
2023-12-04T17:41:35.867+02:00  INFO 19268 --- [  restartedMain] o.h.c.internal.RegionFactoryInitiator    : HHH000026: Second-level cache disabled
2023-12-04T17:41:36.032+02:00  INFO 19268 --- [  restartedMain] o.s.o.j.p.SpringPersistenceUnitInfo      : No LoadTimeWeaver setup: ignoring JPA class transformer
2023-12-04T17:41:36.567+02:00  INFO 19268 --- [  restartedMain] o.h.e.t.j.p.i.JtaPlatformInitiator       : HHH000489: No JTA platform available (set 'hibernate.transaction.jta.platform' to enable JTA platform integration)
2023-12-04T17:41:36.603+02:00  INFO 19268 --- [  restartedMain] j.LocalContainerEntityManagerFactoryBean : Initialized JPA EntityManagerFactory for persistence unit 'default'
2023-12-04T17:41:36.635+02:00  WARN 19268 --- [  restartedMain] JpaBaseConfiguration$JpaWebConfiguration : spring.jpa.open-in-view is enabled by default. Therefore, database queries may be performed during view rendering. Explicitly configure spring.jpa.open-in-view to disable this warning
2023-12-04T17:41:36.653+02:00  INFO 19268 --- [  restartedMain] o.s.b.a.w.s.WelcomePageHandlerMapping    : Adding welcome page template: index
2023-12-04T17:41:36.963+02:00  INFO 19268 --- [  restartedMain] o.s.b.d.a.OptionalLiveReloadServer       : LiveReload server is running on port 35729
2023-12-04T17:41:36.991+02:00  INFO 19268 --- [  restartedMain] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat started on port 8080 (http) with context path ''
2023-12-04T17:41:36.998+02:00  INFO 19268 --- [  restartedMain] c.e.h.HarjoitustyoApplication            : Started HarjoitustyoApplication in 2.632 seconds (process running for 2.921)
Solution

  • By default, data.sql scripts get executed before the Hibernate is initialized. We need Hibernate to create the table before inserting the data into them. To achieve this, we need to defer the initialization of our data source. We’ll use the below two properties to achieve this:

    application.properties:

    spring.jpa.defer-datasource-initialization=true
spring.sql.init.mode=always