Spring Data - JPA Repository - Too many parameters in a Query
<p>I'm working with Spring Data Repositories manipulating data from a PostreSQL database.</p>

<p>In one of my repositories, I have a very simple query:</p>

<pre><code>@Query(value = "FROM MyEntity entity WHERE entity.entityId IN (:entityIds)")
Collection&lt;MyEntity&gt; getEntitiesByIds(@Param("entityIds") Collection&lt;Long&gt; entityIds);
</code></pre>

<p>The problem is, when I pass in a collection that has more than 32k~ ids this query throws the following error:</p>

<pre><code>java.io.IOException: Tried to send an out-of-range integer as a 2-byte value: 70324
</code></pre>

<p>I did some research and  found out that this error happens because postgresql jdbc driver has a limit of 32k~ parameters you can pass in to a query. So I have two options here:</p>

<ol>
<li><p>Do the query in batches. At this point I think this is my best shot but I was hoping to find a solution so I can pull my rows in one database call.</p></li>
<li><p>Build a temporary table with the ids, then join it against MyEntity table, but I'm running again into the same original problem. For this approach I was thinking to pass a String with all the concatenated ids and then use a native query to pull the information but it its not working as I expected:</p>

<pre><code>@Query(value = ";WITH cteAllEntityIds(entity_id) AS( " +
            "VALUES (?1)) " +
            "SELECT p.* FROM my_entity e " +
            "JOIN cteAllEntityIds cte ON e.entity_id = cte.entity_id", nativeQuery = true)
</code></pre>

<p><code>Collection&lt;ProfileViewEntity&gt; getProfilesByIds(String profileIds);</code></p></li>
</ol>

<p>Of course, the parameter I'm passing in is a String whereas the entity_id is a number in the data base.</p>

<p>Do you know a way to accomplish approach #2? How to build queries dynamically and make jpa to interpret it that way?</p>

<p>Do you know a better solution to this problem?</p>

<p>Any suggestions are welcome.</p>

<p>Thanks.</p>