Shekhar Gulati is a Java Consultant with over 5 years experience.He is currently working with Xebia India an Agile Software Development company.The postings on this site and on his blog are his own and do not necessarily represent opinion of his employer. His own blog is at http://whyjava.wordpress.com/ and you can follow him on twitter here. Shekhar has posted 25 posts at DZone. View Full User Profile

Spring 3 Makes Use of Embedded Databases Easy

09.27.2010
| 65089 views |
  • submit to reddit

One of the new features introduced in Spring 3 is the support for embedded Java database engines. Embedded databases like HSQL, H2, or Derby are very useful during the development phase of the project because they are fast, have small memory footprints, improve testability, and are opensource. Using such databases in your development environment eliminates the burden of bigger, bulkier databases like Oracle. If you are using any version of spring prior to version 3, you will configure HSQL databases datasource as shown below

<bean class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close" id="dataSource">
<property name="driverClassName" value="org.hsqldb.jdbcDriver" />
<property name="url" value="jdbc\:hsqldb\:mem\:spring-playground" />
<property name="username" value="sa" />
<property name="password" value="" />
</bean>

 and you refer this datasource bean in your entityManagerFactory bean as shown below

<bean class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean" id="entityManagerFactory">
<property name="dataSource" ref="dataSource" />
</bean>

In order to configure an embedded database, we have to take on the pain of remembering the driver class name, url, username or password. Well if you take a look at most of these configurable parameters, they should each be more of a convention than configuration. Using Spring 3 you can eliminate all this pain by simply using the 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="embeddedDatasource" type="HSQL"/>

</beans>

A single line of xml configuration shown above creates an instance of embedded HSQL database. The database instance is made available to the Spring container as a bean of type javax.sql.DataSource. This bean can then be injected into data access objects as needed. Spring provides support for HSQL, H2, and Derby natively where HSQL is the default database engine if you don't provide any type. The configuration shown above is useful when you are working with JPA and when you want to create an empty database and to auto-generate database schema i.e. by using hibernate.hbm2ddl.auto property.

Using the jdbc:embedded-database tag you can not only create an empty database but can also create a schema and insert data into it. For example,

<jdbc:embedded-database id="embeddedDataSource">
<jdbc:script location="classpath:schema.sql"/>
<jdbc:script location="classpath:data.sql"/>
</jdbc:embedded-database>

The above configuration will not only create an instance of embedded HSQL database instance but will also create a schema and will insert data into it. The SQL files specified in the jdbc:script tag should exist in the classpath otherwise you will get a FileNotFoundException.

Creating an embedded database instance programmatically

You can not only create an embedded database instance using xml but you can also create it programmatically by using EmbeddedDatabaseBuilder class fluent API.  This is helpful in scenarios where you need to test your application in a standalone environment without the full-blown Spring application context. It is recommended that your Unit test should be independent of Spring. Lets suppose that you want to unit test UserDao findUserByUsername method, and you want to test it against an embedded database. To do this you will write test code like this

public class UserDaoTest {

private EmbeddedDatabase database;

@Before
public void setUp() throws Exception {
database = new EmbeddedDatabaseBuilder().addDefaultScripts().build();
assertThat(database, is(notNullValue()));
}

@After
public void tearDown() throws Exception {
database.shutdown();
}

@Test
public void shouldFindUserByUsername() {
UserDao dao = new UserDao();
JdbcTemplate jdbcTemplate = new JdbcTemplate(database);
dao.setJdbcTemplate(jdbcTemplate);
User user = dao.findUserByUsername("shekhar");
assertThat(user, is(notNullValue()));
}
}

The code shown above will create an embedded database instance of HSQL database and will execute the default scripts to populate the database. The default scripts are named schema.sql which creates the database schema and data.sql which inserts the data into database.

Next time you use embedded database, remember Spring 3 embedded database support.

Published at DZone with permission of its author, Shekhar Gulati.

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)

Comments

Firass Shehadeh replied on Mon, 2010/09/27 - 10:59pm

Thanks for the article. I really like the simplicity of these alternative approaches, and they sure come handy when it comes to writing small and light unit tests.

Just wanted to add one minor comment regarding your statement:
"This is helpful in scenarios when you need to test your application in an standalone environment i.e. you don't want to use Spring".

I think you meant "testing without the need for a full-blown Spring application context".

