How to run SQL scripts and get data on application startup?
What worked for me is using DataSourceInitializer
:
@Bean
public DataSourceInitializer dataSourceInitializer(@Qualifier("dataSource") final DataSource dataSource) {
ResourceDatabasePopulator resourceDatabasePopulator = new ResourceDatabasePopulator();
resourceDatabasePopulator.addScript(new ClassPathResource("/data.sql"));
DataSourceInitializer dataSourceInitializer = new DataSourceInitializer();
dataSourceInitializer.setDataSource(dataSource);
dataSourceInitializer.setDatabasePopulator(resourceDatabasePopulator);
return dataSourceInitializer;
}
Used to set up a database during initialization and clean up a database during destruction.
https://docs.spring.io/spring-framework/docs/current/javadoc-api/org/springframework/jdbc/datasource/init/DataSourceInitializer.html
By default, Spring-Boot loads data.sql
and/or data-${platform}.sql
.
However, keep in mind that the script would be loaded at every start, so I would think it makes more sense (at least for production), to just have the values already present in the database, not re-inserted at every start. I've personally only used database initialization for test/dev purposes when using a memory database. Still, this is the feature provided by Spring-Boot.
source: spring-boot-howto-database-initialization:
Spring JDBC has a DataSource initializer feature. Spring Boot enables it by default and loads SQL from the standard locations schema.sql and data.sql (in the root of the classpath). In addition Spring Boot will load the schema-${platform}.sql and data-${platform}.sql files (if present).
src/main/resources/data-oracle.sql:
insert into...
insert into...
- You may define the platform with:
spring.datasource.platform=oracle
. - You may change the name of the sql script to load with:
spring.datasource.data=myscript.sql
. - Along with
data.sql
, Spring-boot also loadsschema.sql
(beforedata.sql
). - You could also have an "update or insert" logic in your data.sql: oracle sql: update if exists else insert