I’m using JPA with hibernate as a provider. One of my entities has several many-to-many junctures with other entities.
Sometimes there can be thousands of related entities, so setting up a collection getter for the primary entity in question isn’t doable, since I’d prefer to acquire the related entities in batches. So then my options are:
- Write a join query in JPQL. But this joins and grabs entities from both sides of the relationship, a much slower query than simply getting a list of IDs sitting in the middle
- Create a separate entity for each individual M2M juncture (but this seems like a lot of needless classes)
- Write a native query to get this information… but unfortunately SQL Server (yuck) deems all the IDs that I select to be BigDecimals rather than Integers, which causes trouble in other parts of the code. I need to support multiple databases, so doing a cast in the native query would mean I have to check what type of database is being used… which really defeats the purpose of an ORM
Is there a way to have JPQL select from this table so that I can have the guarantee of retrieving integers, and have the guarantee that the query will be fast?