CODEWITHSUNDEEP
javamysqljpajpql
sinxanh
sinxanh

Reputation: 71

How to use date_format when using JPQL/JPA

I am doing Java EE with MySQL as database and implementing JPA on my codes.

I have no problem retrieving the data from MySQL Workbench but when I change my syntax to JPQL's it does not work.

For e.g. in MySQL - it works

SELECT date_format(s.date,'%Y, %m, %d') from Transactions s;

in JPQL - it does not

SELECT date_format(s.date,'%Y, %m, %d') from TransactionEntity s;

How do i modify to suit the JPA query?

Note: in JPQL the following works

SELECT s.date from TransactionEntity s;

Upvotes: 5

Views: 25153

Answers (2)

Bilal Demir
Bilal Demir

Reputation: 686

Sql function is available in JPQL. My JPA version is 1.11.9. Sample group by day query:

@Query(value = "SELECT count(ac) as count, function('date_format', max(ac.subscriptionStartDate), '%Y, %m, %d') as date FROM MyTable ac " +
        "WHERE ac.subscriptionStartDate BETWEEN :startDate AND :endDate GROUP BY function('date_format', ac.subscriptionStartDate, '%Y, %m, %d')")
public List<Map<String,Object>> findRegisteredCustomersHistory(@Param("startDate") Date startDate, @Param("endDate") Date endDate);

The result of the query is the list that records the number of records grouped by days in formatted form.

Sample rows:

count: 3, date: 2019, 09, 10
count: 1, date: 2019, 09, 11

for your question, try this in JPQL:

@Query(value = "SELECT function('date_format', s.date, '%Y, %m, %d') as date from Transactions s;")

Upvotes: 3

Neil Stockton
Neil Stockton

Reputation: 11531

SQL function date_format is not part of JPQL, as any documentation would tell you, so don't see the point in just pushing SQL into JPQL and expecting it to work.

What you can do with JPA 2.1 is invoke it as follows

function("date_format", s.date, '%Y, %m, %d')

where function is a way to invoke any native SQL function. This clearly means you lose database independence because that function is not valid on all datastores.

Upvotes: 10

Related Questions