这里简单介绍下三种初始化mysql脚本方法
方法一:配置application.yml文件
在SpringBoot项目中,当有在项目启动时先执行指定的sql语句的需求时,可以在resources文件夹下创建目录sql,并添加需要执行的sql文件(schema1-init.sql,schema2-init.sql,data-init.sql),文件中的sql语句可以是DDL(数据定义)脚本或DML(数据操作),DQL(数据查询)脚本.
spring:
#数据库配置
datasource:
url: jdbc:mysql://${mysql.host}:${mysql.port}/${mysql.dbName}?useUpomnicode=true&characterEncoding=utf-8&useSSL=false&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true
username: ${mysql.username}
password: ${mysql.password}
driver-class-name: com.mysql.jdbc.Driver
type: com.alibaba.druid.pool.DruidDataSource
# 表
schema:
- classpath:sql/schema1-init.sql
- classpath:sql/schema2-init.sql
# 数据
data:
- classpath:sql/data-init.sql
separator: ; #; 为sql脚本中语句分隔符
sql-script-encoding: utf-8 #utf-8 为文件的编码
initialization-mode: always #always为始终执行初始化,embedded只初始化内存数据库(默认值),如h2等,never为不执行初始化。
continue-on-error: true #遇到语句错误时是否继续,若已经执行过某些语句,再执行可能会报错,可以忽略,不会影响程序启动
#druid配置 监控 /druid/index.html
druid:
initial-size: 5
max-active: 30
min-idle: 10
max-wait: 6000
web-stat-filter.enabled: true
stat-view-servlet.enabled: true
需要执行多个SQL文件,也可以进行改成如下写法
spring:
datasource:
schema: classpath:sql/schema1-init.sql,classpath:sql/schema2-init.sql
data: classpath:sql/data-init.sql
方法二、自定义方法
@Configuration
public class CustomizeDataSourceInitializer {
@Value("classpath:sql/schema-init.sql")
private Resource schemaScript;
@Value("classpath:sql/data-init.sql")
private Resource dataScript;
@Bean
public DataSourceInitializer dataSourceInitializer(final DataSource dataSource) {
final DataSourceInitializer initializer = new DataSourceInitializer();
// 设置数据源
initializer.setDataSource(dataSource);
initializer.setDatabasePopulator(databasePopulator());
return initializer;
}
private DatabasePopulator databasePopulator() {
System.out.println("==================sql脚本正在初始化==================");
final ResourceDatabasePopulator populator = new ResourceDatabasePopulator();
populator.addScripts(schemaScript);
populator.addScripts(dataScript);
System.out.println("==================sql脚本初始化完成==================");
return populator;
}
}
方法三、通过mybatis的ScriptRunner实现
@Component
public class CustomizeDataSourceInitializer implements InitializingBean{
@Autowired
ApplicationContext applicationContext;
@Override
public void afterPropertiesSet() throws Exception {
//初始化schema
initTableSchema();
}
private void initTableSchema() {
DataSource dataSource = applicationContext.getBean(DataSource.class);
try (Connection connection= dataSource.getConnection();
Reader resourceAsReader = Resources.getResourceAsReader("sql/schema-init.sql");){
ScriptRunner runner=new ScriptRunner(connection);
runner.runScript(resourceAsReader);
} catch (SQLException | IOException e) {
logger.error("========================mysql建表异常===================", e);
}
}
}