How can I get an Entity with its referenced entity ids in a ManyToMany relation?

Question

I have an basic spring application that uses hibernate and mapstruct

There are two Entities, each are implemented to have their subchild entities as List attribute in a ManyToMany relation

So there is

EntityA.class
with List<EntityB> (fetchType Lazy)

and vice versa

Now when my client calls, it wants to get a DTO that represents like following:

EntityADTO
with List<Long> entityBIds

How can I get my EntityA with only the Ids of EntityB most efficient and without loading the complete EntityB and post process it after?

Thanks a lot!

fladdimir · Answer 1 · 2022-02-07T14:03:43.427

The @ManyToMany association information is persisted in a dedicated (join-)table and is loaded lazily on collection access, so there needs to be another query.

Instead of querying for the complete information of all associated entities, you could specifically query only for the needed id property.
Possible queries could look e.g. like this:

// Spring-Data repository (requires an extra interface for the result):
interface IdOnly(){
  Long getId();
}
interface EntityBRepository extends JpaRepository<EntityB, Long> {
  List<IdOnly> getIdByEntityAId(Long enitityAId);
}

// alternative JPQL query (does not need the interface):
@Query("SELECT b.id FROM EntityB b JOIN b.entityAs as a WHERE a.id=:entityAId")
List<Long> getIdByEntityAIdJpaQuery(@Param("enitityAId") Long enitityAId);

This way, only the needed EntityB ids for an associated EntityA are loaded from the DB.
For even further tuning, one could also write a native query directly accessing only the join-table, which avoids all joins:

@Query(nativeQuery = true, //
        value = "SELECT entityBId FROM entityA_entityB WHERE enitityAId=:enitityAId")
List<Long> getIdByEntityAIdNative(@Param("enitityAId") Long enitityAId);

For executing the query when mapping with mapstruct, you can use the spring repository bean e.g. as described here: https://stackoverflow.com/a/51292920

score 0 · Answer 2 · answered Feb 07 '22 at 16:50

In addition to @Fladdimir's answer which is a great approach if you only need the list of values occasionally, JPA allows defining Entity Graphs that can specify what in an object graph you want loaded. This can allow you to define your entity and specific attributes from child/referenced entities in the graph, allowing objects to be returned but the bulk of the data unfetched. This can allow you to process Entity B instances, but without them being fully populated. There are many tutorials but I've referenced https://www.baeldung.com/jpa-entity-graph more than once. As the tutorial referenced mentions though, Hibernate might have some issues with how it handles attributes that are normally eagerly fetched, so it might not work the way you want (but will with other JPA providers like EclipseLink, which is where I've used this).

Alternatively, if this is a collection of IDs you are going to want/need frequently, you can modify your object model to have them fetched differently.

public class EntityA {
  ..
  @ElementCollection
  @CollectionTable(name = "RELATION_TABLE_NAME", joinColumns = @JoinColumn(name = "A_ID", insertable=false, updatable=false))
  @Column(name = "B_ID", insertable=false, updatable=false)
  List<Long> bIds;
}

This allows fetching the foriegn keys automatically in your AEntity. I've made it read-only, assuming you'd keep the existing A->B relationship and use that to set things. Doing so though means that these two relationships are entirely separate, and so might result in different queries to fetch this same set of data.

If that is a concern, you can alter things again, and remove the existing A->B relationship, and stick it in an intermediary object AB.

public class EntityA {
  ..
  @ElementCollection
  @CollectionTable(name = "RELATION_TABLE_NAME", joinColumns = @JoinColumn(name = "A_ID"))
  List<AB> listOfBs;
}

@Embeddable
public class AB {
  @Column("B_ID", insertable=false, updatable=false)
  Long bId;
  
  @ManyToOne(fetch=LAZY)
  @JoinColumn(name = "B_ID")
  B b;
}

This would allow you to fetch As and use B's ID values without having to fetch from the B table. Note that I've marked the basic bId property as read-only, assuming that your existing app would be setting things by assigning a B reference to the relationship, but you could mark the relationship as read-only instead, and set the FK value using the bId. This might be more efficient long term, as you don't have to look up the B instance to set the relationship.

Alternatively again, you can make AB an entity instead of an embeddable, and allow it to exist and be queried upon outside of As and Bs. There are quite a few options though, and ways to map it, and not likely necessary for a simple model and use case.

How can I get an Entity with its referenced entity ids in a ManyToMany relation?

2 Answers2