-
1. Re: JPQL - Query Parameter
dgeraskov May 14, 2009 9:25 AM (in response to israel.bgf)Try to play with Count() aggregate function.
For example
"from ObjectA o where COUNT(o.list) >=:param" -
2. Re: JPQL - Query Parameter
dgeraskov May 14, 2009 9:28 AM (in response to israel.bgf)Ah, I understood question incorrectly.
-
3. Re: JPQL - Query Parameter
israel.bgf May 14, 2009 9:47 AM (in response to israel.bgf)Hmm,any Idea so?
you can imagine ObjectA being a Product, and ObjectB a Category. I wants all the products that have X categories, remembering that a product can have one or more categories. If i want categories: "cheap, fruit", it would bring at least cheap fruit, but it could be a "imported" or "diet" one.
(Silly example i know. :)) -
4. Re: JPQL - Query Parameter
dgeraskov May 14, 2009 10:06 AM (in response to israel.bgf)May be something like this:
"from ObjectA o where o.id in
(select distinct b.fk_objectA from ObjectB.fk_objectA b where b.id in :param)" -
5. Re: JPQL - Query Parameter
dgeraskov May 14, 2009 10:20 AM (in response to israel.bgf)Check also if it possible for :param with List of ObjectB elements
from ObjectA a, ObjectB b where :param member of a.list -
6. Re: JPQL - Query Parameter
israel.bgf May 14, 2009 1:49 PM (in response to israel.bgf)I tryied your two tips and both didnt work. After tweaking the first one a bit, it started to compile. But it still not doing what i want. My SQL/JPQL skills are not very good so i probabbly did something wrong.
Here is my test class and entity classes.
------------ Test-----------------------package entity; import java.util.ArrayList; import java.util.List; import javax.persistence.EntityManager; import javax.persistence.EntityManagerFactory; import javax.persistence.Persistence; public class ObjectTest { public static void main(String[] args) { EntityManagerFactory emf = Persistence.createEntityManagerFactory("persistenceUnit"); EntityManager em = emf.createEntityManager(); ObjectA a1 = new ObjectA(); ObjectA a2 = new ObjectA(); ObjectB b1 = new ObjectB(); ObjectB b2 = new ObjectB(); ObjectB b3 = new ObjectB(); a1.getList().add(b1); a1.getList().add(b2); a2.getList().add(b3); em.getTransaction().begin(); em.persist(a1); em.persist(a2); em.getTransaction().commit(); List<ObjectB> param = new ArrayList<ObjectB>(); param.add(b1); param.add(b2); //"from ObjectA o where o.id in(select distinct b.fk_objectA from ObjectB.fk_objectA b where b.id in :param)" //"from ObjectA o where o in(select distinct b from ObjectB b where b in(:param))" List<ObjectA> list = em.createQuery("from ObjectA o where o in(select b from ObjectB b where b in(:param))").setParameter("param", param).getResultList(); for (ObjectA t : list) { System.out.println(t); } } }
---------------- Entityies -----------------------package entity; import java.util.ArrayList; import java.util.List; import javax.persistence.CascadeType; import javax.persistence.Entity; import javax.persistence.GeneratedValue; import javax.persistence.GenerationType; import javax.persistence.Id; import javax.persistence.OneToMany; @Entity public class ObjectA { @Id @GeneratedValue(strategy = GenerationType.AUTO) private Long id; @OneToMany(mappedBy="objectA",cascade=CascadeType.ALL) private List<ObjectB> list = new ArrayList<ObjectB>(); public Long getId() { return id; } public void setId(Long id) { this.id = id; } public List<ObjectB> getList() { return list; } public void setList(List<ObjectB> list) { this.list = list; } @Override public String toString() { String tmp = "id: "+id+"\nlist:\n"; for(ObjectB t : list){ tmp += "id:"+ t.getId() +"\n"; } return tmp+="\n"; } @Override public int hashCode() { final int prime = 31; int result = 1; result = prime * result + ((id == null) ? 0 : id.hashCode()); return result; } @Override public boolean equals(Object obj) { if (this == obj) return true; if (obj == null) return false; if (getClass() != obj.getClass()) return false; ObjectA other = (ObjectA) obj; if (id == null) { if (other.id != null) return false; } else if (!id.equals(other.id)) return false; return true; } }
package entity; import javax.persistence.Entity; import javax.persistence.GeneratedValue; import javax.persistence.GenerationType; import javax.persistence.Id; import javax.persistence.ManyToOne; @Entity public class ObjectB { @Id @GeneratedValue(strategy = GenerationType.AUTO) private Long id; @ManyToOne private ObjectA objectA; public Long getId() { return id; } public void setId(Long id) { this.id = id; } public void setObjectA(ObjectA objectA) { this.objectA = objectA; } public ObjectA getObjectA() { return objectA; } @Override public int hashCode() { final int prime = 31; int result = 1; result = prime * result + ((id == null) ? 0 : id.hashCode()); return result; } @Override public boolean equals(Object obj) { if (this == obj) return true; if (obj == null) return false; if (getClass() != obj.getClass()) return false; ObjectB other = (ObjectB) obj; if (id == null) { if (other.id != null) return false; } else if (!id.equals(other.id)) return false; return true; } }
-
7. Re: JPQL - Query Parameter
dgeraskov May 15, 2009 3:10 AM (in response to israel.bgf)Try this one:
em.createQuery("from ObjectA o where o in(select b.ObjectA from ObjectB b where b in(:param))").setParameter("param", param).getResultList(); -
8. Re: JPQL - Query Parameter
israel.bgf May 15, 2009 8:41 AM (in response to israel.bgf)Thank you! Now that worked. Now i can continue my studies... until my next problem. :)
Thank you again,
Israel