18

I have a Spring/Hibernate webapp that has some integration tests that run on an in-memory HSQL database. Hibernate takes this blank database and creates all of my test tables and constraints thanks to hbm2ddl=create. However, I have a new bean that checks for a particular config value from the database during its afterPropertiesSet() method, and so when this bean is initialized, such a row needs to exist in the database.

Is there any good way to set up a Java/Spring/Hibernate equivalent of Rail's test fixtures? I'm trying to find a way to tell Hibernate "whenever you create this table, insert these rows immediately afterwards". I couldn't find a callback or a hook I could add, but maybe there's another way.

Pascal Thivent
  • 562,542
  • 136
  • 1,062
  • 1,124
Brandon Yarbrough
  • 37,021
  • 23
  • 116
  • 145

3 Answers3

29

I'm trying to find a way to tell Hibernate "whenever you create this table, insert these rows immediately afterwards"

Since Hibernate 3.1, you can include a file called import.sql in the runtime classpath of Hibernate and at the time of schema export, Hibernate will execute the SQL statements contained in that file after the schema has been exported.

This feature has been announced in the Rotterdam JBug and Hibernate's import.sql blog post:

import.sql: easily import data in your unit tests

Hibernate has a neat little feature that is heavily under-documented and unknown. You can execute an SQL script during the SessionFactory creation right after the database schema generation to import data in a fresh database. You just need to add a file named import.sql in your classpath root and set either create or create-drop as your hibernate.hbm2ddl.auto property.

I use it for Hibernate Search in Action now that I have started the query chapter. It initializes my database with a fresh set of data for my unit tests. JBoss Seam also uses it a lot in the various examples. import.sql is a very simple feature but is quite useful at time. Remember that the SQL might be dependent on your database (ah portability!).

#import.sql file
delete from PRODUCTS
insert into PRODUCTS (PROD_ID, ASIN, TITLE, PRICE, IMAGE_URL, DESCRIPTION) values ('1', '630522577X', 'My Fair Lady', 19.98, '630522577X.jpg', 'My Fair blah blah...');
insert into PRODUCTS (PROD_ID, ASIN, TITLE, PRICE, IMAGE_URL, DESCRIPTION) values ('2', 'B00003CXCD', 'Roman Holiday ', 12.98, 'B00003CXCD.jpg', 'We could argue that blah blah');

For more information about this feature, check Eyal's blog, he wrote a nice little entry about it. Remember if you want to add additional database objects (indexes, tables and so on), you can also use the auxiliary database objects feature.

It is still not really documented.

Pascal Thivent
  • 562,542
  • 136
  • 1,062
  • 1,124
  • That's awesome! I can't find much documentation about it, but it works as advertised. Thanks! – Brandon Yarbrough Aug 04 '10 at 21:47
  • @CaptainAwesomePants: You're welcome. It's actually an undocumented feature. I'll add the "announcement". – Pascal Thivent Aug 04 '10 at 21:54
  • @Pascal Thivent Hi Pascal, I am aware. See what Emmanuel Bernard says here: http://stackoverflow.com/questions/2108178/id-generatedvalue-but-set-own-id-value and let me know what you think – Arthur Ronald Aug 20 '10 at 03:35
  • @ArthurRonaldFDGarcia: Hello Arthur. I had a hard time trying to understand Emmanuel's answer, but I think I got it now. Emmanuel is actually answering a comment from the OP left on [another answer](http://stackoverflow.com/questions/2108178/id-generatedvalue-but-set-own-id-value/2109847#2109847) saying *"on startup I want to set a 'no User' with id 0"*. To achieve this, the OP can indeed use the `import.sql`. But this is very different from the original question which is much more generic. Emmanuel's answer doesn't cover the original generic question, only the actual specific requirement. – Pascal Thivent Aug 20 '10 at 04:08
  • @Pascal Thivent Very strange! – Arthur Ronald Aug 20 '10 at 05:32
  • @Pascal Thivent Hi, can you provide The answer where you highlights a four-series articles about JSR 303 - bean validation ??? – Arthur Ronald Aug 20 '10 at 19:03
  • @Arthur Do you mean this one: [How do I check that an entity is unreferenced in JPA?](http://stackoverflow.com/questions/3013986/how-do-i-check-that-an-entity-is-unreferenced-in-jpa/3015257#3015257)? – Pascal Thivent Aug 20 '10 at 19:38
  • The first two links are dead. – ChiefTwoPencils Sep 21 '15 at 05:51
6

In hibernate 3.6 the configuration that allows to run arbitrary sql commands is:

hibernate.hbm2ddl.import_files

See in http://docs.jboss.org/hibernate/core/3.6/reference/en-US/html_single/, noticing there is an error in the documentation: the property is import_files, with an s in the end.

mmalmeida
  • 1,037
  • 9
  • 27
1

If you're talking about JUnit tests and using AbstractTransactionalDataSourceSpringContextTests there's methods you can override like onSetupBeforeTransaction that provide a hook to pre-populate test table data etc.

brabster
  • 42,504
  • 27
  • 146
  • 186