2

I have an SQL command that has multiple selects, functions and joins using H2 database. I need to convert it to JPQL.

SELECT TotalHours, ROUND(PRICE*TotalHours, 2) AS TotalPayment, PLATE_NUM, CUSTOMER_ID FROM (

SELECT (DATEDIFF(s, TIMESTAMP'2020-07-29 16:00:00', TIMESTAMP'2020-08-10 07:00:00') / 3600.0) AS TotalHours, c.PRICE, b.PLATE_NUM, b.CUSTOMER_ID
from BOOKING b inner join CAR c on b.PLATE_NUM = c.PLATE_NUM

) AS x;

Could you help, please?

Khaled
  • 345
  • 5
  • 14
  • Are the two timestamps which appear in your call to `DATEDIFF` really constants, or are these two values proxies for columns? If the latter, you may simply calculate the difference in seconds and then hard code it. – Tim Biegeleisen Aug 16 '20 at 12:01
  • These timestamps would be variables in the HQL statement. For example, TIMESTAMP'2020-07-29 16:00:00' would be :startDateTime and TIMESTAMP'2020-08-10 07:00:00' would be :endDateTime – Khaled Aug 16 '20 at 12:07
  • If so, then compute the date diff in Java and use the literal value in your HQL query. You _can't_ use `DATEDIFF` (a SQL Server function) in Hibernate. – Tim Biegeleisen Aug 16 '20 at 12:09
  • https://stackoverflow.com/a/2986354/495157 Formulas may help with part of it. – JGFMK Aug 16 '20 at 12:26
  • Rather use native query – Eklavya Aug 16 '20 at 12:34

1 Answers1

8

This query is too complex for JPQL. But you can execute it as a native SQL query. Your persistence provider (seems to be Hibernate) will send the statement directly to the database. Because of that, you can use all SQL features supported by your database but if you need to support different DBMS, you need to handle the different SQL dialects yourself.

em.createNativeQuery("SELECT TotalHours, ROUND(PRICE*TotalHours, 2) AS TotalPayment, PLATE_NUM, CUSTOMER_ID FROM (SELECT (DATEDIFF(s, TIMESTAMP'2020-07-29 16:00:00', TIMESTAMP'2020-08-10 07:00:00') / 3600.0) AS TotalHours, c.PRICE, b.PLATE_NUM, b.CUSTOMER_ID from BOOKING b inner join CAR c on b.PLATE_NUM = c.PLATE_NUM) AS x");

If you want to map the query result to a DTO object, you can use an @SqlResultSetMapping. See: https://thorben-janssen.com/result-set-mapping-constructor-result-mappings

Thorben Janssen
  • 3,012
  • 9
  • 23