CODEWITHSUNDEEP
javasql-serverhibernatestored-proceduresnamed-query
User51610
User51610

Reputation: 503

Hibernate Named Query or SQL Server Stored Procedure

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.

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.

My Hibnerate named query looks like this

<sql-query name="openOrderSummary" callable="true">
    <![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
    ]]>
</sql-query>

But an exception is thrown for this with a complaint of not being able to map JDBC type -16. This is not helpful. So I debugged through the Hibernate code and found that it has a problem with my XML typed column.

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 order_type in (:firstListOfTypes) 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.

I do the select * 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 Session and Criteron etc. takes several seconds. It may not seem like much, but in this application, it's important.

1) Is there a reasonable way to get this list from Java into a stored procedure?

2) If not, how can I get around Hibernate barfing when it comes to my xml column?

Upvotes: 0

Views: 466

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89361

Is there a reasonable way to get this list from Java into a stored procedure?

Sure.

Pass it as a comma-delimited list to a Varchar(max) or NVarchar(max) stored procedure argument and use STRING_SPLIT.

Pass it as a JSON array to a NVarchar(max) stored procedure argument and use OPENJSON.

Pass it as an XML document to a Varchar(max) or NVarchar(max) stored procedure argument and use xml.nodes() (or the older OPENXML).

Upvotes: 1

Related Questions