In-memory database is not created but logs shows that DDL was executed

Question

I am trying to setup spring environment to run with in memory database which is automatically created based on provided mappings. During execution I can see in logs that DDL statements are executed, but when hibernate tries to insert data into created tables, I am getting 'Table not found' exception.

What can be a problem?

Using spring 3.1.1 and Hibernate 4.1.1 and H2 version 1.3.165.

Logs looks like (only revelant records left):

INFO at '25-04-2012 13:23:56.318' by thread 'main' from category 'org.hibernate.tool.hbm2ddl.SchemaExport':
HHH000227: Running hbm2ddl schema export
DEBUG at '25-04-2012 13:23:56.318' by thread 'main' from category 'org.hibernate.SQL':

    drop table DUMMIES if exists
Hibernate: 
    drop table DUMMIES if exists
DEBUG at '25-04-2012 13:23:56.318' by thread 'main' from category 'org.hibernate.SQL':

    create table DUMMIES (
        id bigint generated by default as identity,
        title varchar(255),
        primary key (id),
        unique (title)
    )
Hibernate: 
    create table DUMMIES (
        id bigint generated by default as identity,
        title varchar(255),
        primary key (id),
        unique (title)
    )
INFO at '25-04-2012 13:23:56.334' by thread 'main' from category 'org.hibernate.tool.hbm2ddl.SchemaExport':
HHH000230: Schema export complete
INFO at '25-04-2012 13:23:56.334' by thread 'main' from category 'org.springframework.context.support.ClassPathXmlApplicationContext':
Bean 'mySessionFactory' of type [class org.springframework.orm.hibernate4.LocalSessionFactoryBean] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)
INFO at '25-04-2012 13:23:56.631' by thread 'main' from category 'org.springframework.orm.hibernate4.HibernateTransactionManager':
Using DataSource [org.springframework.jdbc.datasource.SimpleDriverDataSource@a86d12] of Hibernate SessionFactory for HibernateTransactionManager
WARN at '25-04-2012 13:23:56.865' by thread 'main' from category 'org.hibernate.engine.jdbc.spi.SqlExceptionHelper':
SQL Error: 42102, SQLState: 42S02
ERROR at '25-04-2012 13:23:56.865' by thread 'main' from category 'org.hibernate.engine.jdbc.spi.SqlExceptionHelper':
Table "DUMMIES" not found; SQL statement:
insert into DUMMIES (id, title) values (null, ?) [42102-165]

Beans.xml looks like ('mypackage' is a replacement for full package name):

<bean id="myDataSource"
    class="org.springframework.jdbc.datasource.SimpleDriverDataSource">
    <property name="driverClass" value="org.h2.Driver" />
    <property name="url" value="jdbc:h2:mem:" />
    ...
</bean>

<bean id="mySessionFactory"
    class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
    <property name="dataSource" ref="myDataSource" />
    <property name="packagesToScan">
        <array>
            <value>mypackage.entities</value>
        </array>
    </property>
</bean>

<bean id="myTransactionManager"
    class="org.springframework.orm.hibernate4.HibernateTransactionManager">
    <property name="sessionFactory" ref="mySessionFactory" />
</bean>

<aop:config>
    <aop:pointcut id="daoMethods" expression="execution(* mypackage.*Dao.*(..))" />
    <aop:advisor advice-ref="requiredTxAdvice" pointcut-ref="daoMethods" />
</aop:config>

<tx:advice id="requiredTxAdvice" transaction-manager="myTransactionManager">
    <tx:attributes>
        <tx:method name="*" propagation="REQUIRED" />
    </tx:attributes>
</tx:advice>

class="org.springframework.dao.annotation.PersistenceExceptionTranslationPostProcessor" />
<bean id="dummyDao" class="mypackage.DummyDaoImpl">
    <property name="sessionFactory" ref="mySessionFactory" />
</bean>

hibernate.properties looks like:

hibernate.dialect=org.hibernate.dialect.H2Dialect
hibernate.show_sql=true
hibernate.format_sql=true
hibernate.id.new_generator_mappings=true
hibernate.hbm2ddl.auto=create-drop

(create does not worked too)

Answer 1

When you specify database URL as jdbc:h2:mem: H2 creates new database for each connection, thus each Hibernate session sees its own empty database.

So, you need to specify database name, to access the same database from different connections. Also you need to prevent the database from being closed when it has no active connections. The resulting URL looks like this: jdbc:h2:mem:foo;DB_CLOSE_DELAY=-1.

Also note that Spring provides built-in support for embedded databases, see 12.8 Embedded database support.