CODEWITHSUNDEEP
springjpa
Mezoo
Mezoo

Reputation: 769

JPA Join Custom query with OneToMany

I would like to use @Query annotation to create left join query from a entity with a @OneToMany relationship.

Parent entity is :

    @Entity
    @Table(name="Registration")
    public class Registration {

       @Column(nullable = false)
       @Type(type = "org.jadira.usertype.dateandtime.joda.PersistentLocalDate")
       private LocalDate effect;

       @OneToMany(targetEntity=Payment.class, cascade=CascadeType.ALL,fetch = FetchType.LAZY)
       @JoinColumn(name="uuid")
       private List<Payment> payment;
   }

Child :

    @Entity
    @Table(name="Payment")
    public class Payment {

       @Id
       @GeneratedValue(generator = "uuid")
       @GenericGenerator(name="uuid", strategy = "uuid2")
       @Column(columnDefinition = "BINARY(16)")
       private UUID uuid;
}

For DAO, I do like below :

@Query("SELECT p FROM Registration r JOIN r.payment p WHERE r.effect = :effect")
Iterable<Payment> find(@Param("effect") LocalDate effect);

Obviously, its wrong because generated query is :

select payment1_.uuid as uuid1_9_, payment1_.amount as amount2_9_ from registration registrati0_ inner join payment payment1_ on registrati0_.uuid=payment1_.uuid where registrati0_.effect=?

while the relation table has been generated:

enter image description here

For me, the correct query should be something like this :

select p.* from registration r join registration_payment rp on rp.registration = r.uuid join payment p on p.uuid = rp.payment where r.effect = '2015-10-16'

What is the good query syntax please ? Actual query return en empty array.

Upvotes: 0

Views: 11647

Answers (1)

Mezoo
Mezoo

Reputation: 769

Finally, I found a solution. You must describe relation table with @JoinTable :

@JoinTable(
        name="registration_payment",
        joinColumns = @JoinColumn(name="registration"),
        inverseJoinColumns = @JoinColumn(name = "payment")
)
private List<Payment> payment;

Modify @Query not needed :

@Query("SELECT p FROM Registration r JOIN r.payment p WHERE r.effect = :effect")

Generated query is :

select payment2_.uuid as uuid1_9_, payment2_.amount as amount2_9_ from registration registrati0_ inner join registration_payment payment1_ on registrati0_.uuid=payment1_.registration inner join payment payment2_ on payment1_.payment=payment2_.uuid where registrati0_.effect=?

Upvotes: 1

Related Questions