CODEWITHSUNDEEP
spring-boothibernatespring-data-jpa
Spialdor
Spialdor

Reputation: 1645

Import data at startup Spring boot

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 ?

Upvotes: 0

Views: 774

Answers (2)

eHayik
eHayik

Reputation: 3262

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

Upvotes: 1

Allen D. Ball
Allen D. Ball

Reputation: 2026

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.

Upvotes: 1

Related Questions