JPA initializing reference data

This page describes the process of initializing HSQLDB database located within a OpenEBJ container with reference data from a CSV file for testing purposes.

Full downloadable source for this page is available here. Corrections and enhancements are welcome, fork, change and push back to GitHub.

Software Stack

  1. Java 8
  2. Java EE 6
  3. JPA 2
  4. OpenEBJ 4.7

Pure JPA Approach

In the example below we will maintain user account information in a table capable of maintaining history.

| User                     |
| id : varchar(320)        |
| valid_to_ts : datetime   |
| password : char(40)      |
| created_dt : datetime    |
| modified_dt : datetime   |
| valid_from_ts : datetime |

Initial data

The initial data is stored in a csv file in a Tab delimited format.

We read the csv file in UserDataManager.java and call the updateTemporal() method of the inherited method of the HistoricalCrudDataManager parent class.


package org.test;

import java.io.File;
import java.io.IOException;
import java.io.Serializable;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.util.Random;
import java.util.stream.Stream;

import javax.persistence.TypedQuery;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class UserDataManager extends HistoricalCrudDataManagerImpl<User, Serializable> {

	private static final Logger logger = LoggerFactory.getLogger(UserDataManager.class);
	 * Load data from a TAB delimited file named $HOME/data/User.csv.
	 * If record does not exist then create it. If it exists then update.
	 * Note that tab delimited file not  have more than 1 record per new line.
	 * @param dbName
	public void initData() throws IOException { 
		final String s = File.separator;
		final String home = System.getProperty("user.home");
		String path = home + s + "data" + s + "User.csv";
		File dataFile = new File(path);
		if(!dataFile.canRead()) {
			logger.info("file not found: " + path);
		logger.info("reading records from: " + path);
		try (Stream<String> lines = Files.lines(dataFile.toPath(), StandardCharsets.UTF_8)) {
			lines.forEachOrdered(line -> {
				String[] cols = line.split("\\t");
				User u = new User();
				logger.info("loaded from file user: " + u);		
				updateTemporal(u, new UserPK(u.getId(), u.getValidToTs()));
	public Long getUserCount() {
		TypedQuery<Long> q = entityManager.createNamedQuery("getCount", Long.class);
		q.setParameter("end_ts", END_TS);
		return q.getSingleResult();
	public User getRandom() {
        Long count = getUserCount();
        Long random = getRandomNumberInRange(0, count-1);
        TypedQuery<User> q = entityManager.createNamedQuery("getAll", User.class);
        q.setParameter("end_ts", END_TS);
        User emp = q.setFirstResult(random.intValue())
		return emp;
	private static Long getRandomNumberInRange(long min, long max) {
		Random r = new Random();
		return r.longs(min, (max + 1)).findFirst().getAsLong();

The updateTemporal() method inserts a new record if it does not exist on the user table. If it exists it archives the old record before inserting.


package org.test;

import java.io.Serializable;
import java.lang.reflect.ParameterizedType;
import java.util.Date;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class HistoricalCrudDataManagerImpl<T extends Historical, PK extends Serializable> implements HistoricalCrudDataManager<T, PK> {
	private static final Logger logger = LoggerFactory.getLogger(HistoricalCrudDataManagerImpl.class);
	protected Class<T> entityClass;

	protected EntityManager entityManager;

	public HistoricalCrudDataManagerImpl() {
		ParameterizedType genericSuperclass = (ParameterizedType) getClass().getGenericSuperclass();
		this.entityClass = (Class<T>) genericSuperclass.getActualTypeArguments()[0];

	public T create(T t) {
		if(t.getTimestamps() != null && t.getTimestamps().getValidFromTs()==null)
			t.getTimestamps().setValidFromTs(new Date());
		return t;

	public T read(PK id) {
		return this.entityManager.find(entityClass, id);

	public T updateTemporal(T t, PK id) {
		Date now = new Date();
		T tFromDb = this.entityManager.find(entityClass, id);
		if(tFromDb!=null) {
			if(t.sameAs(tFromDb)) {
				logger.info("Object same as: " + tFromDb);
				return tFromDb;
			} else {
				logger.info("Saving updated temporal object: " + tFromDb);
		return create(t);
	public void delete(T t) {
		t = this.entityManager.merge(t);

Finally put the following file into your $HOME/data directory. IMPORTANT: convert the spaces to TAB character between the fields. The second column is a sha1 hash of the username in the email address. example “smith”


smith@test.org	2b5c240e6abd88e71ffc225b0459016e4cba9bda
allen@test.org	a4aed34f4966dc8688b8e67046bf8b276626e284
ward@test.org	f7d6f8166205aa91930091f422c0634df8a7ceb4
jones@test.org	4c46bc790ffe655a1e65acfacf95da50cd4d3902

Test Case

The following Junit test case exercises the code. See the testInitData() test is where the interesting things happen. Results get saved into your $HOME/data and $HOME/logs directories.


package org.test;

import javax.ejb.embeddable.EJBContainer;

import org.junit.After;
import org.junit.AfterClass;
import org.junit.Assert;
import org.junit.Before;
import org.junit.BeforeClass;
import org.junit.Test;

public class UserManagerServiceTest {
	private static EJBContainer container;
	private static UserManagerService userManagerService;
	public static void beforeClass() throws Exception {
		// the following represents a db file in the users $HOME/data directory
	    container = ContainerProducer.produceContainer("usermanager_test1"); 
	    userManagerService = (UserManagerService) container.getContext().lookup("java:global/JPATemporalDB/UserManagerService");
	public static void afterClass() throws Exception {

	 * Use this method to cleanup and initialize the data before each test method invocation.
	public void before() {}

	 * Use this method to cleanup the data after each test method invocation.
	public void after() {}
	public void testInitData() throws Exception {
	public void testAddUser() throws Exception {
	    String id = "user" + System.currentTimeMillis();
	    String password = "";
	    long userCount = userManagerService.getUserCount();
		userManagerService.addUser(new User(id, password));
		long userCount2 = userManagerService.getUserCount();
		Assert.assertTrue(userCount2 > userCount);
	public void testUpdateUser() throws Exception {
		User randomUser = userManagerService.getRandomUser();
		String newPw = ""+System.currentTimeMillis();
		//String newPw = randomUser.getPassword(); // test with no change in password by uncommenting this line.
		User updatedUser = userManagerService.getUser(new UserPK(randomUser.getId()));
		Assert.assertEquals(newPw, updatedUser.getPassword());
	public void testReadUserById() throws Exception {
		User randomUser = userManagerService.getRandomUser();
		UserPK userPK = new UserPK(randomUser.getId(), HistoricalCrudDataManagerImpl.END_TS);
		User user = userManagerService.getUser(userPK);

Maven Configuration


<?xml version="1.0" encoding="UTF-8"?>
<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">

		<pluginRepository> <!-- Ignore this repository. Its only used for document publication. -->

		<!-- Logging -->
		<!-- Unit Testing -->

		<!-- APIs for services provided by the containers (order counts) this dependency must after openejb-core -->
			<plugin> <!-- Ignore this plugin. Its only used for document publication. -->

Run the test case

The test case should run with no problems.

Database will go to the users $HOME/data directory. Logs will go to $HOME/logs.


0 Responses to “JPA initializing reference data”

  1. Leave a Comment

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 74 other followers

March 2016
« Feb    

Blog Stats

  • 801,304 hits

%d bloggers like this: