Spring boot not executing schema.sql script

Question

I'm developing a Spring Boot web application and want to create a MySql database if it's not already created. So I've done a dump of my current database in order to have an empty schema of it. Put it in /src/main/resources, so maven brings it to /WEB-INF/classes when building the war file. That's how my application.properties is configured (according to Spring docs, that should create the DB from the script):

# DataSource settings: set here configurations for the database connection
spring.datasource.url = jdbc:mysql://localhost:3306/working_zones
spring.datasource.username = root
spring.datasource.password = password
spring.datasource.driverClassName = com.mysql.jdbc.Driver

# Specify the DBMS
spring.jpa.database = MYSQL

# Hibernate settings are prefixed with spring.jpa.hibernate.*
spring.jpa.hibernate.dialect = org.hibernate.dialect.MySQL5InnoDBDialect

And that's the error I get when I try to run the application (it complains about a non-existing DB):

2015-01-13 13:30:24.334 [main] ERROR o.s.boot.SpringApplication - Application startup failed
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'org.springframework.boot.autoconfigure.orm.jpa.HibernateJpaAutoConfiguration': Injection of autowired dependencies failed; nested exception is org.springframework.beans.factory.BeanCreationException: Could not autowire field: private javax.sql.DataSource org.springframework.boot.autoconfigure.orm.jpa.JpaBaseConfiguration.dataSource; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dataSource' defined in class path resource [org/springframework/boot/autoconfigure/jdbc/DataSourceAutoConfiguration$NonEmbeddedConfiguration.class]: Initialization of bean failed; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dataSourceInitializer': Invocation of init method failed; nested exception is org.springframework.jdbc.datasource.init.UncategorizedScriptException: Failed to execute database script; nested exception is org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown database 'working_zones'
    at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor.postProcessPropertyValues(AutowiredAnnotationBeanPostProcessor.java:301) ~[spring-beans-4.0.8.RELEASE.jar:4.0.8.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1186) ~[spring-beans-4.0.8.RELEASE.jar:4.0.8.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:537) ~[spring-beans-4.0.8.RELEASE.jar:4.0.8.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:475) ~[spring-beans-4.0.8.RELEASE.jar:4.0.8.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:302) ~[spring-beans-4.0.8.RELEASE.jar:4.0.8.RELEASE]
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:228) ~[spring-beans-4.0.8.RELEASE.jar:4.0.8.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:298) ~[spring-beans-4.0.8.RELEASE.jar:4.0.8.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:193) ~[spring-beans-4.0.8.RELEASE.jar:4.0.8.RELEASE]
    at org.springframework.beans.factory.support.ConstructorResolver.instantiateUsingFactoryMethod(ConstructorResolver.java:370) ~[spring-beans-4.0.8.RELEASE.jar:4.0.8.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.instantiateUsingFactoryMethod(AbstractAutowireCapableBeanFactory.java:1095) ~[spring-beans-4.0.8.RELEASE.jar:4.0.8.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:990) ~[spring-beans-4.0.8.RELEASE.jar:4.0.8.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:504) ~[spring-beans-4.0.8.RELEASE.jar:4.0.8.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:475) ~[spring-beans-4.0.8.RELEASE.jar:4.0.8.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:302) ~[spring-beans-4.0.8.RELEASE.jar:4.0.8.RELEASE]
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:228) ~[spring-beans-4.0.8.RELEASE.jar:4.0.8.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:298) ~[spring-beans-4.0.8.RELEASE.jar:4.0.8.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:193) ~[spring-beans-4.0.8.RELEASE.jar:4.0.8.RELEASE]
    at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:975) ~[spring-context-4.0.8.RELEASE.jar:4.0.8.RELEASE]
    at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:752) ~[spring-context-4.0.8.RELEASE.jar:4.0.8.RELEASE]
    at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:482) ~[spring-context-4.0.8.RELEASE.jar:4.0.8.RELEASE]
    at org.springframework.boot.context.embedded.EmbeddedWebApplicationContext.refresh(EmbeddedWebApplicationContext.java:109) ~[spring-boot-1.1.9.RELEASE.jar:1.1.9.RELEASE]
    at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:691) [spring-boot-1.1.9.RELEASE.jar:1.1.9.RELEASE]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:320) [spring-boot-1.1.9.RELEASE.jar:1.1.9.RELEASE]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:952) [spring-boot-1.1.9.RELEASE.jar:1.1.9.RELEASE]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:941) [spring-boot-1.1.9.RELEASE.jar:1.1.9.RELEASE]

So it seems that Spring tries to connect to the database even schema.sql, which contains the script to create the DB, not executed. There are some related questions in Stack Overflow about that, but still can't get it working, even if I try with spring.datasource.initialize=true...

Answer 1

As mentioned in this github post, you can add: spring.datasource.url=jdbc:mysql://localhost:3309/course_api_db?createDatabaseIfNotExist=true

