hibernate 5 + ZonedDateTime + postgresql包含时区和偏移量 [英] hibernate 5 + ZonedDateTime + postgresql inlcude time zone and the offset

问题描述

我有一个正在运行的应用程序spring boot 1.3 + hibernate 5 + java 8 + ZonedDateTime + postgresql，并且在其中一个表中，我有以下字段。

  @Column（name =DATE_ENABLED）
 @Type（type =java.time.ZonedDateTime）
 private ZonedDateTime dateEnabled; 
 
 @Column（name =DATE_DISABLED）
 @Type（type =java.time.ZonedDateTime）
 private ZonedDateTime dateDisabled; 
  

如果我运行应用程序，那么我会看到默认情况下会生成没有时区的时间戳 p>

  testDB =＃\ d类型
表public.type
列|类型|修饰符
 -------------------------------- + ------------ ----------------- + ----------- 
 type_id | bytea |不为空
 date_disabled |没有时区的时间戳| 
 date_enabled |没有时区的时间戳| 
  

我知道如果将columnDefinition =TIMESTAMP WITH TIME ZONE添加到列中，

  @Column（name =DATE_DISABLED，columnDefinition =TIMESTAMP WITH TIME ZONE）
  

然后它正常工作，我可以看到hibernate创建了一个带有时区的列，但是如果我正确地理解了这一点，为postgres工作，即如果我明天将数据库更改为mysql，那么hibernate将会抛出一个错误。

因此，我的问题是如何做到这一点，即告诉hibernate创建一个包含时区和偏移量的列。我认为，由于Java类型的ZonedDateTime被创建为包含UTC中的时区和时间偏移量，因此休眠将默认创建一个包含时区的列。因此，再次提出的问题是什么是正确的方法来告诉hibernate包括时区和偏移量。

这里是我的pom部分

 < groupId> org.springframework.boot< / groupId> 
< artifactId> spring-boot-starter-parent< / artifactId> 
< version> 1.3.0.RELEASE< / version> 
 
< hibernate.version> 5.0.4.Final< /hibernate.version> 
 
< dependency> 
< groupId> org.postgresql< / groupId> 
< artifactId> postgresql< / artifactId> 
< /依赖关系> 
 
< dependency> 
< groupId> org.hibernate< / groupId> 
< artifactId> hibernate-java8< / artifactId> 
< version> $ {hibernate.version}< / version> 
< /依赖关系> 
  

和我的属性文件显示方言

<$ （）{
> @Bean
public Properties hibernateProperties（）{
返回新属性（）{
{
setProperty（hibernate.dialect， org.hibernate.dialect.PostgreSQLDialect）;
setProperty（hibernate.chach.provider_class，org.hibernate.cache.NoCacheProvider）;
setProperty（hibernate.show_sql，true）;
setProperty（hibernate.hbm2ddl.auto，create-drop）;
}
};
}


解决方案

如果您使用Hibernate进行模式创建，这是一个障碍。因此，如果在PostgreSQL上为带时区的时间戳创建了列之后，一切正常，请继续。无论如何，让Hibernate生成你的模式是一个不好的做法。手动操作（或者让DBA这样做）。如果要自动化，请使用数据库迁移工具，如 Flyway 或 Liquibase 之后，一个可靠的人写了sql脚本。

除此之外，明天更改数据库的要求听起来真的是虚构的，独立工作的数据库或多或少是不现实的，并且在编写需要执行的更大应用程序时变得更难。

如果您需要更多关于Hibernate / JDBC时间戳行为的信息，可以查看这篇很好的文章。

I have a running app spring boot 1.3 + hibernate 5 + java 8 + ZonedDateTime + postgresql and in one of the tables i have the following fields.

@Column(name = "DATE_ENABLED")
@Type(type="java.time.ZonedDateTime")   
private ZonedDateTime dateEnabled;

@Column(name = "DATE_DISABLED")
@Type(type="java.time.ZonedDateTime")   
private ZonedDateTime dateDisabled;

If i run the app then i see that this by default produces "timestamp without time zone"

testDB=# \d type
                Table "public.type"
             Column             |            Type             | Modifiers 
--------------------------------+-----------------------------+-----------
 type_id                        | bytea                       | not null
 date_disabled                  | timestamp without time zone | 
 date_enabled                   | timestamp without time zone | 

I know that if i add the columnDefinition= "TIMESTAMP WITH TIME ZONE" to the column i.e. something like

@Column(name = "DATE_DISABLED", columnDefinition= "TIMESTAMP WITH TIME ZONE")

then it works correctly and i am able to see that hibernate created a column with time zone, but if i understand this correctly this will be only working for postgres i.e. if i change the database tomorrow to mysql then hibernate will throw an error.

Thus my question is how to do that in general i.e. to tell hibernate to create a column that should include the time zone and the offset. I was the opinion that since the java type "ZonedDateTime" is deliberetelly created to include the time zone and the time offset in UTC then hibernate will by default create a column that include the time zone. Thus the question again what is the proper way to tell hibernate to include the time zone and the offset.

here are parts of my pom

    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>1.3.0.RELEASE</version>

    <hibernate.version>5.0.4.Final</hibernate.version>

    <dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
    </dependency>

    <dependency>
        <groupId>org.hibernate</groupId>
        <artifactId>hibernate-java8</artifactId>
        <version>${hibernate.version}</version>
    </dependency>

and my properties file showing the dialect

    @Bean
    public  Properties hibernateProperties() {
      return new Properties() {
        {
            setProperty("hibernate.dialect", "org.hibernate.dialect.PostgreSQLDialect");
            setProperty("hibernate.chach.provider_class", "org.hibernate.cache.NoCacheProvider");
            setProperty("hibernate.show_sql", "true");              
            setProperty("hibernate.hbm2ddl.auto", "create-drop");
         }
      };
   }

解决方案

It seems this problem is only an obstacle if you use Hibernate for schema creation. So if everything works well after you created the column as timestamp with time zone on PostgreSQL, just go with that. Anyway it's a bad practice to let Hibernate generate your schema. Do it manually (or let a DBA do that). If you want to automate, use a database migration tool like Flyway or Liquibase after a reliable person wrote the sql scripts.

Beside this, the requirement "change the database tomorrow" sounds really fictional, working database independent is more or less unrealistic and gets harder on writing bigger applications which have to perform.

If you need more information about Hibernate / JDBC timestamp behavior, maybe check out this nice article about that.

这篇关于hibernate 5 + ZonedDateTime + postgresql包含时区和偏移量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！