The tale about Hibernate and Temporal Tables

the original article was released in the Lunatech blog by Nataliia Dziubenko.

Foreword

Earlier this year, I was involved in solving an issue on a project that was using Hibernate + Envers and Microsoft SQL Server as the database. The choice of database was mainly driven by a project requirement to have history data which is provided by temporal tables. In the Java code that is used to query the database, there is an entity class for historical data named VersionedRecord, which is a @MappedSuperclass, and a couple of entities are extending it.

Much to our surprise, when we fetched data, something unexpected happened: Hibernate returned the correct number of records, but it repeated the data for the first row, effectively ignoring the rest of the result set! Some investigation showed that this is caused by Hibernate’s first-level caching, and we had to look for some trick to overcome this without changing the VersionedRecord entity class. The main goal was to make a change as painless as possible and avoid changes in the entity class: creating an extra entity class just for this purpose seemed like an overkill.

In the remainder of this article, I will explain what happened on a clean and simple example that was used to experiment, and also share a trick that we used to solve the problem. Hopefully this will be useful for someone else.

The experiment

To make the experiment clean, I used only Hibernate with nothing on top of it. First of all, I created a table with history table like this:

CREATE TABLE Department
(
    DeptID INT NOT NULL PRIMARY KEY CLUSTERED
  , DeptName VARCHAR(50) NOT NULL
  , SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL
  , SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL
  , PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DepartmentHistory));

This example comes from the official documentation about temporal tables.

With the table definition in place, I added some records to it:

insert into department (deptid, deptname) values (1, 'test1');
insert into department (deptid, deptname) values (2, 'test2');

And update some records so history table will also get filled:

update department set deptname = 'updated' where deptid = 1;
update department set deptname = 'updated_again' where deptid = 1;
update department set deptname = 'updated2' where deptid = 2;

if we query the history table:

select * from departmenthistory;

we will see information like this:

The department with DeptId = 1 was updated twice, so both old values are here and department with DeptId = 2 was updated once, so one old value of this record is also in the history table now.

And if we want to query all history records for department with DeptId = 1 we will do this:

select * from departmenthistory where deptid = 1;

And will get this result:

So, that works as advertised! Now let’s use Hibernate with just a small piece of code to run a native query:

Query q = entityManager.createNativeQuery("select DeptId, DeptName from DepartmentHistory where DeptId = 1");

The result is the following (which completely makes sense):

1 test1
1 updated

Now we can create an Entity to easily operate with the data:

@javax.persistence.Entity
@Table(name = "DepartmentHistory")
public class DepartmentHistory {

    @Id
    @Column(name = "DeptId")
    private int DeptId;

    @Column(name="DeptName")
    private String DeptName;

// other fields and getters and setters…

}

…​and map our result properly to our entity:

Query q = entityManager
    .createNativeQuery("select * from DepartmentHistory where DeptId = 1",
    DepartmentHistory.class);

We expect to get the same result, but this is what we get instead:

1 test1
1 test1

Want to know how this article continues? Read the full article at the Lunatech blog!

0

0