Home

Introduction

The project DbAssist provides a clean and simple way to solve the well-known issue with JVM adding improper time shift to dates, when saving to or reading from a database. The issue does not occur at all on condition that all the components we are using in the project (servers, clients etc.) are set up in the same time zone. If at least one component has a different time zone (for example, we want to store dates in a database as UTC0, but leave the application servers in local time zone), then we experience unexpected time shift whenever we perform read/write operation. The solution differs depending on whether we use JPA Annotations or Hibernate Mapping files (.hbm) to define entities and their relations in the database.

The provided unit testing should be also treated as an example of how to use the classes provided with the DbAssist library. In order to run the unit tests you need to set up a database, so that the Liquibase plugin used in DbAssist-commons project could create necessary schemas and tables. More details about how to install the project properly are available on the wiki Installation page.

Installation of the fix

Add the dependency

In order to fix the issue with date shift, you need to determine first if you want to use JPA annotations or .hbm files to map your entities. Depending on your choice, add the following dependency to your project's pom file and pick the correct version from the table below.

<dependency>
    <groupId>com.montrosesoftware</groupId>
    <artifactId>DbAssist-5.2.2</artifactId>
    <version>1.0-RELEASE</version>
</dependency>

The supported Hibernate versions and their fix counterparts:

Hibernate version	HBM	JPA
3.3.2.GA	DbAssist-hbm-3.3.2	N/A
3.6.10.Final	DbAssist-hbm-3.6.10	N/A
4.2.21.Final	DbAssist-4.2.21	DbAssist-4.2.21
4.3.11.Final	DbAssist-4.3.11	DbAssist-4.3.11
5.0.10.Final	DbAssist-5.0.10	DbAssist-5.0.10
5.1.1.Final	DbAssist-5.1.1	DbAssist-5.1.1
5.2.2.Final	DbAssist-5.2.2	DbAssist-5.2.2

Apply the fix

The fix is slightly different for both entity mapping methods:

In HBM case:

You do not modify the java.util.Date type of dates fields in your entity class. However, you need to change the way how they are mapped in the .hbm file of your entities. You can do it by using our custom type, UtcDateType:

ExampleEntity.hbm.xml

<property name="createdAt" type="com.montrosesoftware.dbassist.types.UtcDateType" column="created_at"/>

ExampleEntity.java (not modified)

public class ExampleEntity {

    private int id;
    private String name;
    private Date createdAt;
   
    //setters and getters
}

In JPA case:

In case of JPA Annotations with Spring Boot, just add the @EnableAutoConfiguration annotation before the application class. However, if you are using just plain Hibernate (with persistence.xml configuration file), add the following property into the configuration. It will include the necessary metadata defined at the package level (e.g. package-info.java).

persistence.xml

...
<persistence-unit ...>
    ...
    <class>com.montrosesoftware.dbassist.types</class>
    ...
</persistence-unit>
...

The exception is when we are using Hibernate's Specification class to specify WHERE conditions. In order to fix it we have two options:

A. Using named parameters

The following code shows how to use Specification to add WHERE conditions with use of named parameters. The commented out part shows the standard way to use it. As mentioned before, the standard way would fail and add improper time shift to utcDate:

CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<User> criteriaQuery = criteriaBuilder.createQuery(User.class);
Root<User> userRoot = criteriaQuery.from(User.class);

//Specification<User> specs = (root, query, cb) ->
//        cb.equal(root.get("createdAt"), utcDate);
String paramName = "pn";
Specification<User> specs = (root, query, cb) ->
          cb.equal(root.get("createdAt"), cb.parameter(Date.class, paramName));

Predicate predicate = specs.toPredicate(userRoot, criteriaQuery, criteriaBuilder);
criteriaQuery.where(predicate);

//TypedQuery<User> typedQuery = entityManager.createQuery(criteriaQuery);
typedQuery.setParameter(paramName, utcDate);

List<User> results = typedQuery.getResultList();

Unfortunately, using such a fix makes the code less readable and more difficult to maintain. Therefore we recommend using the next option.

B. Using DbAssist-jpa-commons library to create conditions and read data from DB.

First, install the library by adding the following dependency into your .pom file:

<dependency>
    <groupId>com.montrosesoftware</groupId>
    <artifactId>DbAssist-jpa-commons</artifactId>
    <version>1.1-RELEASE</version>
</dependency>

The following code snippet shows how DbAssist-jpa-commons allows us to create complex conditions and fix date shift issue in an easy way:

ConditionsBuilder cb = new ConditionsBuilder();
Condition conA = cb.equal("createdAt", utcDateA);
Condition conB = cb.equal("createdAt", utcDateB);
cb.apply(or(conA, conB));
List<User> results = uRepo.find(cb);

More information how to use ConditionsBuilder and other classes from the library is presented in the links below.

Links to more information

Building the project from the source code

DbAssist-jpa-commons overview

Building conditions

Using aggregate functions

Grouping and sorting