Using the builder pattern will still introduce dependency on Spring classes, and the Spring jars still have to be in the classpath. But then again, there is nothing wrong with this dependency in our unit tests, because after all, I hope that we would be using other Spring classes in the main application classes.

Shekhar Gulati replied on Tue, 2010/09/28 - 11:24am in response to: Firass Shehadeh

Yes..You are correct. I meant without full-blown Spring application context. I will update the article. Thanks

Mayank Jain replied on Thu, 2014/05/01 - 11:03am

Hi Shekhar,

Thanks for the article. I am trying to implement the same with Spring 3.0.6, but unfortunately facing some issues.

Here is my persistence configuration file. (I am using the second approach which you have suggested in the article)

<?xml version="1.0" encoding="UTF-8"?>

<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="embeddedDatasource" type="Derby"/>
</beans>

Here is the exception stack trace

org.springframework.beans.factory.parsing.BeanDefinitionParsingException: Configuration problem: Failed to import bean definitions from relative location [persistence.xml]
Offending resource: class path resource [applicationContext.xml]; nested exception is org.springframework.beans.factory.xml.XmlBeanDefinitionStoreException: Line 13 in XML document from class path resource [persistence.xml] is invalid; nested exception is org.xml.sax.SAXParseException; lineNumber: 13; columnNumber: 80; Attribute "xmlns:context" was already specified for element "beans".
   at org.springframework.beans.factory.parsing.FailFastProblemReporter.error(FailFastProblemReporter.java:68)
   at org.springframework.beans.factory.parsing.ReaderContext.error(ReaderContext.java:85)
   at org.springframework.beans.factory.parsing.ReaderContext.error(ReaderContext.java:76)
   at org.springframework.beans.factory.xml.DefaultBeanDefinitionDocumentReader.importBeanDefinitionResource(DefaultBeanDefinitionDocumentReader.java:218)
   at org.springframework.beans.factory.xml.DefaultBeanDefinitionDocumentReader.parseDefaultElement(DefaultBeanDefinitionDocumentReader.java:147)
   at org.springframework.beans.factory.xml.DefaultBeanDefinitionDocumentReader.parseBeanDefinitions(DefaultBeanDefinitionDocumentReader.java:132)
   at org.springframework.beans.factory.xml.DefaultBeanDefinitionDocumentReader.registerBeanDefinitions(DefaultBeanDefinitionDocumentReader.java:93)
   at org.springframework.beans.factory.xml.XmlBeanDefinitionReader.registerBeanDefinitions(XmlBeanDefinitionReader.java:493)
   at org.springframework.beans.factory.xml.XmlBeanDefinitionReader.doLoadBeanDefinitions(XmlBeanDefinitionReader.java:390)
   at org.springframework.beans.factory.xml.XmlBeanDefinitionReader.loadBeanDefinitions(XmlBeanDefinitionReader.java:334)
   at org.springframework.beans.factory.xml.XmlBeanDefinitionReader.loadBeanDefinitions(XmlBeanDefinitionReader.java:302)
   at org.springframework.beans.factory.support.AbstractBeanDefinitionReader.loadBeanDefinitions(AbstractBeanDefinitionReader.java:143)
   at org.springframework.beans.factory.support.AbstractBeanDefinitionReader.loadBeanDefinitions(AbstractBeanDefinitionReader.java:178)
   at org.springframework.beans.factory.support.AbstractBeanDefinitionReader.loadBeanDefinitions(AbstractBeanDefinitionReader.java:149)
   at org.springframework.beans.factory.support.AbstractBeanDefinitionReader.loadBeanDefinitions(AbstractBeanDefinitionReader.java:212)
   at org.springframework.context.support.AbstractXmlApplicationContext.loadBeanDefinitions(AbstractXmlApplicationContext.java:126)
   at org.springframework.context.support.AbstractXmlApplicationContext.loadBeanDefinitions(AbstractXmlApplicationContext.java:92)
   at org.springframework.context.support.AbstractRefreshableApplicationContext.refreshBeanFactory(AbstractRefreshableApplicationContext.java:130)
   at org.springframework.context.support.AbstractApplicationContext.obtainFreshBeanFactory(AbstractApplicationContext.java:467)
   at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:397)
   at org.springframework.context.support.ClassPathXmlApplicationContext.<init>(ClassPathXmlApplicationContext.java:139)
   at org.springframework.context.support.ClassPathXmlApplicationContext.<init>(ClassPathXmlApplicationContext.java:93)


Thanks,

Mayank Jain

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.