Shulou(Shulou.com)06/02 Report--

What is the mapping relationship between Java 8 date and database date? I believe many inexperienced people don't know what to do about it. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

There are many new date types in Java8, which are easier to use than traditional date types. What to use to map to the date of the database is a more complex issue.

JDBC 4.2 specification

According to the JDBC4.2 specification, the relationship between the Java date type and the database date type is as follows:

Java date Database date java.sql.DateDATEjava.sql.TimeTIMEjava.sql.TimestampTIMESTAMPjava.util.CalendarTIMESTAMPjava.util.DateTIMESTAMPjava.time.LocalDateDATEjava.time.LocalTimeTIMEjava.time.LocalDateTimeTIMESTAMPjava.time.OffsetTimeTIME_WITH_TIMEZONEjava.time.OffsetDatetimeTIMESTAMP_WITH_TIMEZONE

Two of them are special.

TIMESTAMP_WITH_TIMEZONE: contains the date time (DateTime) of the Time Zone, mapped to OffsetDatetime.

TIME_WITH_TIMEZONE: contains the time of Time Zone (Time), mapped to OffsetTime.

The new date type in java8 replaces the old date type

Java.time.LocalDate replaces java.sql.Date

Java.time.LocalTime replaces java.sql.Time

Java.time.LocalDateTime replaces java.sql.Timestamp

Note: the JDBC4.2 specification does not support Instant and ZonedOffsetDateTime

PostgreSQL JDBC implements Java date database date java.time.LocalDateDATEjava.time.LocalTimeTIME [WITHOUT TIME ZONE] java.time.LocalDateTimeTIMESTAMP [WITHOUT TIME ZONE] java.time.OffsetDatetimeTIMESTAMP WITH TIME ZONE

In addition to not supporting Instant and ZonedOffsetDateTime, OffsetTime does not support either.

Reference: PostgreSQL JDBC: Using Java 8 Date and Time classes

PostgreSQL database

Timestamp is similar to LocalDateTime, but local time. Make sure that the time zone of the JVM is the same as that of the database, otherwise there will be jet lag.

Timestamptz is of type TIMESTAMP WITH TIME ZONE, but does not save Time Zone information, but simply uses UTC standard time. The reason is that Time Zone is only used for display, and how to display the time should be handled by the application, there is no need to save to the database.

Mysql- "> MySQL database

MySQL does not even provide the type of TIMESTAMP WITH TIME ZONE, the date-time type is only DateTime, there is no concept of Time Zone. The time zone must be determined using serverTimezone in the jdbc connection. Such as jdbc:mysql://localhost/ujcms?serverTimezone=Asia/Shanghai.

Oracle database

Date: local time. Accuracy to seconds.

Timestamp: local time. The accuracy can reach nanosecond.

TIMESTAMP WITH TIME ZONE: a standard OffsetDateTime that holds Time Zone information.

TIMESTAMP WITH LOCAL TIME ZONE: similar to PostgresSQL's timestamptz, only the standard UTC time is saved and then calculated based on the local Time Zone.

SQL Server database

Datetime2: local time.

Datetimeoffset: a standard OffsetDateTime that holds Time Zone information.

JPA

JPA's support for dates is consistent with the JDBC specification.

Hibernate

Hibernate extends on the basis of JPA to support Instant and ZonedDateTime.

But all Java8 date types are eventually converted to Timestamp for processing. That is, even if the database supports TIMESTAMP WITH TIME ZONE and saves the time zone information, Hibernate discards it and uses the JVM time zone instead (the time is guaranteed to be correct).

MyBatis

Instant is supported. Converted to Timestamp processing.

Support for ZonedDateTime, directly using native. Poor compatibility, if PostgreSQL JDBC does not support this type, an error will be reported.

Freemarker

Format using freemarker-java-8.

Supports formatting of OffsetDateTime and ZonedDateTime, using the time zone that comes with the object.

Instant formatting is not supported and the toString () method is called directly. Officials say they will increase support for Instant, but no new version has been released for three years.

Thymeleaf

Supports formatting of OffsetDateTime and ZonedDateTime, using the time zone that comes with the object.

Instant formatting is supported, using the JVM default time zone.

Jackson

Instant:2008-08-08T08:00:00Z

OffsetDateTime: consistent with Instant.

ZondDateTime:2008-08-08T08:00:00Z [UTC]

How to choose

Although the date of the LocalDateTime is friendly, the time zone is uncertain, depending on the time zone of the JVM. As a result, the time is uncertain, and JVM in different time zones will get different time to access the database. This is very fatal, and when using LocalDateTime, make sure that the time zone of the JVM and the database are the same.

According to the JDBC specification, there is no doubt that OffsetDateTime should be chosen. OffsetDateTime is a good choice, with a friendly date display like LocalDateTime, while ensuring the certainty of time.

However, since neither MySQL nor PostgreSQL provides a real time zone preservation, TIMESTAMP WITH TIME ZONE,OffsetDateTime has actually been downgraded to Instant (PostgresSQL's timestamptz is essentially Instant). In particular, PostgreSQL provides a standard UTC time zone, while what is actually required is the Beijing time of UTC+8, which makes it impossible to get the correct format in both Freemarker and Thymeleaf.

Considering database compatibility, Instant seems to be a better choice. However, neither JDBC4.2 nor JDBC4.3 supports Instant, and Instant cannot be formatted in Freemarker.

Most databases provide real TIMESTAMP WITH TIME ZONE, and even MySQL can get the correct OffsetDateTime for the time zone by setting serverTimezone. Coupled with the requirements of the JDBC specification, OffsetDateTime is still the first choice. As for PostgreSQL compatibility, you can customize date formatting methods in FreeMarker and Thymeleaf.

What is Java? Java is an object-oriented programming language that can write desktop applications, Web applications, distributed systems, and embedded system applications.

After reading the above, have you mastered the method of mapping Java 8 dates to database dates? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.