如何映射postgresql“时间戳与时区”在JPA 2实体中 [英] How to map postgresql "timestamp with time zone" in a JPA 2 entity

问题描述

我有一个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".

这篇关于如何映射postgresql“时间戳与时区”在JPA 2实体中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！