2023年5月15日 04:39:13go评论63阅读模式

英文:

Wrong number of total pages in returned Page

问题

以下是您提供的代码的翻译部分：

Spring boot starter parent 3.0.6

我有一个控制器，接受get参数和可分页的搜索交易卡游戏卡片的请求：

@RestController
@RequestMapping("/api/cards")
@RequiredArgsConstructor
public class CardsController {

private final BerserkHeroesCardService cardService;

@GetMapping
@ResponseBody
public Page<BerserkCardModel> getCards(CardSearchRequest searchRequest, Pageable pageable) {
    return cardService.getCards(searchRequest, pageable);
}

}


调用 `http://.../api/cards?page=1&size=12` 时，我得到了正确的结果：

```json
... 
"last": false, 
"totalPages": 228, 
"totalElements": 2726, 
"size": 12, 
...

调用 http://.../api/cards?page=2&size=12 时，我得到了意外和不正确的结果：

...  
"last": true,  
"totalPages": 2,  
"totalElements": 23,  
"size": 12, 
...

调用 http://.../api/cards?page=3&size=12 时，我得到了与前面不同的不正确的结果：

...  
"last": true,  
"totalPages": 3,  
"totalElements": 35,  
"size": 12, 
...

再次调用 http://.../api/cards?page=4&size=12 时，再次返回正确的结果：

...  
"last": false,  
"totalPages": 228,  
"totalElements": 2726,  
"size": 12, 
...

我的服务方法如下：

@Override     
public Page<BerserkCardModel> getCards(CardSearchRequest request, Pageable pageable) {
    Specification<BerserkCard> spec = Specification.where(null); // 用于调试的模拟内容         
    Page<BerserkCard> all = berserkCardRepo.findAll(spec, pageable); // findAll 返回了错误的第2和第3页的结果         
    return all.map(mapper::toModel);     
}

我尝试查找类似错误的信息，但没有找到。其他相同的服务正常工作。

更新：

我尝试了我的JPA存储库并注释了 @EntityGraph 注解，现在无论页码如何，页面请求都返回了正确的结果。

public interface BerserkCardRepo extends JpaRepository<BerserkCard, Long>, JpaSpecificationExecutor<BerserkCard> {
   @Override
    //@EntityGraph(attributePaths = {"elements"})
    Page<BerserkCard> findAll(@Nullable Specification<BerserkCard> spec, Pageable pageable);

}

另外，我注意到对于第2和第3页，Hibernate 没有生成计数查询。对于其他页面，Hibernate 会生成计数查询。

我还添加了启用实体图时的 SQL 日志记录。

服务：

@Override
public Page<BerserkCardModel> getCards(CardSearchRequest request, Pageable pageable) {
    log.info(String.format("接受可分页请求。页数 %d, 大小 %d", pageable.getPageNumber(), pageable.getPageSize()));
    Specification<BerserkCard> spec = Specification.where(null);
    Page<BerserkCard> all = berserkCardRepo
            .findAll(spec, pageable);
    log.info(String.format("找到 %d 个元素，总页数：%d", all.getTotalElements(), all.getTotalPages()));
    return all
            .map(mapper::toModel);
}

存储库：

public interface BerserkCardRepo extends JpaRepository<BerserkCard, Long>, JpaSpecificationExecutor<BerserkCard> {
    @Override
    @EntityGraph(attributePaths = {"elements"})
    Page<BerserkCard> findAll(@Nullable Specification<BerserkCard> spec, Pageable pageable);

}

实体：

@Data
@Entity
@Table(name = "berserk_cards")
public class BerserkCard {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String set;
    private String name;
    private Integer cost;

    @ElementCollection
    @CollectionTable(name = "berserk_card_elements", joinColumns = @JoinColumn(name = "card_id"))
    @Column(name = "element")
    private Set<String> elements = new HashSet<>();

    private String type;
    private String subtype;
    private Integer attack;
    private Integer health;
    private String rarity;
    private String text;
    private String painter;
    private Integer number;
}

可以看出，错误响应和正确响应之间的不同在于计数查询，错误响应情况下未生成计数查询。

您是否有关于为什么会发生这种情况的想法？


请注意，我已根据您的请求提供了代码的翻译和部分说明，没有包含其他额外的内容。如果您有进一步的问题或需要进一步的帮助，请随时提问。

<details>
<summary>英文:</summary>

Spring boot starter parent 3.0.6

I have a controller that accepts get params and pageable for searching cards for trading card game:

@RestController
@RequestMapping("/api/cards")
@RequiredArgsConstructor
public class CardsController {

private final BerserkHeroesCardService cardService;

@GetMapping
@ResponseBody
public Page&lt;BerserkCardModel&gt; getCards(CardSearchRequest searchRequest, Pageable pageable) {
    return cardService.getCards(searchRequest, pageable);
}

}




calling `http://.../api/cards?page=1&amp;size=12` I get correct result:

