20
Mar
16

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

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

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

Solutions:

  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.

src/test/java/org/test/UserManagerServiceTest.java

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;
	
	@BeforeClass
	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");
	}
	
	@AfterClass
	public static void afterClass() throws Exception {
		if(container!=null)
			container.close();
	}

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

	/**
	 * Use this method to cleanup the data after each test method invocation.
	 */
	@After
	public void after() {}
	
	@Test
	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);
	}
	
	@Test
	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.
		randomUser.setPassword(newPw);
		userManagerService.updateUser(randomUser);
		User updatedUser = userManagerService.getUser(new UserPK(randomUser.getId()));
		Assert.assertEquals(newPw, updatedUser.getPassword());
	}
	
	@Test
	public void testReadUserById() throws Exception {
		User randomUser = userManagerService.getRandomUser();
		UserPK userPK = new UserPK(randomUser.getId(), HistoricalCrudDataManagerImpl.END_TS);
		User user = userManagerService.getUser(userPK);
		Assert.assertNotNull(user);
		System.out.println(user);
	}
}

Temporal Interface

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

src/main/java/org/test/Historical.java

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);
}

HistoricalCrudDataManagerImpl

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.

src/main/java/org/test/HistoricalCrudDataManagerImpl.java

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;

	@PersistenceContext
	protected EntityManager entityManager;

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

	@Override
	public T create(T t) {
		if(t.getTimestamps() != null && t.getTimestamps().getValidFromTs()==null)
			t.getTimestamps().setValidFromTs(new Date());
		this.entityManager.persist(t);
		return t;
	}

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

	@Override
	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);
			}
			t.getTimestamps().setCreateTs(tFromDb.getTimestamps().getCreateTs());
			entityManager.remove(tFromDb);
			entityManager.flush();
			entityManager.detach(tFromDb);
			tFromDb.setValidToTs(now);
			entityManager.persist(tFromDb);
		}
		t.getTimestamps().setUpdateTs(now);
		t.getTimestamps().setValidFromTs(now);
		t.setValidToTs(END_TS);
		return create(t);
	}
	
	@Override
	public void delete(T t) {
		t = this.entityManager.merge(t);
		this.entityManager.remove(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.

src/main/java/org/test/User.java

package org.test;

import java.io.Serializable;
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;

@Entity
@NamedQueries({
	@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")
})
@IdClass(value=UserPK.class)
public class User implements Serializable, Historical {
	private static final long serialVersionUID = 1L;
	@Id
	private String id;

	@Id
	@Temporal(TemporalType.TIMESTAMP)
	@Column(name="VALID_TO_TS")
	private Date validToTs;

	@Column(length=40, nullable=false)
	private String password;
	
	@Embedded
	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) {
		this.id = id;
		this.password = password;
	}
	
	public String getId() {
		return id;
	}
	public void setId(String id) {
		this.id = id;
	}
	public String getPassword() {
		return password;
	}
	public void setPassword(String password) {
		this.password = password;
	}
	
	@Override
	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;
	}
	@Override
	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

pom.xml

<?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">
	<modelVersion>4.0.0</modelVersion>
	<groupId>org.test</groupId>
	<artifactId>JPATemporalDB</artifactId>
	<version>20160320</version>
	<packaging>jar</packaging>

	<name>UserManager</name>
	<pluginRepositories>
		<pluginRepository> <!-- Ignore this repository. Its only used for document publication. -->
			<id>numberformat-releases</id>
			<url>https://raw.githubusercontent.com/numberformat/wordpress/master/20130213/repo/</url>
		</pluginRepository>
	</pluginRepositories>


	<dependencies>
		<!-- Logging -->
		<dependency>
			<groupId>org.slf4j</groupId>
			<artifactId>slf4j-api</artifactId>
			<version>1.7.1</version>
		</dependency>
		<dependency>
			<groupId>org.slf4j</groupId>
			<artifactId>slf4j-log4j12</artifactId>
			<version>1.7.1</version>
		</dependency>
		<!-- Unit Testing -->
		<dependency>
			<groupId>junit</groupId>
			<artifactId>junit</artifactId>
			<version>4.12</version>
			<scope>test</scope>
		</dependency>
		<dependency>
			<groupId>org.apache.openejb</groupId>
			<artifactId>openejb-core</artifactId>
			<version>4.7.1</version>
			<scope>test</scope>
		</dependency>

		<!-- APIs for services provided by the containers (order counts) this dependency must after openejb-core -->
		<dependency>
			<groupId>javax</groupId>
			<artifactId>javaee-web-api</artifactId>
			<version>6.0</version>
			<scope>provided</scope>
		</dependency>
		
		
	</dependencies>
	<build>
		<plugins>
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-compiler-plugin</artifactId>
				<version>2.3.2</version>
				<configuration>
					<source>1.8</source>
					<target>1.8</target>
				</configuration>
			</plugin>
			<plugin> <!-- Ignore this plugin. Its only used for document publication. -->
				<groupId>github.numberformat</groupId>
				<artifactId>blog-plugin</artifactId>
				<version>1.0-SNAPSHOT</version>
				<configuration>
					<gitUrl>https://github.com/numberformat/wordpress/tree/master/${project.version}/${project.artifactId}</gitUrl>
				</configuration>
				<executions>
					<execution>
						<id>1</id>
						<phase>site</phase>
						<goals>
							<goal>generate</goal>
						</goals>
					</execution>
				</executions>
			</plugin>
		</plugins>
	</build>
</project>

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.

Advertisements

0 Responses to “Temporal Tables using JPA 2”



  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
S M T W T F S
« Feb    
 12345
6789101112
13141516171819
20212223242526
2728293031  

Blog Stats

  • 801,397 hits

%d bloggers like this: