Christopher Lakey

I knew that hibernate had a mechanism to load an sql script on startup, but I never took the time to track it down. Today I finally decided it was worth the effort. The mechanism is fairly simple, but not well documented. I found the following on the hibernate web site:

Ant tasks for schema creation and documentation

...

Also note that since Hibernate 3.1 you can include a file called "import.sql" in the runtime classpath of Hibernate. At the time of schema export it will execute the SQL statements contained in that file after the schema has been exported.

...

Emmanuel Bernard (Hibernate Search) also wrote a brief blurb about import.sql on the hibernate blog:

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-dropas 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.

import.sql works as expected. If you are using maven, simply create the import.sql in your src/test/resources or src/main/resources and the sql will be applied after the hibernate ddl statements have executed.

Update (2009/09/08)

While it is possible to change the file name of the imported script from "import.sql" to a user specified name, it is not possible to import multiple scripts. It looks like it would be fairly straight forward to support multiple scripts. Below is an extract from org/hibernate/tool/hbm2ddl/SchemaExport.java where the import is performed.

76     private String importFile = "/import.sql";
 ...
  
237    try {
238      InputStream stream = ConfigHelper.getResourceAsStream( importFile );
239      importFileReader = new InputStreamReader( stream );
240    }
 
 ...

261    if ( !justDrop ) {
262      create( script, export, outputFileWriter, statement );
263      if ( export && importFileReader != null ) {
264        importScript( importFileReader, statement );
265      }
266    }

Another approach to supporting multiple files is to use features of the database. For instance, h2 database provides a RUNSCRIPT command for executing script files. h2 only supports referencing scripts by file name, and not as resources on the classpath, but this could be changed using a custom function. Other databases should provide similar capabilities.