CODEWITHSUNDEEP
sqlhibernatehqlh2jpql
Khaled
Khaled

Reputation: 435

how to convert a complex SQL to HQL or JPQL query

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?

Upvotes: 2

Views: 1836

Answers (1)

Thorben Janssen
Thorben Janssen

Reputation: 3275

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

Upvotes: 8

Related Questions