我们如何通过 JPA 获取完整的分层数据列表

Question

作为我们的数据端要求，有一个像下面的 mysql 表结构：

id  category_id category_name   parent_id   delete_status
3  145         Lip Makeup      123           0
2  134         Face Makeup     123           0
1  123         Makeup          0             0

数据结构就像树一样，id = 1 的重新编码是其他 2 条记录的父项(ID = 2 和 id = 3(。连接列为parent_id，映射DTO为：

@Entity
@Data
@Table(name = "category_tab")
public class CategoryDto {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id", unique = true, nullable = false, length = 20)
private long id;
@Column(name = "category_id", nullable = false)
Long categoryId;
@Column(name = "category_name", nullable = false)
String name;
@Column(name = "parent_id", nullable = false)
Long parentId;
@Column(name = "delete_status", nullable = false)
Integer deleteStatus;
@OneToMany(fetch = FetchType.LAZY)
@JoinColumn(name = "parent_id", insertable = false, updatable = false)
private Set<CategoryDto> children = new HashSet<>();
@Override
public boolean equals(Object o) {
if (this == o) return true;
if (o == null || getClass() != o.getClass()) return false;
ShopeeCategoryDto that = (ShopeeCategoryDto) o;
return Objects.equals(categoryId, that.categoryId) &&
Objects.equals(name, that.name);
}
@Override
public int hashCode() {
return Objects.hash(categoryId, name);
}}

现在我想获得包含其子列表的 CategoryDto 的完整列表，因为它随后可以轻松处理。 如道英普：

List<CategoryDto> fetchAll();

那么我应该如何实现这种方法呢？我使用了弹簧启动 + Jpa 框架

Answer 1

@Query("select category from CategoryDto category where category.deleteStatus != ?1")
CategoryDto findAllBydeleteStatus(Integer deleteStatus);

文档 ： https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.query-methods.query-creation