Hi,

I have two objects A, B having getBs() and getAs() respectively, and I've successfully mapped them as bidirectional many-to-many with three tables A, B, and A_B.

I now wish to find all As who has a B of a particular id, but I have no idea how to do it through HQL. Session.find("from A where A.bs....?")

Normally with SQL I can simply do "select A_id from A_B where B_id = ?". Those As would be the ones I want.

Thank you.

Try

Forget it, it's a mistake.


Hibernate Team, I have a question for you, if I want to do the request by comparing a property instead of id, how can I do.

Actually I don't really understand the reference guide sample

what property of score in compared to 3 ?

That example uses a collection of -values-.

If you want to compare a propety, use a full subquery. However, these queries can be rewritten to just use a join, and that is preferred.

Thanks for the quick reply.
But hmm it's more complex that i thought. I hope the SQL issued by Hibernate is efficient in this case.

It's not complex

I'm still having problem executing the query, my code is as followed :

// Applicant is A, Job is B
Job theJob = new Job();
theJob.setId(new Long(7)); // desired id is 7
List apps = ses.find("from " + Applicant.class.getName() + " as app where ? in elements(app.jobs)", theJob, Hibernate.entity(Job.class));
out.println("There are " + apps.size());

I get this exception :
Could not execute query: Syntax error or access violation: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'select jobs1_.jobId from Job_Applicant jobs1_ where applican0_.

Is it that MySQL does not support the operation?

Also, I think correct sql to be issued by Hibernate should be
"select distinct a.id from A as a join A_B as ab where ab.b_id = ?""
instead of
"select distinct a from A as a join B as b where b.id = ?"

My last query is an HQL query, not an (My)SQL one

I never tried myself the 'in elements' query, my qusry may be wrong. Use (HQL query). Its is more simple and as Gavin said, it preferable.

Beware the join you want (outer, inner) Have a look at the Hibernate reference guide section 9.3.

Finally got it working with a small difference:
"from A as a inner join a.bs as bs where bs.id=?"

but took some time to figure out that binding variables start at 0 instead of 1! :O whew

You may want to consider using named paramters in your queries like:

There are several nice things about named parameters.

1. They're self documenting
2. You can use them more than once in a query
3. You don't have to worry about the order in which a paramater appears
4. In a long query with multiple parameters, you're far less likely to make a mistake (i.e. identify a parameter as number 3 when it was supposed to be number 4)
5. If you modify the query later by adding a new parameter in the middle somewhere, you won't have to renumber the parameters which follow it.

For me, number 1 and 4 are the most important.

-Matt