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?

Flunk

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!

Solenne

from what I can see, the sql doesn't seem to be db-agnostic – Flunk

+! for the import.sql should be in the root not just anywhere – Koontz

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.

Trevino

It also works only if hbm2ddl.auto is set to "create" or "create-drop". – Frumenty

This comment was really helpful form me. – Pregnable

The requirement to put each sql statement in separate line is weird. Thanks for pointing it out – Explosive

You can enable multi-line sql commands by adding <property name="hibernate.hbm2ddl.import_files_sql_extractor">org.hibernate.tool.hbm2ddl.MultipleLinesSqlCommandExtractor</property> to your hibernate.cfg.xml (stackoverflow.com/a/15090964) – Alister

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

Extrados

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.

Vargas

Liquibase is awsome, and for a product that is somewhat settled and in development by a team, that's an excellent idea. On the other hand if you are a solo developer, building a prototype, fleshing out the basics or otherwise exploring something that will churn very heavily, being able to quickly get it working in seconds rather than minutes or hours on deploy is quite valuable. Typically once there is such a thing as a QA data set or certainly by the time you have production data, hbm2ddl.auto should be removed. Silly to say either way is evil. – Epact

@Epact "nothing is more definitive than the temporary" ;) And something you really seem to have overlooked "(When misused as a tool for database change management)" The question clearly states that we are talking of deployments and hence db change management is the context here. And I uphold my statement: For deployment, both a are outright evil. BTW, even as s single developer, liquibase is not more work. In the long run, it is less. – Vargas

This is silly and pretty unhelpful. For simple automated tests, hbm2ddl is a perfectly adequate tool for most people. – Actual

Well, and how you make sure your production setup will migrate to your development setup? Fire and pray? – Vargas

You called a useful tool I wrote that hundreds of thousands of people have used successfully for almost two decades "evil". That it isn't the be-all end-all solution to every single problem in data management doesn't make it "evil". – Actual

You seem to have missed a part: "(When misused as a tool for database change management)". It can not be used for that without severe drawbacks, as laid out. – Vargas

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.

Menken

The "First-Line-Issue" sounds like BOM – Lodgment

I think you mean import.sql, not file.sql. – Gracia

By "file.sql" i meant any *.sql file, nevermind the name, as long as you specify it in the persistence file. import.sql is the name of the file imported by default (if present) without the need for specifying it in the persistence file. – Menken

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.

Actual

Answer 7

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

Gocart