Spring Boot - How to initial data.sql in particular database when there are multi-datasources?

Question

Introduction: There are two datasources in my spring boot application and default database is mysql. I want to create a schema and insert data in h2 database.

Actual Result: Create them into mysql database.

My Try: I study with spring-boot-loading-initial-data. and set spring.sql.init.platform=h2 and change sql name to data/schema-h2.sql. based on document 18.9.3. Initialize a Database Using Basic SQL Scripts

Expect Result: Create schema and insert data into h2 database when spring boot application launches.

Here are my Code:

#application.properties

spring.datasource.sql.jdbc-url=jdbc:mysql://localhost:3306/test?useSSL=false&autoreconnect=true&zeroDateTimeBehavior=convertToNull
spring.datasource.sql.username=root
spring.datasource.sql.password=
spring.datasource.sql.driver-class-name=com.mysql.cj.jdbc.Driver

spring.datasource.h2.jdbc-url=jdbc:h2:mem:mydb
spring.datasource.h2.driverClassName=org.h2.Driver
spring.datasource.h2.username=sa
spring.datasource.h2.password=password

spring.h2.console.enabled=true
spring.h2.console.path=/h2-console
spring.h2.console.settings.trace=false
spring.h2.console.settings.web-allow-others=false

spring.sql.init.platform=h2
spring.jpa.hibernate.ddl-auto=none
spring.sql.init.mode=always

spring.jpa.properties.hibernate.show_sql=true
spring.jpa.properties.hibernate.use_sql_comments=true
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.properties.hibernate.type=trace

#schema-h2.sql

CREATE TABLE country (
    id   INTEGER      NOT NULL AUTO_INCREMENT,
    name VARCHAR(128) NOT NULL,
    PRIMARY KEY (id)
);

#data-h2.sql

INSERT INTO country (name) VALUES ('India');
INSERT INTO country (name) VALUES ('Brazil');
INSERT INTO country (name) VALUES ('USA');
INSERT INTO country (name) VALUES ('Italy');

#DBConfig.java

package com.example.h21;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.core.JdbcTemplate;
import javax.sql.DataSource;
@Configuration
public class DBConfig {

    @Bean(name = "h2DataSource")
    @ConfigurationProperties(prefix = "spring.datasource.h2")
    public DataSource h2DataSource() {
        return DataSourceBuilder.create().build();
    }
    @Primary
    @Bean(name = "sqlDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.sql")
    public DataSource sqlDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Autowired
    @Bean(name = "h2JdbcTemplate")
    public JdbcTemplate h2JdbcTemplate(@Qualifier("h2DataSource") DataSource h2DataSource) {
        return new JdbcTemplate(h2DataSource);
    }

    @Primary
    @Bean(name ="sqlJdbcTemplate")
    @Autowired
    public JdbcTemplate sqlJdbcTemplate(@Qualifier("sqlDataSource") DataSource sqlDataSource) {
        return new JdbcTemplate(sqlDataSource);
    }
}

If I may ask why you have two datasources here with h2 and mysql Is it the case that you will use h2 for testing purposes only? — swapyonubuntu, Mar 01 '23 at 05:52
thx for your comment and I launch them to production. Because we use h2 for temporary use every day. the data should be clear when I restart the programme every morning as data is useless for long tern. — Timothy2022, Mar 01 '23 at 06:52

score 0 · Answer 1 · answered Mar 01 '23 at 09:14

0

According to Spring documentation you should annotate one datasource as Primary. Only this one will be initialized. In your case it would be h2 and not mysql.

https://docs.spring.io/spring-boot/docs/current/reference/html/howto.html#howto.data-access.configure-two-datasources

answered Mar 01 '23 at 09:14

Mar-Z

2,660
2
4
16

A lot of code use mysql so I add @primary for mysql. But I only run the above sql for h2 only. – Timothy2022 Mar 01 '23 at 09:45

score 0 · Answer 2 · answered Mar 01 '23 at 09:22

The Spring Auto-Configuration does not know that you have different data-sources at the time the application context is initializing. The trick is to create a Bean, that is responsible to for inserting data in H2 Database.

I suggest that you name the main DataSource (MySql) to 'dataSource' and H2 to H2DataSource, so that the JPA works automatically with your MySql. In addition create a bean that checks if initial data are stored in H2 DB and if not it insert them while context is initilizing:

@Component
public Class H2DbInitializer{

  @Autowired
  public H2DbInitializer(@Qualifier("h2JdbcTemplate")JdbcTemplate  h2JdbcTemplate){
     // Check if init data are already stored
     //if not then insert
  }  
}

Spring Boot - How to initial data.sql in particular database when there are multi-datasources?

2 Answers2