This article solves the problem of Spring executing SQL script (file).

The scene description can be ignored.

Scenario Description:

When I run a single test, that is, when the Spring project is started, Spring will execute classpath:schema.sql (explained later). I want to use this to solve a problem:

Run multiple test files at a time, and each file runs independently one by one. The data created in the previous file will affect the running of the next file, so I need to reset the database after the execution of each file, not only to delete the data, but also to create the drop table and create table in schema.sql.

resolvent:

//Schema processor
@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);
    }
}

// Get 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;
    }

}

Remarks:

You can refer to the source code for why Spring will execute classpath:schema.sql

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

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() + ")");
            }
        }
    }

/**
 * By default, take classpath*:schema-all.sql and 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);
    }

Reference resources: https://github.com/spring-pro...

Original link:
http://zhige.me/2019/02/28/20...