What is the best way to get data from table (Many to Many Self Join) Hibernate?-CodePudding

I have a table to save Friendship of 2 people. So, in Friendship I have person_a_id and person_b_id. It's clearly a relationship many to many of the table Person.

With SQL, we can have simple way to get all friends of a person by using select and join.

The problem is while I'm using Hibernate (Spring boot). I used this instruction https://www.roseindia.net/hibernate/hibernate4/ManytoManySelfJoin.shtml

It's many to many connection between 2 tables. But they don't show how to get data back from the table.

Can anyone show me how to complete it? Or a better way to do it?

CodePudding user response：

You should create new table which will hold the linking between Friendship and Person table . Table Person_FriendShip columns = person_id,friendship_id

When defining entity you should add

Person Entity class:
@ManyToMany(cascade= CascadeType.ALL)
@JoinTable(
name="Person_FriendShip",
joinColumns=@JoinColumn(name="person_id"),
inverseJoinColumns=@JoinColumn(name="friendship_id"))
Set<FriendShip> friendships=new Hashset<>();

in Friendship Entity Class:
@ManyToMany(mappedBy="friendship")
Set<Person> persons=new HashSet<>();

This will make M2M relation between 2 entities and will have required data when you use it with JPA Repo.

CodePudding user response：

If you have your Person to Friendship relationship as follows

Person {
    // truncated

    @ManyToMany(cascade = {
            CascadeType.ALL
    })
    @JoinTable(name = "friendship",
            joinColumns = @JoinColumn(name = "friend_a_id"),
            inverseJoinColumns = @JoinColumn(name = "friend_b_id")
    )
    private List<Person> friendsA = new ArrayList<>();

    @ManyToMany(mappedBy = "friendsA")
    private List<Person> friendsB = new ArrayList<>();
}

You can get a person (by id) and eagerly fetch their friends using the following queries to populate friendsA and friendsB. Using two queries will prevent MultipleBagFetchException

    // Assume Person with Id 1 exists
    Person person = entityManager.createQuery(
                    "SELECT DISTINCT p From Person p "  
                            "LEFT JOIN FETCH p.friendsA "  
                            "WHERE p.id = :id", Person.class)
            .setParameter("id", 1L)
            .getSingleResult();

    entityManager.createQuery(
                    "SELECT DISTINCT p From Person p "  
                            "LEFT JOIN FETCH p.friendsB "  
                            "WHERE p.id = :id", Person.class)
            .setParameter("id", 1L)
            .getSingleResult();

If using Set rather than List you can do it in one query

    Person person = entityManager.createQuery(
                    "SELECT DISTINCT p From Person p "  
                            "LEFT JOIN FETCH p.friendsA "  
                            "LEFT JOIN FETCH p.friendsB "  
                            "WHERE p.id = :id", Person.class)
            .setParameter("id", 1L)
            .getSingleResult();

Alternatively just lazily fetch the friends collections and access them if required

        Person person = entityManager.find(Person.class, 1L);
        person.getFriendsA();
        person.getFriendsB();