2 Replies Latest reply on May 6, 2005 3:36 PM by ryoung2504

    NamedQuery with IN bind variable

    ryoung2504

      I have the following named query at the top of my @Entity annottated class:-

       @NamedQuery(name = "InnerTemplate_find", queryString = "SELECT record FROM InnerTemplate AS record WHERE securityRole IN (:securityRoles)")
      


      I have tried

      this.manager.createNamedQuery("InnerTemplate_find").setParameter("securityRoles", "'a','b'").getResultList();
      


      and also

      List<String> roles = new ArrayList<String>();
      roles.add('a');
      roles.add('b');
      this.manager.createNamedQuery("InnerTemplate_find").setParameter("securityRoles", roles).getResultList();
      


      but neither seem to work.

      It is possible to pass an array of strings into a named query as a parameter? Have I got the syntax for the named query wrong?

      Note: the size and contents of the array will vary dynamically at runtime and thus
      IN ('a','b')

      will not suffice.

        • 1. Re: NamedQuery with IN bind variable
          bill.burke

          What you are trying to do is a dynamic query. Think of a @NamedQuery as a PreparedStatement.

          • 2. Re: NamedQuery with IN bind variable
            ryoung2504

            Thanks,

            I've worked out a solution that takes the named query, gets the query language string from it, replaces the IN parameter and generates another query from the result. This works for me but doesn't seem too efficient.


            query = this.manager.createNamedQuery(queryString);
            org.hibernate.Query hbQuery = ((HibernateQuery) query).getHibernateQuery();
            query = this.manager.createQuery(hbQuery.getQueryString().replaceAll(":securityRoles", roles.toString()));