常见问题03:SpringBoot项目启动初始化数据(执行sql文件)

男娘i 2023-10-14 17:22 11阅读 0赞

### 1、类ResourceDatabasePopulator介绍 ###

**使用外部资源中定义的 SQL 脚本填充、初始化或清理数据库。**

> 1.  调用[addScript(org.springframework.core.io.Resource)][addScript_org.springframework.core.io.Resource]以添加单个 SQL 脚本位置。
> 2.  调用[addScripts(org.springframework.core.io.Resource…)][addScripts_org.springframework.core.io.Resource]以添加多个 SQL 脚本位置。
> 3.  请参阅此类中的 setter 方法以获取更多配置选项。
> 4.  调用[populate(java.sql.Connection)][populate_java.sql.Connection]或[execute(javax.sql.DataSource)][execute_javax.sql.DataSource]使用配置的脚本初始化或清理数据库。

### 2、方法总结 ###

![在这里插入图片描述][a979a6d56c5148a19ae23175f2bada8a.png]  
![在这里插入图片描述][6fc2a1d487814677b6b626226f255487.png]

### 3、通过注解@PostConstruct实现 ###

package com.shucha.deveiface.web.config;
    
    import com.sdy.common.utils.DateUtil;
    import lombok.extern.slf4j.Slf4j;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.context.annotation.Configuration;
    import org.springframework.core.io.ClassPathResource;
    import org.springframework.core.io.Resource;
    import org.springframework.jdbc.datasource.init.ResourceDatabasePopulator;
    
    import javax.annotation.PostConstruct;
    import javax.sql.DataSource;
    import java.util.Date;
    
    /**
     * @author tqf
     * @Description 项目启动初始化数据
     * @Version 1.0
     * @since 2022-04-13 09:29
     */
    @Configuration
    @Slf4j
    public class DataInitializationConfig {
        
    
        @Autowired
        DataSource dataSource;
    
        @PostConstruct
        public void init() {
        
            // 项目启动初始化基本数据
            log.info("数据初始化开始: " + DateUtil.dateToString(new Date(),"yyyy-MM-dd HH:mm:ss"));
            // 通过直接读取sql文件执行
            Resource resources = new ClassPathResource("sql/client_api_auth.sql");
            ResourceDatabasePopulator resourceDatabasePopulator = new ResourceDatabasePopulator();
            resourceDatabasePopulator.addScripts(resources);
            resourceDatabasePopulator.execute(dataSource);
            log.info("数据初始化结束: " + DateUtil.dateToString(new Date(),"yyyy-MM-dd HH:mm:ss"));
        }
    }

![在这里插入图片描述][c605212c8866480aad7f42e7a1ed6e74.png]

[addScript_org.springframework.core.io.Resource]: https://docs.spring.io/spring-framework/docs/5.3.13/javadoc-api/org/springframework/jdbc/datasource/init/ResourceDatabasePopulator.html#addScript-org.springframework.core.io.Resource-
[addScripts_org.springframework.core.io.Resource]: https://docs.spring.io/spring-framework/docs/5.3.13/javadoc-api/org/springframework/jdbc/datasource/init/ResourceDatabasePopulator.html#addScripts-org.springframework.core.io.Resource...-
[populate_java.sql.Connection]: https://docs.spring.io/spring-framework/docs/5.3.13/javadoc-api/org/springframework/jdbc/datasource/init/ResourceDatabasePopulator.html#populate-java.sql.Connection-
[execute_javax.sql.DataSource]: https://docs.spring.io/spring-framework/docs/5.3.13/javadoc-api/org/springframework/jdbc/datasource/init/ResourceDatabasePopulator.html#execute-javax.sql.DataSource-
[a979a6d56c5148a19ae23175f2bada8a.png]: https://img-blog.csdnimg.cn/a979a6d56c5148a19ae23175f2bada8a.png
[6fc2a1d487814677b6b626226f255487.png]: https://img-blog.csdnimg.cn/6fc2a1d487814677b6b626226f255487.png
[c605212c8866480aad7f42e7a1ed6e74.png]: https://img-blog.csdnimg.cn/c605212c8866480aad7f42e7a1ed6e74.png