Cannot see content of h2 database when using it "in-memory"

Question

i am currently developing a spring mvc app with my domain models mapped to a h2 database by hibernate (4). My current problem is, that when i start the h2 webserver as bean (see context below) i cannot see the database when it´s created "in-memory" via the jdbcUrl of the datasource. Setting the jdbcUrl to a file and then accessing it via the web console -> Everything´s ok!

In a word...

Accessing h2 in memory, ie "jdbc:h2:mem:myapp", via webconsole doesn´t work.

Accessing it via "jdbc:h2:file:/tmp/myapp" works just fine.

Note that the problem is not saving entries via hibernate or so... i just don´t see the in memory database via the web console of h2. I´ve also tried to start a tcp server like mentioned in this thread (link), but it didn´t solve my problem.

Any ideas?

Here is my servlet-context.xml:

<?xml version="1.0" encoding="UTF-8"?>
<beans:beans xmlns="http://www.springframework.org/schema/mvc"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:beans="http://www.springframework.org/schema/beans"
    xmlns:context="http://www.springframework.org/schema/context"
    xmlns:tx="http://www.springframework.org/schema/tx"
    xsi:schemaLocation="http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc.xsd
        http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.1.xsd
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">

    <!-- DispatcherServlet Context: defines this servlet's request-processing infrastructure -->

    <!-- Enables the Spring MVC @Controller programming model -->
    <annotation-driven />

    <context:annotation-config />

    <context:component-scan base-package="org.wt.myapp" />

    <!-- Handles HTTP GET requests for /resources/** by efficiently serving up static resources in the ${webappRoot}/resources directory -->
    <resources mapping="/resources/**" location="/resources/" />

    <!-- Resolves views selected for rendering by @Controllers to .jsp resources in the /WEB-INF/views directory -->
    <beans:bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
        <beans:property name="prefix" value="/WEB-INF/views/" />
        <beans:property name="suffix" value=".jsp" />
    </beans:bean>

<!--    Database   -->

    <beans:bean id="h2console" class="org.h2.tools.Server"
        lazy-init="false" factory-method="createWebServer" init-method="start" >
        <beans:constructor-arg value="-web,-webAllowOthers,-webPort,8082" />
    </beans:bean>

    <beans:bean id="dataSource"
        class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <beans:property name="username" value="" />
        <beans:property name="password" value="" />
        <beans:property name="driverClassName" value="org.h2.Driver" />
        <beans:property name="url" value="jdbc:h2:mem:myapp" />
    </beans:bean>

    <beans:bean id="sessionFactory" class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
       <beans:property name="dataSource" ref="dataSource" />
       <beans:property name="packagesToScan" value="org.wt.myapp.db.domain" />
       <beans:property name="hibernateProperties">
           <beans:props>
               <beans:prop key="hibernate.dialect">org.hibernate.dialect.H2Dialect</beans:prop>
               <beans:prop key="hibernate.show_sql">true</beans:prop>
               <beans:prop key="hibernate.hbm2ddl.auto">create-drop</beans:prop>
           </beans:props>
       </beans:property>
    </beans:bean>

    <beans:bean id="transactionManager" 
               class="org.springframework.orm.hibernate4.HibernateTransactionManager" >
       <beans:property name="sessionFactory" ref="sessionFactory" />
    </beans:bean>

    <tx:annotation-driven/>

</beans:beans>

score 4 · Answer 1 · answered Nov 25 '13 at 19:19

Digging a little bit deeper into the h2 docs, i found what was missing:

When using an in-memory database, the default behaviour is that it automatically shuts down as soon as the last connection is closed. My other app (where the console just worked fine nearly out of the box) used a c3p0 connection pool, which always keeps some connections open -> this behaviour never occurs.

In the question (and my current app) there was a DriverManagerDataSource which doesn´t keeps connections open all the time... So, when hibernate was ready and finished creating the schema, the database shuts down.

To prevent this behaviour you can simply add the DB_CLOSE_DELAY=-1 at the end of the jdbcUrl. With that, the database keeps running, as long as the jvm does.

My jdbcUrl now looks like

jdbc:h2:mem:myapp;DB_CLOSE_DELAY=-1

For those interested in the docs, see h2 docs

Hope, i could help somebody.

Cannot see content of h2 database when using it "in-memory"

1 Answers1