Create mysql at startup db within Hibernate

Question

i am writing spring mvc project. i wanna to create mysql database at startUp of my application. tried to read file "import.sql". And i got an error:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown database 'test3'

here is my spring-servlet.xml:

    <context:annotation-config />
<context:component-scan base-package="com.joseph" />    


<bean id="propertyConfigurer"
    class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"
    p:location="/WEB-INF/jdbc.properties" />

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
    destroy-method="close" p:driverClassName="${jdbc.driverClassName}"
    p:url="${jdbc.databaseurl}" p:username="${jdbc.username}" p:password="${jdbc.password}" />


<bean id="sessionFactory"
    class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
    <property name="dataSource" ref="dataSource" />
    <property name="configLocation">
        <value>classpath:hibernate.cfg.xml</value>
    </property>
    <property name="configurationClass">
        <value>org.hibernate.cfg.AnnotationConfiguration</value>
    </property>
    <property name="hibernateProperties">
    <value>
        hibernate.dialect=${hibernate.dialect}
        hibernate.jdbc.batch_size=${hibernate.jdbc.batch_size}
        hibernate.show_sql=${hibernate.show_sql}
        hibernate.hbm2ddl.auto=${hibernate.hbm2ddl.auto}
        hibernate.id.new_generator_mappings=${hibernate.id.new_generator_mappings}
        hibernate.hbm2ddl.import_files=${hibernate.hbm2ddl.import_files}

     </value>
</property>

</bean>


<bean id="viewResolver" class="org.springframework.web.servlet.view.UrlBasedViewResolver">
    <property name="viewClass" value="org.springframework.web.servlet.view.JstlView"/>
    <property name="prefix" value="/WEB-INF/jsp/"/>
    <property name="suffix" value=".jsp"/>
</bean>
<tx:annotation-driven />

<bean id="transactionManager"
    class="org.springframework.orm.hibernate3.HibernateTransactionManager">
    <property name="sessionFactory" ref="sessionFactory" />
</bean>

and jdbc.properties:

jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.dialect=org.hibernate.dialect.MySQLDialect
jdbc.databaseurl=jdbc:mysql://localhost:3306/test3  
jdbc.username=root
jdbc.password=root

    #org.hibernate.dialect.PostgreSQLDialect
    hibernate.dialect=org.hibernate.dialect.MySQLDialect
    hibernate.show_sql = true
    hibernate.id.new_generator_mappings = true
    hibernate.hbm2ddl.auto = create
    hibernate.jdbc.batch_size = 5
    hibernate.transaction.factory_class=org.transaction.JDBCTransactionFactory
    hibernate.hbm2ddl.import_files = import.sql

import.sql

CREATE SCHEMA IF NOT EXISTS test3;

hibernate.cfg.xml

<hibernate-configuration>
    <session-factory>   
     <mapping class="com.joseph.model.Student" />        
    </session-factory>         
</hibernate-configuration>

Because no database with test3 does not exist. you should create a database (schema) named test3 first, then during application startup, your tables will be created. — Ali Dehghani
– Ali Dehghani, Commented Dec 30, 2014 at 10:30
that's it. How can i automatically create schema at application startup ? — lev
– lev, Commented Dec 30, 2014 at 10:40
It's not a good idea to generate schema (and even tables) automatically. See Hibernate/JPA DB Schema Generation Best Practices — Ali Dehghani
– Ali Dehghani, Commented Dec 30, 2014 at 10:50

Community · Accepted Answer · 2017-05-23 12:12:49Z

1

Add a import.sql file in resource as follow:

/*create database at first time*/ 
CREATE SCHEMA your-database-name;

'hibernate.cfg.xml' as follow:

<hibernate-configuration>
    <session-factory>
       ...
       ...
       <property name="hbm2ddl.import_files">import.sql</property>
       ...
       ...
    </session-factory>
</hibernate-configuration>

Also refer this for doing this programmatically

edited May 23, 2017 at 12:12

CommunityBot

11 silver badge

answered Dec 30, 2014 at 10:54

Ankur Singhal

26.1k17 gold badges92 silver badges129 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

lev Over a year ago

problem of this solution that hibernate first trying to connect to the database(scheme) and only after that goes to import.sql and creates a new scheme(according to the file context). How to make order opposite?

Collectives™ on Stack Overflow

Create mysql at startup db within Hibernate

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related