Spring Data JPA中使用@ManyToMany的分页查询

Question

我有一个 Post 和 Tag 模型，它们之间有 @manytomany 关系。

Post

@Entity
public class Post {
     private long id;

     @ManyToMany(fetch = FetchType.EAGER, cascade = { CascadeType.PERSIST, CascadeType.MERGE })
     @JoinTable(joinColumns = @JoinColumn(name = "post_id"), inverseJoinColumns = @JoinColumn(name = "tag_id"))
     private Set<Tag> tags;

     ...
}

标签

@Entity
public class Tag {
     private String name;

     @ManyToMany(fetch = FetchType.LAZY, cascade = { CascadeType.PERSIST, CascadeType.MERGE }, mappedBy = "tags")
     private List<Post> posts = new ArrayList<Post>();

我想创建一个方法，通过标签名称查找所有分页的帖子。

我发现JPQL不支持LIMIT。

我是否需要使用setFirstResult().setMaxResults().getResultList()实现自己的分页逻辑？

@manytomany的分页最佳实践是什么？

我稍微编辑了一下我的问题。我编写的代码如下所示：

@SuppressWarnings("unchecked")
public Page<Post> findByTagName(String tagName, Pageable pageable) {

    long total = (long) em
            .createQuery("SELECT COUNT(p.id) FROM Post p JOIN p.tags t WHERE t.name = :tagName")
            .setParameter("tagName", tagName)
            .getSingleResult();

    List<Post> content = (List<Post>) em
            .createQuery("SELECT p FROM Post p JOIN FETCH p.tags t WHERE t.name = :tagName")
            .setParameter("tagName", tagName)
            .setFirstResult(pageable.getOffset())
            .setMaxResults(pageable.getPageSize())
            .getResultList();

    PageImpl<Post> page = new PageImpl<Post>(content, pageable, total);

    return page;
}

这段代码运行正常，但我仍然想知道是否这是正确的方式。

谢谢。

- pincoin

---

我没有看到你在哪里使用[spring-data-jpa]。 - Robert Niestroj

@RobertNiestroj 我想使用 [spring-data-jpa] 存储库接口方法声明，但它似乎不支持带有 limit 和 JOIN 的 JPQL。作为替代方案，我使用 [em] 实现了自定义方法。希望它返回可重用的 [Page] 类型。所以，我想知道我是否做得对。谢谢。 - pincoin

Answer 1

与页面和@ManyToMany映射一起工作是一项非常简单的任务。

首先，这里有与您类似的模型（基本上只添加了@Id和@GeneratedValue注解以获取生成的数据库标识符）。

帖子实体：

package com.example.model;

import java.util.HashSet;
import java.util.Set;

import javax.persistence.CascadeType;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.JoinTable;
import javax.persistence.ManyToMany;

@Entity
public class Post {

    @Id
    @GeneratedValue
    private long id;

    @ManyToMany(fetch = FetchType.EAGER, cascade = { CascadeType.PERSIST, CascadeType.MERGE })
    @JoinTable(joinColumns = @JoinColumn(name = "post_id"), inverseJoinColumns = @JoinColumn(name = "tag_id"))
    private Set<Tag> tags = new HashSet<>();

    public Set<Tag> getTags() {
        return tags;
    }

}

标签实体：

package com.example.model;

import java.util.ArrayList;
import java.util.List;

import javax.persistence.CascadeType;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.ManyToMany;

@Entity
public class Tag {

    @Id
    @GeneratedValue
    private long id;

    private String name;

    @ManyToMany(fetch = FetchType.LAZY, cascade = { CascadeType.PERSIST, CascadeType.MERGE }, mappedBy = "tags")
    private List<Post> posts = new ArrayList<Post>();

    public void setName(String name) {
        this.name = name;
    }

}

现在您需要一个PagingAndSortingRepository来获取帖子实体：

package com.example.repository;

import java.util.Set;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.example.model.Post;

@Repository
public interface PostRepository extends PagingAndSortingRepository<Post, Long> {

    @Transactional(readOnly = true)
    Set<Post> findByTagsName(String name);

    @Transactional(readOnly = true)
    Page<Post> findByTagsName(String name, Pageable pageable);

}

与编写常规的Spring Data JPA查找方法几乎一样简单，使用可分页的方法。如果您想要通过指定标签实体名称查找文章，只需像写常规查找器一样链接字段名称，例如findByTags+ Name。这将创建一个类似于您的JPQL方法SELECT p FROM Post p JOIN FETCH p.tags t WHERE t.name = :tagName的查询。仅传递标签名称作为方法参数就可以了。

现在 - 如果您想添加可分页支持 - 只需将类型为Pageable的参数添加为第二个参数，并将返回值转换为Page而不是Set。就是这样。

至少以下是一些测试来验证代码：

package com.example.repository;

import static org.hamcrest.CoreMatchers.is;
import static org.hamcrest.Matchers.empty;
import static org.hamcrest.Matchers.hasSize;
import static org.junit.Assert.assertThat;

import java.util.Set;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.test.context.junit4.SpringRunner;
import org.springframework.transaction.annotation.Transactional;

import com.example.model.Post;
import com.example.model.Tag;

@RunWith(SpringRunner.class)
@Transactional
@SpringBootTest
public class PostRepositoryTests {

