HQL: is it possible to pass a collection as a parameter...

msenin · **Posted:** Tue Aug 22, 2006 9:52 pm

Hibernate version: 3.0.5
Name and version of the database you are using: MS SQLServer 2000

The problem:
I am fetching objects whose properties I want to display in a web app:

Code:

        select vk
        from VendorKeyword vk fetch all properties,
             GlobalExcludedKeyword g fetch all properties
             join fetch vk.adGroup ag fetch all properties
             join fetch vk.adGroup.campaign c fetch all properties
             join fetch vk.adGroup.campaign.account a fetch all properties
             join fetch vk.keyword k fetch all properties
        where (
            (vk.keyword.searchString like ' %'+g.excludedWord+'% ') or
            (vk.keyword.searchString like g.excludedWord+'% ') or
            (vk.keyword.searchString like ' %'+g.excludedWord) )

This way I can fetch all objects/properties in single select statement.

What I want to do is to replace GlobalExcludedKeyword with an arbitrary collection of Strings and write something like

Code:

...join elements of (:myCollectionOfStrings) as XXXX where vk.keyword.searchString like '%'+XXXX.items[0]

Since I don't know if that's even possible in HQL, I tried a different approach: I write my strings collection into CSV file, then do BULK INSERT into a table and then call a stored proc that has basically same SQL that corresponds to the HQL above except it used SQL table into which I just BULK INSERTed instead of GlobalExcludedKeyword object.

Problem is that with stored proc the "vk" is loaded, but all the joined objects and not greedily fetched so I end up with N+1 select for each of the joins.

What to do? Is HQL capable of somehow turning a parameter of type Collection<String> into some sort of collection that I can join to and use in the WHERE clause? If not, what's the best way to fetch all the properties I need in 1 select statement (or 1 stored proc call)?

I know procs do not support <join-return> tag in HBM in Hibernate 3.0.5, otherwise I think I could have used it...

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

Don't constrct query strings like that. Use positional or named parameters. In addition to being more readable, more maintainable, more self-documenting (in the case of named parameters) and more efficient, it also allows you to use setParameterList to pass collections to queries.

However, this won't help you, as it is impossible to pass table or class names to queries. You cannot join to a variable: even if you could, there's no way to join to a collection. You would better off having a different query for each possible value of GlobalExcludedKeyword.

To solve your problem, I would use an SQL query. Read up on Session.createSQLQuery.