将本机查询重构为JPQL查询 [英] Refactor native query to JPQL query

问题描述

假设我们有一个名为文档"的下表:

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

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

两个* date列是时间戳，而另一个都是字符串(甚至是id)

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

目前，我在Spring存储库中使用以下本机查询:

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

如您所见，这必须用作本机查询，因为JPA不支持from子句中的select查询.现在，我必须将此查询转换为JPQL查询，以使其独立于数据库.这有可能吗? 欢迎使用其他方法，例如使用规范"或类似方法...

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

推荐答案

您声明自己

希望有一个独立于数据库的查询.

want to have a database independent query.

我确实认为您可能已经有一个.

I do think you might already have one.

SQL语句是一个相当简单的语句，虽然我不假装不了解那里的每个SQL方言，但我希望它可以与许多数据库一起使用，很有可能与所有相关的数据库一起工作.

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.

如果您坚持使用JPQL，则可能会起作用:

If you insist to use JPQL the following might work:

添加仅具有一列(id)和两个条目的实体/表Two:1和2.

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)

不确定是否支持count( function(x)) .

这篇关于将本机查询重构为JPQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！