EclipseLink/Examples/JPA/TemporalEntity

bug 361016

EclipseLink JPA Futures Example

This example is intended to illustrate how entity versions can be stored and queried in a database. Unlike the history functionality of EclipseLink this example is focused on creating versions of an entity that may take effect in the future.

Requirements

• Optimize queries against current version. Queries against future versions
• Allow versions for the future to be created with a time they are expected to become the current
• When a future version is promoted to current its temporal storage is not required (no history remains)
• Allow multiple future versions and support changing when a future version takes effect
• Support defining a new entity that does not yet exist but may exist in the future

Software

This example is being built using EclipseLink 2.3.1. To access the source of the example check it out of SVN at:

/svnroot/rt/org.eclipse.persistence/trunk/examples/org.eclipse.persistence.example.jpa.futures

How it Works

Coming Soon...

Examples

The following examples are from test cases.

Schema Generation

At the start of each test suite the schema is replaced. The resulting CREATE TABLE operations are:

CREATE TABLE TPERSON (OID NUMBER(10) NOT NULL, 
                      END_TS NUMBER(19) NULL, 
                      F_NAME VARCHAR2(255) NULL, 
                      L_NAME VARCHAR2(255) NULL, 
                      START_TS NUMBER(19) NULL, 
                      CID NUMBER(10) NULL, 
                      ADDRESS_OID NUMBER(10) NULL, 
                      PRIMARY KEY (OID))
 
CREATE TABLE TADDRESS (OID NUMBER(10) NOT NULL, 
                       CITY VARCHAR2(255) NULL, 
                       END_TS NUMBER(19) NULL, 
                       START_TS NUMBER(19) NULL, 
                       STATE VARCHAR2(255) NULL, 
                       STREET VARCHAR2(255) NULL, 
                       CID NUMBER(10) NULL, 
                       PRIMARY KEY (OID))
 
ALTER TABLE TPERSON ADD CONSTRAINT FK_TPERSON_ADDRESS_OID FOREIGN KEY (ADDRESS_OID) REFERENCES TADDRESS (OID)
 
ALTER TABLE TPERSON ADD CONSTRAINT FK_TPERSON_CID FOREIGN KEY (CID) REFERENCES TPERSON (OID)
 
ALTER TABLE TADDRESS ADD CONSTRAINT FK_TADDRESS_CID FOREIGN KEY (CID) REFERENCES TADDRESS (OID)

Create Current Person

Person p = new Person();
p.setFirstName("Doug");
p.setLastName("Clarke");
 
Address a = new Address();
a.setStreet("45 O'Connor Street");
a.setCity("Ottawa");
a.setState("Ontario");
 
p.setAddress(a);
em.persist(a);
em.persist(p);

Resulting SQL:

[EL Fine]: INSERT INTO TADDRESS (OID, CITY, END_TS, START_TS, STATE, STREET, CID) VALUES (?, ?, ?, ?, ?, ?, ?)
	bind => [1, Ottawa, 9223372036854775807, 0, Ontario, 45 OConnor Street, NULL]
[EL Fine]: UPDATE TADDRESS SET STATE = ?, STREET = ?, CID = ?, CITY = ? WHERE (OID = ?)
	bind => [Ontario, 45 OConnor Street, 1, Ottawa, 1]
[EL Fine]: INSERT INTO TPERSON (OID, END_TS, F_NAME, L_NAME, START_TS, CID, ADDRESS_OID) VALUES (?, ?, ?, ?, ?, ?, ?)
	bind => [2, 9223372036854775807, Doug, Clarke, 0, NULL, NULL]
[EL Fine]: UPDATE TPERSON SET CID = ?, ADDRESS_OID = ?, F_NAME = ?, L_NAME = ? WHERE (OID = ?)
	bind => [2, 1, Doug, Clarke, 2]

Note: The INSERT followed by the UPDATE statement are to populate the self-referencing FK values. Optimized solution being investigated

Create Person Edition

This example creates an edition of a current Person (id={currentPersonId}) which is set to take effect at T2 and be deleted or replaced at T4. The current version of the person is updated accordingly to end at T2.

Person currentPerson = em.find(Person.class, currentPersonId);
 
Person p = TemporalHelper.createEdition(em, currentPerson);
p.setFirstName("Dougie");
p.setStart(T2);
currentPerson.setEnd(T2);
p.setEnd(T4);
em.persist(p);

SQL:

...

Create Future Person

This creates a new Person that does not currently exist but will exist as of T3.

Person p = new Person();
p.setFirstName("John");
p.setLastName("Doe");
p.setStart(T3);
em.persist(p);

SQL:

[EL Fine]: INSERT INTO TPERSON (OID, END_TS, F_NAME, L_NAME, START_TS, CID, ADDRESS_OID) VALUES (?, ?, ?, ?, ?, ?, ?)
	bind => [1, 9223372036854775807, John, Doe, 3, NULL, NULL]
