Hibernate Books

All times are UTC - 5 hours [ DST ]



Post new topic Reply to topic  [ 5 posts ] 
Author Message
 Post subject: How to select directly from a @ManyToMany JPA link table?
PostPosted: Wed Mar 01, 2017 1:59 am 
Newbie

Joined: Wed Feb 22, 2017 4:35 pm
Posts: 17
I have a ManyToMany relationship where I need to fetch all Bs for each A. If I were dealing with the SQL directly, I would do two queries--one to fetch all relevant items from A, and then another from the m2m bridge table joining on B. (I'm aware that this would probably use an IN () statement, which certainly isn't great..)

I'm finding that there isn't a way to do this without hibernate running an N+1 number of queries for each A.

Unfortunately, I can't use JOIN FETCH because I also need to use setMaxResults. Doing both gets me the warning:

"firstResult/maxResults specified with collection fetch; applying in memory!"

...and I don't want to incur the memory penalties that the warning is directing me away from. But I really don't want to resort to native queries, either. Is there a way to do this with JPQL or the criteria API?


Top
 Profile  
 
 Post subject: Re: JPA -- a way to select directly from an m2m bridge table?
PostPosted: Wed Mar 01, 2017 2:23 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1534
Location: Romania
Yes, you can. But it requires you to map the bridge table as a separate entity, where each FK gets its own @ManyToOne association. On the parent-side, both entities which originally contained the @ManyToMany construct will now use a bidirectional mappedBy @OneToMany.

This way, you have better flexibility for running your queries, and the auto-generated statements will be more efficient as well.

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
 Post subject: Re: How to select directly from a @ManyToMany JPA link table?
PostPosted: Tue Apr 11, 2017 1:08 pm 
Newbie

Joined: Wed Feb 22, 2017 4:35 pm
Posts: 17
If I take that approach, I think I'd also need to create a separate ID on the many to many link table (which currently just stores ID pairs), or else I'd have to do embeddedID compound primary key stuff?
And if I make this M2M table a separate entity, wouldn't I have to manage its creation and deletion manually? I think that would be a pain.. there's really no other way to target this table without native queries? D:

Specifically, is there a way to *just* select the list of ID integers from a many to many table in a criteria query?


Top
 Profile  
 
 Post subject: Re: How to select directly from a @ManyToMany JPA link table?
PostPosted: Tue Apr 11, 2017 2:03 pm 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1534
Location: Romania
Quote:
I think I'd also need to create a separate ID on the many to many link table


You can use a @GeneratedValue @Id for the linl table if you want.

Quote:
wouldn't I have to manage its creation and deletion manually?


Not if you use Cascading.

Quote:
there's really no other way to target this table without native queries?


Unless you need to use derived tables, CTE, PIVOT, or window functions, JPQL can emulate common SQL queries too.

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
 Post subject: Re: How to select directly from a @ManyToMany JPA link table?
PostPosted: Tue Apr 11, 2017 3:10 pm 
Newbie

Joined: Wed Feb 22, 2017 4:35 pm
Posts: 17
My fault, I had a wayward FetchType.EAGER on a ManyToMany. It's no longer throwing out a bajillion queries.


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 5 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.