How to add Fixtures to Spring Hibernate project

Question

I'm trying to add fixtures / sample data to a Spring project which uses Hibernate as JPA.

I've created a fixtures.sql file (in WEB-INF/classes/fixtures.sql) containing a bunch of SQL queries that I want to execute to fill the database with sample data while I develop. In development mode I'm using the in memory database Hypersonic InMemory, when I deploy to either Postgres or MySQL, this same data will be loaded.

How do I load this data when the application restarts / starts up? Hypersonic wipes the in-memory database every time the application restarts which means I need to go through all the steps to create sample data every time I want to test something in the front-end.

database.properties:

database.password=
database.url=jdbc\:hsqldb\:mem\:myproject
database.username=sa
database.driverClassName=org.hsqldb.jdbcDriver

persistence.xml

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" version="2.0" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
<persistence-unit name="persistenceUnit" transaction-type="RESOURCE_LOCAL">
        <provider>org.hibernate.ejb.HibernatePersistence</provider>
        <properties>
            <property name="hibernate.dialect" value="org.hibernate.dialect.HSQLDialect"/>
            <!-- value="create" to build a new database on each run; value="update" to modify an existing database; value="create-drop" means the same as "create" but also drops tables when Hibernate closes; value="validate" makes no changes to the database -->
            <property name="hibernate.hbm2ddl.auto" value="create"/>
            <property name="hibernate.ejb.naming_strategy" value="org.hibernate.cfg.ImprovedNamingStrategy"/>
            <property name="hibernate.connection.charSet" value="UTF-8"/> 
        </properties>
    </persistence-unit>
</persistence>

Answer 1

You can use <jdbc:initialize-database>, see 12.9 Initializing a DataSource.

Answer 2

Three potential ways:

1. Use import.sql (see http://blog.eyallupu.com/2007/05/hibernates-hbm2ddl-tool.html in the section on "create")

2. Use DBUnit (see http://onjava.com/pub/a/onjava/2004/01/21/dbunit.html)

3. Write code to populate the database at @SetUp (or app startup) using by creating domain objects and saving them.