Spring Data JPA Pageable with @ManyToMany

Question

I have Post and Tag models which have @manytomany relationship.

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;

     ...
}

Tag

@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>();

I'd like to make a method which finds all paginated posts by tag name.

I found out JPQL doesn't support LIMIT.

Do I have to implement my own paging logic using setFirstResult().setMaxResults().getResultList()?

What's the best practice for pagination of @manytomany?

I edited my question a bit. I wrote my codes as shown below:

@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;
}

This code works fine, but I'm still wondering if this is a correct way.

Thank you.

Answer 1

Working with pages and the @ManyToMany mapping is a really straightforward task.

First here are models similar to yours (basically only added @Id and @GeneratedValue annotations to get generated database identifiers).

Post entity:

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;
    }

}

Tag entity:

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;
    }

}

Now you need a PagingAndSortingRepository for fetching the post entities:

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);

}

Working with pagables is nearly as simple as writing regular Spring Data JPA finder methods. If you want to find posts by names of assigned tag entities just write the regular finder by chaining the field names like findByTags + Name. This creates a query similar to your JPQL approach SELECT p FROM Post p JOIN FETCH p.tags t WHERE t.name = :tagName. Pass the parameter for the tag name as only method parameter.

Now - if you want to add Pageable support - just add a parameter of type Pageable as second parameter and turn the return value into a Page instead a Set. That's all.

At least here are some tests to verify the code:

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));
    }

}

Answer 2

Not sure if it works in your case but check this link

setMaxResults for Spring-Data-JPA annotation?

Also,

You can use findFirst or findTop methods for Limit in spring jpa.

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