Dealing with "Database is already closed" with H2 embedded database and spring-jdbc

Question

Event though the built-in H2 EmbeddedDatabaseConfigurer provides a JDBC connection with DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=false options set, the annoying exception is caused by AbstractEmbeddedDatabaseConfigurer.shutdown().

There are already plenty of discussions around this topic on stackoverflow. I have tried to play a bit with the options and have come to the conclusion that the rather odd settings (DB_CLOSE_DELAY=20;DB_CLOSE_ON_EXIT=false) provided with a custom DataSourceFactory reduce the number of occurrences but do not completely eliminate it.

/**
 * This factory tries to solve the problem that Spring tries to close a data source that has already been closed by the H2 engine/H2 driver.
 */
public class H2DataSourceFactory implements DataSourceFactory {
    private final SimpleDriverDataSource dataSource = new SimpleDriverDataSource();

    @Override
    public ConnectionProperties getConnectionProperties() {
        return new ConnectionProperties() {
            @Override
            public void setDriverClass(Class<? extends Driver> driverClass) {
                dataSource.setDriverClass(driverClass);
            }

            @Override
            public void setUrl(String url) {
                // ignore the URL and use a custom URL
                dataSource.setUrl("jdbc:h2:mem:%s;DB_CLOSE_DELAY=20;DB_CLOSE_ON_EXIT=false;MODE=PostgreSQL");
            }

            @Override
            public void setUsername(String username) {
                dataSource.setUsername(username);
            }

            @Override
            public void setPassword(String password) {
                dataSource.setPassword(password);
            }
        };
    }

    @Override
    public DataSource getDataSource() {
        return this.dataSource;
    }
}

Has someone else come to a solution that really gets rid of this effect?

Here is the stacktrace:

14:12:32.691 [localhost-startStop-2] WARN  o.s.j.d.e.H2EmbeddedDatabaseConfigurer - Could not shutdown embedded database
org.h2.jdbc.JdbcSQLException: Database is already closed (to disable automatic closing at VM shutdown, add ";DB_CLOSE_ON_EXIT=FALSE" to the db URL) [90121-160]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:329) ~[h2-1.3.160.jar:1.3.160]
    at org.h2.message.DbException.get(DbException.java:169) ~[h2-1.3.160.jar:1.3.160]
    at org.h2.message.DbException.get(DbException.java:146) ~[h2-1.3.160.jar:1.3.160]
    at org.h2.message.DbException.get(DbException.java:135) ~[h2-1.3.160.jar:1.3.160]
    at org.h2.jdbc.JdbcConnection.checkClosed(JdbcConnection.java:1394) ~[h2-1.3.160.jar:1.3.160]
    at org.h2.jdbc.JdbcConnection.checkClosed(JdbcConnection.java:1369) ~[h2-1.3.160.jar:1.3.160]
    at org.h2.jdbc.JdbcConnection.createStatement(JdbcConnection.java:191) ~[h2-1.3.160.jar:1.3.160]
    at org.springframework.jdbc.datasource.embedded.AbstractEmbeddedDatabaseConfigurer.shutdown(AbstractEmbeddedDatabaseConfigurer.java:42) ~[spring-jdbc-4.1.5.RELEASE.jar:4.1.5.RELEASE]
    at org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseFactory.shutdownDatabase(EmbeddedDatabaseFactory.java:177) [spring-jdbc-4.1.5.RELEASE.jar:4.1.5.RELEASE]
    at org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseFactory$EmbeddedDataSourceProxy.shutdown(EmbeddedDatabaseFactory.java:250) [spring-jdbc-4.1.5.RELEASE.jar:4.1.5.RELEASE]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_25]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_25]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_25]
    at java.lang.reflect.Method.invoke(Method.java:483) ~[na:1.8.0_25]
    at org.springframework.beans.factory.support.DisposableBeanAdapter.invokeCustomDestroyMethod(DisposableBeanAdapter.java:350) [spring-beans-4.1.5.RELEASE.jar:4.1.5.RELEASE]
    at org.springframework.beans.factory.support.DisposableBeanAdapter.destroy(DisposableBeanAdapter.java:273) [spring-beans-4.1.5.RELEASE.jar:4.1.5.RELEASE]
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.destroyBean(DefaultSingletonBeanRegistry.java:578) [spring-beans-4.1.5.RELEASE.jar:4.1.5.RELEASE]
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.destroySingleton(DefaultSingletonBeanRegistry.java:554) [spring-beans-4.1.5.RELEASE.jar:4.1.5.RELEASE]
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.destroySingleton(DefaultListableBeanFactory.java:900) [spring-beans-4.1.5.RELEASE.jar:4.1.5.RELEASE]
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.destroySingletons(DefaultSingletonBeanRegistry.java:523) [spring-beans-4.1.5.RELEASE.jar:4.1.5.RELEASE]
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.destroySingletons(DefaultListableBeanFactory.java:907) [spring-beans-4.1.5.RELEASE.jar:4.1.5.RELEASE]
    at org.springframework.context.support.AbstractApplicationContext.destroyBeans(AbstractApplicationContext.java:908) [spring-context-4.1.5.RELEASE.jar:4.1.5.RELEASE]
    at org.springframework.context.support.AbstractApplicationContext.doClose(AbstractApplicationContext.java:884) [spring-context-4.1.5.RELEASE.jar:4.1.5.RELEASE]
    at org.springframework.context.support.AbstractApplicationContext.close(AbstractApplicationContext.java:836) [spring-context-4.1.5.RELEASE.jar:4.1.5.RELEASE]
    at org.springframework.web.context.ContextLoader.closeWebApplicationContext(ContextLoader.java:579) [spring-web-4.1.5.RELEASE.jar:4.1.5.RELEASE]
    at org.springframework.web.context.ContextLoaderListener.contextDestroyed(ContextLoaderListener.java:115) [spring-web-4.1.5.RELEASE.jar:4.1.5.RELEASE]
    at org.apache.catalina.core.StandardContext.listenerStop(StandardContext.java:4773) [tomcat-embed-core-8.0.20.jar:8.0.20]
    at org.apache.catalina.core.StandardContext.stopInternal(StandardContext.java:5385) [tomcat-embed-core-8.0.20.jar:8.0.20]
    at org.apache.catalina.util.LifecycleBase.stop(LifecycleBase.java:232) [tomcat-embed-core-8.0.20.jar:8.0.20]
    at org.apache.catalina.core.ContainerBase$StopChild.call(ContainerBase.java:1425) [tomcat-embed-core-8.0.20.jar:8.0.20]
    at org.apache.catalina.core.ContainerBase$StopChild.call(ContainerBase.java:1414) [tomcat-embed-core-8.0.20.jar:8.0.20]
    at java.util.concurrent.FutureTask.run(FutureTask.java:266) [na:1.8.0_25]
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [na:1.8.0_25]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [na:1.8.0_25]
    at java.lang.Thread.run(Thread.java:745) [na:1.8.0_25]

Answer 1

For anyone still facing this:

Using the async protocol seems to allow a connection to be interrupted without breaking all other connections. Trying this option to see if this reduces these errors.

You can set this as:

spring.datasource.url=jdbc:h2:async:mem:testdb

Documentation: http://www.h2database.com/html/advanced.html