I have two tables in a many to many relationship. User and Form

I'm trying to create something similar to this:



w/ the only exception being that I only need user data.

I've tried:



where uids is a comma separated list of ids. (doesn't work)

Which gives me the error:


where uids is a comma separated set of ids like: 'id1','id5','id7','id31'.

I've also tried the Query by Criteria:

as:


The latter gives me wrong # of parameters. The SQL generated is:

select this.frm_id as frm_id4_, this.frm_nam as frm_nam4_, this.frm_usg as frm_usg4_, this.bbd_id as bbd_id4_, this.dpt_id as dpt_id4_, this.lst_upd_ts as lst_upd_ts4_, this.crt_ts as crt_ts4_, this.upd_usr_id as upd_usr_id4_, this.crt_usr_id as crt_usr_id4_, bulletinbo1_.bbd_id as bbd_id0_, bulletinbo1_.bbd_nam as bbd_nam0_, bulletinbo1_.bbd_usg as bbd_usg0_, bulletinbo1_.lst_ts as lst_ts0_, bulletinbo1_.dpt_id as dpt_id0_, bulletinbo1_.lst_upd_ts as lst_upd_ts0_, bulletinbo1_.crt_ts as crt_ts0_, bulletinbo1_.upd_usr_id as upd_usr_id0_, bulletinbo1_.crt_usr_id as crt_usr_id0_, department2_.dpt_id as dpt_id1_, department2_.dpt_dsc_tx as dpt_dsc_tx1_, department2_.lst_upd_ts as lst_upd_ts1_, department2_.crt_ts as crt_ts1_, department2_.upd_usr_id as upd_usr_id1_, department2_.crt_usr_id as crt_usr_id1_, department3_.dpt_id as dpt_id2_, department3_.dpt_dsc_tx as dpt_dsc_tx2_, department3_.lst_upd_ts as lst_upd_ts2_, department3_.crt_ts as crt_ts2_, department3_.upd_usr_id as upd_usr_id2_, department3_.crt_usr_id as crt_usr_id2_, formconten4_.fc_id as fc_id3_, formconten4_.fie_nam_tx as fie_nam_tx3_, formconten4_.content as content3_ from form this left outer join bulletin_board bulletinbo1_ on this.bbd_id=bulletinbo1_.bbd_id left outer join department department2_ on bulletinbo1_.dpt_id=department2_.dpt_id left outer join department department3_ on this.dpt_id=department3_.dpt_id left outer join form_content formconten4_ on this.frm_id=formconten4_.fc_id where this.frm_id=? and this.frm_id in (?, ?)

Note, I left out some of the extra tables and attributes.


I've also been messing around with the criteria further to try:



This returned a Form which was not what I was looking for. I'm looking for the Users.

I suspect I might need a bidirectional relationship from User back to Form with an formsAuthored property to make this work.

I'm using Hibernate Version 2.1 and DB2 7.2.



Thanks for your time.

P.S. I've also tried the HQL which used:



and the attribute set was a list of IDs Still no luck

Did you really type that?? Or was it actually:



Cos both appear above, and the second is clearly wrong.

Gavin, sorry about that. I did a string replace on the packages, but I left off the "."

From the exception where you see it. it should have been: test.Form form, test.User user.

I've read through the documentation again and I verified that my use of the in clause is correct (or at least I thought so).

I added a reverse association form User back to Form and called the property (formsAuthored).

I modified my query as follows:

Criteria criteria = session.createCriteria(User.class).setFetchMode("formsAuthored", FetchMode.EAGER).add(
Expression.in("id", listIds(ldapUsers))).createCriteria("formsAuthored").add(Expression.eq("id", formId));

I think I was getting caught up in what was being returned. In my prior criteria attempts, I realized I was getting a Form back as the result. I then went back to the docs and saw that the authors were not filtered unless I use returnMap() method (which is deprecated in favor of setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP).

When I ran this, however and tried to access the authors property of the form, for whatever reason, I couldn't get them.

I should explain a little further. A hashMap was returned, and one Form was in it (because I limited the results to one form using the id), but when I iterated through the array, all of the authors of the form came back, instead of only the ones in the IN clause.

Criteria criteria = session.createCriteria(Form.class).add(
Expression.eq("id", formId)).createCriteria("authors").add(
Expression.in("id", listIds(ldapUsers))).setResultTransformer(
Criteria.ALIAS_TO_ENTITY_MAP);
// Criteria criteria =
// session.createCriteria(User.class).add(Expression.in("id",
// listIds(ldapUsers)));
List entities = criteria.list();
for (Iterator iter = entities.iterator(); iter.hasNext(); ) {
Map map = (Map) iter.next();
Collection values = map.values();
for (Iterator iter2 = values.iterator(); iter2.hasNext(); ) {
Form form = (Form) iter2.next();
Set authors = form.getAuthors();
for (Iterator iter3 = authors.iterator(); iter3.hasNext();) {
User user = (User) iter3.next();
System.out.println(user); // Outputs ALL of the authors instead of just the ones in the IN clause

}
}

}

Hi

I have exactly the same problem.
Did you solve it? How?
I´m using currently Hibernate 3.0

Any help is wellcome.

This works for me on Hibernate 3.0.5:



For the :merchants parameter, I pass an Array of Merchant instances. I seem to recall having to change this at some point from using a Collection, but the details are foggy.

HTH,

Chris