hql - elements function
in [Java Programming]
|
From: Jean-Baptiste Nizet on 2 Dec 2009 13:55 Peter Horlock a �crit : > Hi, > > I am using Hibernate with an xml mapping file. > Using hql, I want to retrieve all entries from the db, which match a > certain criteria. > > From what I've found on the web, what I am looking for might be something > like that: > from myObject as m where ? is in elements(m.myJoinedObject.myList) > > > however, that did't work! :-( > In other words, I got an object, let's say a cat, which in Java has a > member, let's say it's owner, and the owner again has a list of vehicles > he/she is driving (doesn't make much sense, but who cares! ;-) > So I want to find all cat's which have an owner which does have a vehicle > I send to the hibernate function like that: > > final Object[] value = new Object[3]; > value[0] = myVehichle; > [...] > hibernateTemplate.findByNamedQuery(query, value); > > any idea how this is done (properly)? > select cat from Cat cat inner join cat.owner owner inner join owner.vehicles vehicle where vehicle = :myVehicle or, if you have the reverse relations select cat from Vehicle vehicle inner join vehicle.driver driver inner join driver.cats cat where vehicle = :myVehicle JB.
From: Peter Horlock on 3 Dec 2009 07:01 Jean-Baptiste Nizet wrote: > select cat from Cat cat > inner join cat.owner owner > inner join owner.vehicles vehicle > where vehicle = :myVehicle > > or, if you have the reverse relations > > select cat from Vehicle vehicle > inner join vehicle.driver driver > inner join driver.cats cat > where vehicle = :myVehicle Thanks for yor help, JB. However, it didn't work for me. :-( what does the "=:" mean anyway? Shouldn't there be a "?" for the object to be inserted? Thanks in advance, Peter
From: Jean-Baptiste Nizet on 3 Dec 2009 07:14 On 3 déc, 13:01, Peter Horlock <peter.horl...(a)googlemail.com> wrote: > Jean-Baptiste Nizet wrote: > > select cat from Cat cat > > inner join cat.owner owner > > inner join owner.vehicles vehicle > > where vehicle = :myVehicle > > > or, if you have the reverse relations > > > select cat from Vehicle vehicle > > inner join vehicle.driver driver > > inner join driver.cats cat > > where vehicle = :myVehicle > > Thanks for yor help, JB. > > However, it didn't work for me. :-( > > what does the "=:" mean anyway? > :myVehicle is a named placeholder. Instead of writing "where vehicle = ?" and binding the parameter with query.setParameter(0, myVehicle); you may write "where vehicle = :myVehicle" and bind the parameter with query.setParameter("myVehicle", myVehicle); It has three advantages over anonymous placeholders (?) : 1. The query is more readable 2. If the same parameter appears multiple times in the same query, you only have to bind it once. 3. The binding is not dependent on the order of the parameters in the query. JB.
From: Peter Horlock on 3 Dec 2009 07:35 > :myVehicle is a named placeholder. Instead of writing "where vehicle > = ?" and binding the parameter with > > query.setParameter(0, myVehicle); > > you may write "where vehicle = :myVehicle" and bind the parameter with > > query.setParameter("myVehicle", myVehicle); > > It has three advantages over anonymous placeholders (?) : > 1. The query is more readable > 2. If the same parameter appears multiple times in the same query, you > only have to bind it once. > 3. The binding is not dependent on the order of the parameters in the > query. > > JB. > > Hi JB, thanks again, very interesting, didn't know... ---- >>> select cat from Cat cat >>> inner join cat.owner owner >>> inner join owner.vehicles vehicle >>> where vehicle = :myVehicle However, do you have any idea why it's not working though? Maybe the problem is "owner.vehicles vehicle"? cause vehicles is a list of entries, but then you use it as if it was just one object, no idea if that makes sense? In SQL the statment is: select * from schema1.cat c join schema1.owner o on(c.otherid= o.id) join schema1.NmownerVehicles n on(o.id=n.otherid) join schema2.vehicles v on(v.id = s.otherid) where v.id=4711; So in database world there is a table(cat) with a foreign key to another table(owner) with a n:m join table(owner_vehicle) which has foreign keys to the owner and vehicle tables. In my Hibernate mapping, the owner table has a set of vehicles: <set name="vehicles" table="NmownerVehicles" cascade="all" lazy="false" order-by="rolle asc"> <key column="id" /> <many-to-many column="id" not-found="ignore" class="com.aa.bb.ccc.Vehicle" /> </set> ---------- Thanks in advance, Peter
From: Jean-Baptiste Nizet on 3 Dec 2009 07:46
On 3 déc, 13:35, Peter Horlock <peter.horl...(a)googlemail.com> wrote: > > :myVehicle is a named placeholder. Instead of writing "where vehicle > > = ?" and binding the parameter with > > > query.setParameter(0, myVehicle); > > > you may write "where vehicle = :myVehicle" and bind the parameter with > > > query.setParameter("myVehicle", myVehicle); > > > It has three advantages over anonymous placeholders (?) : > > 1. The query is more readable > > 2. If the same parameter appears multiple times in the same query, you > > only have to bind it once. > > 3. The binding is not dependent on the order of the parameters in the > > query. > > > JB. > > Hi JB, thanks again, very interesting, didn't know... > ---- > > >>> select cat from Cat cat > >>> inner join cat.owner owner > >>> inner join owner.vehicles vehicle > >>> where vehicle = :myVehicle > > However, do you have any idea why it's not working though? > Maybe the problem is "owner.vehicles vehicle"? > cause vehicles is a list of entries, but then you use it as if > it was just one object, no idea if that makes sense? > > In SQL the statment is: > select * from schema1.cat c join schema1.owner o on(c.otherid= o.id) join > schema1.NmownerVehicles n on(o.id=n.otherid) join schema2.vehicles v > on(v.id = s.otherid) where v.id=4711; > This query is not valid. "on(v.id = s.otherid)" should be "on v.id = n.theIdPointingToTheVehicleTable". > So in database world there is a table(cat) with a foreign key to another > table(owner) > with a n:m join table(owner_vehicle) which has foreign keys to the owner > and vehicle tables. > > In my Hibernate mapping, the owner table has a set of vehicles: > <set name="vehicles" table="NmownerVehicles" cascade="all" lazy="false" > order-by="rolle asc"> > <key column="id" /> > <many-to-many column="id" not-found="ignore" > class="com.aa.bb.ccc.Vehicle" /> > </set> > ---------- I don't use XML to write my mappings, but annotations, so I can't say if there is a problem or not with your mapping. But you keep saying "It's not working". If you're not more descriptive about the problem you have, we won't be able to help you. What's the problem ? 1. You get an exception when executing the HQL query. If it's the case, what's the message stack trace of the exception 2. The query executes fine, but doesn't return what you expect. If it's the case, what is in the database and what is returned? Which SQL query is generated by Hibernate? > Thanks in advance, > > Peter |