CODEWITHSUNDEEP
javasqlpostgresqlhibernate
Naty Bizz
Naty Bizz

Reputation: 2342

Java Hibernate - Query with a list as join

In java code I generate thousands of ids, next I need to get those ids in the postgresql database, so far I have this (used createNativeQuery as pseudocode):

            Query q = em.createNativeQuery(
                    "select * from mytable where id in (:ids)"
            ).setParameter("ids", listofIds);

I'm afraid about the IN clause, I fear that there might be a huge number of values, is there another better way to perform this?

Upvotes: 0

Views: 386

Answers (1)

Chris Heath
Chris Heath

Reputation: 136

I think you can scale to quite large lists by doing

select * from mytable where id = any(cast(:ids as bigint[]))

but you have to format the list of ids as a string in Java first, like

ids = "{" + String.join(",", listofIds) + "}";

Still, there will always be some threshold where the list is just too large for Java or Postgresql to handle, so you'll need to break it up into batches if you want to support arbitrarily large lists.

Upvotes: 1

Related Questions