Archive for the ‘Hsqldb’ Category

How to create a TopLink EntityManagerFactory with Spring outside a J2EE container

6 September 2008 Leave a comment

Sometimes you need to create a JPA EntityManagerFactory outside a J2EE container. One of the cases is if you are using Spring and jpa with a standalone application. As the jdocs for the JpaTemplate state the preferred way is to use a LocalContainerEntityManagerFactoryBean and set the required properties. The example code below demonstrates the creation of an EntityManagerFactory by using HSQLDB

DataSource datasource = new DriverManagerDataSource("org.hsqldb.jdbcDriver", "jdbc:hsqldb:mem:test", "user", "password");
LocalContainerEntityManagerFactoryBean bean = new LocalContainerEntityManagerFactoryBean();
bean.setJpaVendorAdapter(new TopLinkJpaVendorAdapter());
EntityManagerFactory factory = bean.getObject();

First we create a DriverManagerDataSource (from the Spring framework) and supply the required arguments (driver class, database url, username and password) and then we create an instance of the LocalContainerEntityManagerFactoryBean. We need to set the jpa vendor adapter (we can use Spring’s TopLinkJpaVendorAdapter which is Spring’s implementation for Oracle TopLink Essentials) and the datasource. Then we call the afterPropertiesSet() method which means that the bean instance will only perform initialisation after all properties are set (in the event of misconfiguration it will throw an exception).

This is it. Once we have the EntityManagerFactory we can use it to create a JpaTemplate

JpaTemplate jpa = new JpaTemplate(factory);

or if you programme to interface (which you should) the preferred way of doing this is

JpaOperations jpa = new JpaTemplate(factory);

Also it’s always proper to close the factory after you are done processing since leaving it open can result in a PersistenceException exception with error message “Exception Description: Attempted to deploy PersistenceUnit <unit name> while being in the wrong state [Undeployed]. Close all factories for this PersistenceUnit. “

If you get this error you need to close the factory after you are done processing

Categories: Hsqldb, Java, Spring, TopLink

Testing a stored procedure using hsqldb

5 December 2007 9 comments

Today I needed to test a data access object that will be calling a stored procedure in the database. Since we are using HSQLDB and mock objects to do all the database interactions while we develop, I needed to test it without having access to the actual stored procedure. At first I wasn’t sure how to do it. Then I started looking at the HSQLDB documentation and I realised that I could create an alias to a static method and treat it exactly like I could treat the stored procedure.

Lets say that the stored procedure returns a random number from 1 to 100. In my test class I define a static method that returns this number

public static int random()
return 1 + (int)(Math.random() * 100);

Then I create an alias to this method call

// Define the alias to the stored procedure as a
// static final variable
private static final String SP =
"CREATE ALIAS randomStoredProcedure

Note that I needed to define the full class name of my test class, including the package. I also used double quotes around the class declaration since HSQLDB automatically converts all lower case letters to upper case.

Once the stored procedure is loaded up in the in-memory database using

// Load the alias.

it can be called from the actual DAO implementation class like you would call any other stored procedure

// Call it to get the random number.
int random = jdbc.queryForInt("{call randomStoredProcedure()}");

The jdbc variable is of type SimpleJdbcOperations from the Spring framework. The implementation class can be used without changes with the real stored procedure. Neat.

Categories: Hsqldb, Java, Testing