I'm trying to write a HQL query and am I little confused.

I have one table IntellectualPropery that is assoicated another Usage in a one to many relationship (i.e) IntellectualPropery can have many Usage records but each Usage record is assoicated to an single IntellectualProperty record.

I want to perform a search to find all IntellectualPropery records by a field in Usage called Project or by a number of Projects.

The query string that I've written is :

public List getIntellectualPropertysByProject(Object[] projectIds) {
return getHibernateTemplate().find("select ip from IntellectualProperty as ip join ip.usages as usage where usage.project = :ids", projectIds);
}

But I'm getting the error listed below. Any ideas or pointers?


No positional parameters in query: select ip from IntellectualProperty as ip join ip.usages as usage where usage.project = :ids

Kind Regards,
Barry

Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version:1.2

Mapping documents:

Full stack trace of any exception that occurs:<br/>

No positional parameters in query: select ip from IntellectualProperty as ip join ip.usages as usage where usage.project = :ids

java.lang.IllegalArgumentException: No positional parameters in query: select ip from IntellectualProperty as ip join ip.usages as usage where usage.project = :ids at net.sf.hibernate.impl.AbstractQueryImpl.setParameter(AbstractQueryImpl.java:142) at net.sf.hibernate.impl.AbstractQueryImpl.setParameter(AbstractQueryImpl.java:385) at org.springframework.orm.hibernate.HibernateTemplate$24.doInHibernate(HibernateTemplate.java:438) at org.springframework.orm.hibernate.HibernateTemplate.execute(HibernateTemplate.java:176) at org.springframework.orm.hibernate.HibernateTemplate.executeFind(HibernateTemplate.java:196) at org.springframework.orm.hibernate.HibernateTemplate.find(HibernateTemplate.java:434) at au.edu.tlf.lips.persistence.hibernate.IntellectualPropertyDAOHibernate.getIntellectualPropertysByProject(IntellectualPropertyDAOHibernate.java:79) at au.edu.tlf.lips.persistence.IntellectualPropertyDAOTest.testGetIntellectualPropertysByProject(IntellectualPropertyDAOTest.java:149) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

Name and version of the database you are using:MySQL 4.1

Use a positional parameter, ie. "?"

I have tried that option and it work fine if I replace the :ids with a "?" for when the object array only contains one value but once there is > 1 items in the object array it fails with the following error

Positional parameter does not exist: 1 in query: select ip from IntellectualProperty as ip join ip.usages as usage where usage.project = ?

java.lang.IllegalArgumentException: Positional parameter does not exist: 1 in query: select ip from IntellectualProperty as ip join ip.usages as usage where usage.project = ? at net.sf.hibernate.impl.AbstractQueryImpl.setParameter(AbstractQueryImpl.java:145) at net.sf.hibernate.impl.AbstractQueryImpl.setParameter(AbstractQueryImpl.java:385) at org.springframework.orm.hibernate.HibernateTemplate$24.doInHibernate(HibernateTemplate.java:438) at org.springframework.orm.hibernate.HibernateTemplate.execute(HibernateTemplate.java:176) at org.springframework.orm.hibernate.HibernateTemplate.executeFind(HibernateTemplate.java:196) at org.springframework.orm.hibernate.HibernateTemplate.find(HibernateTemplate.java:434) at au.edu.tlf.lips.persistence.hibernate.IntellectualPropertyDAOHibernate.getIntellectualPropertysByProject(IntellectualPropertyDAOHibernate.java:79) at au.edu.tlf.lips.persistence.IntellectualPropertyDAOTest.testGetIntellectualPropertysByProject(IntellectualPropertyDAOTest.java:149) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

Use Hibernate APIs. We don't recommend HibernateTemplate.

To be clear; are you trying to search for an id in a set of ids?

As far as i can say you cannot use = criteria with a list. Instead an IN should be used. This is gonna work for both 1 value or N values.

:alex |.::the_mindstorm::.|

I am trying to search for all records in the IntellectaulProperty table that have Usage records with a value in the Usage record contained in the list that I am passing to the query, that value being a ProjectID

I guess the query could be written as an OR (i.e) if Object[] size was 3 with values {1,2,3} I could build a query something like



But I just wonder if there is a better hibernate solution?

As Gavin said, don't wrap and hide perfectly usable APIs behind unnecessary wrapper frameworks.

And you probably want "where usage.project.id in ..." if you don't handle entity objects but pass keys around.

Why aren't you able to try it with IN operator and named list parameter? ( IN ( :myids ) ).

:alex |.::the_mindstorm::.|

ha... seems this time i haven't screwed the answer :)

:alex |.::the_mindstorm::.|

What package is the HibernateUtil class part of? I cannot access / find it when I try to import it?

It's at least in two places in the reference documentation. Also here:

http://www.hibernate.org/42.html
http://www.hibernate.org/43.html