Hi,
Does Hibernate impose restrictions on what can be used as a named parameter in a query?

I'm trying to use something like
select new ProductSummary(summary.id, summary.name)
from Products as summary where :searchProperty :searchCriteria :searchValue

This is to write a generalized query where depending on user input, I can search based on desired column, criteria and value.
However, this gives a syntax error. The same query works if I change it to

select new ProductSummary(summary.id, summary.name)
from Products as summary where summary.name like :searchValue

Am I using the syntax incorrectly OR is this kind of named parameter not supported?

If this is not supported, are there other mechanisms to support this kind of generalized query?

Thanks,
Roopa

Parameter substitutions are exactly that: substitutions of parameters. SQL does not allow substitution of keywords (it's not an hibernate thing: I guess that hibernate could add that feature, but...)

If you want to choose your search type at search time, there's a whole package full of criteria-related stuff. Use Criteria to do what you're trying.

Ok, so I'm using Criteria api now and I'm using it as below:

Criteria criteria =
this.sessionFactory.getCurrentSession().createCriteria(clazz);

criteria.addOrder(Order.asc(sortByProperty));
criteria.add(Restrictions.like(searchProperty, searchString,
MatchMode.ANYWHERE));
ProjectionList projList = Projections.projectionList();
for(String property: propertyList) {
projList.add(Projections.property(property), property);
}

criteria.setProjection(projList)
.setResultTransformer( Transformers.aliasToBean(classToRetrieve));
criteria.list();
...

propertyList is an ArrayList containing
propList.add("id");
propList.add("name");

This generates the sql as below with an exception:

Hibernate: select this_.ID as y0_, this_.NAME as y1_ from ADMIN.PRODUCTS this_ where y1_ like ? order by y1_ asc

2007-04-12 01:30:56,013 WARN [org.hibernate.util.JDBCExceptionReporter] - <SQL Error: 20000, SQLState: 42X04>
2007-04-12 01:30:56,013 ERROR [org.hibernate.util.JDBCExceptionReporter] - <Column 'Y1_' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'Y1_' is not a column in the target table.>

However, I tried the same approach without using Projections and transforms and I get the following sql with no errors.
Hibernate: select this_.ID as ID0_0_, this_.NAME as NAME0_0_, this_.PRICE as PRICE0_0_, this_.AMOUNT as AMOUNT0_0_ from ADMIN.PRODUCTS this_ where this_.NAME like ? order by this_.NAME asc
Size is = 1
Name=Jacket
Id=8a95d9c511e2c44e0111e2c456b30002

What am I doing incorrectly?
Thanks,
Roopa

I don't know how you're setting up sortProperty and searchProperty, but the SQL that your first attempt produced looks good to me. Are you using the right Dialect? I'm familiar with only a couple of SQL flavours, but the ones that I know both support using aliases in where and order by clauses (which is what the SQL error seems to be complaining about).

The only other thing in there that looks odd to me is that you've got two variables (sortProperty and searchProperty) which both seem to map to the same alias (y1_). That seems most unlikely: hibernate is quite happy to alias the same column multiple times (when it doesn't need to) if you refer to it in different ways in your Criteria code. This suggests to me that you've omitted some important code that might shed some light on this for me.