如何用nativeQuery和Spring PagingAndSortingRepository在JPA中分页查询
由于JPA不接受JOIN中的子查询，我们必须以nativeQuery的形式进行，但是这样做时，我们在分页方面遇到了问题，因为JPA没有将其与本地查询结合起来。https://docs.spring.io/spring-data/jpa/docs/1.8.0.m1/reference/html/
本机查询@query注释允许通过将nativeQuery标志设置为true来执行本机查询。请注意，我们目前不支持对本机查询执行分页或动态排序，因为我们必须操作声明的实际查询，而且我们不能对本机SQL可靠地这样做。
我们不知道如何继续下去。
+--------------+---------------------+-------------+---------------+
| phone_number | registered | name | first_surname |
+--------------+---------------------+-------------+---------------+
| 222005001 | 2019-05-10 10:01:01 | Alvaro | Garcia |
| 222004001 | 2019-05-13 16:14:21 | David | Garcia |
| 111003001 | 2019-05-13 16:14:43 | Roberto | Martin |
| 111001000 | 2019-05-13 16:14:50 | Juan Manuel | Martin |
| 111001000 | 2019-05-13 16:14:50 | Maria | Alonso |
| 111001000 | 2019-05-13 16:14:50 | Roberto | Martin |
| 333006001 | 2019-05-13 16:14:55 | Benito | Lopera |
| 123456789 | 2019-05-13 16:15:00 | NULL | NULL |
| 987654321 | 2019-05-13 16:15:08 | NULL | NULL |
| 123456789 | 2019-05-13 16:15:13 | NULL | NULL |
| 666999666 | 2019-05-13 16:15:18 | NULL | NULL |
| 454545458 | 2019-05-13 16:15:27 | NULL | NULL |
| 333006001 | 2019-05-13 16:23:36 | Benito | Lopera |
| 987654321 | 2019-05-13 16:23:46 | NULL | NULL |
| 666999666 | 2019-05-13 16:23:50 | NULL | NULL |
| 454545458 | 2019-05-13 16:23:55 | NULL | NULL |
| 666999666 | 2019-05-13 16:24:03 | NULL | NULL |
| 222004001 | 2019-05-13 16:24:10 | David | Garcia |
+--------------+---------------------+-------------+---------------+
+--------------+---------------------+-------------+---------------+
| phone_number | registered | name | first_surname |
+--------------+---------------------+-------------+---------------+
| 222004001 | 2019-05-13 16:24:10 | David | Garcia |
| 222004001 | 2019-05-13 16:14:21 | David | Garcia |
| 666999666 | 2019-05-13 16:24:03 | NULL | NULL |
| 666999666 | 2019-05-13 16:23:50 | NULL | NULL |
| 666999666 | 2019-05-13 16:15:18 | NULL | NULL |
| 454545458 | 2019-05-13 16:23:55 | NULL | NULL |
| 454545458 | 2019-05-13 16:15:27 | NULL | NULL |
| 987654321 | 2019-05-13 16:23:46 | NULL | NULL |
| 987654321 | 2019-05-13 16:15:08 | NULL | NULL |
| 333006001 | 2019-05-13 16:23:36 | Benito | Lopera |
| 333006001 | 2019-05-13 16:14:55 | Benito | Lopera |
| 123456789 | 2019-05-13 16:15:13 | NULL | NULL |
| 123456789 | 2019-05-13 16:15:00 | NULL | NULL |
| 111001000 | 2019-05-13 16:14:50 | Maria | Alonso |
| 111001000 | 2019-05-13 16:14:50 | Roberto | Martin |
| 111001000 | 2019-05-13 16:14:50 | Juan Manuel | Martin |
| 111003001 | 2019-05-13 16:14:43 | Roberto | Martin |
| 222005001 | 2019-05-10 10:01:01 | Alvaro | Garcia |
+--------------+---------------------+-------------+---------------+
可以通过以下查询完成：
SELECT c.phone_number, c.registered, cl.name, cl.first_surname
FROM callers cl
INNER JOIN callers_phones cp ON cl.caller_id = cp.caller_id
RIGHT OUTER JOIN calls c ON c.phone_number = cp.phone_number
JOIN (
SELECT phone_number, MAX(registered) AS registered
FROM calls
GROUP BY phone_number) aux_c ON aux_c.phone_number = c.phone_number
WHERE c.answered = FALSE
AND (null is null or null is null or c.registered between null and null)
AND (null is null or c.phone_number = null)
AND (null is null or cl.caller_id = null)
ORDER BY aux_c.registered DESC, c.registered DESC
以下是表格：
CREATE TABLE callers
(
caller_id int NOT NULL UNIQUE AUTO_INCREMENT,
name varchar(50) NOT NULL,
first_surname varchar(50) NOT NULL,
CONSTRAINT callers_pkey PRIMARY KEY (caller_id)
);
CREATE TABLE callers_phones
(
phone_id int NOT NULL UNIQUE AUTO_INCREMENT,
caller_id int NOT NULL,
phone_number int NOT NULL,
CONSTRAINT callers_phones_pkey PRIMARY KEY (phone_id)
);
ALTER TABLE callers_phones
ADD CONSTRAINT callers_phones_fkey_callers FOREIGN KEY (caller_id)
REFERENCES callers (caller_id);
CREATE TABLE calls
(
call_id int NOT NULL UNIQUE AUTO_INCREMENT,
phone_number int NOT NULL,
answered boolean NOT NULL DEFAULT false,
registered datetime NOT NULL,
CONSTRAINT calls_pkey PRIMARY KEY (call_id)
);
问题是，我们必须在JPA中用分页实现它，但子查询在JOIN子句中不起作用，分页在NativeQuery中也不起作用。
import java.util.Date;
import javax.persistence.Entity;
import javax.persistence.EntityResult;
import javax.persistence.FieldResult;
import javax.persistence.Id;
import javax.persistence.NamedNativeQuery;
import javax.persistence.SqlResultSetMapping;
@SqlResultSetMapping (name = "MissedCallResult",
entities = {
@EntityResult (entityClass = MissedCallEntity.class,
fields = {
@FieldResult (name = "callId", column = "id"),
@FieldResult (name = "phoneNumber", column = "pH"),
@FieldResult (name = "registered", column = "reg"),
@FieldResult (name = "callerName", column = "cN"),
@FieldResult (name = "callerFirstSurname", column = "cFS")
})
})
@NamedNativeQuery (name = "findMissedCalls",
query = "select c.call_id as id, c.phone_number as pH, c.registered as reg, cl.name as cN, cl.first_surname as cFS "
+ "from callers cl "
+ " inner join callers_phones cp on cl.caller_id = cp.caller_id "
+ " right outer join calls c on c.phone_number = cp.phone_number "
+ " join (select c2.phone_number, MAX(c2.registered) as registered "
+ " from calls c2 "
+ " group by c2.phone_number) aux_c on aux_c.phone_number = c.phone_number "
+ "where c.answered = false "
+ " and (:startDate is null or :endDate is null or c.registered between :startDate and :endDate) "
+ " and (:callerId is null or cl.caller_id = :callerId) "
+ " and (:phoneNumber is null or c.phone_number = :phoneNumber) "
+ "order by aux_c.registered desc, c.registered desc",
resultSetMapping = "MissedCallResult")
@Entity
public class MissedCallEntity
{
@Id
private Integer callId;
private Integer phoneNumber;
private Date registered;
private String callerName;
private String callerFirstSurname;
private String callerSecondSurname;
...
}
import java.util.Date;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.data.rest.core.annotation.RepositoryRestResource;
import es.panel.domain.MissedCallEntity;
@RepositoryRestResource (path = "missedCalls", collectionResourceRel = "missedCalls")
public interface MissedCallRepository extends PagingAndSortingRepository<MissedCallEntity, Integer>
{
@Query (nativeQuery = true, name = "findMissedCalls")
Page<MissedCallEntity> findMissedCalls(@Param ("startDate") Date startDate,
@Param ("endDate") Date endDate,
@Param ("callerId") Integer callerId,
@Param ("phoneNumber") Integer phoneNumber,
Pageable page);
}
public Page<MissedCallEntity> getMissedCalls(Date startDate,
Date endDate,
Integer callerId,
Integer phoneNumber,
int actualPage,
int limit)
{
Page<MissedCallEntity> calls = mcRepository.findMissedCalls(
startDate, endDate, callerId, phoneNumber, PageRequest.of(1, 5));
return calls;
}
提前感谢！
共有1个答案
一个非常简单的解决方案是根据您对计算值的查询创建数据库视图，即计数和最大值等。
您可以使用JPA@secondarytable注释将其映射到相关实体，该注释允许您将一个实体映射到一个以上的表（或视图）。
有了is place，您可以使用标准的JPA/Spring数据功能进行排序和筛选，就像任何其他字段一样，并且可以删除您编写的所有代码。
-
QueryDSL定义了一个接口，通过调用或可以轻松地为任何字段获取该接口的实例。Spring Data JPA的接口甚至有一个方法，它将作为参数。 但是对QueryDSL一无所知，它有自己定义查询排序顺序的方法，即。它可以包含许多，它们与非常相似，只是它们不是类型安全的。 所以，如果我想做使用排序的分页查询，真的没有办法使用QueryDSL来定义它吗？
-
问题内容： 我正在使用Spring Data JPA，当我用来定义一个 WITHOUT 的查询时，它可以工作： 但是，如果我添加第二个参数，则将无法正常工作，Spring将解析该方法的名称，然后抛出 异常 。这是错误吗？ 问题答案： 在Spring论坛上提出了一个类似的问题，指出要应用分页，必须派生第二个子查询。因为子查询引用的是相同的字段，所以您需要确保查询对引用的实体/表使用别名。这意味着您在
-
我在mySql中有一个查询，我想在我的控制器中编写。 原因：java.lang.IllegalArgumentException：org.hibernate.hql.internal.ast.QuerySyntaxException：应为CLOSE，在org.hibernate.internal.exceptionConverterImpl.convert（exceptionConverterIm
-
问题内容： 因此，我想在Oracle数据库中选择一系列行。我需要这样做是因为表中有数百万行，并且我想将结果分页给用户（如果您知道另一种在客户端执行此操作的方法，那么我正在使用JavaFX，但我没有认为通过网络发送所有数据以在客户端对它们进行分页是个好主意）。 我有以下查询： 该和只是例子。在应用程序中，我只是要求下限并添加10_000的大小以获取接下来的10_000行。 现在rownum列出现在结
-
所以我想在Oracle DB中选择一系列行。我需要这样做，因为我在表中有数百万行，我想将结果分页给用户（如果您知道在客户端执行此操作的另一种方法，如果重要的话，我正在使用JavaFX，但我认为通过网络发送所有数据以在客户端分页它们不是一个好主意）。 所以读完这篇文章：SQLROWNUM如何在特定范围之间返回行，我有以下疑问： 和就是一个例子。在应用程序中，我只需要求下限，然后添加一个10\u 00
-
为了简化这个问题，我们有一个类/餐桌酒（餐桌“wines”），除其他属性外，它具有： null 我试图在我的存储库中创建一个搜索方法，以供RestController使用。 RestController中的方法声明如下所示： 我现在要做的是：为数据库创建一个查询，如果给出了searchTerm，就使用该数据库，与Origin相同。并且应该是可分页的。示例： 这（除了超级丑，特别是对于更多属性）很可