To execute the schema.sql

Answer 2

A listerner on the application startup can resolve it. Following is the code.

public class DatabaseCreationListener implements ApplicationListener<ApplicationEnvironmentPreparedEvent> {

    private AtomicBoolean received = new AtomicBoolean(false);

    private ConfigurableEnvironment environment;

    private Pattern JDBC_URL_PATTERN = Pattern.compile("jdbc:([a-zA-Z0-9_]+)://[0-9.:]+(?:/([a-zA-Z0-9_]+))?(\\?.*)?");

    @Override
    public void onApplicationEvent(ApplicationEnvironmentPreparedEvent event) {
        // Think about twice invoking this listener
        if (!received.compareAndSet(false, true)) {
            return;
        }

        environment = event.getEnvironment();

        // ConditionalOnClass
        ClassLoader classLoader = event.getSpringApplication().getClassLoader();
        if (!isPresent("org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseType", classLoader)) {
            return;
        }

        // DatabaseProperties
        val databaseProperties = bind(DatabaseProperties.PREFIX, new DatabaseProperties());
        if (!databaseProperties.shouldCreate()) {
            return;
        }

        // DataSourceProperties
        val dataSourceProperties = bind(databaseProperties.getDatasourceConfigPrefix(), new DataSourceProperties());

        // Check for connection url
        String url = dataSourceProperties.getUrl();
        if (url == null) return;
        Matcher matcher = JDBC_URL_PATTERN.matcher(url);
        if (!matcher.matches()) return;

        // Extract database provider and schema name from connection url
        String databaseProvider = matcher.group(1);
        String schemaName = matcher.group(2);
        if (isBlank(schemaName)) return;

        // Reset connection url
        dataSourceProperties.setUrl(url.replace("/" + schemaName, ""));

        // Build a new datasource and do create schema
        DataSource dataSource = buildDataSource(dataSourceProperties);
        try (Connection connection = DataSourceUtils.getConnection(dataSource)) {
            connection.createStatement().execute(createSchemaIfAbsent(databaseProvider, schemaName));
        } catch (SQLException ignored) {
        }
    }

    private <T> T bind(String prefix, T t) {
        RelaxedDataBinder binder = new RelaxedDataBinder(t, prefix);
        binder.bind(new PropertySourcesPropertyValues(environment.getPropertySources()));
        return t;
    }

    private static DataSource buildDataSource(DataSourceProperties dataSourceProperties) {
        String url = dataSourceProperties.getUrl();
        String username = dataSourceProperties.getUsername();
        String password = dataSourceProperties.getPassword();
        return new SingleConnectionDataSource(url, username, password, false);
    }

    private static String createSchemaIfAbsent(String databaseProvider, String schemaName) {
        DatabaseDialects dialects = DatabaseDialects.getDatabaseDialect(databaseProvider);
        if (dialects == null) {
            throw new IllegalArgumentException("Unknown schema：" + schemaName);
        }
        switch (dialects) {
            case MYSQL:
                return "CREATE DATABASE IF NOT EXISTS " + schemaName;
            default:
                throw new UnsupportedOperationException("Unsupported schema：" + dialects);
        }
    }
}

And Following is the DatabaseProperties.

@Data
@ConfigurationProperties(prefix = DatabaseProperties.PREFIX)
public class DatabaseProperties {

    public final static String PREFIX = "spring.database";

    private boolean autoCreate;

    private String datasourceConfigPrefix = "spring.datasource";

    public boolean shouldCreate() {
        return isAutoCreate() && isNotBlank(getDatasourceConfigPrefix());
    }
}

The listener should be actived by config in META-INF/spring.factories.

org.springframework.context.ApplicationListener=\
yourpackage.DatabaseCreationListener

If you want config syntax hint in an underlying IDE, add the optional depenedncy spring-boot-configuration-processor and file META-INF/additional-spring-configuration-metadata.json.

{
  "groups": [
    {
      "sourceType": "yourpackage.DatabaseProperties",
      "name": "spring.database",
      "type": "yourpackage.DatabaseProperties"
    }
  ],
  "properties": [
    {
      "sourceType": "yourpackage.DatabaseProperties",
      "defaultValue": false,
      "name": "auto-create",
      "type": "java.lang.Boolean"
    },
    {
      "sourceType": "youpackage.DatabaseProperties",
      "defaultValue": "spring.datasource",
      "name": "datasource-config-prefix",
      "type": "java.lang.String"
    }
  ]
}

Answer 3

Well, looks like you can't do that from the common JDBC connect: creating a database in mysql from java

Hence Spring Boot can't do that for you automatically.

Please, don't mix the DB creating with its creation for its content: tables, procedures, triggers etc.

UPDATE

Yes, you can do that on the application startup. You just a separate initializer, which has an order before dataSourceInitializer.