CODEWITHSUNDEEP
ejbnetbeans-7jpql
Lawrence Wong
Lawrence Wong

Reputation: 1139

JPQL ManyToMany @JoinTable select query?

I have 2 entities namely Product and Transaction and these 2 entities are link via a Many to Many relationship. I have the below in my transaction entity. 

@ManyToMany(cascade={CascadeType.ALL})
    @JoinTable(name="Transaction_Product")

When I run my project, there will be 3 tables tables created in my database and they are Transaction, Product and Transaction_Product respectively. The Transaction_Product is automatically generated when I run my project.

I am able to get items from my transaction table via this query below.

Query q = em.createQuery("SELECT t FROM Transaction t WHERE t.fulfillStatus = 0");

My question is, how do I get items from the Transaction_Product table?

I tried something like the query below and it did not work.

 Query q = em.createQuery("SELECT bt FROM Transaction_Product bt WHERE bt.ProductID = 1);

Any help please? :)

Upvotes: 1

Views: 3337

Answers (1)

Eugen Halca
Eugen Halca

Reputation: 1785

checking if there are transactions with specifyed product u can use :

Query q = em.createQuery("SELECT t FROM Transaction t join t.products p WHERE p.id = :id");

Assuming that Transaction class has field :

@ManyToMany(cascade={CascadeType.ALL})
    @JoinTable(name="Transaction_Product")
private Set<Product> products;

Upvotes: 1

Related Questions