Spring Boot多数据源下如何指定H2执行数据库初始化脚本

我的Spring Boot应用配置了两个数据源，默认数据库为MySQL，希望在应用启动时为H2数据库创建Schema并插入初始化数据。

初始化脚本被执行到了MySQL数据库中，未达到预期效果。

我参考了Stack Overflow的《spring-boot-loading-initial-data》及Spring Boot官方文档18.9.3章节，设置spring.sql.init.platform=h2，并将脚本命名为schema-h2.sql和data-h2.sql，但问题仍未解决。

Spring Boot应用启动时，自动在H2数据库中创建Schema并插入初始化数据。

相关代码 #

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);
    }
}

Spring Boot默认的spring.sql.init配置仅作用于被标记为@Primary的主数据源，你的MySQL数据源是主数据源，因此脚本被错误执行到了MySQL中。要实现H2数据源的独立初始化，需要手动配置专属的初始化逻辑：

方法一：配置H2专属的DataSourceInitializer #

修改DBConfig.java，添加H2数据源的初始化器，同时禁用全局默认初始化：

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.boot.sql.init.dependency.DependsOnDatabaseInitialization;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.ClassPathResource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.init.DataSourceInitializer;
import org.springframework.jdbc.datasource.init.ResourceDatabasePopulator;
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")
    @DependsOnDatabaseInitialization
    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);
    }

    // 新增：H2数据源专属初始化器
    @Bean
    public DataSourceInitializer h2DataSourceInitializer(@Qualifier("h2DataSource") DataSource h2DataSource) {
        DataSourceInitializer initializer = new DataSourceInitializer();
        initializer.setDataSource(h2DataSource);
        ResourceDatabasePopulator populator = new ResourceDatabasePopulator();
        // 加载并执行H2初始化脚本
        populator.addScript(new ClassPathResource("schema-h2.sql"));
        populator.addScript(new ClassPathResource("data-h2.sql"));
        initializer.setDatabasePopulator(populator);
        initializer.setEnabled(true);
        return initializer;
    }
}

在application.properties中禁用全局默认初始化：

spring.sql.init.enabled=false

方法二：通过@PostConstruct执行脚本 #

在DBConfig.java中添加初始化方法，利用H2的JdbcTemplate手动执行脚本：

// 在DBConfig类中新增以下代码
import org.springframework.core.io.ClassPathResource;
import org.springframework.util.StreamUtils;
import javax.annotation.PostConstruct;
import java.io.IOException;
import java.nio.charset.StandardCharsets;
import java.util.Arrays;

@Autowired
@Qualifier("h2JdbcTemplate")
private JdbcTemplate h2JdbcTemplate;

@PostConstruct
public void initH2Database() {
    try {
        // 执行schema脚本
        String schemaSql = StreamUtils.copyToString(
            new ClassPathResource("schema-h2.sql").getInputStream(),
            StandardCharsets.UTF_8
        );
        h2JdbcTemplate.execute(schemaSql);

        // 执行data脚本（按分号分割语句，避免批量执行问题）
        String dataSql = StreamUtils.copyToString(
            new ClassPathResource("data-h2.sql").getInputStream(),
            StandardCharsets.UTF_8
        );
        Arrays.stream(dataSql.split(";"))
              .filter(sql -> !sql.trim().isEmpty())
              .forEach(h2JdbcTemplate::execute);
    } catch (IOException e) {
        throw new RuntimeException("H2数据库初始化失败", e);
    }
}

同样需要在application.properties中添加：

spring.sql.init.enabled=false

注意事项 #

1. 确保schema-h2.sql和data-h2.sql放在src/main/resources目录下，Spring能正确加载。
2. H2内存数据库的数据会在应用重启后丢失，属于正常特性。
3. @DependsOnDatabaseInitialization注解确保JdbcTemplate在数据库初始化完成后再创建，避免使用时出现未初始化的问题。

内容的提问来源于stack exchange，提问作者Timothy2022