JPA 2 multiple @CollectionTable in Entity causing unrelated outer joins

Question

Hi I have the following JPA entity class. It has two @CollectionTable mapping for Groups and Contact Id's. I am interesting in getting back a list of unique contact id's from the contact_details table and having a reference to them in my Users class variable contacts below:

@Entity
@Table(name = "users")
public class User {

@Id
@GeneratedValue(generator="uuid")
@GenericGenerator(name = "uuid", strategy = "uuid2")
private UUID id;

@NotBlank
private String username;

@NotBlank
@Column(name = "name")
private String name;

@ElementCollection(fetch = FetchType.EAGER)
@CollectionTable(name = "users_groups", joinColumns = @JoinColumn(name = "user_id"))
@Column(name = "[group]")
private List<String> groups = new ArrayList<>();

@ElementCollection(fetch = FetchType.EAGER)
@CollectionTable(name = "contact_detail", joinColumns = @JoinColumn(name = "user_id"))
@Column(name = "[id]")
private List<String> contacts = new ArrayList<String>();
}

When I get a user from the database it produces the following query in the hibernate logs

select user0_.id, user0_.password, user0_.username, contacts1_.user_id as user_id8_4_1_, contacts1_."id" as id1_2_1_, groups2_.user_id as user_id1_4_2_, groups2_."group" as group2_5_2_ 
from users user0_ 
left outer join contact_detail contacts1_ on user0_.id=contacts1_.user_id 
left outer join users_groups groups2_ on user0_.id=groups2_.user_id 
where user0_.id=?

Because of the Left Outer Join on contact_detail and users_groups, it actually retrieves back the same contact_id multiple times. This is a JSON representation of the entity with multiple "47e5b98a-2116-4ad9-b773-3acc99e2c83c" contact id's

{
user: {
    id: "d3b3be2a-8a2a-48ac-94dd-fd85faf1a8ff",
    username: "shiv",
    firstName: "Shivam",
    lastName: "Sinha",
    groups: [
        "ADMIN",
        "USER",
        "ROOT"
    ],
    expired: false,
    locked: false,
    credentialsExpired: false,
    enable: true,
    birthday: "2015-05-18",
    joined: "2015-05-18",
    gender: "M",
    contactDetails: null,
    contacts: [
        "47e5b98a-2116-4ad9-b773-3acc99e2c83c",
        "47e5b98a-2116-4ad9-b773-3acc99e2c83c",
        "47e5b98a-2116-4ad9-b773-3acc99e2c83c"
    ]
}
}

However when I completely remove the following groups class variable:

@ElementCollection(fetch = FetchType.EAGER)
@CollectionTable(name = "users_groups", joinColumns = @JoinColumn(name = "user_id"))
@Column(name = "[group]")
private List<String> groups = new ArrayList<>();

And attempt to retrieve the the user from the database. Hibernate generates the following sql:

select user0_.id, user0_.password, user0_.username, contacts1_.user_id as     user_id8_4_1_, contacts1_."id" as id1_2_1_ 
from users user0_ left outer join contact_detail contacts1_ on user0_.id=contacts1_.user_id 
where user0_.id=?

This is the JSON representation of the entity:

{
user: {
    id: "d3b3be2a-8a2a-48ac-94dd-fd85faf1a8ff",
    username: "shiv",
    firstName: "Shivam",
    lastName: "Sinha",
    expired: false,
    locked: false,
    credentialsExpired: false,
    enable: true,
    birthday: "2015-05-18",
    joined: "2015-05-18",
    gender: "M",
    contacts: [
        "47e5b98a-2116-4ad9-b773-3acc99e2c83c"
    ]
}
}

It only contains unique contact id "47e5b98a-2116-4ad9-b773-3acc99e2c83c". Which is what is expected.

So my question is how can i achieve the same thing without having to remove groups class variable OR changing the datatype from List<String> contacts to Set<String> contacts

score 1 · Accepted Answer · edited May 23 '17 at 12:05

The behaviour that is happening now is as expected as can be seen from the hibernate FAQ.The solution is mentioned in the FAQ here as well

If you are looking to do this via JPA refer here.

Following is the implementation in Spring data using the following Specification

 public class UserSpecifications {

    public static Specification<User> findUniqueUser(Integer userId){

         return new Specification<User>() {

            @Override
            public Predicate toPredicate(Root<User> userRoot,
                    CriteriaQuery<?> criteriaQ, CriteriaBuilder critieriaB) {
                criteriaQ.distinct(true);
                return critieriaB.equal(userRoot.get(User_.id), 1);
            }

         };
    }
}

To find the record

User user = userRepository.findOne(UserSpecifications.findUniqueUser(1));

score 1 · Answer 2 · answered May 22 '15 at 20:20

Also have a look at this

https://developer.jboss.org/wiki/AShortPrimerOnFetchingStrategies .

If you use fetch="join" for more than one collection role for a particular entity instance (in "parallel"), you create a Cartesian product (also called cross join) and two (lazy or non-lazy) SELECT would probably be faster.

This Cartesian product is what you are describing (multiple values). Try attempting to change it to use select as suggested:

@Fetch(FetchMode.JOIN) or Alternatively also try the batch Mode.

This is a further explanation of the fetching strategies: http://www.mkyong.com/hibernate/hibernate-fetching-strategies-examples/

JPA 2 multiple @CollectionTable in Entity causing unrelated outer joins

2 Answers2