Refactor native query to JPQL query

Question

Suppose we have the following table, named as 'documents':

id | createDate | createBy | updateDate | updateBy
--------------------------------------------------
(various rows here)

the two *date columns are timestamps while the other are all strings (even the id)

Currently I have the following native query used in a Spring Repository:

select COUNT(distinct(u.user_working_total)) 
     from
(
    select distinct(createBy) user_working_total
    from documents
    where createDate >= :startDate and 
    createDate <= :endDate

    union

    select distinct(updateBy) user_working_total
    from documents
    where updateDate >= :startDate and 
    updateDate <= :endDate
) as u

As you can see, this must be used as a native query since JPA does not support select query in the from clause. Now I have to convert this query to a JPQL query, in order to make it database independent. Is this possible in some way? Other approaches are welcome, like using Specification or similar...

How about use `@Query(value = "your-native-sql", nativeQuery = true)` ? — RJ.Hwang, Oct 10 '18 at 16:30
This is what we have now. We want tu substitute the native query mechanism. — Lorelorelore, Oct 11 '18 at 05:51
The query works fine on SQL Server, but we want to have a database independent query. — Lorelorelore, Oct 11 '18 at 05:52

score 1 · Accepted Answer · answered Oct 11 '18 at 09:40

You state that you

want to have a database independent query.

I do think you might already have one.

The SQL statement is a rather simple one and while I don't pretend to know every SQL dialect out there I'd expect it to work with many databases, quite possibly with all that are relevant.

My primary recommendation is therefore to setup tests to test against all the databases you need to support and check if it works.

If you insist to use JPQL the following might work:

Add an entity/table Two with just one column (id) and two entries: 1 and 2.

You could construct your query as follows:

select
    count(distinct coalesce(t.id, 1, d.createDate, 2, d.updateDate))
from Documents d, Two t
where (t.id = 1
    and d.createDate >= :startDate 
    and d.createDate <= :endDate)
or (t.id = 2
    and d.updateDate >= :startDate 
    and d.updateDate <= :endDate)

Not sure if count( function(x)) is supported or not.

I have more than a query like this and in some of them there is a `DATEADD` used which is SQL Server specific, but we already know how to overcome this issue. Thank you for your answer, I will evaluate this solution along with my colleagues. — Lorelorelore, Oct 11 '18 at 10:00

Refactor native query to JPQL query

1 Answers1

Linked