Spring Data and Native Query with pagination

从查询和计数查询中删除 n # 可分页 n 对我来说都很有效。 Springboot 版本: 2.1.5. 发布 DB: Mysql

这在 Groovy 中对我很有用(我正在使用 Postgres) :

@RestResource(path="namespaceAndNameAndRawStateContainsMostRecentVersion", rel="namespaceAndNameAndRawStateContainsMostRecentVersion")
@Query(nativeQuery=true,
countQuery="""
SELECT COUNT(1)
FROM
(
SELECT
ROW_NUMBER() OVER (
PARTITION BY name, provider_id, state
ORDER BY version DESC) version_partition,
*
FROM mydb.mytable
WHERE
(name ILIKE ('%' || :name || '%') OR (:name = '')) AND
(namespace ILIKE ('%' || :namespace || '%') OR (:namespace = '')) AND
(state = :state OR (:state = ''))
) t
WHERE version_partition = 1
""",
value="""
SELECT id, version, state, name, internal_name, namespace, provider_id, config, create_date, update_date
FROM
(
SELECT
ROW_NUMBER() OVER (
PARTITION BY name, provider_id, state
ORDER BY version DESC) version_partition,
*
FROM mydb.mytable
WHERE
(name ILIKE ('%' || :name || '%') OR (:name = '')) AND
(namespace ILIKE ('%' || :namespace || '%') OR (:namespace = '')) AND
(state = :state OR (:state = ''))
) t
WHERE version_partition = 1
/*#{#pageable}*/
""")
public Page<Entity> findByNamespaceContainsAndNameContainsAndRawStateContainsMostRecentVersion(@Param("namespace")String namespace, @Param("name")String name, @Param("state")String state, Pageable pageable)

这里的关键是使用: /*#{#pageable}*/

它允许我进行排序和分页。 你可以用这样的东西来测试它: < a href = “ http://localhost: 8080/api/v1/tity/search/namepaceAndNameAndRawStateContainsMostRecentVersion? nampace = & amp; name = & amp; state = publications & amp; page = 0 & amp; size = 3 & amp; sort = name，desc”rel = “ nofollow noReferrer”> http://localhost:8080/api/v1/entities/search/namespaceandnameandrawstatecontainsmostrecentversion?namespace=&name=&state=published&page=0&size=3&sort=name,desc

留意这个问题: SpringPageable 不翻译@Column 名称

您可以使用下面的 h2和 MySQL 代码

    @Query(value = "SELECT req.CREATED_AT createdAt, req.CREATED_BY createdBy,req.APP_ID appId,req.NOTE_ID noteId,req.MODEL model FROM SUMBITED_REQUESTS  req inner join NOTE note where req.NOTE_ID=note.ID and note.CREATED_BY= :userId "
,
countQuery = "SELECT count(*) FROM SUMBITED_REQUESTS req inner join NOTE note WHERE req.NOTE_ID=note.ID and note.CREATED_BY=:userId",
nativeQuery = true)
Page<UserRequestsDataMapper> getAllRequestForCreator(@Param("userId") String userId,Pageable pageable);

我可以成功地将 Pagination 集成到

Spring-data-jpa-2.1.6

如下所示。

@Query(
value = “SELECT * FROM Users”,
countQuery = “SELECT count(*) FROM Users”,
nativeQuery = true)
Page<User> findAllUsersWithPagination(Pageable pageable);

您可以通过使用以下代码来实现它,

@Query(value = "SELECT * FROM users u WHERE  ORDER BY ?#{#pageable}", nativeQuery = true)
List<User> getUsers(String name, Pageable pageable);

Simply use 订购人 and pass page request to your method.

好好享受吧！

我添加这个答案只是作为那些使用最新版本的 SpringBoot 的用户的占位符。在 SpringBoot2.4.3中，我发现没有必要使用任何变通方法，下面的代码对我来说可以直接开箱即用:

public interface UserRepository extends JpaRepository<User, Long> {
@Query(value="SELECT * FROM USERS WHERE LASTNAME = ?1", nativeQuery=true)
Page<User> findByLastname(String lastname, Pageable pageable);
}

不需要 countQuery定义，实际上对 Page#getTotalElements()的调用已经返回了正确的计数，这是由 JPA 自己的内部计数查询返回的。

上面的代码非常强大，提供了通过本机查询进行的分页，但是它将结果返回到实际的 Java 实体(而不是丑陋而笨重的 List<Object[]>，有时这是必要的)。

• Create your custom repository:

public interface ProductsCustomRepository extends JpaRepository<ProductResultEntity, Long> {
@Query(
value = "select tableA.id, tableB.bank_name from tableA join tableB on tableA.id = tableB.a_id where tableA.id = :id
and (:fieldX is null or tableA.fieldX LIKE :fieldX)",
countQuery = "select count(*) from tableA join tableB on tableA.id = tableB.a_id where tableA.id = :id
and (:fieldX is null or tableA.fieldX LIKE :fieldX)",
nativeQuery = true
)
Page<ProductResultEntity> search(@Param("id") Long aId,
@Param("fieldX") String keyword, Pageable pageable
);
}

• 创建查询视图:

create view zzz as select * from tableA join tableB on tableA.id = tableB.a_id

• 从该 滋视图生成 ProductResultEntity (完成后删除视图 zzz)
• 测试呼叫功能，并享受它:

productsRepository.search("123", "%BANK%", PageRequest.of(0, 5, Sort.by(Sort.Direction.ASC, "id")));

• 实体:

@Entity
public class ProductResultEntity {
private Long id;
private String bank;


@Id
@Column(name = "id", nullable = false)
public long getId() {
return id;
}


public void setId(long id) {
this.id = id;
}


@Column(name = "bank_name", nullable = false)
public String getBank() {
return bank;
}


public void setBank(String bank) {
this.bank = bank;
}
}

@Query(value = "select " +
//"row_number() over (order by ba.order_num asc) as id, " +
"row_number() over () as id, " +
"count(ba.order_num),sum(ba.order_qty) as sumqty, " +
"ba.order_num, " +
"md.dpp_code,md.dpp_name, " +
"from biz_arrangement ba " +
"left join mst_dpp md on ba.dpp_code = md.dpp_code " +
"where 1 = 1 " +
"AND (:#{#flilter.customerCodeListCheck}='' OR ba.customer_code IN (:#{#flilter.customerCodeList})) " +
"AND (:#{#flilter.customerNameListCheck}='' OR ba.customer_name IN (:#{#flilter.customerNameList})) " +
"group by " +
"ba.order_num, " +
"md.dpp_code,md.dpp_name ",
countQuery = "select " +
"count ( " +
"distinct ( " +
"ba.order_num, " +
"md.dpp_code,md.dpp_name) " +
")" +
"from biz_arrangement ba " +
"left join mst_dpp md on ba.dpp_code = md.dpp_code " +
"where 1 = 1 " +
"AND (:#{#flilter.customerCodeListCheck}='' OR ba.customer_code IN (:#{#flilter.customerCodeList})) " +
"AND (:#{#flilter.customerNameListCheck}='' OR ba.customer_name IN (:#{#flilter.customerNameList})) ",
nativeQuery = true)
Page<Map<String, Object>> nativeQueryDynamicPageAndSort(@Param("flilter") Flilter flilter, Pageable pageable);

不需要添加吗, 我的问题是当我使用

row_number() over (order by ba.order_num asc) as id,

输入排序不起作用 当我变成

row_number() over () as id,

动态输入排序和分页都没问题！

这是一个具有行 ID 的逐个查询分组。