SpringBoot启动自动执行sql脚本
原创
©著作权归作者所有:来自51CTO博客作者抱琴司棋侍书入画的原创作品,请联系作者获取转载授权,否则将追究法律责任
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
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;
}
}