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?

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

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



Not if you use Cascading.



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

My fault, I had a wayward FetchType.EAGER on a ManyToMany. It's no longer throwing out a bajillion queries.