CODEWITHSUNDEEP
javamysqljpa
Martin Spasov
Martin Spasov

Reputation: 1

JPA many to one/one to many query

I would like to create JPA query based on this tables 

**category**                                              

(pk)CategoryID int (10)
category VARCHAR (45)         

**templatecat**

(pk/fk)templateId int(10)       
(pk/fk)categoryId int (10) 

 **template**

(pk)templateId int (10)
template madiumtext

I also have a "tempaltecat" table that holds foreign keys for category and template table.I`d like to create query that finds all templates in template table based on category and vice versa.

Here is my table mapping

@Entity
@Table(name = "category")
@OneToMany(cascade = CascadeType.ALL)
@JoinTable(name = "templatecat", joinColumns = { @JoinColumn(name = "categoryId", unique = true) }, inverseJoinColumns = { @JoinColumn(name = "templateId") })

private Set<Template> template;

@Entity
@Table(name = "template")
@ManyToOne(optional = true)
@JoinTable(name = "templatecat", joinColumns = { @JoinColumn(name = "templateId") }, inverseJoinColumns = { @JoinColumn(name = "categoryId") })

private Category category;

Thanks in advance

Upvotes: 0

Views: 668

Answers (1)

leozin
leozin

Reputation: 387

It looks like a @ManyToMany relationship,

Instead of using @OneToMany and @ManyToMany, you can use the following configuration:

In Category class:

@ManyToMany(cascade = CascadeType.ALL)
@JoinTable(name = "templatecat", joinColumns = { @JoinColumn(name = "categoryId", unique = true) }, inverseJoinColumns = { @JoinColumn(name = "templateId") })
private Set<Template> templates;

In Template class:

@Entity
@Table(name = "template")
@ManyMany(optional = true, mappedBy="templates");
private Set<Category> categories;

If you want to see all Templates of a given Category, the query would be:

select o.templates from Category o where o.id = ?

The reverse works as well (all Categories from a Template)

select o.categories from Template o where o.id = ?

Hope it has helped you.

Upvotes: 2

Related Questions