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

How to map postgresql "timestamp with time zone" in a JPA 2 entity

提问by Ngure Nyaga

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 ).

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

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

我无法将“带时区的时间戳”字段映射到我的 JPA 实体中。

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

我不断收到以下根本原因的异常： 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.Calendarwith java.util.Date- made no difference
• using java.sql.Timestamp- complained that I cannot apply the @Temporalannotation to a Timestamp
• using org.joda.time.DateTimewith a custom @Typeannotation ( @Type(type="org.joda.time.contrib.hibernate.PersistentDateTimeTZ")) also did not work

• 替换java.util.Calendar为java.util.Date- 没有区别
• using java.sql.Timestamp- 抱怨我无法将@Temporal注释应用于Timestamp
• 使用org.joda.time.DateTime带有自定义@Type注释（@Type(type="org.joda.time.contrib.hibernate.PersistentDateTimeTZ")）也没有工作

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?

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

回答by Ngure Nyaga

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".

以前，我<property name="hibernate.hbm2ddl.auto" value="validate"/>"hibernate.hbm2ddl.auto"在我的persistence.xml 中有过。当我注释掉这个属性时，我的应用程序服务器启动并且模型“工作”。

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.

在阅读了相当多的内容后，我的印象是没有简单的方法可以将 Postgresql 时间戳与时区列映射。

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 typesor completely custom user types can also be used to tackle this problem.

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

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

我需要注意，我对这个实体的用例是“只读”的，所以我可以摆脱一个看似天真的“解决方案”。

回答by Alexei Osipov

Add @Column(columnDefinition= "TIMESTAMP WITH TIME ZONE")

添加 @Column(columnDefinition= "TIMESTAMP WITH TIME ZONE")

@Column(name = "run_from", columnDefinition= "TIMESTAMP WITH TIME ZONE")
@NotNull
@Temporal(TemporalType.TIMESTAMP)
private Date runFrom;