...
"last": false,
"totalPages": 228,
"totalElements": 2726,
"size": 12,
...


calling `http://.../api/cards?page=2&amp;size=12` I got not expected and incorrect result

...
"last": true,
"totalPages": 2,
"totalElements": 23,
"size": 12,
...


calling `http://.../api/cards?page=3&amp;size=12` I got also incorrect result that differ previous

...
"last": true,
"totalPages": 3,
"totalElements": 35,
"size": 12,
...


calling `http://.../api/cards?page=4&amp;size=12` again return correct result

...
"last": false,
"totalPages": 228,
"totalElements": 2726,
"size": 12,
...


My serviece method

@Override
public Page<BerserkCardModel> getCards(CardSearchRequest request, Pageable pageable) {
Specification<BerserkCard> spec = Specification.where(null); // mocked for debug
Page<BerserkCard> all = berserkCardRepo.findAll(spec, pageable); // findAll returned page incorrect
//result for page 2 and 3
return all.map(mapper::toModel);
}


I&#39;im trying to find info about similar bugs but nothing found. Other same services works well.


UPD

I experimented with my jpa repository and commented `@EntityGraph` annotation
and page request now return correct result regardless of page number.

public interface BerserkCardRepo extends JpaRepository<BerserkCard, Long>, JpaSpecificationExecutor<BerserkCard> {
@Override
//@EntityGraph(attributePaths = {"elements"})
Page<BerserkCard> findAll(@Nullable Specification<BerserkCard> spec, Pageable pageable);

}


UPD2

also I&#39;m noticed that for 2 and 3 pages hibernate didn&#39;t generate count query. For each other page hibernate does it.

UPD3

I added logging sql when entity graph enabled.

service:

@Override
public Page&lt;BerserkCardModel&gt; getCards(CardSearchRequest request, Pageable pageable) {
    log.info(String.format(&quot;Accept pageable. Page %d, size %d&quot;, pageable.getPageNumber(), pageable.getPageSize()));
    Specification&lt;BerserkCard&gt; spec = Specification.where(null);
    Page&lt;BerserkCard&gt; all = berserkCardRepo
            .findAll(spec, pageable);
    log.info(String.format(&quot;Found %d elements, total pages: %d&quot;, all.getTotalElements(), all.getTotalPages()));
    return all
            .map(mapper::toModel);
}


Repo:

public interface BerserkCardRepo extends JpaRepository<BerserkCard, Long>, JpaSpecificationExecutor<BerserkCard> {
@Override
@EntityGraph(attributePaths = {"elements"})
Page<BerserkCard> findAll(@Nullable Specification<BerserkCard> spec, Pageable pageable);

}


Entity:

@Data
@Entity
@Table(name = "berserk_cards")
public class BerserkCard {

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;

private String set;
private String name;
private Integer cost;

@ElementCollection
@CollectionTable(name = &quot;berserk_card_elements&quot;, joinColumns = @JoinColumn(name = &quot;card_id&quot;))
@Column(name = &quot;element&quot;)
private Set&lt;String&gt; elements = new HashSet&lt;&gt;();

private String type;
private String subtype;
private Integer attack;
private Integer health;
private String rarity;
private String text;
private String painter;
private Integer number;

}


It&#39;s ok, that page in url start has value = 1, and in logs = 0 because i have configured one-indexed-parameters: true.

Calling `http://.../api/cards?page=1&amp;size=12`

2023-05-15T10:06:51.411+03:00 INFO 21056 --- [nio-9999-exec-1] r.b.c.i.h.s.BerserkHeroesCardServiceImpl : Accept pageable. Page 0, size 12
Hibernate:
select
b1_0.id,
b1_0.attack,
b1_0.cost,
e1_0.card_id,
e1_0.element,
b1_0.health,
b1_0.name,
b1_0.number,
b1_0.painter,
b1_0.rarity,
b1_0.set,
b1_0.subtype,
b1_0.text,
b1_0.type
from
berserk_cards b1_0
left join
berserk_card_elements e1_0
on b1_0.id=e1_0.card_id offset ? rows fetch first ? rows only
Hibernate:
select
count(b1_0.id)
from
berserk_cards b1_0
2023-05-15T10:06:51.472+03:00 INFO 21056 --- [nio-9999-exec-1] r.b.c.i.h.s.BerserkHeroesCardServiceImpl : Found 2726 elements, total pages: 228


