How to import initial data to database with Hibernate?

Question

When deploying applications, I often use Hibernate’s capacity to create database schema in order to simplify the deployment. This is easily achievable by configuring hibernate.hbm2ddl.auto property.

However, on occasion I also need to insert some initial data to database, for example root user. Is there a way I could achieve this via hibernate with some kind of load textual file?

I know I could easily program the code that will do so, but just wondering if there is already some utility that can help me achieve the same via configuration?

Answer 1

I found this by doing a search on "Hibernate fixtures" :

Hibernate will create the database when the entity manager factory is created (actually when Hibernate's SessionFactory is created by the entity manager factory). If a file named import.sql exists in the root of the class path ('/import.sql') Hibernate will execute the SQL statements read from the file after the creation of the database schema. It is important to remember that before Hibernate creates the schema it empties it (delete all tables, constraints, or any other database object that is going to be created in the process of building the schema).

Source: http://www.velocityreviews.com/forums/t667849-hibernate-quotfixturesquot-or-database-population.html

Give it a try and let us know if it works!

Answer 2

Adding import.sql to the class path works great, hbm2ddl checks if the file exists and executes it. The only additional detail is that each sql command must be on its own line, otherwise it will fail to execute.

This will also work only if hbm2ddl.auto is set to create or create-drop.

Answer 3

Add hibernate property hibernate.hbm2ddl.import_files in your hibernate configuration. Change hibernate.hbm2ddl.auto property to create. Add initial_data.sql in /classes directory with initial sql code to insert data. Hibernate execute this after create database schema.

<bean id="sessionFactory"
    class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
    <property name="dataSource" ref="dataSource" />
    <property name="hibernateProperties">
        <props>
            <prop key="hibernate.dialect">${hibernate.dialect}</prop>
            <prop key="hibernate.show_sql">${hibernate.show_sql}</prop>
            <prop key="hibernate.hbm2ddl.auto">create</prop>
            <prop key="hibernate.hbm2ddl.import_files">initial_data.sql</prop>
        </props>
    </property>
</bean>

If you do not want to add a property in your hibernate configuration you can create a file import.sql in /classes directory and hibernate use this by default if property hibernate.hbm2ddl.auto equals to create

Answer 4

Why hbm2ddl.auto and hbm2ddl.import_files properties are evil

(When misused as a tool for database change management)

Like said elsewhere, using hibernate.hbm2ddl.auto and hibernate.hbm2ddl.import_files for database change management has some serious drawbacks:

1. Only the structure can be changed. Existing values might be overwritten or - in a worst case scenario - simply sent into Nirvana. Without a tool like liquibase or scriptella, you do not have any ETL capabilities.
2. This method has no transactions. Both the structure and the data statements will be executed before an transaction manager is taking over. Let's say you have an error in statement 42 of 256. Your database is in an inconsistent state now.
3. Imvho, you loose transparency and control: where a scriptella script or liquibase change set or usually committed together with the changes in the domain models, you do a change in the domain model and hope (basically) that hibernate will find out what to do. (It doesn't, but that is a different story.)
4. For integration, system and acceptance test you merely assume that your test databases are in the absolutely, exactly same state as your production database. You have to keep track of that manually (Good luck and have fun with it! ;) ). In case you make an error, just a small slip is sufficient, the results may very we'll be catastrophic.

I personally use liquibase for database change management and have developed the following workflow to reduce the maintenance work:

• Create a changelog from the command line of my last release structure
• Create a changelog of my latest database
• Manually diff both change logs (usually changes are not that huge, and if they are, they usually meet one of the shortcomings of liquibases diff command.
• create a change set

Even for complicated changes in which one has to implement a customChange, this can be achieved in a matter of hours, including the definition of rollbacks, testing and documentation. For trivial changes, it is a matter of minutes. Basically: you have to do a little more work (I have created customized change sets for 4 database configurations in less than a day), but you get the peace of mind that you have done everything possible to keep the database in a consistent state.

Answer 5

After a couple of hours stumbling with this, I decided to share what I've found, though it's a very old post.

To make it work properly, i had to do the following:

• hbmddl set to create or create-drop
• file.sql in classpath root; in my case, i just put it in resources folder, i'm using maven.
• each sql command in one line
• each file.sql must have a blank line at the beggining of the file ==> don't know the reason for this one, but if i don't insert that blank line, at the time of execution the servers tells me there's a syntax error near the first character.

Hope that helps.

Answer 6

The standard way to do this in JPA is to use the configuration property javax.persistence.sql-load-script-source.

You can explore various settings related to schema export and test data import listed in Hibernate's AvailableSettings class.

Answer 7

Please make sure that your import.sql is formatted correctly. Start with a one liner insert statement to test.