CODEWITHSUNDEEP
javajpa
Barium Scoorge
Barium Scoorge

Reputation: 2028

JPA ManyToMany join table query

Assuming theses Entities

@Entity
public class EntityNote implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    @SequenceGenerator(name="SeqEntityNote", sequenceName="SeqEntityNote", allocationSize = 1)
    @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="SeqEntityNote")
    private long id;
    private Date date;
    private String subject;
    private String content;

    @ManyToMany
    private List<EntityTopic> listEntityTopic;

    //setters/getters

@Entity
public class EntityTopic implements Serializable {
    @Id
    @SequenceGenerator(name="SeqEntityTopic", sequenceName="SeqEntityTopic", allocationSize = 1)
    @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="SeqEntityTopic")
    private long id;
    private String name;

    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
}

In my DB, a join table named "entity_note_list_entity_topic" records the ManyToMany relation.

This works correctly so far.

But I'd like to perform a count query like 'how many EntityNotes per EntitityTopic'

Unfortunatly I'm quite lost in this situation.

How this query can be written ?

Do I need other elements in my two entities ?

(In many examples I see a reverse relation using mappedBy attribute on ManyToMany.. Do I need this ?)

Upvotes: 1

Views: 4441

Answers (2)

Zielu
Zielu

Reputation: 8562

It will be the easiest if you make the many to many relation bidirectional. There are no serious extra costs involved, as it uses the same db structure, and the list are lazy loaded so if the relation is not being used the lists are not populated (you can hide the second direction by making accessors private).

Simply change:

@Entity
public class EntityTopic implements Serializable {
  ...
  @ManyToMany(mappedBy="listEntityTopic")
  private List<EntityNote> notes;
}

You can issue normal count jpql queries, for example:

SELECT count(n) from EntityTopic t INNER JOIN t.notes n where t.name =:name

so you don't neet to retrieve the notes and topics if don't need to.

But I also believe that your original mapping can also be queries with:

SELECT COUNT(n) FROM EntityNote n INNER JOIN n.listEntityTopic t WHERE t.name = :name

Upvotes: 1

manish
manish

Reputation: 20135

If you have the following code:

@Entity
public class EntityNote implements Serializable {
  @ManyToMany(fetch = FetchType.LAZY)
  private List<EntityTopic> topics;
}

@Entity
public class EntityTopic implements Serializable {
  @ManyToMany(fetch = FetchType.LAZY)
  private List<EntityNote> notes;
}

Then, topic.getNotes().size() will give you the number of notes associated with a topic. When using Hibernate as the JPA provider, a SELECT COUNT(...) query is issued for this instead of loading all the associated notes. If this does not work for you out-of-the-box, mark the collections as extra lazy using the instructions in this post.

Upvotes: 0

Related Questions