Spring执行sql脚本文件的方法

时间:2022-03-27 19:47:33

本篇解决 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
//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

以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持服务器之家。

原文链接:https://segmentfault.com/a/1190000018344940