CODEWITHSUNDEEP
javahibernatejpamany-to-many
Anton Ishkov
Anton Ishkov

Reputation: 115

@ManyToMany not all values obtained from DB

I have in project user which have authorities. Authorities types stored in database and linked with user as many to many relation.

    @ManyToMany(fetch = FetchType.EAGER)
@JoinTable(name = "user_authority", joinColumns = @JoinColumn(name = "uauth_user"), inverseJoinColumns = @JoinColumn(name = "uauth_authority"))
private Set<Authority> userAuthorities;

But when I try to get all authorities of selected user I obtain just one of them. Hibernate just get first of them and put it to list, but ignore all other authorities of user.

I already check database and it store this data. Also I found solution with adding one not JPA annotations. It works with @Fetch(FetchMode.SUBSELECT) but I still don't understand what is wrong with it.

Upvotes: 3

Views: 216

Answers (2)

Anton Ishkov
Anton Ishkov

Reputation: 115

Ok problem was in different place.

My fault was that when I tried it with Hibernate annotation and it is just works I started thinking that this is an annotation problem, but actually it was caused with method of obtaining this values.

While refactoring we leae in code one mistake:

return (T) criteria.setMaxResults(1).uniqueResult();

He we set maximal count of results as 1 and in our case it converted to SQL as

SELECT * FROM user OUTER JOIN user_authority ON usr_id = uauth_user INNER JOIN authority ON uauth_authority = auth_id LIMIT 1

This limit removes all authorities, and leave just first authority from first line. But when we specify Hibernate FetchMode as SUBSELECT. It execute this get in two separate SQL queries. In which just main have limit.

Upvotes: 0

perissf
perissf

Reputation: 16273

The following solution works on any JPA 2.0 - compliant implementation.

Table user has primary key id.

Table authority has primary key id.

Table user_authority has fields user_id, authority_id.

Entity class User

@JoinTable(name = "user_authority", 
    joinColumns = {@JoinColumn(name = "user_id", referencedColumnName = "id")},  
    inverseJoinColumns = {@JoinColumn(name = "authority_id", referencedColumnName = "id")})
@ManyToMany(fetch = FetchType.EAGER)
private Set<Authority> authoritySet;

Entity class Authority

@ManyToMany(mappedBy = "authoritySet", fetch = FetchType.EAGER)
private Set<User> userSet;

The Table user_authority doesn't have an Entity represention in JPA.

Upvotes: 2

Related Questions