Java Hibernate - Query with a list as join

Question

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?

Either build a sub-query which fetches the ids, or load your records in multiple batches to reduce the amount of data you process with each query. — Guillaume F.
– Guillaume F., Commented Aug 26, 2020 at 2:29

Chris Heath · Accepted Answer · 2020-08-26 03:26:29Z

1

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.

answered Aug 26, 2020 at 3:26

Chris Heath

1361 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Java Hibernate - Query with a list as join

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related