java - Spring Data JPA : Sorting and paging with joined tables

我有一个场景，我想对 3 个表参与的结果进行过滤、排序和分页。

目前，我使用 Spring Data JPA 的规范功能在单个实体上执行此操作: repository.findAll(specification, pageRequest) 。

这很好用，但现在我有另一个场景，其中排序/过滤器属性分布在 3 个由一对多关系连接的表中。

这是我的场景:

@Entity
public class CustomerEntity ... {
  ...

  @Column(nullable = false)
  public String                                 customerNumber;

  @OneToMany(mappedBy = "customer", cascade = CascadeType.ALL, orphanRemoval = true)
  public List<CustomerItemEntity> items;
}


@Entity
public class CustomerItemEntity ... {
  ...

  @Column(nullable = false)
  public String                                 itemNumber;

  @ManyToOne(optional = false)
  @JoinColumn(name = "customerId")
  public CustomerEntity customer;

  @OneToMany(mappedBy = "item", cascade = CascadeType.ALL, orphanRemoval = true)
  public List<DocumentEntity> documents;
}


@Entity
public class DocumentEntity ... {
  ...

  @Column(nullable = false)
  public LocalDate                                 validDate;

  @ManyToOne(optional = false)
  @JoinColumn(name = "itemId")
  public CustomerItemEntity item;
}

有没有办法使用 PageRequest 和 Specification ，其中 customerNumber 、 itemNumber 和 validDate 同时用于过滤、排序和分页？

最佳答案

尝试这样的事情:

Specification<CustomerEntity> joins = (customer, query, cb) ->  {
    // from CustomerEntity c
    // join c.items i
    Join<CustomerEntity, CustomerItemEntity> items = customer.join("items");

    // join i.documents d
    Join<CustomerItemEntity, DocumentEntity> documents = items.join("documents");

    // // where c.customerNumber = ?1 and i.itemNumber = ?2 and d.validDate = ?3
    return cb.and(
            customer.equal(customer.get("customerNumber", customerNumber)),
            items.equal(items.get("itemNumber", itemNumber)),
            documents.equal(documents.get("validDate", validDate))
    );
};

// sort by c.customerNumber asc
PageRequest pageRequest = new PageRequest(0, 2, new Sort(Sort.Direction.ASC, "customerNumber"));

Page<CustomerEntity> customerPage = CustomerRepo.findAll(joins, pageRequest);

但是我不知道为什么这里需要Specification？

您可以使相同的更简单:

@Query("select c from CustomerEntity c join c.items i join i.documents d where c.customerNumber = ?1 and i.itemNumber = ?2 and d.validDate = ?3")
Page<CustomerEntity> getCustomers(String customerNumber, String itemNumber, LocaleDate validDate, Pageable pageable);

但这一切都没有意义，因为您的三个实体具有连续的一对多关联。在这种情况下，您只能使用最后一个而不是三个条件: where d.validDate = ?1 。然后查询方法变得更加简单:

@Query("select c from CustomerEntity c join c.items i join i.documents d where d.validDate = ?1")
Page<CustomerEntity> getCustomers(LocaleDate validDate, Pageable pageable);

更新

要按连接实体的字段添加排序，我们可以使用 orderBy 的 query 方法:

Specification<CustomerEntity> joins = (customer, query, cb) ->  {

    Join<CustomerEntity, CustomerItemEntity> items = customer.join("items");
    Join<CustomerItemEntity, DocumentEntity> documents = items.join("documents");

    // Ascending order by 'Document.itemNumber'
    query.orderBy(cb.asc(documents.get("itemNumber")));

    return cb.and(
            customer.equal(customer.get("customerNumber", customerNumber)),
            items.equal(items.get("itemNumber", itemNumber)),
            documents.equal(documents.get("validDate", validDate))
    );
};

Page<CustomerEntity> customerPage = CustomerRepo.findAll(joins, new PageRequest(0, 2));

要按多个参数排序，您可以将它们传递给以逗号或 List 分隔的方法:

query.orderBy(cb.asc(items.get("customerNumber")), cb.desc(documents.get("itemNumber")));

关于java - Spring Data JPA : Sorting and paging with joined tables，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/45738336/