These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]

Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 2 posts ] 
Author Message
 Post subject: HQL: is it possible to pass a collection as a parameter...
PostPosted: Tue Aug 22, 2006 9:52 pm 

Joined: Wed Jul 27, 2005 3:44 pm
Posts: 12
Location: Los Angeles, CA
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:

        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

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

Maxim Senin
Custom Software Development for the Enterprise

 Post subject:
PostPosted: Tue Aug 22, 2006 11:57 pm 

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.

Code tags are your friend. Know them and use them.

Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 2 posts ] 

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.