1 Reply Latest reply on Oct 21, 2006 10:35 AM by Laszlo Fogas

    Using SQL IN

    Monkey Den Master

      What is the proper way to do a query using SQL "IN"? The only reference I found is this: http://www.jofti.com/guide/ch3-1.html#title_3_1_8, which is complaining about the colon for the parameter.

      unexpected token: : near line 1, column 54 [from com.xxxxx.entity.Listing where id IN :ids]

      id is the primary key of Listing. My code is as follows:

      @Entity
      @Name("listing")
      @Table(name = "LISTING")
      public class Listing implements java.io.Serializable {
      
       @Id
       @Column(name = "LISTING_ID", length = 22, nullable = false)
       public Long getId() {
       return this.id;
       }
       ...
      }



      List<Long> glids = new ArrayList<Long>();
      
      String queryStr = "from Listing where id IN :ids";
      Query query = em.createQuery(queryStr);
      query.setParameter("ids", glids);
      Object result = query.getResultList();


        • 1. Re: Using SQL IN
          Laszlo Fogas Newbie

           

          "monkeyden" wrote:
          What is the proper way to do a query using SQL "IN"? The only reference I found is this: http://www.jofti.com/guide/ch3-1.html#title_3_1_8, which is complaining about the colon for the parameter.

          unexpected token: : near line 1, column 54 [from com.xxxxx.entity.Listing where id IN :ids]

          id is the primary key of Listing. My code is as follows:

          @Entity
          @Name("listing")
           @Table(name = "LISTING")
          public class Listing implements java.io.Serializable {
          
           @Id
           @Column(name = "LISTING_ID", length = 22, nullable = false)
           public Long getId() {
           return this.id;
           }
           ...
          }



          List<Long> glids = new ArrayList<Long>();
          
          String queryStr = "from Listing where id IN :ids";
          Query query = em.createQuery(queryStr);
          query.setParameter("ids", glids);
          Object result = query.getResultList();


          are you sure, that you can pass a list to the query?

          I don't if it handles the list or not, but i think you can generate the query string dinamically:

          String queryStr = "from Listing where id IN (";
          for(Long l: glids){
          queryStr += l.toString+", ";
          }
          queryStr += ") ";

          should work..