Java hibernate.jdbc.time_zone = UTC 忽略

hibernate.jdbc.time_zone = UTC ignored

提问by Bhavna Jhunjhunwala

Using set up as

使用设置为

• Spring framework 5.0.2.RELEASE
• Spring Security 5.0.0.RELEASE
• Hibernate 5.2.11.Final

• Spring 框架 5.0.2.RELEASE
• Spring Security 5.0.0.RELEASE
• 休眠 5.2.11.Final

I followed spring boot& Vlad's linkto configure my application as below

我按照spring boot& Vlad 的链接配置我的应用程序如下

db.properties -

db.properties -

jdbc.driverClassName = com.mysql.jdbc.Driver
jdbc.url = jdbc:mysql://hostname:port/appname?useSSL=false&serverTimezone=UTC&useLegacyDatetimeCode=false
jdbc.username = xxx
jdbc.password = xxx
jdbc.minPoolSize = 5
jdbc.maxPoolSize = 20
jdbc.maxIdleTime = 30000
hibernate.dialect = org.hibernate.dialect.MySQLDialect
hibernate.show_sql = true
hibernate.format_sql = false
spring.jpa.properties.hibernate.jdbc.time_zone = UTC

HibernateConfig.java

休眠配置文件

@Configuration
@EnableTransactionManagement
@ComponentScan(basePackages="in.greenstack.ikon")
@PropertySource(value = { "classpath:/resources/db.properties" })
public class HibernateConfig {

    @Autowired
    private Environment environment;

    @Bean(name = "myDataSource")
    ....

    @Bean
    public LocalSessionFactoryBean sessionFactory() {
        LocalSessionFactoryBean sessionFactory = new LocalSessionFactoryBean();
        sessionFactory.setDataSource(dataSource());
        sessionFactory.setPackagesToScan(new String[] { "in.greenstack.ikon.entity" });
        sessionFactory.setHibernateProperties(hibernateProperties());
        return sessionFactory;
     }

    private Properties hibernateProperties() {
        Properties properties = new Properties();
        properties.put("hibernate.dialect", environment.getRequiredProperty("hibernate.dialect"));
        properties.put("hibernate.show_sql", environment.getRequiredProperty("hibernate.show_sql"));
        properties.put("hibernate.format_sql", environment.getRequiredProperty("hibernate.format_sql"));
        properties.put("spring.jpa.properties.hibernate.jdbc.time_zone", 
                environment.getRequiredProperty("spring.jpa.properties.hibernate.jdbc.time_zone"));
        return properties;        
   }

Entity -

实体 -

@Entity
@Table(name = "PROJECT_MASTER")
public class Project {

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "ID")
private int id;

@Column(name = "NAME")
private String name;

@Column(name = "START_DATE")
private Date startDate;

@Column(name = "END_DATE")
private Date endDate;

@ManyToOne
@JoinColumn(name = "FINANCIAL_YEAR")
private FinancialYear financialYear;

Table Definition -

表定义 -

