Spring data JPA: Three entities ManyToMany relations, query duplicate result with findById

User.java

@Entity
@Table(name = "test_user")
public class User implements Serializable {
    private static final long serialVersionUID = 1L;

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

    @JsonProperty(access = Access.WRITE_ONLY)
    private String password;

    private String username;

    private String name;

    @ManyToMany(fetch = FetchType.EAGER, cascade = CascadeType.ALL)
    @JoinTable(name = "test_user_and_role", inverseJoinColumns = @JoinColumn(name = "role_id"), joinColumns = @JoinColumn(name = "user_id"))
    private List<UserRole> userRoles = new ArrayList<>();

    public User() {
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getName() {
        return name;
    }

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

    public List<UserRole> getUserRoles() {
        return userRoles;
    }

    public void setUserRoles(List<UserRole> userRoles) {
        this.userRoles = userRoles;
    }

}

UserRole.java

@Entity
@Table(name = "test_user_role")
public class UserRole implements Serializable {
    private static final long serialVersionUID = 1L;

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

    private String role;

    @ManyToMany(mappedBy = "userRoles")
    @JsonProperty(access = Access.WRITE_ONLY)
    private List<User> users = new ArrayList<>();

    @ManyToMany(fetch = FetchType.EAGER, cascade = CascadeType.REFRESH)
    @JoinTable(name = "test_role_and_permission", inverseJoinColumns = @JoinColumn(name = "permission_id"), joinColumns = @JoinColumn(name = "role_id"))
    private List<RolePermission> permissions = new ArrayList<>();

    public UserRole() {
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getRole() {
        return role;
    }

    public void setRole(String role) {
        this.role = role;
    }

    public List<User> getUsers() {
        return users;
    }

    public void setUsers(List<User> users) {
        this.users = users;
    }

    public List<RolePermission> getPermissions() {
        return permissions;
    }

    public void setPermissions(List<RolePermission> permissions) {
        this.permissions = permissions;
    }

}

RolePermission.java

@Entity
@Table(name="test_role_permission")
public class RolePermission implements Serializable{

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    private Long id;
    
    private String code;

    private String des;
    
    
    
    @ManyToMany(mappedBy = "permissions")
    @JsonProperty(access = Access.WRITE_ONLY)
    private List<UserRole> roles = new ArrayList<>();

    public String getAuthority() {
        return this.code;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getCode() {
        return code;
    }

    public void setCode(String code) {
        this.code = code;
    }

    public String getDes() {
        return des;
    }

    public void setDes(String des) {
        this.des = des;
    }

    public List<UserRole> getRoles() {
        return roles;
    }

    public void setRoles(List<UserRole> roles) {
        this.roles = roles;
    }
    
    
}

JDK:17 SpringBoot:2.7.4

findById and findBy are also generate different structure SQL. findById uses left outer Join. findByName uses subquery.

the user only have one role, the role have three permissions.

if use findByUsername It will return the user info correctly.

 {
    "username": "admin",
    "userRoles": [
        {
            "created": null,
            "id": 1,
            "role": "admin",
            "roleName": "admin",
            "permissions": [
                {
                    "authority": "user_query",
                    "code": "user_query",
                    "id": 1
                },
                {
                    "authority": "role_query",
                    "code": "role_query",
                    "id": 2
                },
                {
                    "authority": "equ_query",
                    "code": "equ_query",
                    "id": 3
                }
            }
        ]
    }

If I use findById, the same role repeat three times。

 {
"username": "admin",
"userRoles": [
    {
        "id": 1,
        "permissions": [
            {
                "authority": "user_query",
                "code": "user_query",
                "id": 1
            },
            {
                "authority": "role_query",
                "code": "role_query",
                "id": 2
            },
            {
                "authority": "equ_query",
                "code": "equ_query",
                "id": 3
            }
        ],
        "role": "admin",
        "roleName": "admin",
    },
    {
        "id": 1,
        "permissions": [
            {
                "authority": "user_query",
                "code": "user_query",
                "id": 1
            },
            {
                "authority": "role_query",
                "code": "role_query",
                "id": 2
            },
            {
                "authority": "equ_query",
                "code": "equ_query",
                "id": 3
            }
        ],
        "role": "admin",
        "roleName": "admin",
        "updated": null
    },
    {
       
        "id": 1,
        "permissions": [
            {
                "authority": "user_query",
                "code": "user_query",
                "id": 1
            },
            {
                "authority": "role_query",
                "code": "role_query",
                "id": 2
            },
            {
                "authority": "equ_query",
                "code": "equ_query",
                "id": 3
            }
        ],
        "role": "admin",
        "roleName": "admin",
    }
]

}

code : https://github.com/shuanshuan/demo-questions

Answer

While fetching data using id Spring Data JPa considers your fetch = FetchType.EAGER but while fetching with other property it ignores and lazy loads those so you don’t see duplicate records.

To achieve same output at both query you need to add how Spring Data JPA construct query.

@ManyToMany(fetch = FetchType.EAGER, cascade = CascadeType.REFRESH)
@Fetch(FetchMode.SUBSELECT)
@JoinTable(name = "test_role_and_permission", inverseJoinColumns = 
    @JoinColumn(name = "permission_id"), joinColumns = @JoinColumn(name = "role_id"))

The @Fetch annotation is from org.hibernate.annotations package.