Spring 执行 sql 脚本(文件)

本篇解决 Spring 执行SQL脚本(文件)的问题。

场景描述可以不看。

场景描述:

我在运行单测的时候,也就是 Spring 工程启动的时候,Spring 会去执行 classpath:schema.sql(后面会解释),我想利用这一点,解决一个问题:

一次运行多个测试文件,每个文件先后独立运行,而上一个文件创建的数据,会对下一个文件运行时造成影响,所以我要在每个文件执行完成之后,重置数据库,不单单是把数据删掉,而 schema.sql 里面有 drop table 和create table。

解决方法:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
//Schema 处理器
@Component
public class SchemaHandler {
private final String SCHEMA_SQL = "classpath:schema.sql";
@Autowired
private DataSource datasource;
@Autowired
private SpringContextGetter springContextGetter;

public void execute() throws Exception {
Resource resource = springContextGetter.getApplicationContext().getResource(SCHEMA_SQL);
ScriptUtils.executeSqlScript(datasource.getConnection(), resource);
}
}

// 获取 ApplicationContext
@Component
public class SpringContextGetter implements ApplicationContextAware {

private ApplicationContext applicationContext;

public ApplicationContext getApplicationContext() {
return applicationContext;
}

@Override
public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
this.applicationContext = applicationContext;
}

}

备注:

关于为何 Spring 会去执行 classpath:schema.sql,可以参考源码

org.springframework.boot.autoconfigure.jdbc.DataSourceInitializer#runSchemaScripts

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
private void runSchemaScripts() {
List<Resource> scripts = getScripts("spring.datasource.schema",
this.properties.getSchema(), "schema");
if (!scripts.isEmpty()) {
String username = this.properties.getSchemaUsername();
String password = this.properties.getSchemaPassword();
runScripts(scripts, username, password);
try {
this.applicationContext
.publishEvent(new DataSourceInitializedEvent(this.dataSource));
// The listener might not be registered yet, so don't rely on it.
if (!this.initialized) {
runDataScripts();
this.initialized = true;
}
}
catch (IllegalStateException ex) {
logger.warn("Could not send event to complete DataSource initialization ("
+ ex.getMessage() + ")");
}
}
}

/**
* 默认拿 classpath*:schema-all.sql 和 classpath*:schema.sql
*/
private List<Resource> getScripts(String propertyName, List<String> resources,
String fallback) {
if (resources != null) {
return getResources(propertyName, resources, true);
}
String platform = this.properties.getPlatform();
List<String> fallbackResources = new ArrayList<String>();
fallbackResources.add("classpath*:" + fallback + "-" + platform + ".sql");
fallbackResources.add("classpath*:" + fallback + ".sql");
return getResources(propertyName, fallbackResources, false);
}

参考:https://github.com/spring-projects/spring-boot/issues/9048

志哥 wechat
扫描上方,可以关注我的微信公众号哦。
能加个鸡腿吗?