Using SQL Server Temporal Tables with Hibernate
Introduction
Most software requires keeping track of states and events; databases are the choice for state and one choice for events. While there are many different architecture designs that let you replay the event log ad hoc, such as event sourcing, it is quite common that in a SQL application you need to see the data changes without replay. The traditional approach for this will be using a secondary table to keep track of changes. However this also means that you need to write join queries as well as some maths for time operations.
We as the Performance Marketing team at THG had to introduce a feature to keep track of the changes by business users that would enable us to easily revert wrong changes and, eventually, prevent bad changes by seeing exactly how it evolved throughout the time. We already have a SQL Server instance up and running and noticed that we could avoid a load of implementation and testing by using the default feature that is provided by the DBA engine itself.
Getting started
Temporal tables are a handy feature that was made available on SQL Server 2016. It allows developers to keep track of the changes on database easily and retrieve the data back in time without join operations and having to maintain a secondary table manually. By retrieving previous records, this doesn’t mean a database backup but a fully queryable table that can retrieve results in a certain time in the past.
Hibernate is a popular ORM that’s been battle tested in various enterprise applications. One of the main advantages of using an ORM like Hibernate is to isolate the underlying database engine so when you switch to a different vendor there is no need to change existing queries in your application. However, this doesn’t mean that you cannot use vendor specific features with Hibernate. In this post we will be implementing a sample application with Hibernate SQL Server and Spring Boot. We will explore how to use temporal tables effectively with Hibernate and Spring Boot.
Idea
Implement a HR system to keep track of all the progress of an employee during their employment. This should include things like department changes, pay increases etc.
I. Getting DB Up and Ready
Possibly, the most straightforward way of getting SQL Server is to run inside a Docker container. You can run following command to get SQL Server Express
docker run -e ‘ACCEPT_EULA=Y’ -e ‘SA_PASSWORD=yourStrong(!)Password’ -e ‘MSSQL_PID=Express’ -p 1433:1433 -d mcr.microsoft.com/mssql/server:2017-latest-ubuntu
Create a sample table with the query below. Note that this is a versioned table and with “HISTORY_TABLE” we explicitly declare the table name for our historic records. Nice part of temporal tables is that you don’t have to query this table to retrieve older records. While you can still query, you need to use the keywords for versioned tables on the actual table (Employee in this case) to retrieve historic results back in time.
create table dbo.Employee
(
Employee_Id int identity
constraint Employee_pk
primary key nonclustered,
First_Name nvarchar(50),
Last_Name nvarchar(50),
Salary decimal(10, 2),
Department nvarchar(50),
Sys_Start_Time datetime2 GENERATED ALWAYS AS ROW START HIDDEN,
Sys_End_Time datetime2 GENERATED ALWAYS AS ROW END HIDDEN,
PERIOD FOR SYSTEM_TIME (Sys_Start_Time, Sys_End_Time)
)WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Employee_History));
goOnce the table is created, we are ready to create our JPA mappings that represent the table.
I. Getting Spring Boot with dependencies
Most straightforward way to obtain Spring Boot with additional dependencies is visiting https://start.spring.io/ and adding required dependencies from the selectbox. Required dependencies for this project are:
- SQL Server [SQL]
- JPA [SQL]
After adding the required dependencies, generate the project and open with your favourite IDE.
II. Mapping entity
As seen on the SQL query, we have Employee table and we need the corresponding entity mapping for this one.
@Entity
public class Employee {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long employeeId;
private String firstName;
private String lastName;
private BigDecimal salary;
private String department;
@Column(insertable = false, updatable = false, name = "Sys_Start_Time")
private LocalDateTime changeStart;
@Column(insertable = false, updatable = false, name = "Sys_End_Time")
private LocalDateTime changeEnd; public Employee() {
}// Constructors, getters and setters}
We need to annotate the class with @Entity to mark it as an entity class. In case your entity name and table name are different and don’t match, you need to annotate the class with @Table(name = “table_name”). This can be useful in the cases where table name is not quite Java naming convention friendly.
@Id is to specify that the field is a primary key and @GeneratedValue is used to define the primary key generation strategy. Depending on the strategy, you might want to use an auto incremented value or use many of the more complex strategies available.
The tricky bit of using temporal tables with Hibernate resides in the @Column annotated fields. SQL Server doesn’t let you to manually insert or update Sys_Start_Time and Sys_End_Time fields. By annotating these fields with @Column but with options insertable = false, updatable = false we tell the ORM to prevent insert and update queries being run on these columns. In case you try to manually alter these columns with SQL while still having versioning on, the DB engine would return an error. You need to run the query below to disable versioning on the table. Note that, once you run it the table is no longer versioned and doesn’t keep track of the changes.
ALTER TABLE dbo.Employee
SET (SYSTEM_VERSIONING = OFF);Notice that we don’t have to manually annotate fields with the `@Column` annotation where the field name matches the corresponding column of the database. In case you want to have a field on your entity class but don’t want to persist it you can mark it with @Transient to exclude it.
III. Repository level
We are making use of Spring Data JPA and thanks to it we don’t have to deal with lots of cluttered code to save an entity. For generic use cases, I prefer to extend CrudRepository and let Spring Data do its job for CRUD queries.
Another convenient feature of Spring Data JPA is that it enables you to run native queries as well. Temporal tables are platform dependant and not a standard with all other DBMS. However, with the ability to execute native queries, we are still able to use it. Let’s create an EmployeeRepository interface and demonstrate the native queries.
@Repository
public interface EmployeeRepository extends CrudRepository<Employee, Long> {}This simple interface without any line in it is sufficient for us to execute CRUD queries and map it to entities. Let’s add some native query for this repository interface.
First we need to think about our native query. Imagine we want to find out the salary and other details of employee’s in the past. Let’s write the query for it.
Select Employee_Id, First_Name, Last_Name, Salary, Department, sys_end_time, sys_start_time from Employee for system_time as of ‘2013–01–01 T10:00:00.0000’ where Employee_Id = 1
This is the standard query which we use to query SQL Server directly. Since we need to create a Java method and bind parameters we need to provide a method in our EmployeeRepository interface.
Let’s add the abstract method getEmployeeDetailsAtPast to our interface. We don’t need to provide an implementation for this class. The `@Query` annotation can be used to query hibernate entities with HQL. By providing the flag nativeQuery=true, we can use native SQL queries as the name suggest. Here’s the code to add the abstract method to our interface:
@Query(value = "select Employee_Id, First_Name, Last_Name, Salary, " + "Department, sys_end_time, sys_start_time from Employee for system_time as of ?2 where Employee_Id = ?1", nativeQuery = true)
Employee getEmployeeDetailsAtPast(long employeeId, Instant dateTime);As you can see the method takes an employee Id and Instant variable that refers to the date. We are using placeholders for the parameters using ?1 and ?2. Employee_Id = ?1 refers to the first argument of the method and ?2 refers to dateTime respectively.
Testing
Now that we have the interface, we can test the method. By nature, temporal tables are hard to test as you can’t manipulate the insertion date while the versioning is on. That means during test we need to manually disable and activate versioning. We will make use of some JPA and Spring Framework features to execute manual queries on our table prior to our test class run.
Here is the strategy:
I. Disable versioning on table
II. Insert some older data that points back in time
III. Enable versioning back
IV. Cleanup after tests
Step I.
We need to execute the query below to disable versioning on our table. Save the file below as disable_versioning.sql and save it under src/test/resources
ALTER TABLE [dbo].[Employee] SET ( SYSTEM_VERSIONING = OFF )Step II.
Let’s insert some dummy data into our table. Note that since DB engine queries the history table, your insertions must be executed on the history table. In our case this refers to Employee_History case. Save the query below as ‘insert_records.sql’.
insert into dbo.Employee_History
(Employee_Id, First_Name, Last_Name, Salary, Department, Sys_Start_Time, Sys_End_Time)
values (1000, 'Salih', 'Gedik', 5.00 ,'Tech', '2000-01-01 15:00:0.0000000', '2001-01-01 15:00:0.0000000')Step III.
Since we inserted sample data to test, we can now enable versioning back. Again, we manually specify the table name to prevent SQL Server creating a meaningless table name for us. Save the file into src/test/resources with the name ‘enable_versioning.sql’.
ALTER TABLE dbo.Employee
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Employee_History));Step IV.
It would be useful to clear the dummy record(s) that we inserted previously to prevent errors occurring later on when same tests runs again. We inserted a single row and corresponding removal query would be below:
delete from Employee_History where Employee_Id = 1000;In order to help us with common code we will create a DatabaseUtils.class. This class lets us to execute a number of query files on classpath and it relies on a Hibernate session that we retrieve from EntityManager. We make use of executeScriptOnClassPath method to execute SQL files on the classpath.
public class DatabaseUtils {
private final EntityManager entityManager;
public DatabaseUtils(EntityManager entityManager) {
this.entityManager = entityManager;
}
private void executeInserts() {
executeScriptOnClassPath("insert_records.sql");
}
private void enableVersioning() {
executeScriptOnClassPath("enable_versioning.sql");
}
private void disableVersioning() {
executeScriptOnClassPath("disable_versioning.sql");
}
private void cleanUpHistory() {
executeScriptOnClassPath("clean_up_history.sql");
}
public void cleanUpAfterTest() {
disableVersioning();
cleanUpHistory();
enableVersioning();
}
public void setUpBeforeTest() {
disableVersioning();
executeInserts();
enableVersioning();
}
private void executeScriptOnClassPath(String fileName) {
Session session = (Session) entityManager.getDelegate();
session.doWork(connection -> {
Resource resource = new ClassPathResource(fileName);
ScriptUtils.executeSqlScript(connection,
resource);
});
}
}Now that we have our utilities and database scripts in place, we are good to go to write some tests. Let’s write a test to verify that our employee with ID 1000 was ‘Salih’ earning £5 on 03/02/2000.
Create a EmployeeRepositoryTest under src/test. We will actually initiate a Spring application context and run transactional queries over a database connection. Our Employee repository class should have these annotations to be able to inject dependencies that we need, connect the database and run Transactional queries.
@SpringBootTest
@RunWith(SpringRunner.class)
@Transactional
@Rollback
public class EmployeeRepositoryTest {
}Since we are testing the EmployeeRepository, we will need that dependency autowired. Notice that we don’t need to provide or initiate an implementation for this interface. We also need the helper DatabaseUtilities class to setup and clean up the database. In addition, our DatabaseUtilities class needs an instance of EntityManager to initiate a session. Simply adding autowiring EmployeeRepository and EntityManager is sufficient. However, we need to pass EntityManager to DatabaseUtils class on setUp method. We depend on JUnit’s @Before annotation to execute the setup method before each test.
public class EmployeeRepositoryTest {
@Autowired
private EmployeeRepository employeeRepository;
@Autowired
private EntityManager entityManager;
private DatabaseUtils databaseUtils;
@Before
public void setUp() throws Exception {
databaseUtils = new DatabaseUtils(entityManager);
databaseUtils.setUpBeforeTest();
}}
Now that we have the skeleton to test our persistence layer we can write the actual test code to retrieve the Employee object back in the time.
@Test
public void getEmployeeDetailsAtPast() {
LocalDateTime millenium = LocalDateTime.of(2000,2,3,3,0);
Employee pastDetails =
employeeRepository.getEmployeeDetailsAtPast(1000, millenium.toInstant(ZoneOffset.UTC));
assertEquals("Salih", pastDetails.getFirstName());
assertEquals(BigDecimal.valueOf(5).setScale(1), pastDetails.getSalary().setScale(1));
}We create a LocalDateTime object that points back to 03/02/2000 and retrieve the representative Employee object on that time. getEmployeeDetailsAtPast method of the Repository takes the ID of the employee that we query and the time which we want to retrieve the details at. Since we know what we inserted in our queries, we expect employee name to be ‘Salih’ and salary to be 5.0.
It is beneficial to keep the state of history table clear for testing purposes. We need to execute an automatic @After query as our tearDown method. We already provided this feature in DatabaseUtils class so simply following lines are enough.
@After
public void tearDown() throws Exception {
databaseUtils.cleanUpAfterTest();
}Our final EmployeeRepositoryTest class should look like this:
@SpringBootTest
@RunWith(SpringRunner.class)
@Transactional
@Rollback
public class EmployeeRepositoryTest {
@Autowired
private EmployeeRepository employeeRepository;
@Autowired
private EntityManager entityManager;
private DatabaseUtils databaseUtils;
@Before
public void setUp() throws Exception {
databaseUtils = new DatabaseUtils(entityManager);
databaseUtils.setUpBeforeTest();
}
@Test
public void getEmployeeDetailsAtPast() {
LocalDateTime millenium = LocalDateTime.of(2000,2,3,3,0);
Employee pastDetails =
employeeRepository.getEmployeeDetailsAtPast(1000, millenium.toInstant(ZoneOffset.UTC));
assertEquals("Salih", pastDetails.getFirstName());
assertEquals(BigDecimal.valueOf(5).setScale(1), pastDetails.getSalary().setScale(1));
}
@After
public void tearDown() throws Exception {
databaseUtils.cleanUpAfterTest();
}
}You can use Maven or Gradle to execute tests. Alternatively, you can run from your IDE of preference as well. Simply following commands should be enough to execute.
Gradle:
./gradlew clean test
Maven
./mvnw clean test
Conclusion
In this post, we covered what is “temporal tables” feature and how to use this type of DBA engine specific features with Hibernate framework. While ORM’s abstract the underlying DBA engine, if you stick to a solution like this and change your DBA engine to the one that doesn’t support the feature, you need to refactor your codebase and even database model to make it work again.
We’re recruiting
Find out about the exciting opportunities at THG here:
References
You can learn more about temporal tables in this link.
The opening image was by Microsoft on this page.
