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>

That is very clear and most useful. Thank you very much for making this fully functionnal example available.

I found out that these two artifacts conflict:

<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>

In facto, the first one seems to contain all the classes of the second one and you can have strange runtime errors if you keep both.

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