JPA Criteria API:如何检索"mm/dd/yyyy"中的日期格式 [英] JPA Criteria API: how to retrieve date in "mm/dd/yyyy" format

问题描述

我想检索"mm/dd/yyyy"格式的列日期之一.该列当前同时返回日期和时间，但我只希望使用"mm/dd/yyy"格式的日期.

I want to retrieve one of my column date in "mm/dd/yyyy" format. This column is currently returning date and time both but i want only date in "mm/dd/yyy" format.

以下是我要转换为条件api的postgresql查询

Below is my postgresql query that i want to convert to criteria api

select    DISTINCT c.name as Facility,
          to_char(begin_exam,'mm/dd/yyyy') as begin_exam
from  a inner join  b on a.rad_exam_id = b.id 
                      inner join  c on c.id = b.site_id
group by c.name,to_char(begin_exam,'mm/dd/yyyy')
order by c.name,to_char(begin_exam,'mm/dd/yyyy')

我在互联网上进行了很多搜索，但没有找到对我有帮助的解决方案.请帮助我为此编写标准api查询.

I searched on the internet a lot but did't find any solution that will help me. please help me in writing criteria api query for this.

推荐答案

标准API定义function expression以在CriteriaBuilder接口中执行本机SQL函数，如下所示:

Criteria API defines function expression to execute native SQL functions in the CriteriaBuilder interface as follows:

<T> Expression<T> function(String name, Class<T> type, Expression<?>... args);

其中name是SQL函数的名称，type是预期的返回类型，而args是参数的变量列表(如果有).

where name is the name of the SQL function, type is the expected return type and args is a variable list of arguments (if any).

以下是在Criteria查询中如何使用它的示例:

Here is an example how to use it in a Criteria query:

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery cq = cb.createQuery(String.class);
Root<RadExamTimes> root = cq.from(RadExamTimes.class);
cq.select( cb.function("to_char", String.class, root.get("begin_exam"), cb.literal("MM/DD/YYYY")));

TypedQuery<String> query = entityManager.createQuery(cq);
List<String> result = query.getResultList();

其中

• RadExamTimes:一个假设的根实体
• MM/DD/YYYY:一种特定于数据库的格式(在此示例中 PostgreSQL日期格式；对于Oracle使用Ora格式等)
• to_char:Postgresql函数将日期值转换为字符串
• begin_exam:要格式化的日期字段

• RadExamTimes: a hypothetical root entity
• MM/DD/YYYY: a database-specific format (in this example Postgresql date format; for Oracle use Ora format, etc)
• to_char: Postgresql function to convert date value to string
• begin_exam: the date field to be formatted

由于不能按原样传递格式字符串，因此使用literal()方法包装它.

The format string cannot be passed as is so that the literal() method is used to wrap it.

注意:上面的示例在具有MySQL功能和相应日期格式的MySQL数据库上进行了测试；但是示例更改为与Postgresql语法匹配.

Note: The above example is tested on MySQL database with MySQL function and corresponding date format; but the example changed to match Postgresql syntax.

这篇关于JPA Criteria API:如何检索"mm/dd/yyyy"中的日期格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！