英文:
Java Hibernate - Query with a list as join
问题
在Java代码中，我生成了成千上万个标识（ids），接下来我需要在PostgreSQL数据库中获取这些标识。到目前为止，我有以下代码（将createNativeQuery用作伪代码）：
Query q = em.createNativeQuery(
"select * from mytable where id in (:ids)"
).setParameter("ids", listofIds);
我担心涉及到IN子句，我担心值的数量可能会非常大，有没有其他更好的方法来执行这个操作？
英文:
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?
答案1
得分: 1
我认为你可以通过以下方式扩展到相当大的列表：
select * from mytable where id = any(cast(:ids as bigint[]))
但是你需要先在Java中将ID列表格式化为字符串，例如：
ids = "{" + String.join(",", listofIds) + "}";
然而，总会存在某个阈值，超过该阈值，Java或PostgreSQL都无法处理列表，因此如果你想支持任意大的列表，就需要将其分批处理。
英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论