How to run SQL scripts and get data on application startup?

I am developing a Spring Boot application. At the moment some of my configs are hard coded (e.g. Hystrix properties).

So I would like to get these configs on my application start up time or just after that.

Is it possible to do that using Spring Boot? I mean to run SQL script on start up and get data.

How should properties/configs be retrieved and stored in my application?

I am using MyBatis and Oracle DB.

If you want to insert data based on some business logic I would recommend you to have Event Listener. So basically on application startup "OnApplicationEvent" as it is annotated with @EventListener method will be called automatically.

Also as in your case you need to get the data, you simply use your repository object to get the data as well.

Here's one example:

@Component
public class OnApplicationStartUp {

@Autowired
private ServiceRepository repository;


@EventListener
public void onApplicationEvent(ContextRefreshedEvent event) {

//Write your business logic here.
if (repository.findAll().size() <= 0) {
preloadData();
}else{
fetchData();
}
}

private void preloadData() {

List<Service> services = new ArrayList<>();
Service someService= new Service("name", "type");
services.add(someService);
...
...
repository.saveAll(services);
}
}

If you getting from application.properties file you can use Environment class.
Like that

Autowired
private Environment environment;
...
environment.getProperty("propertyName")

or you can define your own property file. then you can get from it with
`@PropertySource(name = "myProperties", value = "example.properties")` annotation

You need to use @Value annotation to get a specific value from the property file which you defined.

@Value("${propertyNameInYourPropertFile}")
private String url;


And You want to start something when Application is just started, you can use this before a method

@EventListener(ApplicationReadyEvent.class)
But need to use @Service or @Component Annotation, which Class has the method.

Totally, You can use this.

example.properties :

url=yourValue
userName=yourDBUserName
password=yourDBPassword

example class :

@Service
@PropertySource(name = "myProperties", value = "example.properties")
public class Start{

@Value("${url}")
private String url;

@Value("${userName}")
private String userName;

@Value("${password}")
private String password;


//Run this method when application started
@EventListener(ApplicationReadyEvent.class)
public ResultSet getConnection()
{

//Connect to Database
Connection connection = null;
String QUERY="your sql query";
try {
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
connection = DriverManager.getConnection(url, userName, password );
} catch (SQLException e) {
}


//Run your query
Statement stmt = null;
try {
stmt = connection.createStatement();
} catch (SQLException e1) {
e1.printStackTrace();
}
ResultSet rs = null;
try {
rs = stmt.executeQuery(QUERY);
} catch (SQLException e1) {
e1.printStackTrace();
}

return rs;
}

}

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.

[To see links please register here]

1. Define a Spring Bean (e.g. GlobalConfigurationBean)<br>
with Scope: @Scope(scopeName = WebApplicationContext.SCOPE_APPLICATION) <br>
This Bean would be responsible for fetching the data from your Database Table (maintaining Configuration properties) during Bean initialization.

2. Annotate a method using @PostConstruct.<br> This method would have the logic to fetch the Configuration parameters from your database table.

3. The scope of this bean would ensure that required Configurations are fetched only once from the database table (e.g. using Hibernate query or pure native SQL) and <br> are available to all beans from different contexts within same application.

Now just inject this bean wherever you wish to use those Configuration Properties or parameters.<br>
<br>
OR
<br><br>
Use:: Apache Commons DatabaseConfiguration <-- <br>
NOTE:: It doesn't support caching. But I guess you don't need caching as the database configuration properties should be loaded only once, at the start of application launch. <br>
<br>
OR
<br><br>
Trandational Old way: Define a Custom Implementation of "PropertyPlaceHolderConfigurer" by extending it, and define it as a Spring Bean.<br>
This implementaion should have logic to fetch the data from your database table, that holds configuration properties.

If want to load data from sql script after startup, use ResourceDatabasePopulator class object as follows.

```
import org.springframework.boot.context.event.ApplicationReadyEvent;
import org.springframework.context.event.EventListener;
import org.springframework.core.io.ClassPathResource;
import org.springframework.jdbc.datasource.init.ResourceDatabasePopulator;

import javax.sql.DataSource;

@Component
public class InitializeData {

@Autowired
private DataSource dataSource;

@EventListener(ApplicationReadyEvent.class)
public void loadData() {
ResourceDatabasePopulator resourceDatabasePopulator = new ResourceDatabasePopulator(false, false, "UTF-8", new ClassPathResource("data.sql"));
resourceDatabasePopulator.execute(dataSource);
}
}
```
It will easily load data from sql file and dont worry about wrong sql statements inside sql file as it will be ignored.

Initialize Database(Execute schema.sql and data.sql) using JDBC in spring boot

Note: Follow these steps

1. create sample project pom.xml (edit it based on your version)

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0

[To see links please register here]

;
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>3.0.1</version>
<relativePath /> <!-- lookup parent from repository -->
</parent>
<groupId>com.example</groupId>
<artifactId>Init_Sql_Script</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>Init_Sql_Script</name>
<description>Initialize a Database Using Basic SQL Scripts</description>
<properties>
<java.version>17</java.version>
</properties>
<dependencies>

<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>

<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>

<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>

<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>

<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>

</project>


2. application properties && create new db name like employee in ur DB

# connection code for spring boot to your DB
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/employee
spring.datasource.username=Hari
spring.datasource.password=1998

spring.jpa.hibernate.ddl-auto=none

#--- control the sql db initialization (from schema.sql and data.sql)---
spring. SQL.init.mode=always


#---if any error it skips the script and executes the next script----
#spring.sql.init.continue-on-error=true

3. create two SQL files (schema.sql-create table query and data.sql-insert query) in the resource folder
4. run the application after that check db thats all Try it.


It is working for me I Hope its working for u also

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][1]:

> 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.sql.init.platform=oracle`.
- You may change the name of the sql script to load with: `spring.sql.init.data-locations=myscript.sql`.
- Along with `data.sql`, Spring-boot also loads `schema.sql` (before `data.sql`).
- You could also have an "update or insert" logic in your data.sql:

[To see links please register here]

[1]:

[To see links please register here]