[EL Fine]: UPDATE TPERSON SET CID = ?, START_TS = ?, F_NAME = ?, L_NAME = ? WHERE (OID = ?)
	bind => [1, 3, John, Doe, 1]

Query for Current Person

EntityManager em = createEntityManager();
 
Person person = em.createQuery("SELECT p From Person p WHERE p.id = " + currentPersonId, Person.class).getSingleResult();
Address address = person.getAddress();
 
Assert.assertNotNull(person);
 
System.out.println("FIND CURRENT: " + person);

OUTPUT:

[EL Fine]: SELECT OID, END_TS, F_NAME, L_NAME, START_TS, CID, ADDR_ID FROM TPERSON WHERE ((OID = ?) AND (START_TS = ?))
	bind => [2, 0]
[EL Fine]: SELECT OID, CITY, END_TS, START_TS, STATE, STREET, CID FROM TADDRESS WHERE ((OID = ?) AND (START_TS = ?))
	bind => [1, 0]
FIND CURRENT: Person(2)::Doug @ 0-2

Query for Current Person Joining Address

EntityManager em = createEntityManager();
 
Person person = em.createQuery("SELECT p From Person p JOIN FETCH p.address WHERE p.id = " + currentPersonId, Person.class).getSingleResult();
Address address = person.getAddress();
 
System.out.println("FIND CURRENT: " + person);

OUTPUT:

[EL Fine]: SELECT t1.OID, t1.END_TS, t1.F_NAME, t1.L_NAME, t1.START_TS, t1.CID, t1.ADDR_ID, t0.OID, t0.CITY, t0.END_TS, t0.START_TS, t0.STATE, t0.STREET, t0.CID 
                  FROM TADDRESS t0, TPERSON t1 
                  WHERE (((t1.OID = ?) AND (t1.START_TS = ?)) AND ((t0.OID = t1.ADDR_ID) AND (t0.START_TS = ?)))
	bind => [2, 0, 0]
FIND CURRENT: Person(2)::Doug @ 0-2

Find Current Person

Person person = em.find(Person.class, currentPersonId);
System.out.println("FIND CURRENT: " + person);

OUTPUT:

[EL Fine]: SELECT OID, CITY, END_TS, START_TS, STATE, STREET, CID FROM TADDRESS WHERE ((OID = ?) AND (START_TS = ?))
	bind => [1, 0]
FIND CURRENT: model.Person(2)::Doug @ 0-2

Query all Current Persons

List<Person> results = em.createQuery("SELECT p From Person p", Person.class).getResultList();

LOG:

[EL Fine]: SELECT OID, END_TS, F_NAME, L_NAME, START_TS, CID, ADDRESS_OID FROM TPERSON WHERE (START_TS = ?)
	bind => [0]

Find Person edition @ T3

Map<String, Object> props = new HashMap<String, Object>();
props.put("EFF_TS", T3);
EntityManager em = emf.createEntityManager(props);
 
Person personEdition = em.createQuery("SELECT p From PersonEdition p WHERE p.cid = " + currentPersonId, Person.class).getSingleResult();
 
System.out.println("QUERY EFFECTIVE @ T3: " + personEdition);

Log Output:

[EL Fine]: SELECT OID, END_TS, F_NAME, L_NAME, START_TS, CID, ADDRESS_OID FROM TPERSON WHERE ((CID = ?) AND ((? >= START_TS) AND (? < END_TS)))
	bind => [2, 3, 3]
QUERY EFFECTIVE @ T3:
Person(3)::Dougie @ 2-4

Query Future Edition Person @ T2 with Address Joined

EntityManager em = createEntityManager();
em.setProperty("EFF_TS", T2);
 
Person person = em.createQuery("SELECT p From PersonEdition p JOIN FETCH p.address WHERE p.cid = " + currentPersonId, Person.class).getSingleResult();
Address address = pEdition.getAddress();
 
System.out.println("QUERY EDITION @ T2: " + person);
System.out.println("\t> " + address);

OUTPUT:

[EL Fine]: SELECT t1.OID, t1.END_TS, t1.F_NAME, t1.L_NAME, t1.START_TS, t1.CID, t1.ADDR_ID, t0.OID, t0.CITY, t0.END_TS, t0.START_TS, t0.STATE, t0.STREET, t0.CID 
                  FROM TADDRESS t0, TPERSON t1 
                  WHERE (((t1.CID = ?) AND ((? >= t1.START_TS) AND (? < t1.END_TS))) AND ((t0.CID = t1.ADDR_ID) AND ((? >= t0.START_TS) AND (? < t0.END_TS))))
	bind => [2, 2, 2, 2, 2]
QUERY EDITION @ T2: model.PersonEdition(3 @ 2::4)
	> model.AddressEdition(4 @ 2::9223372036854775807)