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!

Why does Hibernate to drop foreign keys on an empty database? Try to enable SQL logging to see how Hibernate tries to examine the existing database schema. — Aaron Digulla, Oct 09 '13 at 15:10
it didn't even try to create a table at first or check if one is there it just tried directly to drop the keys. hell i dont know why it does it. i still get the same error on the "first" run on the DB but after that, the tables are there and it works with them... very odd — pascalwhoop, Oct 09 '13 at 15:39
The error suggests that you're not using the database that you think. Check the config once more, set breakpoints and debug this. — Aaron Digulla, Oct 09 '13 at 15:42

dcrivella · Answer 1 · 2018-06-12T03:27:21.043

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>

score 0 · Answer 2 · edited May 23 '17 at 11:53

This sounds like https://hibernate.atlassian.net/browse/HHH-7002.

In my case, I was able to eliminate the exceptions by configuring Hibernate to use the following class via the hibernate.dialect property in persistence.xml:

import org.hibernate.dialect.H2Dialect;

// This class is a workaround for https://hibernate.atlassian.net/browse/HHH-7002
public class CustomH2Dialect extends H2Dialect {

    @Override
    public String getDropSequenceString(String sequenceName) {
        // Adding the "if exists" clause to avoid warnings
        return "drop sequence if exists " + sequenceName;
    }

    @Override
    public boolean dropConstraints() {
        // We don't need to drop constraints before dropping tables; that just
        // leads to error messages about missing tables when we don't have a
        // schema in the database
        return false;
    }

    @Override
    public boolean supportsIfExistsBeforeTableName() {
        return true;
    }

    @Override
    public boolean supportsIfExistsAfterTableName() {
        return false;
    }

    @Override
    public String getCascadeConstraintsString() {
        return " CASCADE ";
    }
}

(Thanks to https://stackoverflow.com/a/20698339/14379)

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

2 Answers2

USE this JPA PROPERTY:

INSTEAD this HIBERNATE PROPERTY:

WORKING H2 in Memory persistence.xml example:

Linked