如何映射postgresql“时间戳与时区”在JPA 2实体中

本文介绍了如何映射postgresql“时间戳与时区”在JPA 2实体中的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个JPA 2应用程序（使用Hibernate 3.6作为JPA实现），它使用Postgresql（使用9.0-801.jdbc3 JDBC驱动程序）。

我是在将时区与时区字段映射到我的JPA实体时遇到问题。

以下是一个例子：

CREATE TABLE主题（ id序列NOT NULL，＃对该问题不重要的字段已被编辑出 run_from带时区的时间戳NOT NULL，带时区的run_to时间戳NOT NULL， CONSTRAINT theme_pkey PRIMARY KEY（id）， CONSTRAINT theme_name_key UNIQUE（name））

我试图映射如下：

@Entity @Table（模式=content，name =theme） public class Theme extends AbstractBaseEntity { private static final long serialVersionUID = 1L; @Column（name =run_from） @NotNull @Temporal（TemporalType.TIMESTAMP） private日期runFrom; @Column（name =run_to） @NotNull @Temporal（TemporalType.TIMESTAMP） private日期runTo; / *实体的其余部分已被编辑* /

我一直以下面的根本原因得到一个异常：由以下原因引发：org.hibernate.HibernateException：创建的列的public.backend_themetopic中的列类型错误。发现：timestamptz，expected：date

我曾尝试过

• 用 java.util.Date替换 java.util.Calendar - 使用 java.sql.Timestamp 没有区别
• - 抱怨我无法应用 @Temporal 注释到时间戳
• 使用 org.joda.time.DateTime
code>自定义 @Type 注释（ @Type（type =org.joda.time.contrib.hibernate.PersistentDateTimeTZ）

限制

• 此应用程序与遗留系统交互 - 因此，更改日期字段的类型不是一个好选择

我的问题是：我应该如何将这些时区感知时间戳映射到我的JPA实体中？

解决方案

我最终做出了这个工作 - 以一种骇人的方式y - 通过关闭模式验证。

以前，我有< property name =hibernate.hbm2ddl.autovalue =validate在我的persistence.xml中/>\"hibernate.hbm2ddl.auto>。当我注释掉这个属性时，我的应用服务器启动了，模型工作了。

我的实体的最终形式为：

@Entity @Table（schema =content，name =theme） public class Theme extends AbstractBaseEntity { private static final long serialVersionUID = 1L; @Column（name =run_from，columnDefinition =timestamp with time zone not null） @NotNull @Temporal（TemporalType.TIMESTAMP） private日期runFrom; @Column（name =run_to，columnDefinition =timestampt with time zone not null） @NotNull @Temporal（TemporalType.TIMESTAMP） private日期runTo; $ b $ * * Getters，setters，.hashCode（），.equals（）等省略* /

在阅读了这篇文章后，我得到的印象是，没有简单的方法将Postgresql时间戳与时区列映射。

一些JPA实现+数据库组合本身支持这个（EclipseLink + Oracle就是一个例子）。对于使用jodatime扩展的休眠模式，可以使用时区的正常时间戳+ varchar字段来存储时区感知时间戳（由于受限于更改数据库模式，我无法这样做）。 Jadira用户类型或完全自定义的用户类型也可用于解决此问题。

我需要说明的是，这个实体的用例是只读，所以我可以用一个看似天真的解决方案逃脱。

I have a JPA 2 application ( with Hibernate 3.6 as the JPA implementation ) that uses Postgresql ( with the 9.0-801.jdbc3 JDBC driver ).

I am having trouble mapping "timestamp with time zone" fields into my JPA entities.

Here is an example:

CREATE TABLE theme ( id serial NOT NULL, # Fields that are not material to the question have been edited out run_from timestamp with time zone NOT NULL, run_to timestamp with time zone NOT NULL, CONSTRAINT theme_pkey PRIMARY KEY (id ), CONSTRAINT theme_name_key UNIQUE (name ) )

I have tried to map as follows:

@Entity @Table(schema = "content", name = "theme") public class Theme extends AbstractBaseEntity { private static final long serialVersionUID = 1L; @Column(name = "run_from") @NotNull @Temporal(TemporalType.TIMESTAMP) private Date runFrom; @Column(name = "run_to") @NotNull @Temporal(TemporalType.TIMESTAMP) private Date runTo; /* The rest of the entity has been edited out */

I keep on getting an exception with the following root cause: Caused by: org.hibernate.HibernateException: Wrong column type in public.backend_themetopic for column created. Found: timestamptz, expected: date

What I have tried

• replacing java.util.Calendar with java.util.Date - made no difference
• using java.sql.Timestamp - complained that I cannot apply the @Temporal annotation to a Timestamp
• using org.joda.time.DateTime with a custom @Type annotation ( @Type(type="org.joda.time.contrib.hibernate.PersistentDateTimeTZ") ) also did not work

Constraints

• This application interacts with a "legacy system" - so, changing the types of the date fields is not a good option

My question is: how should I map these timezone aware timestamps into my JPA entities?

解决方案

I eventually made this "work" - in a hackish sort of way - by turning off schema validation.

Previously, I had <property name="hibernate.hbm2ddl.auto" value="validate"/>"hibernate.hbm2ddl.auto" in my persistence.xml. When I commented out this property, my app server started and the model "worked".

The final form of my entity was:

@Entity @Table(schema = "content", name = "theme") public class Theme extends AbstractBaseEntity { private static final long serialVersionUID = 1L; @Column(name = "run_from", columnDefinition = "timestamp with time zone not null") @NotNull @Temporal(TemporalType.TIMESTAMP) private Date runFrom; @Column(name = "run_to", columnDefinition = "timestampt with time zone not null") @NotNull @Temporal(TemporalType.TIMESTAMP) private Date runTo; /* Getters, setters, .hashCode(), .equals() etc omitted */

After reading quite a bit on this, I got the impression is that there is no easy way to map Postgresql timestamp with time zone columns.

Some JPA implementation + database combinations support this natively ( EclipseLink + Oracle is one example ). For hibernate, with jodatime extensions, it is possible to store timezone aware timestamps using a normal timestamp + a varchar field for the timezone( I could not do that since I was constrained from changing the database schema ). Jadira user types or completely custom user types can also be used to tackle this problem.

I need to note that my use-case for this entity is "read only", so I could get away with a seemingly naive "solution".