SpringBoot启动自动执行sql脚本

navicat导出sql脚本

右键数据库>转储SQL文件>结构和数据…(包含建表语句和数据库已有记录的数据插入语句等)

SpringBoot启动自动执行sql脚本逻辑编写

大致逻辑：配置文件配置数据库连接；编写建表及新增记录sql脚本，编写@Configuration类读取并执行sql脚本；
 
项目总览图如下

• 编写sql脚本，置于resources/sql目录下

  schema.sql

  DROP TABLE IF EXISTS people;
  
  CREATE TABLE IF NOT EXISTS people(
      persion_id INT NOT NULL,
      first_name VARCHAR(20),
      last_name VARCHAR(20),
      PRIMARY KEY (persion_id)
  );
  

  ---

  data.sql

  INSERT INTO people VALUES (1, 'ashe', '弗雷尔卓德');
  INSERT INTO people VALUES (2, 'annie', '班德尔城');
  INSERT INTO people VALUES (3, 'akali', '均衡教派');
  

• 配置数据库连接

  spring.datasource.driver-class-name=org.sqlite.JDBC
  spring.datasource.url=jdbc:sqlite:C:/Users/catface/Desktop/auto.db
  

• 编写@Configuration配置类

  import org.springframework.beans.factory.annotation.Value;
  import org.springframework.context.annotation.Bean;
  import org.springframework.context.annotation.Configuration;
  import org.springframework.core.io.Resource;
  import org.springframework.jdbc.datasource.init.DataSourceInitializer;
  import org.springframework.jdbc.datasource.init.DatabasePopulator;
  import org.springframework.jdbc.datasource.init.ResourceDatabasePopulator;
  
  import javax.sql.DataSource;
  
  @Configuration
  public class CustomizeDataSourceInitializer {
  
      @Value("classpath:sql/schema.sql")
      private Resource sqlScriptSchema;
      @Value("classpath:sql/data.sql")
      private Resource sqlScriptData;
  
      @Bean
      public DataSourceInitializer dataSourceInitializer(final DataSource dataSource) {
          DataSourceInitializer dataSourceInitializer = new DataSourceInitializer();
          dataSourceInitializer.setDataSource(dataSource);
          dataSourceInitializer.setDatabasePopulator(databasePopulator());
          return dataSourceInitializer;
      }
  
      private DatabasePopulator databasePopulator() {
          ResourceDatabasePopulator resourceDatabasePopulator = new ResourceDatabasePopulator();
          resourceDatabasePopulator.addScript(sqlScriptSchema);
          resourceDatabasePopulator.addScript(sqlScriptData);
          resourceDatabasePopulator.setSeparator(";");//分隔符[默认;]
          return resourceDatabasePopulator;
      }
  }
  

• 项目运行后会立即执行sql脚本以测试