如何在Spring Boot代码中执行sql脚本
在spring应用运行时，有一些建表语句，或则初始化数据，需要从sql脚本导入。
本文推荐以下两种方法。
假设脚本位于/resources/ddl.sql
1 使用@sql注解
该注解可用于类和方法。
@Sql(scripts = {"/ddl.sql"},
config = @SqlConfig(encoding = "utf-8",
transactionMode = SqlConfig.TransactionMode.ISOLATED))
@SpringBootTest
@TestMethodOrder(MethodOrderer.OrderAnnotation.class)
@Slf4j
public class RepositoryTest {
@Autowired
AppRepository appRepository;
@Test
@Order(1)
public void insertApp(){
appRepository.add(...)
// ...
}
}
注意，如果@Sql用于class，那么测试类里面的每个测试方法在运行之前都会跑一次这个脚本。
@sql上的注释有说明：
@Sql is used to annotate a test class or test method to configure SQL scripts and statements to be executed against a given database during integration tests.
...
Script execution is performed by the SqlScriptsTestExecutionListener...
看见during integration tests了吧。
另外，从注释可知，脚本是被SqlScriptsTestExecutionListener执行的。打开这个类，可以看到里面有一些方法和debug级别的日志。将这个包的日志级别设为debug：
logging.level.root=info
logging.level.org.springframework.test.context.jdbc=debug
然后运行，即可看到类似下面的日志：
INFO [ Test worker] cn.whu.wy.osgov.test.RepositoryTest : Started RepositoryTest in 1.959 seconds (JVM running for 2.746)
DEBUG [ Test worker] .s.t.c.j.SqlScriptsTestExecutionListener : Processing [MergedSqlConfig@e4927bb dataSource = '', transactionManager = '', transactionMode = ISOLATED, encoding = 'utf-8', separator = ';',...
DEBUG [ Test worker] .s.t.c.j.SqlScriptsTestExecutionListener : Executing SQL scripts: [class path resource [ddl.sql]]
INFO [ Test worker] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting...
INFO [ Test worker] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed.
INFO [ Test worker] cn.whu.wy.osgov.test.RepositoryTest : insertApp
DEBUG [ Test worker] .s.t.c.j.SqlScriptsTestExecutionListener : Processing [MergedSqlConfig@5e704109 dataSource = '', transactionManager = '', transactionMode = ISOLATED, encoding = 'utf-8', separator = ';',...
DEBUG [ Test worker] .s.t.c.j.SqlScriptsTestExecutionListener : Executing SQL scripts: [class path resource [ddl.sql]]
INFO [ Test worker] cn.whu.wy.osgov.test.RepositoryTest : insertArtifact
DEBUG [ Test worker] .s.t.c.j.SqlScriptsTestExecutionListener : Processing [MergedSqlConfig@701e3274 dataSource = '', transactionManager = '', transactionMode = ISOLATED, encoding = 'utf-8', separator = ';',...
DEBUG [ Test worker] .s.t.c.j.SqlScriptsTestExecutionListener : Executing SQL scripts: [class path resource [ddl.sql]]
INFO [ Test worker] cn.whu.wy.osgov.test.RepositoryTest : insertHost
DEBUG [ Test worker] .s.t.c.j.SqlScriptsTestExecutionListener : Processing [MergedSqlConfig@48ab1f96 dataSource = '', transactionManager = '', transactionMode = ISOLATED, encoding = 'utf-8', separator = ';',...
DEBUG [ Test worker] .s.t.c.j.SqlScriptsTestExecutionListener : Executing SQL scripts: [class path resource [ddl.sql]]
INFO [ Test worker] cn.whu.wy.osgov.test.RepositoryTest : insertLicense
DEBUG [ Test worker] .s.t.c.j.SqlScriptsTestExecutionListener : Processing [MergedSqlConfig@67d2c696 dataSource = '', transactionManager = '', transactionMode = ISOLATED, encoding = 'utf-8', separator = ';',...
DEBUG [ Test worker] .s.t.c.j.SqlScriptsTestExecutionListener : Executing SQL scripts: [class path resource [ddl.sql]]
INFO [ Test worker] cn.whu.wy.osgov.test.RepositoryTest : insertTag
DEBUG [ Test worker] .s.t.c.j.SqlScriptsTestExecutionListener : Processing [MergedSqlConfig@2473bf20 dataSource = '', transactionManager = '', transactionMode = ISOLATED, encoding = 'utf-8', separator = ';',...
DEBUG [ Test worker] .s.t.c.j.SqlScriptsTestExecutionListener : Executing SQL scripts: [class path resource [ddl.sql]]
INFO [ Test worker] cn.whu.wy.osgov.test.RepositoryTest : insertVulnerability
INFO [ionShutdownHook] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Shutdown initiated...
INFO [ionShutdownHook] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Shutdown completed.
BUILD SUCCESSFUL in 7s
从日志可见，确实在每个测试用例之前执行了脚本：
Executing SQL scripts: [class path resource [ddl.sql]]
如果只需要该脚本执行一次该怎么做呢？将@Sql(....)注解放在某个@Test方法上，比如init方法，那么该脚本只会在执行init方法之前执行一次：
@Sql(scripts = {"/ddl.sql"}, config = @SqlConfig(encoding = "utf-8", transactionMode = SqlConfig.TransactionMode.ISOLATED))
@Test
void init() {
log.info("createTableUseAnno");
}
日志：
INFO [ Test worker] cn.whu.wy.osgov.test.RepositoryTest : Started RepositoryTest in 1.916 seconds (JVM running for 2.683)
DEBUG [ Test worker] .s.t.c.j.SqlScriptsTestExecutionListener : Processing [MergedSqlConfig@6367d99b dataSource = '', transactionManager = ...
DEBUG [ Test worker] .s.t.c.j.SqlScriptsTestExecutionListener : Executing SQL scripts: [class path resource [ddl.sql]]
INFO [ Test worker] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting...
INFO [ Test worker] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed.
INFO [ Test worker] cn.whu.wy.osgov.test.RepositoryTest : createTableUseAnno
INFO [ Test worker] cn.whu.wy.osgov.test.RepositoryTest : insertApp
INFO [ Test worker] cn.whu.wy.osgov.test.RepositoryTest : insertArtifact
...
2 使用ScriptUtils.executeSqlScript
@Autowired
DataSource dataSource;
void createTable() throws SQLException {
Resource classPathResource = new ClassPathResource("ddl.sql");
EncodedResource encodedResource = new EncodedResource(classPathResource, "utf-8");
ScriptUtils.executeSqlScript(dataSource.getConnection(), encodedResource);
}
这种方式更加灵活，DataSource可以是自己创建的。
中文编码
上面两种方法都指定了utf-8编码。当表里面有中文时，不指定时会报错。
案例：有这样一个表，
CREATE TABLE app
(
id int NOT NULL AUTO_INCREMENT,
name varchar(50) NOT NULL,
env enum ('生产','测试') NOT NULL,
PRIMARY KEY (id),
INDEX idx_name (name)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4;
env字段为枚举类型，通过本文提到的两种方法建表后（未指定utf-8编码），在插入数据时报错：
> INSERT app(name, env) VALUES ('app-1', '生产');
PreparedStatementCallback; Data truncated for column 'env' at row 1;
nested exception is java.sql.SQLException: Data truncated for column 'env' at row 1
代码
我的这个项目使用了本文提到的内容，可以参考。
