Hi all,
is it possible to use named parmeters also for the property names?
In this case I used ":show" to represent showPPO property of the POJO. When I execute the code no errors are returned but the list I get is empty. If I remove ":show" and put p.showPPO than I get a filled list.

Hibernate version: 2.1.8

Mapping documents:

Code between sessionFactory.openSession() and session.close():

Code:

String queryString = "from Promotion p where :date between p.startDate AND p.endDate AND p.status = :status"
   + " AND (:show like :id1 OR :show like :id2 OR :show like :id3 OR p.showPPO like 'message.promotion.show.all' OR p.showto like 'message.promotion.showto.both')";
           
Query hqlQuery = session.createQuery(queryString).setDate("date", new Date()).setString("status", "message.promotion.state.active");

hqlQuery.setString("show", "p.showPPO");
hqlQuery.setString("id1", String.valueOf(user.getWorkmate().getPpoId() + ",%"));
hqlQuery.setString("id2", "%," + String.valueOf(user.getWorkmate().getPpoId() + ",%"));
hqlQuery.setString("id3", "%," + String.valueOf(user.getWorkmate().getPpoId()));

Full stack trace of any exception that occurs: No errors

Name and version of the database you are using: Oracle 9i

The generated SQL (show_sql=true):
from Promotion p where :date between p.startDate AND p.endDate AND p.status = :status AND (:show like :id1 OR :show like :id2 OR :show like :id3 OR p.showPPO like 'message.promotion.show.all' OR p.showto like 'message.promotion.showto.both')

Debug level Hibernate log excerpt:

I think you may want to look at using Criteria queries for this.

This would allow you to perform something like the following (example from the javadoc of Criteria).

Can you actually do what you're attempting using indexed parameters ?

This has nothing to do with Hibernate. I'm pretty sure that the SQL standard doesn't allow you to do things like "WHERE :x = :y"

The probelm with criteria is how to implement ":date between p.startDate and p.EndDate"

Normally the criteria supports "p.startDate between :date1 and :date2"

This is a really stupid reply. It has nothing to do with the question asked.
He's isn't asking about the date part of the query - he's asking about the :show -

As for the answer, I don't believe you can do it - I have tried a zillion ways to do this and nothing works unless you create the query in the java object itself which allows you to use double quotes around the query string and + in the value you want for :show

like this:

String queryString = "from Promotion p where :date between p.startDate AND p.endDate AND p.status = :status"
+ " AND (" + show + " like :id1 OR " + show + " like :id2 OR " + show + " like :id3 OR p.showPPO like 'message.promotion.show.all' OR p.showto like 'message.promotion.showto.both')";

The only drawback is that you can't use a named query as far as I can tell because named queries don't allow double quotes.

If anyone has been able to do it in a named query, I would like to know how.

It has come to my attention that I might have offended people. I didn't mean to use the word stupid in the way americans use it. I meant it to mean unreasoned thinking, going down the wrong path. Sorry for offensing.

No offense taken, mainly because I read the last post before reading the prior post calling me stupid. ;-)

Now that I finally understand your question, you can definitely NOT do what you are trying to do. The named and indexed parameter replacement is just that, parameter replacement. It's not intended for replacing any arbitrary part of the query.

Thanks for all the answers. Now I understand why I can't replace columns with named parameters.
So I created some filters that alter from the second part of the query the comlumn names.
The problem is the result that I get from the query.
The second query that looks up throug a column - that contains string ids seperated by comma's (2,5,8,12,18) or contains the text "message.promotion.show.all" - and should return the matching record by given parameter user.getWorkmate().getPpoId().

So if the parameter is 8 it should look through the column for an "8,%" "%,8,%" and "%,8".
The weird thing is that this query allways returns results, even if the 8 isn't present inside the column. And idea's on how to rectify this?

I think I have located the problem:
(p.showto like 'message.promotion.showto.both') is the dirty one. Thanks for all your input guys.