Mapping JPA in Your Database With the Time API

Background

Many times, we encounter the need to store date and time in our database. Throughout application development, we need to make CRUD operations on that stored data. In my case, I use Spring Boot with Spring Data as a persistence layer. I’m still not sure whether the word “layer” is truly appropriate in modern development techniques, but nevertheless, we’ll say that Spring Data is my persistence layer.
As database I use Postgres which has quite nice features and what is most important it’s free.
Detailed technology stack looks like:

• PostgreSQL DB
• Spring Boot 1.5.3
• Spring Data JPA
• Lombok (since I do not like to write getters, setters and so on)
• Angular 2 on front end (but that is not important in this post)

Problem

The first problem that I encounter is JPA mapping types in my database. I have a Timestamp column type, which fits all my needs at the database level — it stores date and time with the appropriate time zones, so everything that I need is right there.
But, JPA (the Hibernate implementation in our case) implements a Postgres SQL timestamp as java.sql.Timestamp or java.sql.Date which is not what I want in my Spring Boot application. I mean, I could deal with ava.sql.* types but as of Java 8 we have LocalDate and LocalDateTime classes with that nice new features like plusDays methods.
Generally I would like to have new Time API in my application.
So this is what our database table looks like:

CREATE TABLE public.tableA (
	id int4 NOT NULL DEFAULT nextval('terminykursow_seq'::regclass),
	termA timestamp NULL,
	termB timestamp NULL,
	idother int4 NULL
);

 And this is the corresponding entity:

@Entity

@Data

@Table(name = „tableA”, schema = „public”)

public class EntityA implements Serializable {
    @Id
    @SequenceGenerator(name=„tableA_seq”, sequenceName=„tableA_seq”, allocationSize=1)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator=„tableA_seq”)
    private Integer id;
    private LocalDateTime terminod;
    private LocalDateTime termindo;



    … other entity stuff …
}

Solution

Without any changes, the code compiles and the application starts correctly… but there are several errors, like:

“2017-08-08 18:32:06.911 ERROR 28428 --- [nio-8472-exec-2] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet] with root cause
org.postgresql.util.PSQLException: ERROR: operator does not exist: timestamp without time zone > bytea”

The error does not tell exactly what the problem is, which is, in my opinion, the weakest characteristic of Spring Boot and Spring Data JPA logging. Nevertheless, I decided to add some converters as described in the JPA documentation.

Tip no 1: Always use appropriate documentation.

The error does not tell exactly what the problem is, which is, in my opinion, the weakest characteristic of Spring Boot and Spring Data JPA logging. Nevertheless, I decided to add some converters as described in the JPA documentation.

Tip no 1: Always use appropriate documentation.

Please note that Converter is class that implements

javax.persistence.AttributeConverter

interface and You do not need any annotation on that class.
So my implementation looks like this:

import javax.persistence.AttributeConverter;
import java.sql.Timestamp;
import java.time.LocalDateTime;

public class LocalDateTimeConverter implements AttributeConverter {
    @Override
    public Timestamp convertToDatabaseColumn(LocalDateTime attribute) {
        return attribute != null ? Timestamp.valueOf(attribute) : null;
    }


    @Override
    public LocalDateTime convertToEntityAttribute(Timestamp dbData) {
        return dbData != null ? dbData.toLocalDateTime() : null;
    }
}

At this point, all I need to add is an annotation to my Entity fields that drives to the Converter class:

Convert(converter = LocalDateTimeConverter.class)

The final Entity looks like this:

@Entity
@Data
@Table(name = „tableA”, schema = „public”)
public class EntityA implements Serializable {
    @Id
    @SequenceGenerator(name=„tableA_seq”, sequenceName=„tableA_seq”, allocationSize=1)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator=„tableA_seq”)
    private Integer id;
    @Convert(converter = LocalDateTimeConverter.class)
    private LocalDateTime terminod;
    @Convert(converter = LocalDateTimeConverter.class)    
    private LocalDateTime termindo;

    … other entity stuff …
}

 And BOOM 🙂 everything work as expected.

Summary

There are many good mechanisms in Java 8 that You can use in Your Spring Data application, one of my favorite is Time API and with Converter mechanism You are no longer sentenced to javax.sql.* classes.

—

More articles at: http://3pio.pl/