Import data at startup Spring boot

Question

I'm trying to launch a SQL file at my database initialization.

Here is my configuration:

spring:
  profiles: local
  jpa:
    properties:
      hibernate.temp.use_jdbc_metadata_defaults: false
    generate-ddl: true
    hibernate:
      ddl-auto: update
    database: h2
    show-sql: true
    autoCommit: false
  datasource:
    platform: h2
    url: jdbc:h2:mem:db;DB_CLOSE_DELAY=-1;DATABASE_TO_UPPER=false;
    driver-class-name: org.h2.Driver
    initialization-mode: always
    data: classpath:/sql/CreateGeographicZones.sql

My script is just this line (atm):

INSERT INTO GEOGRAPHIC_ZONE (name) VALUES ('EUROPE');

And the related entity:

@NoArgsConstructor
@Data
@Entity
@Table(name = "GEOGRAPHIC_ZONE")
public class GeographicZone {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "geo_zone_sequence")
    @SequenceGenerator(name = "geo_zone_sequence", sequenceName = "geo_zone_id_seq", allocationSize = 1)
    @Column(nullable = false)
    private Long id;
    
   ...
}

The table is created as I can see in the logs: Hibernate: create table geographic_zone (id bigint not null, name varchar(100) not null, primary key (id))

But I have an SQL error when the script is executed:

Table "GEOGRAPHIC_ZONE" not found; SQL statement:
INSERT INTO GEOGRAPHIC_ZONE (name) VALUES ('EUROPE')

In the logs I can see that my table is created before the script execution, so why it's not working ?

Allen D. Ball · Answer 1 · 2020-11-03T08:25:26.570

1

You don't show how you've defined the id column but the schema indicates there is no auto-generation scheme. So, try:

INSERT INTO GEOGRAPHIC_ZONE (id, name) VALUES (1, 'EUROPE');

in your data file. If that works, you'll need to either manually set the id in your inserts or add something like @GeneratedValue(strategy = AUTO) to your @Id property.

edited Nov 03 '20 at 08:25

answered Oct 30 '20 at 19:44

Allen D. Ball

1,916
1
9
16

I have an id in the class with a @GeneratedValue, it's working well with all my controllers, just the script doesn't find my table at all – Spialdor Oct 31 '20 at 20:02
What is your @GeneratedValue strategy? – Allen D. Ball Oct 31 '20 at 20:56
I add it in my question, but as I say before it's working well when I'm inserting via my Rest endpoint (without id as well) – Spialdor Nov 03 '20 at 08:06
With SEQUENCE, there is a separate table (hibernate_sequence by default) that has the next in the sequence. Spring JPA is querying this table and using the result for the ID in the insert. Use AUTO if you want to insert with SQL without specifying the ID – Allen D. Ball Nov 03 '20 at 08:23

eHayik · Accepted Answer · 2021-01-22T11:16:32.030

According with your entity's metadata Hibernate is querying geo_zone_id_seq sequence's next value and using it for the ID on each insert.

If you would like to use the same approach when inserting directly in your database then you will need to implement a H2 Trigger

Also you may use either the EntityManager bean or your Spring JPA Repository to insert your data after application startup via CommandLineRunner interface.

Using EntityManager:

    @Bean
    CommandLineRunner registerZonesDataRunner(EntityManager entityManager, TransactionTemplate transactionTemplate) {
        return args -> transactionTemplate.execute(new TransactionCallbackWithoutResult() {
            @Override
            protected void doInTransactionWithoutResult(TransactionStatus status) {
                // presuming that GeographicZone has a constructor expecting NAME 
                Stream.of("AFRICA", "EUROPE")
                        .map(GeographicZone::new)
                        .forEach(entityManager::persist);
            }
        });

Using Spring JPA Repository:

    @Bean
    CommandLineRunner registerZonesDataRunner(GeographicZoneRepository repository) {
                    // presuming that GeographicZone has a constructor expecting NAME 
        return args -> repository.saveAll(Stream.of("AFRICA", "EUROPE")
                .map(GeographicZone::new)
                .collector(Collectors.toList()));
    }

minimal, reproducible example

Import data at startup Spring boot

2 Answers2