Spring Boot auto-generated data source with EmbeddedDatabase Derby leads to "Schema '<schema-name>' does not exist"

2.3k views Asked by At

I do use Spring Boot 1.4.2 in combination with profile dependent auto-generated data sources based on the configuration of an "application-{profile}.properties" file and the JAR dependencies Spring Boot detects on the classpath:

Maven Spring Boot dependencies in "pom.xml":

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
    <dependency>
        <groupId>org.apache.derby</groupId>
        <artifactId>derby</artifactId>
    </dependency>
</dependencies>

Spring Boot "application-dev.properties":

# JPA (JpaBaseConfiguration, HibernateJpaAutoConfiguration)
spring.jpa.generate-ddl=true
spring.jpa.hibernate.ddl-auto=create-drop
spring.jpa.database=default
spring.jpa.show-sql=true
spring.jpa.hibernate.naming.strategy=org.hibernate.boot.model.naming.ImplicitNamingStrategyJpaCompliantImpl

# DATASOURCE (DataSourceAutoConfiguration & DataSourceProperties)
spring.datasource.continue-on-error=false
spring.datasource.generate-unique-name=false

When starting the Spring Boot application where Hibernate should auto-generate the Derby DDL and the underlying schema I get the same error reported here:

java.sql.SQLSyntaxErrorException: Schema 'SA' does not exist

The generated JDBC Derby URL and username looks like:

  • url=jdbc:derby:memory:testdb;create=true;
  • username=sa

Important: When switching the default "spring.jpa.hibernate.ddl-auto" from "create-drop" to "update" the DDL execution works. But: according to the Spring Boot documentation, "create-drop" is the default setting for embedded databases (like DERBY) - even when not having configured "spring.jpa.hibernate.ddl-auto" explicitly.

Once the DDL creation passed using "update" setting, a preceding run with "create-drop" works successfully.

Is there a way where the auto-generated data source approach in Spring Boot can work with "create-drop" for embedded databases like DERBY initially without using this workaround?

0

There are 0 answers