java - 如何从 Spring Boot JPA 中的列实体进行分页存储库调用

Question

我有这个实体模型类（书），作者可以写多本书。

@Entity
@Table(name="book")
public class Book {
 
    @Id
    @GeneratedValue
    private Long id;
     
    @Column(name="book_name")
    private String bookName;
     
    @Column(name="author_id")
    private Long authorId;
     
    //Setters and getters
}

在我的 Spring Boot 项目中，我不想有一个作者表，因为有一个定义作者及其 id 的第三方服务，我怎样才能对所有 authorId 和他们的书进行分页存储库调用？

我想要一个接收（页面，大小）并返回 AuthorDTO 的分页列表的端点，如下所示：

public abstract class AuthorDTO implements Serializable {
    public abstract Long authorId();

    public abstract List<Book> books();
}

[
    {
        "authorId": 123,
        "books": [...]
    },
    ...

]

我的第一个想法是创建一个存储库调用，不确定如何获取自定义对象的页面。这在下面无效，但我想做类似下面的事情。

Page<AuthorDTO> findAllBooksGroupedByAuthorId(Pageable pageable);

标签： javaspring-bootjpapaginationspring-repositories

Answer 1

您的代码似乎表明您正在尝试将类中的外键关系显示为 id。JPA 并没有真正做到这一点。JPA = "Java Persistence Language" 即您表示反映数据库的 Java 类之间的关系。

因此，在数据库中，您可能在 book 表中有一个像“author_id”这样的外键，但在 JPA/Java 方面，它将是一个“作者”类，而不仅仅是一个 long/int。

我希望下面的帮助。我只是将它放在我的代码的 main() 上，所以它可能并不完美，但我也留下了一些评论。

一旦你有了一个Page<Book>，你可能想把它映射到java中的DTO。

由于查询是“按作者 ID 获取书籍”，我们可以假设它们都具有相同的作者 ID……所以没有真正需要尝试在数据库中获取此投影。

编辑：是否根本不可能从第 3 方引用作者？

即我不知道你是如何填充“书”的......但是当你从第 3 方获得“书”时，你能不能看看你是否有一个带有书 'author_id' 的作者实体，而不是坚持一个新的“作者" 如果它不存在，则使用该 ID？

在这种情况下，您可以执行 AuthorRepo 并简单地查询如下：

Page<Author> findAllBy(Pageable page)

==================================================== =========================

好像您正在通过作者 ID 获取一页书...您确实应该具有 JPA 关系以表明：

    @Entity
    private class Book{

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

        @Column(name = "book_name")
        private String name;

        //Technically this could be Many:Many as a book could have 2 authors? If so....@ManyToMany
        //For simplicity (and what you seem to want) Many Books have ONE author.
        @ManyToOne(fetch = FetchType.LAZY)
        private Author author;

    }

    @Entity
    private class Author{

        //ID here - omitted for clarity

        @Column(name = "authors_name")
        String name;

        //The Author has many books.
        // Mapped by shows the bi-direction relationship. You can then do 'Author.getAuthorsBooks()'
        //Lazy means it wont fetch all the books from database/(hibernate wont) when you do AuthorRepo.get()
        //and will only do the `JOIN ON Books where` if you do Author.getAuthorsBooks()
        @OneToMany(fetch = FetchType.LAZY,mappedBy = "author")
        private Set<Book> authorsBooks = new HashSet<>();
    }

    private interface AuthorRepo extends JpaRepository<Author,Long>{
        //Note the JPA syntax.
        Page<Book> findAll(Pageable pageable);
    }

编辑： 我只把它写在一个空文件中......所以它可能需要调整或有错别字等

如果由于某种原因您不能为作者提供单独的实体，则必须保持您的实体目前的状态......我会做 2 次查询。

我觉得你可以通过各种方式做到这一点。

如果你必须坚持使用 spring 的 Pageable：

在控制器中获取页面请求并将其放入新的PageRequest.of(pagenum,size)

并将其输入以执行下面的页面查询

List<Long> getPageOfUniqueAuthorIds(Pageable pageable);

这将给出作者 ID 的页面。

然后，您想使用该 Longs 列表 (aithorIds) 来执行第二个查询。

List<AuthorDTOProjection> getBooksAndAuthorIdsWithAuthorsIdsIn(List<Long> authorIds);

    @Entity
    @Table(name="book")
    public class Book {

        @Id
        @GeneratedValue
        private Long id;

        @Column(name="book_name")
        private String bookName;

        @Column(name="author_id")
        private Long authorId;

        //Setters and getters
    }



    private interface BookRepo extends JpaRepository<Book,Long> {

        //The countQuery is required by Spring Paging.
        //Hibernate will need to use the count query when doing paging on a native query.
        @Query(nativeQuery = true,
        value = "SELECT DISTINCT(author_id) FROM book b ",
        countQuery = "SELECT count(*) \n" +
                "FROM (SELECT DISTINCT(author_id) FROM book b) authorIds ")
        List<Long> getPageOfUniqueAuthorIds(Pageable pageable);

        //This is not paged. You want all books with the author IDs from the page query above.
      List<Book> findAllByAuthorIdIn(List<Long> authorIds);
    }

然后，您必须将实体映射到服务层中的 DTO。

        @Autowired
        BookRepo bookRepo;

        //This would be from the controller method...not declared here...
        Pageable pageableFromController = PageRequest.of(0,10);

        List<Long> pageOfUniqueAuthorIds = bookRepo.getPageOfUniqueAuthorIds(pageableFromController);

        //Get All the books with Author Ids.
        List<Book> books = bookRepo.findAllByAuthorIdIn(pageOfUniqueAuthorIds);

        //Your abstract AuthorDTO.
        abstract class AuthorDTO implements Serializable {
            public abstract Long authorId();

            public abstract List<Book> books();
        }

        //Your Author DTO needs to be implemented so I made a "View".
        @AllArgsConstructor
        class AuthorView extends AuthorDTO{

            private long authorId;
            private List<Book> books;

            @Override
            public Long authorId() {
                return authorId;
            }

            @Override
            public List<Book> books() {
                return books;
            }
        }

        //Get a List of the authorIds in the List<Books>. Could also use the original Page<Long> authorIds...
        //As an author without a book is not possible in your database.
        final List<Long> authorIdsInBooks = books.stream().map(it -> it.authorId).distinct().collect(Collectors.toList());

        //Map the Ids of authors to an Impl of your abstract DTO. Personally I don't see why the AuthorDTO is abstract.
        //I'd have expected just an abstract DTO class called "DTO" or something and then AuthorDTO impl that.
        //But as the way you have it this will work. I guess you may want more impl of the  AuthorDTO so maybe leave the AuthorDTO as abstract.
        //This can be returned to client.
        final List<AuthorView> authorViews = authorIdsInBooks.stream()
                .map(authorId -> new AuthorView(
                        authorId,
                        books.stream().filter(it -> it.authorId.equals(authorId)).collect(Collectors.toList()))
                )
                .collect(Collectors.toList());