1

I am in the process of updating an application that deals with the delivery of packages. Technology used: java 8 and hibernate 5.1 (with the possibility to upgrade to newest version) SQL Server.

So far the application was used only in a single country and all the events in the system (package registered, package delivered etc.) are managed in the code and stored in the database using old java Date. Entities, calculations, everything uses java.util.Date. I want to write new module using java 8 time classes.

The events will be using the new java.time.Instant class as it is a specific and time-zone independent moment in time.

The question: is there a possibility to define entities with java.time.Instant and database schema with definitions that are human readable?

By default, when declaring entities with Instant:

    @Column(name = "Event")
    protected Instant event;

Database schema is created as Event varbinary(255), which is impossible to read.

I could define it in the database as Event datetime, but then I would have to make a define it as java.sql.Timestamp and make a conversion to java.time.Instant every time.

Is there some SQL Server definition I'm missing? Maybe some entity annotation to make conversion easy? Possibly some different class to use as an entity field to make conversion trivial? Should I use different class from java.time package?

osiem888
  • 13
  • 3
  • 1
    I recommend you use `OffsetDateTime`. Check [this answer](https://stackoverflow.com/a/67752047/10819573) and [this answer](https://stackoverflow.com/a/67505173/10819573) to learn how to use `java.time` API with JDBC. – Arvind Kumar Avinash Aug 20 '21 at 20:05
  • you can use LocalDateTime https://docs.oracle.com/en/java/javase/11/docs/api/java.base/java/time/LocalDateTime.html while fixing the zone ID –  Aug 20 '21 at 20:12
  • @ArvindKumarAvinash Thank you for the links, but those do not seem to help me. First and foremost, they are about MySQL and not SQL Server. Second, they don't mention entity mappings at all. Thirdly, OffsetDateTime is also stored using unreadable varbinary. – osiem888 Aug 20 '21 at 20:13
  • 1
    Why does it need to be readable? Even in Java [`java.time.Instant`](https://docs.oracle.com/javase/8/docs/api/java/time/Instant.html) is a composite type: a 64-bit long for the Epoch seconds since `1970-002-02T00:00:00Z` and a 32-bit int for the leftover nanoseconds. A `binary(12)` would be sufficient to store these 96 bits. If you want to make it "readable" then you'd need to write your own code to wrangle it into something like `decimal(38,9)` where the whole part encodes seconds and the decimal part encodes the nanoseconds. – AlwaysLearning Aug 21 '21 at 00:16
  • 1
    @AlwaysLearning The client is used to having an easily readable dates in the database. It's much easier to prepare data or modify it, even for non tech savvy person. Service, bug seeking, testing, you name it is easier. Imagine if all data would be stored in a binary form and how annoying that would be. – osiem888 Aug 21 '21 at 04:28

1 Answers1

1

I think for version 5.1 you still need a special artifact that contains the type implementations for the Java 8 time types: https://search.maven.org/artifact/org.hibernate/hibernate-java8/5.1.0.Final/jar

Newer Hibernate versions that depend on Java 8 already include that in hibernate-core.

Christian Beikov
  • 15,141
  • 2
  • 32
  • 58