JPA ManyToMany join table query

Question

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 ?)

you could indeed do the reverse mapping where every entityTopic has a list of entityNotes. Then the size of this list is the count. Be aware that this is expensive to do. Maybe a better way is to set a transient field on your entityTopic "amountOfEntityNotes". And setting this field through a constructor when executing the retreive query — David Maes, Feb 22 '15 at 11:07
that could do the trick, but I'm not sure this design will serve well my app design — Barium Scoorge, Feb 22 '15 at 20:29
I'm planning to update a EntityTopic.count column. Might not be the best solution, but for now, i'm not sure to completly understand @ManyToMany and their reverse lists.. — Barium Scoorge, Feb 22 '15 at 20:59
I hope you are not still doing this but naming an entity `EntityNote` is kinda bad practice — Ojonugwa Jude Ochalifu, Apr 20 '20 at 21:33

score 1 · Answer 1 · answered Feb 22 '15 at 15:13

1

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

answered Feb 22 '15 at 15:13

Zielu

7,144
4
26
39

I'm getting a "ERROR: Column listentity0_.list_entity_note_id does not exists" with this – Barium Scoorge Feb 22 '15 at 20:24
Complete the @ManyToMany annotations of your EntityTopics to match your d (JoinTable): http://en.wikibooks.org/wiki/Java_Persistence/ManyToMany – Zielu Feb 22 '15 at 20:29
Depends on the JPA defaults. If you just starting, you can regenerate the DB structure to match the defaults, judging by your exception there is mismatch between JPA maping and the actual DB structure. – Zielu Feb 22 '15 at 20:39

score 0 · Answer 2 · edited May 23 '17 at 12:16

0

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.

edited May 23 '17 at 12:16

Community

1
1

answered Feb 22 '15 at 14:43

manish

17,766
4
59
85

That is not working ; when i'm requesting entityTopic.getListEntityNote(), i'm getting a zero-sized persistent bag – Barium Scoorge Feb 22 '15 at 20:27
Can you post a sample somewhere? Works in my production application. I have a many-to-many relationship between `Employee` and `Office` that I have to query on every single employee login and it has been working fine for the past many years (otherwise user logins would not have been working). Another thing you could try is to examine the SQL queries generated by your JPA provider and try running them directly on the database to see why one side of your many-to-many collection has a zero size. – manish Feb 23 '15 at 03:37

JPA ManyToMany join table query

2 Answers2