Eyal Lupu - All About Java

Embedding and Initializing Databases in Spring 3.0

I was upgrading my blog environment to Spring 3.0 when I noticed a small, but useful, new feature in Spring 3.0: support for embedding and initializing databases using the application context. Using this support one can configure embedded database engine as part of the application context and use it just as another bean. Obviously this can be done without any help from Spring (for example many times I use embedded Derby just by setting the appropriate properties in JPA's persistence.xml) but externalizing this configuration can be useful in many cases. In addition Spring supports the execution of multiple scripts for the database initialization both for embedded and external databases. In this short entry I'll illustrate the configuration I was using for my Hibernate environment.

How to Configure

So I'm using Spring 3.0 release and I want to configure my Hibernate's Entity Manager to use an embedded database configured by Spring. First I have to setup my Hibernate environment as usual (persistence.xml, mappings, and so on) one thing I've taken out of my persistence XML is my database connection properties (hibernate.connection.driver_class, hibernate.connection.username, hibernate.connection.password, hibernate.connection.url). The next step is to configure my application context to include the entity manager factory and the embedded database:

<bean class="org.springframework.orm.jpa.JpaTransactionManager"
  id="transactionManager" p:entityManagerFactory-ref="entityManagerFactory" />

<jdbc:embedded-database id="dataSource" type="DERBY">
</jdbc:embedded-database>

<bean class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean"
id="entityManagerFactory" p:dataSource-ref="dataSource"/>

Once I'll obtain the entity manager factory from the application context it will be using the embedded database. Since I'm using the Spring testing support all I have to do it correctly annotate my test class:

@RunWith(SpringJUnit4ClassRunner.class)@ContextConfiguration
public class UsingSpringCustomerTest{

Random random = new Random();

@PersistenceUnit
private EntityManagerFactory entityManagerFactory;

@Test
public void testSelects() {
 ...
 ...
}

That's it - as simple as that.

Some More Configuration

Out of the box Spring support the following databases:

• Derby (use "DERBY" in the type attribute - as the sample above)
• H2 (use "H2" in the type attribute), and
• HSQL (use "HSQL" in the type attribute, or just remove the attribute since this is the default)

Using the jdbc:embedded-database element we can also include a set of SQL scripts to initialize our database. As oppose to Hibernate's import.sql file we can use multiple files and even ANT like patterns matching:

<bean class="org.springframework.orm.jpa.JpaTransactionManager"
  id="transactionManager" p:entityManagerFactory-ref="entityManagerFactory" />

<jdbc:embedded-database id="dataSource" type="DERBY">
   <jdbc:script location="classpath:com/jroller/blogs/eyallupu/derby/tables.sql"/>
   <jdbc:script location="classpath:com/jroller/blogs/eyallupu/derby/constraints.sql"/>
   <jdbc:script location="classpath*:com/jroller/blogs/eyallupu/derby/data*.sql"/>
</jdbc:embedded-database>

<bean class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean"
id="entityManagerFactory" p:dataSource-ref="dataSource"/>

When using the ANT style wildcards the files will be loaded in a lexical order (data-01.sql, data-02.sql...). Section 12.9 in the 3.0 documentation (I didn't try it myself) says that the database initialization support can also be used for any bean that provides an instance of DataSource to the context. For example, taking us out from the testing environments, in a "real" environment we can provide a data source bean (using JNDI lookup, factory bean or any other mean) and initialize it using the initialize-database XML element:

<bean class="...." id="myDataSource"/>


<jdbc:initialize-database data-source="myDataSource" enabled="true">
   <jdbc:script location="classpath:com/jroller/blogs/eyallupu/derby/tables.sql"/>
   <jdbc:script location="classpath:com/jroller/blogs/eyallupu/derby/constraints.sql"/>
   <jdbc:script location="classpath*:com/jroller/blogs/eyallupu/derby/data*.sql"/>
</jdbc:initialize-database>

Posted at 02:54PM Jan 15, 2010 by Eyal Lupu in Spring Framework  |  Comments[0]