Calling `http://.../api/cards?page=2&amp;size=12`

2023-05-15T10:06:53.277+03:00 INFO 21056 --- [nio-9999-exec-5] r.b.c.i.h.s.BerserkHeroesCardServiceImpl : Accept pageable. Page 1, size 12
Hibernate:
select
b1_0.id,
b1_0.attack,
b1_0.cost,
e1_0.card_id,
e1_0.element,
b1_0.health,
b1_0.name,
b1_0.number,
b1_0.painter,
b1_0.rarity,
b1_0.set,
b1_0.subtype,
b1_0.text,
b1_0.type
from
berserk_cards b1_0
left join
berserk_card_elements e1_0
on b1_0.id=e1_0.card_id offset ? rows fetch first ? rows only
2023-05-15T10:06:53.300+03:00 INFO 21056 --- [nio-9999-exec-5] r.b.c.i.h.s.BerserkHeroesCardServiceImpl : Found 23 elements, total pages: 2


Calling `http://.../api/cards?page=3&amp;size=12`

2023-05-15T10:06:54.272+03:00 INFO 21056 --- [nio-9999-exec-2] r.b.c.i.h.s.BerserkHeroesCardServiceImpl : Accept pageable. Page 2, size 12
Hibernate:
select
b1_0.id,
b1_0.attack,
b1_0.cost,
e1_0.card_id,
e1_0.element,
b1_0.health,
b1_0.name,
b1_0.number,
b1_0.painter,
b1_0.rarity,
b1_0.set,
b1_0.subtype,
b1_0.text,
b1_0.type
from
berserk_cards b1_0
left join
berserk_card_elements e1_0
on b1_0.id=e1_0.card_id offset ? rows fetch first ? rows only
2023-05-15T10:06:54.299+03:00 INFO 21056 --- [nio-9999-exec-2] r.b.c.i.h.s.BerserkHeroesCardServiceImpl : Found 35 elements, total pages: 3


Calling `http://.../api/cards?page=4&amp;size=12`

2023-05-15T10:06:55.521+03:00 INFO 21056 --- [nio-9999-exec-9] r.b.c.i.h.s.BerserkHeroesCardServiceImpl : Accept pageable. Page 3, size 12
Hibernate:
select
b1_0.id,
b1_0.attack,
b1_0.cost,
e1_0.card_id,
e1_0.element,
b1_0.health,
b1_0.name,
b1_0.number,
b1_0.painter,
b1_0.rarity,
b1_0.set,
b1_0.subtype,
b1_0.text,
b1_0.type
from
berserk_cards b1_0
left join
berserk_card_elements e1_0
on b1_0.id=e1_0.card_id offset ? rows fetch first ? rows only
Hibernate:
select
count(b1_0.id)
from
berserk_cards b1_0
2023-05-15T10:06:55.561+03:00 INFO 21056 --- [nio-9999-exec-9] r.b.c.i.h.s.BerserkHeroesCardServiceImpl : Found 2726 elements, total pages: 228


and now i commented entity graph annotation

public interface BerserkCardRepo extends JpaRepository<BerserkCard, Long>, JpaSpecificationExecutor<BerserkCard> {

@Override
//@EntityGraph(attributePaths = {&quot;elements&quot;})
Page&lt;BerserkCard&gt; findAll(@Nullable Specification&lt;BerserkCard&gt; spec, Pageable pageable);

}


Calling `http://.../api/cards?page=1&amp;size=12`

2023-05-15T10:14:26.704+03:00 INFO 4036 --- [nio-9999-exec-4] r.b.c.i.h.s.BerserkHeroesCardServiceImpl : Accept pageable. Page 0, size 12
Hibernate:
select
b1_0.id,
b1_0.attack,
b1_0.cost,
b1_0.health,
b1_0.name,
b1_0.number,
b1_0.painter,
b1_0.rarity,
b1_0.set,
b1_0.subtype,
b1_0.text,
b1_0.type
from
berserk_cards b1_0 offset ? rows fetch first ? rows only
Hibernate:
select
count(b1_0.id)
from
berserk_cards b1_0
2023-05-15T10:14:26.742+03:00 INFO 4036 --- [nio-9999-exec-4] r.b.c.i.h.s.BerserkHeroesCardServiceImpl : Found 2726 elements, total pages: 228


