How can I get object from join table with ManyToMany relationship? Spring + Hibernate

Question

I have two entities:

@Entity
public class Customer {
@Id
@GeneratedValue
private int id;
@Column(nullable = false)
private String name;
@ManyToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
private Collection<Coupon> coupons;
}

@Entity
public class Coupon {
@Id
@GeneratedValue
private int id;
@Column(nullable = false)
private String title;
@ManyToMany(mappedBy = "coupons")
private Collection<Customer> customers;
}

There are three tables in database: customer, coupon and customer_coupons, customer can purchase coupon and after purchasing table customer_coupons saves customers_id and coupons_id (Customer can have many coupons and coupon can have many customers).

I need somehow to get coupon of the customer by customerId and couponId from customer_coupons table. I have interface CouponRepository:

@Repository
public interface CouponRepository extends JpaRepository<Coupon, Integer> {
    @Query("SELECT c FROM Coupon c WHERE c.id IN (SELECT coupons.id FROM customer_coupons WHERE coupons_id = ?1 AND customer_id = ?2)")
    Coupon findCustomerCoupon(int couponId, int customerId);
}

But this one query doesn't work, I get QuerySyntaxException: customer_coupons is not mapped. Can somebody help me to create the right query?

Answer 1

SELECT coupon FROM Customer c JOIN c.coupons coupon WHERE c.id = :customerId AND coupon.id = :couponId

Answer from JB Nizet