In-memory database using Spring 3

In my previous post we discussed about how to run embedded web container. It helped you to remove the dependency of web container for integration testing. In this post, we will discuss about using h2 in-memory database.

What is embedded database and its uses

An embedded database is one you don't see, It's running inside another software product. It stores data entirely in main memory. This kind of database is quite different from the type most people are familiar with, what might be called as exposed database (Oracle, Sql, MySql etc), which are designed for data storage on persistent media. I used in-memory database (h2 db) for integration testing where i wanted application to use  in-memory db so that i can remove the dependency of db while running my integration test cases during maven build. This is also very useful in testing database related code.

Configuration using Spring 3

Spring 3 release has support for in-memory database.Spring 3 supports HSQL, H2 and DERBY natively.In case if you are using spring 3, you can define the data source in spring context file using jdbc:embedded-database tag in the spring-jdbc namespace.

<beans xmlns="http://www.springframework.org/schema/beans"
  xmlns:context="http://www.springframework.org/schema/context"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
  xmlns:context="http://www.springframework.org/schema/jdbc/spring-jdbc-3.0.xsd"
  xsi:schemaLocation="http://www.springframework.org/schema/beans 
  http://www.springframework.org/schema/beans/spring-beans-3.0.xsd   
  http://www.springframework.org/schema/context 
  http://www.springframework.org/schema/context/spring-context-3.0.xsd 
  http://www.springframework.org/schema/jdbc 
  http://www.springframework.org/schema/jdbc/spring-jdbc-3.0.xsd">
  
  <jdbc:embedded-database id="embedded-datasource" type="H2"> 
    <jdbc:script location="classpath:schema.sql"/> 
    <jdbc:script location="classpath:data.sql"/> 
  </jdbc:embedded-database>

  .............
  ..........
</beans>

This will automatically create a new data source called "embedded-datasource" using H2, and after starting the database, it will load the sql scripts from classpath. In my sql scripts, i had script for creating the schema, table and insert statement for populating the data in db  with which i want to run my test cases.

In case, if you want to use HSQL db or DERBY db, you will have to change type to hsql or derby. That's all you need to do to switch from one db to another, rest all is taken care by spring.

I'm using JPA with hibernate for my application development. There will not be any change in hibernate configuration. You can create entity manager factory as shown below.

<bean id="entityManagerFactory"
  class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
  <property name="dataSource" ref="embeded-datasource" />
  <property name="persistenceUnitName" value="SamplePU"></property>
</bean>