CODEWITHSUNDEEP
hibernatejpaspring-dataspring-data-jpa
robert trudel
robert trudel

Reputation: 5779

Jpa many to many relation, bad sql query generated

I have a lodger object who have possibility to have many contact. Same contact can be associated to many lodger. It's a many to many relation.

In lodger i have,

@ManyToMany(mappedBy = "lodger")
private List<Contact> contactList;

In contact, I have

@ManyToMany
@JoinTable(name = "lodger_contact", joinColumns = @JoinColumn(name = "lodger_id"), inverseJoinColumns = @JoinColumn(name = "contact_id"))
private List<Lodger> lodger;

I use spring-jpa data. Jpa implementation used is hibernate

In my Contact repository interface, i created this method

List<Contact> findByLodgerLodgerIdNot(Long lodgerId);

I have a lodger and one contact (who are not associated). When i call this method nothing is returned.

Query generated is

    select
    contact0_.contact_id as contact_1_9_,
    contact0_.address as address2_9_,
    contact0_.city_cityId as city_cit5_9_,
    contact0_.contact_sub_category_contactSubCategoryId as contact_6_9_,
    contact0_.first_name as first_na3_9_,
    contact0_.last_name as last_nam4_9_,
    contact0_.phone_phoneId as phone_ph7_9_ 
from
    contact contact0_ 
left outer join
    lodger_contact lodger1_ 
        on contact0_.contact_id=lodger1_.lodger_id 
left outer join
    lodger lodger2_ 
        on lodger1_.contact_id=lodger2_.lodger_id 
where
    lodger2_.lodger_id<>?

Edit, i done change asked by JB Nizet

select
    contact0_.contact_id as contact_1_9_,
    contact0_.address as address2_9_,
    contact0_.city_cityId as city_cit5_9_,
    contact0_.contact_sub_category_contactSubCategoryId as contact_6_9_,
    contact0_.first_name as first_na3_9_,
    contact0_.last_name as last_nam4_9_,
    contact0_.phone_phoneId as phone_ph7_9_ 
from
    contact contact0_ 
left outer join
    lodger_contact lodger1_ 
        on contact0_.contact_id=lodger1_.contact_id 
left outer join
    lodger lodger2_ 
        on lodger1_.lodger_id=lodger2_.lodger_id 
where
    lodger2_.lodger_id<>?

Same result

Edit 2

With this code, that work

@Query("select c from Contact c where :lodger not member of c.lodger")
List<Contact> findByLodgerLodgerIdNot(@Param("lodger") Lodger lodger);

Instead of use lodger, is it possible to use lodgerId?

That generate this code

select
    contact0_.contact_id as contact_1_9_,
    contact0_.address as address2_9_,
    contact0_.city_cityId as city_cit5_9_,
    contact0_.contact_sub_category_contactSubCategoryId as contact_6_9_,
    contact0_.first_name as first_na3_9_,
    contact0_.last_name as last_nam4_9_,
    contact0_.phone_phoneId as phone_ph7_9_ 
from
    contact contact0_ 
where
    ? not in  (
        select
            lodger1_.lodger_id 
        from
            lodger_contact lodger1_ 
        where
            contact0_.contact_id=lodger1_.contact_id

Edit 3

@Query("select c from Contact c where :lodgerId not member of c.lodger.lodgerId")
List<Contact> findByLodgerLodgerIdNot(@Param("lodgerId") Long lodger);

org.hibernate.QueryException: illegal attempt to dereference collection [contact0_.contact_id.lodger] with element property reference [lodgerId]

Upvotes: 0

Views: 509

Answers (1)

JB Nizet
JB Nizet

Reputation: 692121

As you can see from the generated JPQL, your mapping is wrong: Hibernate joins the tables on contact_id = lodger_id (and vice-versa), instead of joining them on contact_id = contact_id (and lodger_id = lodger_id).

That's because you inversed the joinColumns with the inverseJoinColumns. The mapping should be

@JoinTable(name = "lodger_contact", 
           joinColumns = @JoinColumn(name = "contact_id"), 
           inverseJoinColumns = @JoinColumn(name = "lodger_id"))

Upvotes: 2

Related Questions