Situation
I have a local H2 in memory database for integration tests and an Oracle db for production. I do not control the Oracle DB model. The in memory H2 database is created automatically by adding
update
to hibernate properties in AnnotationSessionFactoryBean. The definition of the entity stored in DB points to a schema
@Entity
@Table(name = "business_operations", schema = "sowa")
public class BusinessOperation {
...
The problem
When creating the H2 database, Hibernate won’t create the schema before creating tables. As a result it will show errors when trying to create the tables in non existing schema and fail in any query (queries will be run with sowa.business_operations).
2011-01-18 15:13:30,884 INFO [org.hibernate.tool.hbm2ddl.SchemaUpdate] - Running hbm2ddl schema update 2011-01-18 15:13:30,885 INFO [org.hibernate.tool.hbm2ddl.SchemaUpdate] - fetching database metadata 2011-01-18 15:13:30,915 INFO [org.hibernate.tool.hbm2ddl.SchemaUpdate] - updating schema 2011-01-18 15:13:30,927 INFO [org.hibernate.tool.hbm2ddl.DatabaseMetadata] - table not found: business_operations 2011-01-18 15:13:30,941 ERROR [org.hibernate.tool.hbm2ddl.SchemaUpdate] - Unsuccessful: create table sowa.business_operations 2011-01-18 15:13:30,942 ERROR [org.hibernate.tool.hbm2ddl.SchemaUpdate] - Schema "SOWA" not found;
Turns out this bug is reported and open since 2006: link.
The solution
The solution to this problem is to create the schema before hibernate’s hbm2ddl turns on. That would be easy with H2 if we could tell H2 to initialize itself like this:
database.url=jdbc:h2:mem:;INIT=RUNSCRIPT FROM 'src/main/resources/scripts/create.sql';
All seems nice, except H2 RUNSCRIPT FROM command doesn’t work with relative resources as you may expect. Fortunatelly INIT allows us to give any commands, not just point to a script, so this little change will solve the problem:
database.url=jdbc:h2:mem:;INIT=create schema IF NOT EXISTS sowa
Yeah, I know it’s obvious and simple stupid, but looking at all the questions on all the mailing lists in google I may have just saved a little bit of somebody’s time.
