Spring, Hibernate, HSQLDB and automatically creating tables

Description

An example of using Spring and Hibernate together to automatically create a HSQLDB in-memory database for testing hibernate mappings.

Foo.java

package net.twasink.hsqldbtest;

import static javax.persistence.GenerationType.AUTO;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table
public class Foo {
    
    @Id
    @GeneratedValue(strategy = AUTO)
    @Column
    private long id;
    
    @Column(length = 50, nullable = false, unique = true)
    private String name;
    
    @Column(length = 200, nullable = true)
    private String description;
    
    Foo() {
        // for hibernate.
    }
    
    public Foo(String name) {
        this.name = name;
    }
    
    public long getId() {
        return id;
    }
    
    public String getName() {
        return name;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }
}

FooTest.java

package net.twasink.hsqldbtest;

import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertNotNull;
import static org.junit.Assert.assertTrue;

import java.util.List;

import org.hibernate.SessionFactory;
import org.hibernate.classic.Session;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.transaction.annotation.Transactional;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("/testContext.xml")
@Transactional
public class FooTest {

    @Autowired
    private SessionFactory sessionFactory;
    private Session currentSession;

    @Before
    public void openSession() {
        currentSession = sessionFactory.getCurrentSession();
    }

    @Test
    public void shouldHaveASessionFactory() {
        assertNotNull(sessionFactory);
    }

    @Test
    public void shouldHaveNoObjectsAtStart() {
        List<?> results = currentSession.createQuery("from Foo").list();
        assertTrue(results.isEmpty());
    }

    @Test
    public void shouldBeAbleToPersistAnObject() {
        assertEquals(0, currentSession.createQuery("from Foo").list().size());
        Foo jobUser = new Foo("Bar");
        currentSession.persist(jobUser);
        currentSession.flush();
        assertEquals(1, currentSession.createQuery("from Foo").list().size());
    }

    @Test
    public void shouldBeABleToQueryForObjects() {
        shouldBeAbleToPersistAnObject();

        assertEquals(1, currentSession.createQuery("from Foo where name = 'Bar'").list().size());
        assertEquals(0, currentSession.createQuery("from Foo where name = 'Baz'").list().size());
    }
}

pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>net.twasink</groupId>
  <artifactId>hsqldb-hibernatetest</artifactId>
  <version>0.0.1-SNAPSHOT</version>

  <dependencies>
  	
  	
  	<dependency>
  		<groupId>org.springframework</groupId>
  		<artifactId>spring-orm</artifactId>
  		<version>3.0.6.RELEASE</version>
  	</dependency>
  	
  	<dependency>
  		<groupId>org.springframework</groupId>
  		<artifactId>spring-tx</artifactId>
  		<version>3.0.6.RELEASE</version>
  	</dependency>
  	<dependency>
  		<groupId>org.springframework</groupId>
  		<artifactId>spring-aspects</artifactId>
  		<version>3.0.6.RELEASE</version>
  	</dependency>
  	
  	<dependency>
  		<groupId>org.hibernate</groupId>
  		<artifactId>hibernate-core</artifactId>
  		<version>3.6.9.Final</version>
  	</dependency>
  	<dependency>
  		<groupId>org.hibernate</groupId>
  		<artifactId>hibernate-annotations</artifactId>
  		<version>3.5.6-Final</version>
  	</dependency>
  	<dependency>
  		<groupId>org.aspectj</groupId>
  		<artifactId>aspectjrt</artifactId>
  		<version>1.5.4</version>
  		<scope>runtime</scope>
  	</dependency>
        <dependency>
  		<groupId>org.javassist</groupId>
  		<artifactId>javassist</artifactId>
  		<version>3.16.1-GA</version>
  		<scope>runtime</scope>
  	</dependency>
        <!-- without a slf4j bridge, you get no logging if anything goes wrong. -->
        <dependency>
		<groupId>org.slf4j</groupId>
		<artifactId>slf4j-log4j12</artifactId>
		<version>1.6.4</version>
		<scope>runtime</scope>
	</dependency>

  	<dependency>
  		<groupId>junit</groupId>
  		<artifactId>junit</artifactId>
  		<version>4.10</version>
  		<scope>test</scope>
  	</dependency>
    <dependency>
  		<groupId>org.springframework</groupId>
  		<artifactId>spring-test</artifactId>
  		<version>3.0.6.RELEASE</version>
  		<scope>test</scope>
  	</dependency>
  	<dependency> <!--  needed to get AOPs around the test cases -->
  		<groupId>cglib</groupId>
  		<artifactId>cglib-nodep</artifactId>
  		<version>2.2.2</version>
  		<scope>test</scope>
  	</dependency>
  	<dependency>
  		<groupId>org.hsqldb</groupId>
  		<artifactId>hsqldb</artifactId>
  		<version>2.2.8</version>
  		<scope>test</scope>
  	</dependency>
  </dependencies>
</project>

testContext.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:jdbc="http://www.springframework.org/schema/jdbc"
	xmlns:tx="http://www.springframework.org/schema/tx"
	xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
	        http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.0.xsd
	        http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.0.xsd
	">

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

	<bean id="sessionFactory"
		class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
		<property name="dataSource" ref="dataSource" />
		<property name="packagesToScan" value="net.twasink.hsqldbtest" />
		<property name="hibernateProperties">
			<props>
				<prop key="hibernate.dialect">org.hibernate.dialect.HSQLDialect</prop>
				<prop key="hibernate.hbm2ddl.auto">create</prop>
				<prop key="hibernate.show_sql">true</prop>
			</props>
		</property>
		<!-- Another way of indicating that we want to update the schema; Spring 
			calls this _after_ the session factory is created, while the hbm2ddl flag 
			above is processed by Hibernate _during_ creationo f the session factory. 
			<property name="schemaUpdate" value="true" /> -->
	</bean>


	<bean id="transactionManager"
		class="org.springframework.orm.hibernate3.HibernateTransactionManager">
		<property name="sessionFactory" ref="sessionFactory" />
	</bean>

	<tx:annotation-driven />

</beans>

Thanks for very helpful tutorial. I’m new in Java and Hibernate as well. I managed to compile the code and understood the concept as well. But the problem is that I don’t know how to execute the code to debug some stuff. When I start app as java application in eclipse. I have a long list to of option. But I don’t see my actual test class to run. So How to run it and debug this app? Please see the link I asked same question in the stackoverflow @ http://stackoverflow.com/questions/26563177/using-in-memory-database-with-hibernate-tutorial-how-to-execute

How would I use the embedded data source for a hsqldb on a file?

Hi. Im having those errors:

Results :

Tests in error:
shouldHaveNoObjectsAtStart(datasource.FooTest): Foo is not mapped [from Foo]
shouldBeAbleToPersistAnObject(datasource.FooTest): Foo is not mapped [from Foo]
shouldBeABleToQueryForObjects(datasource.FooTest): Foo is not mapped [from Foo]

@kafelez

You are having those errors because maybe you are using a different package structure.
Check the below line in 'testContext.xml' -

<property name="packagesToScan" value="net.twasink.hsqldbtest" />

If you are using a different package then Spring would not be able to scan it for finding the Table mapping. Update it as per your package structure and check if it works.

Nice work