JPA Criteria API: how to retrieve date in “mm/dd/y

问题:

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.

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')

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.

回答1:

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);

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).

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();

where

• 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

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

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

回答2:

SELECT '2001-02-16 20:38:40'::date;

date
----------------
2001-02-16
(1 row)

Or you can use @TemporalType on JPA entity field.