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...