hibernate create schema if not exists mysql

Question

I'm using Spring 3 and hibernate 4.

Here is my root-context.xml

<bean id="dataSource"
        class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="url" value="jdbc:mysql://localhost:3306/musicstore"></property>
        <property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
        <property name="username" value="root"></property>
        <property name="password" value="123456"></property>
    </bean>


    <bean id="sessionFactory" name="sessionFactory"
        class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
        <property name="dataSource" ref="dataSource"></property>

        <property name="hibernateProperties">
            <props>     
                <prop key="hibernate.dialect"> org.hibernate.dialect.MySQLDialect </prop>
                <prop key="hibernate.max_fetch_depth">3</prop>
                <prop key="hibernate.jdbc.fetch_size">50</prop>
                <prop key="hibernate.jdbc.batch_size">10</prop>
                <prop key="hibernate.show_sql">true</prop>
                <prop key="hibernate.hbm2ddl.auto">update</prop>

            </props>
        </property>
        <property name="packagesToScan" value="domain" /><!-- 
            entity -->
    </bean>

And I've got this :

WARN : org.hibernate.engine.jdbc.internal.JdbcServicesImpl - HHH000342: Could not obtain connection to query metadata : Unknown database 'musicstore'

When I deploy my project in tomcat, I want hibernate will create the schema if it's not exist. I have tried hibernate.hbm2ddl.auto= create but it's not working

Are there any ways to do create the schema automatically at run time ? Any suggestions would be helpful :D

Thanks in advance.

Read http://stackoverflow.com/questions/438146/hibernate-hbm2ddl-auto-possible-values-and-what-they-do#1689769 — Reimeus, Oct 09 '14 at 07:57
@Reimeus Thanks for your suggestion, I have tried hibernate.hbm2ddl.auto= create but it does not create the database schema too. — Nessaj Nguyen, Oct 09 '14 at 08:12
refer ---> http://stackoverflow.com/questions/24632954/why-is-hibernate-not-creating-database-for-mysql — sushama Lanke, Apr 26 '17 at 10:53

score 22 · Accepted Answer · answered Mar 22 '16 at 22:10

I don't know how to solve your problem in a hibernate specific way, but a cool thing about MySQL is that you can (at the very least under certain conditions) specify for the Database itself to be created if it doesn't already exist via the connection string by appending "?createDatabaseIfNotExist=true" to the end.

So, by changing your Spring config to the following you should get the results you need.

<bean id="dataSource"
    class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="url" value="jdbc:mysql://localhost:3306/musicstore?createDatabaseIfNotExist=true"></property>
    <property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
    <property name="username" value="root"></property>
    <property name="password" value="123456"></property>
</bean>


<bean id="sessionFactory" name="sessionFactory"
    class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
    <property name="dataSource" ref="dataSource"></property>

    <property name="hibernateProperties">
        <props>     
            <prop key="hibernate.dialect"> org.hibernate.dialect.MySQLDialect </prop>
            <prop key="hibernate.max_fetch_depth">3</prop>
            <prop key="hibernate.jdbc.fetch_size">50</prop>
            <prop key="hibernate.jdbc.batch_size">10</prop>
            <prop key="hibernate.show_sql">true</prop>
            <prop key="hibernate.hbm2ddl.auto">update</prop>

        </props>
    </property>
    <property name="packagesToScan" value="domain" /><!-- 
        entity -->
</bean>

It's worth noting that I don't know anything more about this than that it works, so it may very well have limitations of which I am unaware.

thanx for this hack jdbc:mysql://localhost:3306/musicstore?createDatabaseIfNotExist=true — vvator, Feb 05 '17 at 22:20

score 0 · Answer 2 · edited May 23 '17 at 12:18

Hibernate requires the database to exist: it cannot help you create the database. If you need to create the database the you will need to implement another solution that executes before Hibernate is initialized.

As you are using Spring the following may be useful for executing the initial 'CREATE DATABASE X' statement.

http://docs.spring.io/spring-framework/docs/3.0.0.RC3/reference/html/ch12s09.html

You obviously need to ensure the initailization executes before the sessionFactory bean is initialized.

You would also likely define 2 datasources, one configured as outlined at the link provided by mretierer (Create MySQL database from Java) i.e. with no database defined and which is used by the Spring db initializer bean and one for use by Hibernate which references the database created during initialization.

No idea if any of this will work but looks feasible...

I have add this snipet code in my root-context.xml but it's not working :( — Nessaj Nguyen, Oct 09 '14 at 13:58

score -2 · Answer 3 · answered Oct 09 '14 at 07:59

-2

First the answer to your question: The property you already supplied should do what you were asking for.

   <prop key="hibernate.hbm2ddl.auto">update</prop>

The exception your are getting refers to a different problem. Hibernate can't connect to the database you specified.

Pls check your connection string

 <property name="url" value="jdbc:mysql://localhost:3306/musicstore"></property>

answered Oct 09 '14 at 07:59

mreiterer

556
1
4
15

in my opinion "musicstore" is the database name. so if the database exists it should work – mreiterer Oct 09 '14 at 08:23
It just create the table if the database schema "musicstore" is existing in mysql, but I want hibernate will create a database "musicstore" if it's not exist. – Nessaj Nguyen Oct 09 '14 at 08:24
2

I see. Take a look at: http://stackoverflow.com/questions/717436/create-mysql-database-from-java – mreiterer Oct 09 '14 at 08:28

hibernate create schema if not exists mysql

3 Answers3