H2 in-mem-DB with hibernate set to create giving me table not found errors

Question

I wanted to set up a project with hibernate, spring mvc and H2 as (for now) in memory DB. When everything boots up (in jetty) I get errors saying that the tables aren't yet present.

This is the error I get:

Okt 09, 2013 3:42:47 PM org.hibernate.tool.hbm2ddl.SchemaExport execute
INFO: HHH000227: Running hbm2ddl schema export
Okt 09, 2013 3:42:47 PM org.hibernate.tool.hbm2ddl.SchemaExport perform
ERROR: HHH000389: Unsuccessful: alter table PUBLIC.Object drop constraint FK_9sd2x4ehbugdjyrp1xqmsjw00
Okt 09, 2013 3:42:47 PM org.hibernate.tool.hbm2ddl.SchemaExport perform
ERROR: Tabelle "OBJECT" nicht gefunden
Table "OBJECT" not found; SQL statement:
...

This would be OK if I hadn't set Hibernate such as this:

hibernate.hbm2ddl.auto=create

and the connection string for H2 is this:

jdbc:h2:mem:test;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=TRUE

I can connect to the DB using IntelliJ so it is there..

I expected Hibernate to generate the tables for me and then the constraints and whatever else follows but for some reason it doesn't do that. Could it be a user rights thing? these are the user settings i set up:

jdbc.user=sa
jdbc.pass=

Any help is appreciated! Thanks :)

UPDATE

If i change the connection string to this:

jdbc.url=jdbc:h2:~/db/database.db;DB_CLOSE_ON_EXIT=TRUE;INIT=create schema IF NOT EXISTS generic;

it seems to work. But why it doesn't work in memory and why it didn't work before when i set the default schema to "public" (which was there already so i figured might as well just dump my stuff in there...) IDK!

Answer 1

In addition to using:

• DB_CLOSE_DELAY=-1;
• DB_CLOSE_ON_EXIT=FALSE;
• DATABASE_TO_UPPER=false

USE this JPA PROPERTY:

<property name="javax.persistence.schema-generation.database.action" value="drop-and-create"/>

INSTEAD this HIBERNATE PROPERTY:

<property name="hibernate.hbm2ddl.auto" value="create-drop" />

WORKING H2 in Memory persistence.xml example:

<?xml version="1.0" encoding="UTF-8" ?>
<persistence xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"
    version="2.0" xmlns="http://java.sun.com/xml/ns/persistence">

    <persistence-unit name="testPU" transaction-type="RESOURCE_LOCAL">

        <description>Hibernate test case template Persistence Unit</description>    

        <class>com.domain.A</class>
        <class>com.domain.B</class>

        <exclude-unlisted-classes>true</exclude-unlisted-classes>

        <properties>
            <property name="hibernate.connection.url" value="jdbc:h2:mem:test;MODE=PostgreSQL;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE;DATABASE_TO_UPPER=false" />
            <property name="hibernate.dialect" value="org.hibernate.dialect.H2Dialect" />
            <property name="hibernate.show_sql" value="true" />
            <property name="hibernate.format_sql" value="false" />          
            <property name="javax.persistence.schema-generation.database.action" value="drop-and-create"/>

            <property name="javax.persistence.schema-generation.scripts.action" value="drop-and-create" />
            <property name="javax.persistence.schema-generation.scripts.create-target" value="db-schema.jpa.ddl" />
            <property name="javax.persistence.schema-generation.scripts.drop-target" value="db-schema.jpa.ddl" />
        </properties>
    </persistence-unit>
</persistence>