Hibernate and H2 failure to create tables

[Rick]

I'm trying to use H2 for unit testing and basic deployment scenarios
with a Hibernate and Spring framework. For more advanced scenarios
we'll be supporting PostgreSQL. So in my unit tests I have a data
source for both H2 and PostgreSQL, as well as different Hibernate
properties for each. The data sources and Hibernate properties are
pretty straightforward:

* The data sources have driver, url, username, and password (H2 URL is
"jdbc:h2:mem:test")
* The properties have dialect, show_sql, and hbm2ddl.auto (set to
"create-drop")

I'm able to use both data sources with no problem through basic JDBC
operations, so I can verify that the databases both work. The problem
comes when I try to create some new objects and insert them into the
database with Hibernate. This works just fine with PostgreSQL, but
when I switch to H2 I get an error:

09:32:45,971 INFO impl.DefaultNotificationServiceImplTests: 49 -
Initializing test class
Hibernate: insert into category (event, scope, id) values
(?, ?, ?)
09:32:46,034 WARN org.hibernate.util.JDBCExceptionReporter: 359 -
SQL Error: 42102, SQLState: 42S02
09:32:46,035 ERROR org.hibernate.util.JDBCExceptionReporter: 456 -
Table "CATEGORY" not found; SQL statement:
insert into category (event, scope, id) values (?, ?, ?)
[42102-159]

It's not that the code can't create tables in the H2 schema. I added
the following code before my Hibernate code:

Connection connection = _dataSource.getConnection();
Statement statement = connection.createStatement();
statement.execute("CREATE TABLE category(id int PRIMARY KEY, event
varchar(255), scope int)");

When I do that, everything works fine. So it appears that, in the H2
dialect or with the H2 driver or SOMETHING, the tables are not getting
auto-created.

Any help with this issue would be greatly appreciated!

[Rick]

I have some more information and context on this issue that may help
in understanding it. For reference, I've created a sample project,
which is available here:

http://www.rickherrick.com/hbm_sample.jar

This is your basic Eclipse/STS/Maven 2-type project, so just extract
and you should be able to run:

jar xf hbm_sample.jar
cd hbm_sample
mvn clean install

When you first run it, you should get a successful build. The reason
this works is that the class CategoryServiceTests, in the method
testCreateCategory(), actually explicitly creates the category table
in lines 46-48. Now delete those three lines and run again. Now you
should get the error about which I'm curious:

Tests in error:

testCreateCategory(org.test.hbm_sample.services.CategoryServiceTests):
could not insert: [org.test.hbm_sample.api.Category]; SQL [insert into
category (event, scope, id) values (?, ?, ?)]; nested exception is
org.hibernate.exception.SQLGrammarException: could not insert:
[org.test.hbm_sample.api.Category]

Tests run: 3, Failures: 0, Errors: 1, Skipped: 0

This shows the failure to create that category table under these
circumstances. And here's where the H2 and PostgreSQL runs diverge.
Try changing lines 34 and 35 of the BasicPlatformTests-context.xml and
CategoryServiceTests-context.xml configuration files to use the
PostgreSQL data source and Hibernate properties:

<bean id="dataSource" parent="postgresqlDataSource" />
<bean id="hibernateProperties"
parent="postgresqlHibernateProperties" />

Note that, to use the data source as configured, you need a database
named test owned by a user named test with the password test. You can
just modify the PostgreSQL data source definition to use an existing
table and user if that's easier.

Now run Maven on this again and it should execute successfully,
meaning that the same code and configuration that just failed for H2
works properly for PostgreSQL.

Now here's what's REALLY strange about it, I think. The
BasicPlatformTests unit tests never fail, in spite of the fact that
they're doing nearly the same thing in the testCategoryDAO() method as
what the CategoryServiceTests.testCreateCategory() function is doing.
They're both using the DAO to create and retrieve objects in the
database. The primary difference that I see between them is that the
CategoryServiceTests calls cross @Transactional boundaries, whereas
everything in the BasicPlatformTests is within a single @Transactional
block. That might mean that the database is getting dropped between
the session invocations on the service, but the same behavior doesn't
happen with PostgreSQL, which it seems like it should.

If anyone has any insight or ideas on this, I'd love to hear them. We
really want to be able to run our unit tests without having to stand
up a test database in the build environment, but until we can
understand why we're seeing different behavior from H2, we can't
really do that!

Thanks!

[Noel Grandin]

Sounds to me that somehow the H2 database is being closed between the calls.
Because it's an in-memory database, that means that it'll get wiped clean.

You possibly need to open a connection in the beginning and hold it
open somehow so the database doesn't go away.

> --
> You received this message because you are subscribed to the Google Groups "H2 Database" group.
> To post to this group, send email to h2-da...@googlegroups.com.
> To unsubscribe from this group, send email to h2-database...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
>
>

[Thomas Mueller]

Hi,

To find out if the database is closed between the calls, could you try
what happens if you use a a persistent H2 database? (database URL
jdbc:h2:/data/db/test or so).

Regards,
Thomas