    @Autowired
    private PostRepository postRepository;

    @PersistenceContext
    private EntityManager entityManager;

    @Test
    public void receiveMultiplePostsWithTagsByName() {
        final String nameA = "A";
        final String nameB = "B";
        final String nameC = "C";
        final String nameD = "D";
        final String nameE = "E";

        final Tag tagA = new Tag();
        tagA.setName(nameA);
        final Tag tagB = new Tag();
        tagB.setName(nameB);
        final Tag tagC = new Tag();
        tagC.setName(nameC);
        final Tag tagD = new Tag();
        tagD.setName(nameD);
        final Tag tagE = new Tag();
        tagE.setName(nameE);

        final Post postOne = new Post();
        postOne.getTags().add(tagA);
        postOne.getTags().add(tagB);
        postRepository.save(postOne);

        final Post postTwo = new Post();
        postTwo.getTags().add(tagA);
        postTwo.getTags().add(tagB);
        postTwo.getTags().add(tagE);
        postRepository.save(postTwo);

        final Post postThree = new Post();
        postThree.getTags().add(tagA);
        postThree.getTags().add(tagB);
        postThree.getTags().add(tagC);
        postThree.getTags().add(tagE);
        postRepository.save(postThree);

        entityManager.flush();
        entityManager.clear();

        final Set<Post> tagsByA = postRepository.findByTagsName(nameA);
        assertThat("Expected three hits!", tagsByA, hasSize(3));

        final Set<Post> tagsByB = postRepository.findByTagsName(nameB);
        assertThat("Expected three hits!", tagsByB, hasSize(3));

        final Set<Post> tagsByC = postRepository.findByTagsName(nameC);
        assertThat("Expected one hit!", tagsByC, hasSize(1));

        final Set<Post> tagsByD = postRepository.findByTagsName(nameD);
        assertThat("Expected no hits!", tagsByD, empty());

        final Set<Post> tagsByE = postRepository.findByTagsName(nameE);
        assertThat("Expected two hits!", tagsByE, hasSize(2));
    }

    @Test
    public void receiveMultiplePostsWithTagsByNamePaged() {
        final String nameA = "A";

        final Tag tagA = new Tag();
        tagA.setName(nameA);

        final Post postOne = new Post();
        postOne.getTags().add(tagA);
        postRepository.save(postOne);

        final Post postTwo = new Post();
        postTwo.getTags().add(tagA);
        postRepository.save(postTwo);

        final Post postThree = new Post();
        postThree.getTags().add(tagA);
        postRepository.save(postThree);

        final Post postFour = new Post();
        postFour.getTags().add(tagA);
        postRepository.save(postFour);

        final Post postFive = new Post();
        postFive.getTags().add(tagA);
        postRepository.save(postFive);

        entityManager.flush();
        entityManager.clear();

        final Page<Post> tagsByAFirstPageSize2 = postRepository.findByTagsName(nameA, new PageRequest(0, 2));
        assertThat("Expected two page items!", tagsByAFirstPageSize2.getContent(), hasSize(2));
        assertThat("Expected five items in sum!", tagsByAFirstPageSize2.getTotalElements(), is(5L));
        assertThat("Should be first page!", tagsByAFirstPageSize2.isFirst(), is(true));
        assertThat("Should not be last page!", tagsByAFirstPageSize2.isLast(), is(false));

        final Page<Post> tagsBySecondPageSize2 = postRepository.findByTagsName(nameA, new PageRequest(1, 2));
        assertThat("Expected two page items!", tagsBySecondPageSize2.getContent(), hasSize(2));
        assertThat("Expected five items in sum!", tagsBySecondPageSize2.getTotalElements(), is(5L));
        assertThat("Should not be first page!", tagsBySecondPageSize2.isFirst(), is(false));
        assertThat("Should not be last page!", tagsBySecondPageSize2.isLast(), is(false));

        final Page<Post> tagsByLastPageSize2 = postRepository.findByTagsName(nameA, new PageRequest(2, 2));
        assertThat("Expected one last page item!", tagsByLastPageSize2.getContent(), hasSize(1));
        assertThat("Expected five items in sum!", tagsByLastPageSize2.getTotalElements(), is(5L));
        assertThat("Should not be first page!", tagsByLastPageSize2.isFirst(), is(false));
        assertThat("Should be last page!", tagsByLastPageSize2.isLast(), is(true));
    }

}

- Kevin Peters

---

这个答案非常好。我已经寻找解决方案很长时间了，而这个答案为我解决了问题。感谢您的贡献。 - SagiZiv

Answer 2

不确定是否适用于您的情况，但请检查此链接

{{link1：Spring-Data-JPA注释的setMaxResults？}}

另外，

您可以在Spring JPA中使用findFirst或findTop方法来限制结果。

http://docs.spring.io/spring-data/jpa/docs/current/reference/html/#repositories.limit-query-result

- Arun

---

我知道如何使用Pageable、first或top来获取“单个”实体。然而，它似乎不支持多对多关系。因此，我编辑了我的问题，并附加了一些代码。请告诉我您的意见。谢谢。 - pincoin