Sometimes it can be useful to use JPA in a minimal environment to test your model or just if you’re curious of how things work in your DAO layer behind the scenes. In this blog post, we will create a simple project then see how you can use dbUnit to run some unit tests on your database.

The source code of this blog post is available on my github : https://github.com/geowarin/hibernate-examples/tree/master/hibernate-jpa-standalone-dbunit

First thing is to create a persistence.xml file in your resources. Default location is under META-INF/persistence.xml. Here is a snippet using an embedded hsqldb.

javax.persistence.jdbc.driver, url, user and password are standard JPA properties. The driver tells jdbc how to connect to your databse. Hsqldb must be in your classpath to find the corresponding driver. You could also init hsqdb to write to a file or use a different protocol, see the documentation for more information.

The properties prefixed with hibernate are vendor-specific properties :

  • hibernate.dialect will tell hibernate how to issue SQL queries for your database
  • hibernate.hbm2ddl.auto can be set to validate, update, create, create-drop. Since we will start a new database every time we launch our application, we will just create the tables on each run
  • hbm2ddl.import_files is a comma-separated list of paths to some custom SQL scripts that hibernate will execute after the database is created, we will explain this a little bit further
  • hibernate.show_sql and hibernate.format_sql will display the SQL queries generated by hibernate. You can tell hibernate to format them if you want a more human readable output

More information is available in the hibernate documentation.

Then, we will create a simple entity :

@Table(name = "users")
@Entity
public class User implements Serializable {

	@Id
	@GeneratedValue(strategy=GenerationType.AUTO)
	private long id;

	@Column(name = "name", nullable = false, unique=true, length=50)
	private String name;

	// Getters and setters omitted

}

Notice the @Table annotation that we use to specify the name of the table we are going to store our users in. This is useful information for our SQL scripts for example. For table naming, you should consider having simple conventions. Here, I just use lower case name of the entity and add an s to my table.

Same thing can be said about the @Column annotation.

Now let’s review our init script, import-users.sql :

INSERT INTO users(id, name) VALUES(1, 'admin');

Pretty straight forward. You just have to figure out the correct syntax from your annotations.

Last is the instantiation and use of the entity manager :

public class App {

	private static Logger log = LoggerFactory.getLogger(App.class);

	public static void main(String[] args) {

		EntityManagerFactory entityManagerFactory = Persistence.createEntityManagerFactory("persistence");
		EntityManager entityManager = entityManagerFactory.createEntityManager();

		User found = entityManager.find(User.class, 1L);
		log.info("found=" + found);
	}
}

Here you go! Since JPA 2 you can create the entity manager factory using the Persistence class. The string passed as parameter is the name of your persistence unit declared in your persistence.xml file. Spring and others will bootstrap the entity manager factory a little bit differently but the concept is essentially the same.

Using dbUnit

DbUnit is a database testing framework which allows you to load data and verify the correctness of your DAO layer using datasets.

Datasets are simple xml files which represent a database state. Here is an sample dataset :

We can use dbUnit in our simple project, we just have to deal with a little bit of boilerplate code. Here is the abstract class that my test will extend.

/**
 * Abstract unit test case class.
 * This will load the test-data.xml dataset before each test case and will clean the database before each test
 *
 * @author Geoffroy Warin (https://github.com/geowarin)
 *
 */
public abstract class AbstractDbUnitJpaTest {

	private static EntityManagerFactory entityManagerFactory;
	private static IDatabaseConnection connection;
	private static IDataSet dataset;
	protected static EntityManager entityManager;

	/**
	 * Will load test-dataset.xml before each test case
	 * @throws DatabaseUnitException
	 * @throws HibernateException
	 */
	@BeforeClass
	public static void initEntityManager() throws HibernateException, DatabaseUnitException {
		entityManagerFactory = Persistence.createEntityManagerFactory("persistence-test");
		entityManager = entityManagerFactory.createEntityManager();
		connection = new DatabaseConnection(((SessionImpl) (entityManager.getDelegate())).connection());
		connection.getConfig().setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY, new HsqldbDataTypeFactory());

		FlatXmlDataSetBuilder flatXmlDataSetBuilder = new FlatXmlDataSetBuilder();
		flatXmlDataSetBuilder.setColumnSensing(true);
		InputStream dataSet = Thread.currentThread().getContextClassLoader().getResourceAsStream("test-data.xml");
		dataset = flatXmlDataSetBuilder.build(dataSet);
	}

	@AfterClass
	public static void closeEntityManager() {
		entityManager.close();
		entityManagerFactory.close();
	}

	/**
	 * Will clean the dataBase before each test
	 *
	 * @throws SQLException
	 * @throws DatabaseUnitException
	 */
	@Before
	public void cleanDB() throws DatabaseUnitException, SQLException {
		DatabaseOperation.CLEAN_INSERT.execute(connection, dataset);
	}
}

And an example of usage :

public class AppTest extends AbstractDbUnitJpaTest {

	@Test
	public void testFind() {

		User user = entityManager.find(User.class, 1L);
		Assert.assertNotNull(user);
		Assert.assertEquals("userTest", user.getName());
	}

	@Test
	public void testInsert() {

		User newUser = new User();
		newUser.setName("insert");

		entityManager.getTransaction().begin();

		entityManager.persist(newUser);
		long id = newUser.getId();

		entityManager.getTransaction().commit();

		User user = entityManager.find(User.class, id);
		Assert.assertNotNull(user);
		Assert.assertEquals("insert", user.getName());
	}

	@Test
	public void testFindAll() {

		List<User> allUsers = entityManager.createQuery("from User").getResultList();
		Assert.assertEquals(2, allUsers.size());
	}
}

Notice we have to deal with the transactions ourselves which can be pretty tiresome. Recommended way to do this is to create a service layer which will be responsible for opening and committing transactions.

The findAll test uses a standard JPQL query to find all users in the database. The select clause is facultative. The “User” keyword is the name of our entity.

Conclusion

Since JPA 2, we can use JPA in a simple SE environment. Downside is we have to handle transactions and write some boilerplate code to use JPQL queries and a framework like dbUnit.

Issues that can be addressed beautifully by project like Spring data JPA, check out my other article.