Spring JPA add pagination to @ManyToMany

Question

In my current project, I am using JPA and Hibernate to handle my database layer.

One of my entities has a Many-To-Many relation to itself, which works great using the @ManyToMany and the @JoinTable annotations.

My problem is that I am asked to add a pagination support to the entity. I have searched online for a solution but the closest thing I have found to a solution works on a different use case, where the relation is between 2 entities (and not an entity to itself).

This is the important parts from the entity's class:

package iob.data;

import iob.data.primarykeys.InstancePrimaryKey;
import iob.logic.exceptions.instance.InvalidBindingOperationException;
import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.NonNull;
import lombok.RequiredArgsConstructor;
import lombok.Setter;
import lombok.ToString;

import javax.persistence.Embedded;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.IdClass;
import javax.persistence.JoinColumn;
import javax.persistence.JoinTable;
import javax.persistence.ManyToMany;
import javax.persistence.ManyToOne;
import javax.persistence.SequenceGenerator;
import javax.persistence.Table;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;
import java.util.Date;
import java.util.HashSet;
import java.util.Map;
import java.util.Objects;
import java.util.Set;


@NoArgsConstructor
@AllArgsConstructor
@Getter
@Setter
@RequiredArgsConstructor
@Entity
@Table(name = "INSTANCES")
@IdClass(InstancePrimaryKey.class)
public class InstanceEntity {
    //<editor-fold desc="Primary key">
    @Id
    // The index would be generated from a sequence named "INSTANCES_SEQUENCE"
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "INSTANCES_SEQUENCE")
    // Here we create that sequence, and define it to be updated every insertion (allocationSize = 1).
    @SequenceGenerator(name = "INSTANCES_SEQUENCE", sequenceName = "INSTANCES_SEQUENCE", allocationSize = 1)
    private long id;
    @Id
    @NonNull
    private String domain;
    //</editor-fold>

    //<editor-fold desc="Many to Many relation">
    // Define a new table that would store the references
    @JoinTable(name = "INSTANCES_TO_INSTANCES",
            // The main columns for this attribute are the parent's primary key which is constructed from domain and id
            inverseJoinColumns = {@JoinColumn(name = "PARENT_ID", referencedColumnName = "id"),
                    @JoinColumn(name = "PARENT_DOMAIN", referencedColumnName = "domain")},

            // The referenced columns for this attribute are the child's primary key which is also constructed from domain and id
            joinColumns = {@JoinColumn(name = "CHILD_ID", referencedColumnName = "id"),
                    @JoinColumn(name = "CHILD_DOMAIN", referencedColumnName = "domain")})
    // Declare the parent's side of the relation
    @ManyToMany(fetch = FetchType.LAZY)
    private Set<InstanceEntity> parentInstances = new HashSet<>();

    // Declare the children's size of the relation, and define that it is related to the parentInstances
    @ManyToMany(mappedBy = "parentInstances", fetch = FetchType.LAZY)
    private Set<InstanceEntity> childInstances = new HashSet<>();
    //</editor-fold>

    public void addParent(InstanceEntity parent) {
        if (this.equals(parent))
            throw new InvalidBindingOperationException("Cannot assign parent to himself");
        parentInstances.add(parent);
    }

    public void addChild(InstanceEntity child) {
        if (this.equals(child))
            throw new InvalidBindingOperationException("Cannot assign child to himself");
        childInstances.add(child);
    }

    @Override
    public int hashCode() {
        return Objects.hash(id, domain);
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        InstanceEntity entity = (InstanceEntity) o;
        return id == entity.id && domain.equals(entity.domain);
    }
}

So far I tried (probably naively) write the getter for parentInstance and pass it a Pagable object (because it works on derived-queries, so worth a shot ) but obviously it did nothing.

The only solution I can think of is removing the relation, and creating it manually (create the table using another entity and add a new PagingAndSortingRepository that would retrieve the relevant instances and then convert them to InstanceEntity in the service).

So, how can I add a pagination support for parentInstances and childInstances?

score 0 · Accepted Answer · answered Dec 22 '21 at 21:58

I have found the solution in some unexpected post. It is not about pagination, but it still solved my problem.

So the solution in my case was to add the following function to my PagingAndSortingRepository interface:

Page<InstanceEntity> findDistinctByParentInstancesIn(@Param("parent_instances") Set<InstanceEntity> parentInstances, Pageable pageable);

And then calling it is:

InstanceEntity ie = new InstanceEntity();
ie.setDomain(parentDomain);
ie.setId(Long.parseLong(parentId));
instancesDao.findDistinctByParentInstancesIn(Collections.singleton(ie), PageRequest.of(page, size));

To be honest, I'm not sure how/why it works. IntelliJ IDEA gives me an error in the repository interface Expected parameter types: Collection<Set<InstanceEntity>>.

So if someone has an explanation, I would be glad to hear it.

Spring JPA add pagination to @ManyToMany

1 Answers1