many to one, in clause

sg3 · **Posted:** Wed Dec 10, 2003 2:20 pm

I have a many to one relationship between a table with demographic data and one with geographic areas. So there is a population figure, a housing figure, a per capita income figure for any one geographic area. In my application I get a set of data figures requested for a set of geographic indicators. So I should get back the number geographic regions * the number of data figures requested. In MYSQL this query works fine:

SELECT *
FROM `demoData` where
geospatialID in ("1","2") and
demoDataID in ("3","4")

geospatialID is the foreign key to the other table, although I have not added this constraint. Anyway this gives me four rows all distinct. Exactly what I expect.

The following find call though gives me the same geographic object data for every row-the first one:

session.find(DemoData where geoSpatialID in" + geoSpatialIDList + "and client=? and DemoID in" + demoList)

In my hbm.xml for the DemoData I have this to get the many to one relationship
<many-to-one name="geoSpatialTag" class="GeoSpatialTag" column="geoSpatialID"/>

If I actually get a geoSpatialTag object through a join as opposed to the composed geoSpatialTag within the DemoData - this works. But I thought the many-to-one tag should handle the composed object.

I am very new to hibernate - a week maybe and I hope I am not missing something obvious. I have assumed I am missing something in the HQL query. Is there something I need to do with forreign key constraints or indexes? Is the in clause not the way to do this- just seemed since I am getting multiple values from the user this would work. Thank you.

gavin · **Posted:** Wed Dec 10, 2003 6:42 pm

Your query is not valid HQL. HQL uses object property names, ALWAYS qualified by an entity alias.

Also, you should never use string manipulations to bind parameter values.

As for the rest, I don't quite understand....

Have another look over the examples in the documentation.

sg3 · **Posted:** Thu Dec 11, 2003 11:44 am

Thanks so much for your reply. Is there a way in HQL to bind an object containing parameters, that is not a predefined domain object, and use it with an in clause? I see how you do that with a string. Sorry if that is obvious, I didn't see it in the doc. Again, I'm just getting started with this.

emmanuel · **Posted:** Fri Dec 12, 2003 6:26 am

I don't think. What would be the usage of that ?
You can map, "classic" types, custom types, entity, components ( ? I'm not sure of that).

michael · **Posted:** Fri Dec 12, 2003 6:32 am

I dont get exactly what you mean. Do you mean something like that:

Code:

List names = new ArrayList();
names.add("Izi");
names.add("Fritz");
Query q = sess.createQuery("from DomesticCat cat where cat.name in (:namesList)");
q.setParameterList("namesList", names);
List cats = q.list();

sg3 · **Posted:** Fri Dec 12, 2003 11:36 am

Thanks for your replies. Thats exactly what I mean - it might be an array of Strings or could be Collection. They are user parameters - not an object I want to have mapped really. Does that syntax work?

sg3 · **Posted:** Fri Dec 12, 2003 11:51 am

Sorry - now I see it - I missed that in the doc - many thanks. That is really nice how that works.