How to import source data into a database using Hibernate?

Question

When deploying applications, I often use the capabilities of Hibernates to create a database schema to simplify deployment. This is easily achieved by setting the hibernate.hbm2ddl.auto property.

However, sometimes I also need to insert some source data into a database, such as root user. Is there a way that I could achieve this through hibernation with some kind of boot text file?

I know that I could easily program code that will do this, but just wondering if there is any utility that can help me achieve the same through configuration?

Answer 1

I found this by searching in "Hibernate fixtures":

Hibernate will create the database when the factory object manager (in fact, when the factory object manager is created by Hibernate's SessionFactory). If the named import.sql file exists in the root of the class path ('/import.sql'), Hibernate will execute SQL statements read from the file after creating the database schema. It is important to remember that before Hibernate creates the schema, it frees it up (it removes all tables, constraints, or any other database object that will be created in the process of constructing the schema).

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

Try it and let us know if it works!

Answer 2

Adding import.sql to the class path works fine, hbm2ddl checks if the file exists and executes it. The only additional information is that each sql command must be on a separate line, otherwise it will not be executed.

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

Answer 3

Add the hibernate hibernate.hbm2ddl.import_files configuration to the hibernation configuration. Modify the hibernate.hbm2ddl.auto property to create. Add the initial_data.sql file to / classes with sql source code to insert the data. Hibernate will do this after creating the 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 the property to your hibernation configuration, you can create the import.sql file in the / classes directory and use hibernate by default if the hibernate.hbm2ddl.auto property is equal to creation

Answer 4

Why the hbm2ddl.auto and hbm2ddl.import_files are evil

(If used improperly as a database change management tool)

As stated elsewhere , using hibernate.hbm2ddl.auto and hibernate.hbm2ddl.import_files to manage database changes has a number of serious drawbacks:

1. Only the structure can be changed. Existing values ​​can be overwritten or, in the worst case, simply sent to Nirvana. Without a tool like liquibase or scriptella , you will not have any ETL capabilities.
2. This method has no transactions. Both the structure and the data operators will be executed before the transaction manager takes over. Suppose you have a 42 statement error in 256. Your database is currently in an inconsistent state.
3. Imvho, you lose transparency and control: where the script or liquidation change is installed or usually fixed along with changes in the domain models, you make changes to the domain model and hope (mainly) that hibernate will find what to do. (This is not so, but it is a different story.)
4. For integration, system and acceptance testing, you simply assume that your test databases are in exactly the same state as the production database. You have to follow this manually (good luck and have fun !;)). If you make a mistake, a small mistake is enough, the results can be very disastrous.

I personally use liquibase to manage database changes and have developed the following workflow to reduce maintenance workloads:

• Create a change log from the command line of my latest release structure
• Create a change log for my latest database
• Manually diff of both change logs (usually the changes are not so large, and if they are, they usually come across one of the disadvantages of the liquibases diff command .
• create change set

Even for complex changes in which customChange needs to be implemented, this can be achieved within hours, including defining rollbacks, testing, and documenting. For trivial changes, this is a matter of minutes. Essentially: you need to do a bit more work (I created custom change sets for 4 database configurations in less than a day), but you are sure that you have done everything possible to keep the database in a consistent state.

Answer 5

After a couple of hours, tripping over it, I decided to share what I found, although this is a very old post.

For it to work correctly, I had to do the following:

• hbmddl set to create or create-drop
• file.sql in the root of the path; in my case, I just put it in the resources folder, I use maven.
• each sql command on one line
• each .sql file should have an empty line at the beginning of the file ==>, I don’t know the reason for this, but if I do not insert this empty line, the server runtime tells me there is a syntax error near the first character.

Hope this helps.

Answer 6

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