DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones AWS Cloud
by AWS Developer Relations
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones
AWS Cloud
by AWS Developer Relations
The Latest "Software Integration: The Intersection of APIs, Microservices, and Cloud-Based Systems" Trend Report
Get the report
  1. DZone
  2. Data Engineering
  3. Databases
  4. Mapping JPA in Your Database With the Time API

Mapping JPA in Your Database With the Time API

Throughout application development, you need to make CRUD operations on our stored data. Postgres and the Time API can help you map JPA in your database.

Tomasz J-ski user avatar by
Tomasz J-ski
·
Aug. 09, 17 · Tutorial
Like (5)
Save
Tweet
Share
45.86K Views

Join the DZone community and get the full member experience.

Join For Free

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 a database, I use Postgres, which has quite a few nice features. And what's most important is that it's free!

A detailed technology stack includes:

  •  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 the 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 nice new features like plusDays methods.

Generally, I would like to have a 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 1: Always Use Appropriate Documentation

Please note that Converter is a class that implements the javax.persistence.AttributeConverter interface. You do not need any annotations 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 < LocalDateTime, Timestamp > {
 @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 works as expected.

Summary

There are many good mechanisms in Java 8 that you can use in your Spring Data application. One of my favorites is the Time API and the Converter mechanism. With these, you are no longer sentenced to javax.sql.* classes.

Database API Spring Framework Spring Data Spring Boot application Data (computing) PostgreSQL sql

Published at DZone with permission of Tomasz J-ski. See the original article here.

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • Rust vs Go: Which Is Better?
  • Journey to Event Driven, Part 1: Why Event-First Programming Changes Everything
  • What Is API-First?
  • What Is JavaScript Slice? Practical Examples and Guide

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends: