title: rewrite Hibernate query without huge list parameter
body: <p>In my database I have a <code>zip</code> table with a <code>code</code> column. The user can upload a list of Zip codes and I need to figure out which ones are already in the database. Currently, I do this using the following Hibernate query (HQL):</p>
  
  <pre><code>select zip.code from Zip zip
  where zip.code in (:zipCodes)
  </code></pre>
  
  <p>The value of the <code>:zipCodes</code> parameter is the list of codes uploaded by the user. However, in the version of Hibernate I'm using there's <a href="https://hibernate.onjira.com/browse/HHH-2166">a bug</a> which limits the size of such list parameters and on occasions we're exceeding this limit.</p>
  
  <p>So I need to find another way to figure out which of the (potentially very long) list of Zip codes are already in the database. Here are a few options I've considered</p>
  
  <h2>Option A</h2>
  
  <p>Rewrite the query using SQL instead of HQL. While this will avoid the Hibernate bug, I suspect the performance will be terrible if there are 30,000 Zip codes that need to be checked.</p>
  
  <h2>Option B</h2>
  
  <p>Split the list of Zip codes into a series of sub-lists and execute a separate query for each sub-list. Again, this will avoid the Hibernate bug, but performance will likely still be terrible</p>
  
  <h2>Option C</h2>
  
  <p>Use a temporary table, i.e. insert the Zip codes to be checked into a temporary table, then join that to the <code>zip</code> table. It seems the querying part of this solution should perform reasonably well, but the creation of the temporary table and insertion of up to 30,000 rows will not. But perhaps I'm not going about it the right way, here's what I had in mind in pseudo-Java code</p>
  
  <pre><code>/**
   * Indicates which of the Zip codes are already in the database
   *
   * @param zipCodes the zip codes to check
   * @return the codes that already exist in the database
   * @throws IllegalArgumentException if the list is null or empty
   */
  List&lt;Zip&gt; validateZipCodes(List&lt;String&gt; zipCodes) {
  
    try {
      // start transaction
  
      // execute the following SQL
      CREATE TEMPORARY TABLE zip_tmp
      (code VARCHAR(255) NOT NULL) 
      ON COMMIT DELETE ROWS;
  
      // create SQL string that will insert data into zip_tmp
      StringBuilder insertSql = new StringBuilder()
  
      for (String code : zipCodes) {
        insertSql.append("INSERT INTO zip_tmp (code) VALUES (" + code + ");")
      }     
  
      // execute insertSql to insert data into zip_tmp
  
      // now run the following query and return the result   
      SELECT z.*
      FROM zip z
      JOIN zip_tmp zt ON z.code = zt.code
  
    } finally {
      // rollback transaction so that temporary table is removed to ensure
      // that concurrent invocations of this method operate do not interfere
      // with each other
    }    
  }
  </code></pre>
  
  <p>Is there a more efficient way to implement this than in the pseudo-code above, or is there another solution that I haven't thought of? I'm using a Postgres database.</p>