如何在JPQL或HQL查询中使用MySQL CONVERT_TZ函数

问题描述

我目前在MySQL数据库中有一个表，该表中的startDate和endDate都为timestamps.这是使用休眠链接到代码的.

I currently have table in a MySQL database which has a startDate and endDate which are both timestamps. This is linked to the code using hibernate.

我可以使用以下HQL返回这些

I can return these by using the following HQL

SELECT startDate, endDate FROM Reservation where id = :id

我想做的是根据给定的时区返回这些日期.我知道MySQL具有功能CONVERT_TZ，该功能可以根据给定的时区返回日期，我想知道HQL是否具有类似的功能?

What I am trying to do is return these dates based on a given timezone. I know MySQL has a function CONVERT_TZ which can return the dates based on a given timezone, I'm wondering if HQL has a similar function?

我知道HQL具有TIMESTAMP_WITH_ZONE功能，但是当我需要指定时区时会使用本地时区，这可以做到吗?

I know HQL has a TIMESTAMP_WITH_ZONE function but this uses a local timezone when I need to specify the timezone, can this be done?

推荐答案

自Hibernate ORM 5.2.18起

正如我在这篇文章中所述， 5.2.18，您可以通过MetadataBuilderContributor:

Since Hibernate ORM 5.2.18

As I explained in this article, since 5.2.18, you can register SQL functions via the MetadataBuilderContributor:

public class SqlFunctionsMetadataBuilderContributor
        implements MetadataBuilderContributor {

    @Override
    public void contribute(MetadataBuilder metadataBuilder) {
        metadataBuilder.applySqlFunction(
            "convert_tz",
            new StandardSQLFunction( "convert_tz", StandardBasicTypes.TIMESTAMP )
        );
    }
}

Ans只需通过hibernate.metadata_builder_contributor配置属性提供MetadataBuilderContributor:

Ans simply provide the MetadataBuilderContributor via the hibernate.metadata_builder_contributor configuration property:

<property>
    name="hibernate.metadata_builder_contributor"
    value="com.vladmihalcea.book.hpjp.hibernate.query.function.SqlFunctionsMetadataBuilderContributor"
</property>

Hibernate ORM 5.2.18之前的版本

或者，如果您使用Hibernate本机机制进行引导，则可以按照休眠用户指南.

注册SQL函数的一种非常常见但幼稚的方法是覆盖MySQL方言并注册新函数，如下所示:

A very common, yet naive way to register a SQL function, is override the MySQL Dialect and register the new function like this:

class CustomMySQLDialect extends MySQL5InnoDBDialect {
    public CustomMySQLDialect() {
        super();
        registerFunction( "convert_tz", new StandardSQLFunction( "convert_tz", StandardBasicTypes.TIMESTAMP ) );
    }

}

将Hibernate配置为使用新的方言:

The configure Hibernate to use the new dialect:

<property>
    name="hibernate.metadata_builder_contributor"
    value="com.vladmihalcea.book.hpjp.hibernate.CustomMySQLDialect"
</property>

这篇关于如何在JPQL或HQL查询中使用MySQL CONVERT_TZ函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！