{"id":589,"date":"2011-01-18T16:37:00","date_gmt":"2011-01-18T14:37:00","guid":{"rendered":""},"modified":"2023-03-23T13:19:48","modified_gmt":"2023-03-23T12:19:48","slug":"hibernate-hbm2ddl-wont-create-schema-before-creating-tables","status":"publish","type":"post","link":"https:\/\/touk.pl\/blog\/2011\/01\/18\/hibernate-hbm2ddl-wont-create-schema-before-creating-tables\/","title":{"rendered":"Hibernate hbm2ddl won&#8217;t create schema before creating tables"},"content":{"rendered":"<p><strong>Situation<\/strong> 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<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">update<\/pre>\n<p>&nbsp; to hibernate properties in AnnotationSessionFactoryBean. The definition of the entity stored in DB points to a schema<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"java\">@Entity\r\n@Table(name = \"business_operations\", schema = \"sowa\")\r\npublic class BusinessOperation {\r\n...<\/pre>\n<p><strong>The problem<\/strong> When creating the H2 database, Hibernate won&#8217;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).<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">2011-01-18 15:13:30,884 INFO [org.hibernate.tool.hbm2ddl.SchemaUpdate] - Running hbm2ddl schema update\r\n2011-01-18 15:13:30,885 INFO [org.hibernate.tool.hbm2ddl.SchemaUpdate] - fetching database metadata\r\n2011-01-18 15:13:30,915 INFO [org.hibernate.tool.hbm2ddl.SchemaUpdate] - updating schema\r\n2011-01-18 15:13:30,927 INFO [org.hibernate.tool.hbm2ddl.DatabaseMetadata] - table not found: business_operations\r\n2011-01-18 15:13:30,941 ERROR [org.hibernate.tool.hbm2ddl.SchemaUpdate] - Unsuccessful: create table sowa.business_operations \r\n2011-01-18 15:13:30,942 ERROR [org.hibernate.tool.hbm2ddl.SchemaUpdate] - \r\nSchema \"SOWA\" not found;<\/pre>\n<p>Turns out this bug is reported and open since 2006:<\/p>\n<p><a href=\"http:\/\/opensource.atlassian.com\/projects\/hibernate\/browse\/HHH-1853\">link<\/a>. <strong>The solution<\/strong> The solution to this problem is to create the schema before hibernate&#8217;s hbm2ddl turns on. That would be easy with H2 if we could tell H2 to initialize itself like this:<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">database.url=jdbc:h2:mem:;INIT=RUNSCRIPT FROM 'src\/main\/resources\/scripts\/create.sql';<\/pre>\n<p>All seems nice, except H2 RUNSCRIPT FROM command doesn&#8217;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:<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">database.url=jdbc:h2:mem:;INIT=create schema IF NOT EXISTS sowa<\/pre>\n<p>Yeah, I know it&#8217;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&#8217;s time.<\/p>\n","protected":false},"excerpt":{"rendered":"Situation\nI 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\n&lt;prop key=&#8221;hibernate.hbm2ddl.auto&#8221;&gt;update&amp;l&#8230;Situation\nI 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\n&lt;prop key=&#8221;hibernate.hbm2ddl.auto&#8221;&gt;update&amp;l&#8230;\n","protected":false},"author":3,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[11],"tags":[687,142,43,68],"class_list":{"0":"post-589","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-development-design","7":"tag-db","8":"tag-h2","9":"tag-hibernate","10":"tag-java"},"_links":{"self":[{"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/posts\/589","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/comments?post=589"}],"version-history":[{"count":34,"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/posts\/589\/revisions"}],"predecessor-version":[{"id":15621,"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/posts\/589\/revisions\/15621"}],"wp:attachment":[{"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/media?parent=589"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/categories?post=589"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/tags?post=589"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}