Archive for March 20th, 2016


Temporal Tables using JPA 2

This page is about storing and fetching data into tables that keep history using JPA 2. The method of implementing a relational database table and JPA code to allow storage of temporal information. This page uses a built-in HSQLDB database that outputs to a text file in the users home directory. No database configuration is needed.

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. Since both id and valid_to_ts is in the primary key we can store multiple records for the same user id each with its own valid_to time stamp. The current valid record is indicated with a valid_to_ts as ‘9999-12-31’.

To expire the existing record we update the value to current time stamp and insert a new record with time stamp that expires in the future ex. ‘9999-12-31’. The data managers will perform the update and insert operation within the context of a transaction.

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

All current and historical information about a Entity is on a single table.

Because the “valid_to_ts” column will be update to reflect the time record was updated, you will not be able to use foreign key relations for these temporal tables.

A word about time zones and MAX dates

The valid_to_ts needs to have a time component since we need to record the time the record was expired and replaced with a new record.

The java.util.Date value represents the number of mili-seconds since January, 1 1970 midnight UTC. To indicate a end date of 9999-12-31 UTC the value is: 253402214400000. JDBC stores time stamps based on the time zone JVM is in. For example try to store the value of java.util.Date(0) into the database. On a JVM and database located in the “America/New_York” time zone the column value is stored with 1969-12-31 19:00:00.000000. This is (UTC-5). This is a problem because a JVM located in a different time zone like UTC will read the value literally as 1969-12-31 19:00:00.000000 which is wrong.


  1. Accept the fact that dates stored in the database are from a defined time zone like America/New_York and only use JVMs configured to that time zone. For example in your startup code put the following line: TimeZone.setDefault(TimeZone.getTimeZone(“America/New_York”)); In New York, Don’t use EDT or EST since those change from summer to winter.
  2. Store the time stamps as long. Use this approach if your JVMs run from different locations than your database. Its easy to convert and compare equality

For the purpose of this page we will assume the JVM and database are in the same time zone (America/New_York) and the “END” time stamp for current records is ‘9999-12-31 00:00:00’ local time which is (10000-1-1 05:00:00 UTC).

It’s useful to define the END_TS as a final constant somewhere in your code-base. This will save you the effort of creating an object that represents this value using java Calendar etc…

	public static final Date END_TS = new Date(253402214400000L);

Test Case

The following Junit test case exercises the code. See the testAddUser() and testUpdateUser() tests 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 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);

Temporal Interface

The following interface should be placed on any entity that you want history on.


package org.test;

import java.util.Date;

 * Used to get and set Temporal information from entity objects.
public interface Historical {
	public Timestamps getTimestamps();
	public void setTimestamps(Timestamps timestamps);
	public Date getValidToTs();
	public void setValidToTs(Date validToTs);
	public boolean sameAs(Object obj);


The following data manager base class has been modified to handle recording of history for each entity. Since the valid_to_ts appears in the primary key, In JPA 2 you can’t modify any part of the key directly. You need to query the entity, remove(), flush(), detach() it. Once detached you may update the PK and then persist(). An example of this is seen in the below data manager.


package org.test;

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)) {"Object same as: " + tFromDb);
				return tFromDb;
			} else {"Saving updated temporal object: " + tFromDb);
		return create(t);
	public void delete(T t) {
		t = this.entityManager.merge(t);

User Entity

The user entity just needs to have the validToTs field since its part of the primary key. The rest of the time stamp values like update, created, validFrom time stamps are in a embedded object to make the entity class look a bit more cleaner.


package org.test;

import java.util.Date;

import javax.persistence.Column;
import javax.persistence.Embedded;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.IdClass;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;

	@NamedQuery(name="getCount", query = "SELECT COUNT(u) from User u where u.validToTs = :end_ts"),
	@NamedQuery(name="getAll", query = "SELECT u from User u where u.validToTs = :end_ts")
public class User implements Serializable, Historical {
	private static final long serialVersionUID = 1L;
	private String id;

	private Date validToTs;

	@Column(length=40, nullable=false)
	private String password;
	private Timestamps timestamps = new Timestamps();
	public Date getValidToTs() {
		return validToTs;
	public void setValidToTs(Date validToTs) {
		this.validToTs = validToTs;
	public User(){}
	public User(String id, String password) { = id;
		this.password = password;
	public String getId() {
		return id;
	public void setId(String id) { = id;
	public String getPassword() {
		return password;
	public void setPassword(String password) {
		this.password = password;
	public String toString() {
		return "User [id=" + id + ", validToTs=" + validToTs + ", password=" + password + ", timestamps=" + timestamps
				+ "]";
	public Timestamps getTimestamps() {
		return timestamps;
	public void setTimestamps(Timestamps timestamps) {
		this.timestamps = timestamps;
	public boolean sameAs(Object obj) {
		if (this == obj)
			return true;
		if (obj == null)
			return false;
		if (!(obj instanceof User))
			return false;
		User other = (User) obj;
		if (getId() == null) {
			if (other.getId() != null)
				return false;
		} else if (!getId().equals(other.getId()))
			return false;
		if (getPassword() == null) {
			if (other.getPassword() != null)
				return false;
		} else if (!getPassword().equals(other.getPassword()))
			return false;
		return true;

Nothing really interesting happening in the rest of the code. You may checkout the Git repository for this page if interested.

Maven Configuration


<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="" xmlns:xsi=""

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


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

Join 77 other followers

March 2016
« Feb   Mar »

Blog Stats

  • 841,714 hits