title: JPA 2.0: Batch query, safe and performant?
body: <p>I am looking for a JPA-solution (vendor-independent) to execute a query in batches. The challenge is to make this performant as well as thread-safe.</p>
  
  <p>Example query:</p>
  
  <pre><code>Query query = em.createQuery("select e from Entity e where e.property in :list");
  </code></pre>
  
  <p>The list is a collection of size between 1 and 385000. Hence, the requirement to batch this query.</p>
  
  <p>Initial naive approach was to get a sublist from the original list and loop through until done. This was safe and working well except that it was not performant.</p>
  
  <p>Second approach was to load everything from the list onto a temp table (permanent in existence, but used as a temporary table) and then use the original query and join with the temp table. This is definitely performant, but is not thread-safe as I need to clear the temp table after each batch and without having any thread id or something of that sort in the temp table its pretty unsafe (which is at the moment).</p>
  
  <p>I would really appreciate suggestions to arrive at a performant and safe way to tackle this issue.</p>
  
  <p>Thanks</p>