8 Replies Latest reply on May 15, 2009 8:41 AM by Israel Fonseca

    JPQL - Query Parameter

    Israel Fonseca Apprentice

      Supose the following situation:

      class ObjectA

      Integer id;
      List list;

      How can i make a JPQL that retrives a list of ObjectA that have at least N ObjectB. N is variable number. Example:

      table ObjectA
      [id]
      1
      2
      ------------------
      table ObjectB
      [id] [fk_objectA]
      1 1
      2 1
      3 2

      My search method would be...

      List search(List param){
      return em.createQuery("from ObjectA o where o have at least these :param").setParameter("param",param).getResultlist()
      }

      If param is a list with ObjectB(id=1) it would retrieve: ObjectA(id=1), the same would be true if param was a list with [ObjectB(id=1),ObjectB(id=2)]

      Off course that this query don't work, but i want to do something like that. Is it possible? Or do i have to make a lot of string concatenation?

      Thanks!

      Israel

        • 1. Re: JPQL - Query Parameter
          Dmitry Geraskov Newbie

          Try to play with Count() aggregate function.
          For example
          "from ObjectA o where COUNT(o.list) >=:param"

          • 2. Re: JPQL - Query Parameter
            Dmitry Geraskov Newbie

            Ah, I understood question incorrectly.

            • 3. Re: JPQL - Query Parameter
              Israel Fonseca Apprentice

              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
                Dmitry Geraskov Newbie

                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
                  Dmitry Geraskov Newbie

                  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 Fonseca Apprentice

                    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
                      Dmitry Geraskov Newbie

                      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 Fonseca Apprentice

                        Thank you! Now that worked. Now i can continue my studies... until my next problem. :)

                        Thank you again,

                        Israel