Spring Boot使用——项目启动自动执行sql脚本

使用

• 前提：项目数据源配置完成
• 方法
  • 通过@Configuration、@Bean和@Value三个注解实现自定义DataSourceInitializer Bean，现在Bean的定义中实现自动化执行脚本的业务逻辑。
  • 代码如下

<span style="color: #0000ff;">package</span><span style="color: #000000;"> com.test.integration.rpc.initdata;
</span><span style="color: #0000ff;">import</span><span style="color: #000000;"> org.springframework.beans.factory.annotation.Value;
</span><span style="color: #0000ff;">import</span><span style="color: #000000;"> org.springframework.context.annotation.Bean;
</span><span style="color: #0000ff;">import</span><span style="color: #000000;"> org.springframework.context.annotation.Configuration;
</span><span style="color: #0000ff;">import</span><span style="color: #000000;"> org.springframework.core.io.Resource;
</span><span style="color: #0000ff;">import</span><span style="color: #000000;"> org.springframework.jdbc.datasource.init.DataSourceInitializer;
</span><span style="color: #0000ff;">import</span><span style="color: #000000;"> org.springframework.jdbc.datasource.init.DatabasePopulator;
</span><span style="color: #0000ff;">import</span><span style="color: #000000;"> org.springframework.jdbc.datasource.init.ResourceDatabasePopulator;
</span><span style="color: #0000ff;">import</span><span style="color: #000000;"> javax.sql.DataSource;
</span><span style="color: #008000;">/**</span><span style="color: #008000;">
 * </span><span style="color: #808080;">@author</span><span style="color: #008000;"> test
 * @Description: 自定义初始化数据源
 * @date 2020/4/11
 </span><span style="color: #008000;">*/</span><span style="color: #000000;">
@Configuration
</span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">class</span><span style="color: #000000;"> CustomizeDataSourceInitializer {
    @Value(</span>"classpath:testSql/test_farms.sql"<span style="color: #000000;">)
    </span><span style="color: #0000ff;">private</span><span style="color: #000000;"> Resource functionScriptFarms;
    @Value(</span>"classpath:testSql/test_miners.sql"<span style="color: #000000;">)
    </span><span style="color: #0000ff;">private</span><span style="color: #000000;"> Resource functionScriptMiners;
    @Value(</span>"classpath:testSql/test_pool_config.sql"<span style="color: #000000;">)
    </span><span style="color: #0000ff;">private</span><span style="color: #000000;"> Resource functionScriptPoolConfig;
    @Bean
    </span><span style="color: #0000ff;">public</span> DataSourceInitializer dataSourceInitializer(<span style="color: #0000ff;">final</span><span style="color: #000000;"> DataSource dataSource) {
        </span><span style="color: #0000ff;">final</span> DataSourceInitializer initializer = <span style="color: #0000ff;">new</span><span style="color: #000000;"> DataSourceInitializer();
        </span><span style="color: #008000;">//</span><span style="color: #008000;"> 设置数据源</span>
<span style="color: #000000;">        initializer.setDataSource(dataSource);
        initializer.setDatabasePopulator(databasePopulator());
        </span><span style="color: #0000ff;">return</span><span style="color: #000000;"> initializer;
    }
    </span><span style="color: #0000ff;">private</span><span style="color: #000000;"> DatabasePopulator databasePopulator() {
        </span><span style="color: #0000ff;">final</span> ResourceDatabasePopulator populator = <span style="color: #0000ff;">new</span><span style="color: #000000;"> ResourceDatabasePopulator();
        populator.addScripts(functionScriptFarms);
        populator.addScripts(functionScriptMiners);
        populator.addScripts(functionScriptPoolConfig);
        </span><span style="color: #0000ff;">return</span><span style="color: #000000;"> populator;
    }
}</span>

• • SQL脚本如下：例子test_pool_config.sql

<span style="color: #0000ff;">delete</span> <span style="color: #0000ff;">from</span><span style="color: #000000;"> `pool_configs`;
</span><span style="color: #0000ff;">INSERT</span> <span style="color: #0000ff;">INTO</span> `pool_configs`(`pool_config_id`, `cdate`, `mdate`, `pool_config_mid` ) <span style="color: #0000ff;">VALUES</span> (<span style="color: #800000; font-weight: bold;">1</span>, <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">2020-04-10 18:02:54</span><span style="color: #ff0000;">‘</span>, <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">2020-04-10 18:02:54</span><span style="color: #ff0000;">‘</span>, <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">bcf2a18a-b38b-4e3a-8852-7efbabc7954d</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">);
</span><span style="color: #0000ff;">INSERT</span> <span style="color: #0000ff;">INTO</span> `pool_configs`(`pool_config_id`, `cdate`, `mdate`, `pool_config_mid` ) <span style="color: #0000ff;">VALUES</span> (<span style="color: #800000; font-weight: bold;">2</span>, <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">2020-04-10 18:02:54</span><span style="color: #ff0000;">‘</span>, <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">2020-04-10 18:02:54</span><span style="color: #ff0000;">‘</span>, <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">bcf3a18a-b38b-4e3a-8852-7efbabc7954d</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">);
</span><span style="color: #0000ff;">INSERT</span> <span style="color: #0000ff;">INTO</span> `pool_configs`(`pool_config_id`, `cdate`, `mdate`, `pool_config_mid` ) <span style="color: #0000ff;">VALUES</span> (<span style="color: #800000; font-weight: bold;">3</span>, <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">2020-04-10 18:02:54</span><span style="color: #ff0000;">‘</span>, <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">2020-04-10 18:02:54</span><span style="color: #ff0000;">‘</span>, <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">bcf4a18a-b38b-4e3a-8852-7efbabc7954d</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">);
</span><span style="color: #0000ff;">INSERT</span> <span style="color: #0000ff;">INTO</span> `pool_configs`(`pool_config_id`, `cdate`, `mdate`, `pool_config_mid` ) <span style="color: #0000ff;">VALUES</span> (<span style="color: #800000; font-weight: bold;">4</span>, <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">2020-04-10 18:02:54</span><span style="color: #ff0000;">‘</span>, <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">2020-04-10 18:02:54</span><span style="color: #ff0000;">‘</span>, <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">bcf5a18a-b38b-4e3a-8852-7efbabc7954d</span><span style="color: #ff0000;">‘</span>);

Spring Boot使用——项目启动自动执行sql脚本

标签：port   注解   ali   des   frame   重启   framework   set   架构