    CREATE TABLE `PROJECT_MASTER` (
    `ID` int(11) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(50) NOT NULL,
  `FINANCIAL_YEAR` varchar(20) DEFAULT NULL,
  `START_DATE` date DEFAULT NULL,
  `END_DATE` date DEFAULT NULL,

Logs -

日志 -

    2018-06-13 16:39:03 DEBUG SQL:92 - insert into PROJECT_MASTER (ACCOUNTING_STANDARD_ID, CONVERSION_METHOD_ID, CURRENCY_ID, DESCRIPTION, END_DATE, FINANCIAL_YEAR, HIERARCHY_ID, IMPORT_FROM_PREVIOUS, NAME, OPENING_PROJECT_ID, START_DATE) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into PROJECT_MASTER (ACCOUNTING_STANDARD_ID, CONVERSION_METHOD_ID, CURRENCY_ID, DESCRIPTION, END_DATE, FINANCIAL_YEAR, HIERARCHY_ID, IMPORT_FROM_PREVIOUS, NAME, OPENING_PROJECT_ID, START_DATE) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
2018-06-13 16:39:03 TRACE ResourceRegistryStandardImpl:55 - Registering statement [com.mchange.v2.c3p0.impl.NewProxyPreparedStatement@747dab [wrapping: com.mysql.cj.jdbc.PreparedStatement@1abace9: insert into PROJECT_MASTER (ACCOUNTING_STANDARD_ID, CONVERSION_METHOD_ID, CURRENCY_ID, DESCRIPTION, END_DATE, FINANCIAL_YEAR, HIERARCHY_ID, IMPORT_FROM_PREVIOUS, NAME, OPENING_PROJECT_ID, START_DATE) values (** NOT SPECIFIED **, ** NOT SPECIFIED **, ** NOT SPECIFIED **, ** NOT SPECIFIED **, ** NOT SPECIFIED **, ** NOT SPECIFIED **, ** NOT SPECIFIED **, ** NOT SPECIFIED **, ** NOT SPECIFIED **, ** NOT SPECIFIED **, ** NOT SPECIFIED **)]]
2018-06-13 16:39:03 TRACE AbstractEntityPersister:2709 - Dehydrating entity: [in.greenstack.ikon.entity.Project#<null>]
2018-06-13 16:39:03 TRACE IdentifierValue:130 - ID unsaved-value: 0
2018-06-13 16:39:03 TRACE BasicBinder:65 - binding parameter [1] as [INTEGER] - [1]
2018-06-13 16:39:03 TRACE BasicBinder:53 - binding parameter [2] as [INTEGER] - [null]
2018-06-13 16:39:03 TRACE IdentifierValue:130 - ID unsaved-value: 0
2018-06-13 16:39:03 TRACE BasicBinder:65 - binding parameter [3] as [INTEGER] - [2]
2018-06-13 16:39:03 TRACE BasicBinder:65 - binding parameter [4] as [VARCHAR] - [TestTimeZone1 FY2017-2018 IGAAP]
**2018-06-13 16:39:03 TRACE BasicBinder:65 - binding parameter [5] as [DATE] - [2018-03-31]**
2018-06-13 16:39:03 TRACE IdentifierValue:130 - ID unsaved-value: 0
2018-06-13 16:39:03 TRACE BasicBinder:65 - binding parameter [6] as [INTEGER] - [2]
2018-06-13 16:39:03 TRACE BasicBinder:65 - binding parameter [7] as [INTEGER] - [30]
2018-06-13 16:39:03 TRACE BasicBinder:53 - binding parameter [8] as [BOOLEAN] - [null]
2018-06-13 16:39:03 TRACE BasicBinder:65 - binding parameter [9] as [VARCHAR] - [TestTimeZone1]
2018-06-13 16:39:03 TRACE BasicBinder:53 - binding parameter [10] as [INTEGER] - [null]
**2018-06-13 16:39:03 TRACE BasicBinder:65 - binding parameter [11] as [DATE] - [2017-04-01]**
2018-06-13 16:39:03 DEBUG IdentifierGeneratorHelper:78 - Natively generated identity: 63
2018-06-13 16:39:03 TRACE ResourceRegistryStandardImpl:91 - Releasing result set [com.mchange.v2.c3p0.impl.NewProxyResultSet@182014e [wrapping: com.mysql.cj.jdbc.result.ResultSetImpl@d243c6]]
2018-06-13 16:39:03 DEBUG ResourceRegistryStandardImpl:104 - HHH000387: ResultSet's statement was not registered
2018-06-13 16:39:03 TRACE ResourceRegistryStandardImpl:132 - Closing result set [com.mchange.v2.c3p0.impl.NewProxyResultSet@182014e [wrapping: com.mysql.cj.jdbc.result.ResultSetImpl@d243c6]]
2018-06-13 16:39:03 TRACE ResourceRegistryStandardImpl:68 - Releasing statement [com.mchange.v2.c3p0.impl.NewProxyPreparedStatement@747dab [wrapping: com.mysql.cj.jdbc.PreparedStatement@1abace9: **insert into PROJECT_MASTER (ACCOUNTING_STANDARD_ID, CONVERSION_METHOD_ID, CURRENCY_ID, DESCRIPTION, END_DATE, FINANCIAL_YEAR, HIERARCHY_ID, IMPORT_FROM_PREVIOUS, NAME, OPENING_PROJECT_ID, START_DATE) values (1, null, 2, 'TestTimeZone1 FY2017-2018 IGAAP', '2018-03-30', 2, 30, null, 'TestTimeZone1', null, '2017-03-31')]]**

As can be seen from above logs, that the binding parameter is the correct value (1st April 2017 & 31st March 2018) sent from client, but when saving in db it is still saving incorrect dates.

从上面的日志中可以看出，绑定参数是从客户端发送的正确值（2017 年 4 月 1 日和 2018 年 3 月 31 日），但是在保存到 db 时，它仍然保存了错误的日期。

Kindly advice what I have missed.

请建议我错过了什么。

pom.xml -

pom.xml -

<properties>
        <spring.version>5.0.2.RELEASE</spring.version>
        <springsecurity.version>5.0.0.RELEASE</springsecurity.version>
        <mysql.connector.version>8.0.11</mysql.connector.version>
        <hibernate.version>5.2.17.Final</hibernate.version>
        <hibernate.validator.version>5.2.3.Final</hibernate.validator.version>
    </properties>

回答by Vlad Mihalcea

Change this:

改变这个：

 properties.put("spring.jpa.properties.hibernate.jdbc.time_zone", 
                environment.getRequiredProperty("spring.jpa.properties.hibernate.jdbc.time_zone"));

to this:

对此：

 properties.put("hibernate.jdbc.time_zone", 
            environment.getRequiredProperty("spring.jpa.properties.hibernate.jdbc.time_zone"));

回答by Shaunak Patel

Try this(it worked for me). Write below code snippet in your spring boot main application file.

试试这个（它对我有用）。在您的 Spring Boot 主应用程序文件中写入以下代码片段。

@PostConstruct
public void started() {
    TimeZone.setDefault(TimeZone.getTimeZone("UTC"));
}

Source

来源

回答by WesternGun

I think you should put spring.jpa.properties.hibernate.jdbc.time_zone = UTCin application.yml/application.propertiesof main/or test/, not in db.properties. And, I think .propertiestakes precedence over .yml. Correct me if I am wrong.

我认为你应该spring.jpa.properties.hibernate.jdbc.time_zone = UTC输入application.yml/ application.propertiesof main/or test/，而不是db.properties. 而且，我认为.properties优先于.yml. 如果我错了，请纠正我。

And, this timezone config is also affected by Timezone.getDefault().

而且，这个时区配置也受Timezone.getDefault().

See this post: https://aboullaite.me/spring-boot-time-zone-configuration-using-hibernate/

看到这个帖子：https: //aboullaite.me/spring-boot-time-zone-configuration-using-hibernate/

And, as I test, I see that if you want to save a java.util.Date, which has no timezone info in itself(only in its Calendarproperty, this is true), and the column definition hold no place for timezone info, this property will affect the value saved to DB, but notthat when you retrieve the row from DB; the latter is affected only by Timezone.getDefault(). Meanwhile, if you set Timezone.getDefault(), the savedand retrievedvalue will both be the timezone you want.

而且，在我测试时，我看到如果你想保存一个java.util.Date本身没有时区信息的 ，它本身没有时区信息（只有在它的Calendar属性中，这是真的），并且列定义没有时区信息的位置，这个属性将影响保存到 DB 的值，但不是从 DB 检索行时的值；后者仅受 影响Timezone.getDefault()。同时，如果您设置Timezone.getDefault()，则保存和检索的值都将是您想要的时区。

So, this property may not be the most proper way to manipulate the value of Date, if you saveand queryfor the information in the same application. Just use Timezone.setDefault(Timezone.getTimezone("XXX")).

因此，如果您在同一个应用程序中保存和查询信息，则此属性可能不是操作 Date 值的最合适的方式。只需使用Timezone.setDefault(Timezone.getTimezone("XXX")).