Calling `http://.../api/cards?page=2&amp;size=12`

2023-05-15T10:15:47.211+03:00 INFO 4036 --- [nio-9999-exec-3] r.b.c.i.h.s.BerserkHeroesCardServiceImpl : Accept pageable. Page 1, size 12
Hibernate:
select
b1_0.id,
b1_0.attack,
b1_0.cost,
b1_0.health,
b1_0.name,
b1_0.number,
b1_0.painter,
b1_0.rarity,
b1_0.set,
b1_0.subtype,
b1_0.text,
b1_0.type
from
berserk_cards b1_0 offset ? rows fetch first ? rows only
Hibernate:
select
count(b1_0.id)
from
berserk_cards b1_0
2023-05-15T10:15:47.250+03:00 INFO 4036 --- [nio-9999-exec-3] r.b.c.i.h.s.BerserkHeroesCardServiceImpl : Found 2726 elements, total pages: 228


Calling `http://.../api/cards?page=3&amp;size=12`(the same for all other pages)

2023-05-15T10:17:03.992+03:00 INFO 4036 --- [io-9999-exec-10] r.b.c.i.h.s.BerserkHeroesCardServiceImpl : Accept pageable. Page 2, size 12
Hibernate:
select
b1_0.id,
b1_0.attack,
b1_0.cost,
b1_0.health,
b1_0.name,
b1_0.number,
b1_0.painter,
b1_0.rarity,
b1_0.set,
b1_0.subtype,
b1_0.text,
b1_0.type
from
berserk_cards b1_0 offset ? rows fetch first ? rows only
Hibernate:
select
count(b1_0.id)
from
berserk_cards b1_0
2023-05-15T10:17:04.029+03:00 INFO 4036 --- [io-9999-exec-10] r.b.c.i.h.s.BerserkHeroesCardServiceImpl : Found 2726 elements, total pages: 228


As You can see, the differ in logs between wrong response and correct response is count query, which is not generated in incorrect response case.

Any have idea why is this happening?

</details>


# 答案1
**得分**: 0

`elements`集合中存在问题。
如果查看日志中的SQL，我们发现跳过了?行并提取了?行。
例如，结果集中有一张带有两个元素的卡片，另一张带有1个元素的卡片。这意味着结果集中有11个唯一实体。
当Spring将结果集解析为实体时，它发现实体数少于页面大小，认为这是最后一页，因此不调用计数查询。

<details>
<summary>英文:</summary>

Problem in eager collection `elements`.
If we see at sql in logs

Hibernate:
select
b1_0.id,
b1_0.attack,
b1_0.cost,
e1_0.card_id,
e1_0.element,
b1_0.health,
b1_0.name,
b1_0.number,
b1_0.painter,
b1_0.rarity,
b1_0.set,
b1_0.subtype,
b1_0.text,
b1_0.type
from
berserk_cards b1_0
left join
berserk_card_elements e1_0
on b1_0.id=e1_0.card_id offset ? rows fetch first ? rows only


We found that we skip ? rows and fetch ? rows. If there are two or more elements card has, we will found less unique entities in result set. 

For example, in resultSet we have one card with two elements, and other with 1 elements. It means in resultSet we have 11 unique entities. 

When spring parse resultSet to entities it found, that there are 11 entities it found meanwhile page size is 12. Spring saw that founded count of entities less than page size and it thinks that it&#39;s last page and don&#39;t call count query.

</details>

通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。

Wrong number of total pages in returned Page

问题

Upsource – PKIX路径SSL证书问题

从Spring验证器检查重复项后，bindingResult返回null吗？

如何在OkHttp的RequestBody中传递JSON对象以调用外部API。

如何确定一个字符串是否不是数组的一部分并计算出现次数？

What's the correct way to type hint an empty list as a literal in python?

如何在Highcharts Gantt中更改本地化的星期名称

如何在同一个流中使用多个过滤器和映射函数？

如何使用Map/Set来将代码优化到O(n)？

.NET MAUI Android在GitHub Actions上构建失败，错误代码为1。

如何在Playwright视觉比较中屏蔽多个定位器？

在C++中，可以使用可变模板参数来检索类型的内部类型。

selenium.common.exceptions.StaleElementReferenceException: Message: stale element reference: stale element not found

Creating and opening a URL to log in to Website via Basic Auth with Robot Framework/Selenium (Python)

AG Grid 在上下文菜单中以大文本形式打开

发表评论