title: Hibernate Named Query or SQL Server Stored Procedure
body: <p>I need to make either a named query or a stored procedure. This is running from a Java application that is attached to SQL Server through Hibernate. I would prefer a stored procedure, but a named query might suffice.</p>
  
  <p>I have a table in SQL Server that has a column of XML type. This is not important in and of itself, but it's causing a problem.</p>
  
  <p>My Hibnerate named query looks like this</p>
  
  <pre><code>&lt;sql-query name="openOrderSummary" callable="true"&gt;
      &lt;![CDATA[
          SELECT * FROM orders
          WHERE (order_type in (:firstListOfTypes)
          OR order_type in (:secondListOfTypes))
          AND open_flag = 'true'
          AND customer_id = :customerId
          ORDER BY id DESC
      ]]&gt;
  &lt;/sql-query&gt;
  </code></pre>
  
  <p>But an exception is thrown for this with a complaint of not being able to map JDBC type <code>-16</code>. This is not helpful. So I debugged through the Hibernate code and found that it has a problem with my XML typed column.</p>
  
  <p>I preferred to do a stored procedure, but I couldn't find a non-screwy way (on my own) of doing the list of strings for the <code>order_type in (:firstListOfTypes)</code> part (and, by extension, the second list). This is not a fixed list. It is pulled from Java code and it could change at any time with new types being added, so I can't just make it a hardcoded list in the query.</p>
  
  <p>I do the <code>select *</code> because I need the whole row. I'm doing either the named query or stored procedure because speed is a factor. Similar stored procedures (on a small subset of columns with very simple types) are so fast they appear to be instant. Running a normal fetch of this information through typical JDBC operations with <code>Session</code> and <code>Criteron</code> etc. takes several seconds. It may not seem like much, but in this application, it's important.</p>
  
  <p>1) Is there a reasonable way to get this list from Java into a stored procedure?</p>
  
  <p>2) If not, how can I get around Hibernate barfing when it comes to my xml column?</p>