5 Replies Latest reply on May 2, 2006 4:50 PM by Jean-Paul Huybens

    Checking a field against a list of values

    guidoscalise Newbie

      Hi there guys!,
      I need to create a EJBQL or JBossQL query that receives a list of values as an argument, and checks a field against that list of values...
      Do I have any way of doing that in any of the QL implementations?

      It's equivalent in SQL would be like:

      SELECT * FROM Something where field IN(1,21,32,34,45,63);
      


      I'm afraid that I'll have to do N queries for N values...

      Kind Regards,
      Guido Scalise
      Buenos Aires - Argentina

        • 1. Re: Checking a field against a list of values
          sesques Novice

          HI,

          JBossQL supports parameters in the IN operator. But is the number of your parameters always the same ?

          If not, you should use DynamicQL instead.

          Pascal

          • 2. Re: Checking a field against a list of values
            guidoscalise Newbie

             

            "sesques" wrote:
            HI,

            JBossQL supports parameters in the IN operator. But is the number of your parameters always the same ?

            If not, you should use DynamicQL instead.

            Pascal


            Thanks for your prompt reply!
            No, the list of parameters varies... :(
            I've been searching all the web looking for DynamycQL/JBossQL examples/tutorials/documentation, but I haven't found anything... Where can I access some (JBoss/Dynamic)QL documentation?

            Kind Regards,
            Guido Scalise
            Buenos Aires - Argentina

            • 3. Re: Checking a field against a list of values
              sesques Novice

              Hi,

              Perhaps a bad news to you. JBossQL and DynamicQL is documented in the JBoss DOCO, and you have to buy it.
              The DOCO is "JBoss AdminDevel Documentation".

              Pascal

              • 4. Re: Checking a field against a list of values
                Hernan Terzian Newbie

                Que haces Guido,
                te paso un ejemplo de un entity bean usando xdoclet:

                a nivel de clase (despues de los imports) pone estos tags:

                * @ejb.select
                * signature="java.util.Collection ejbSelectGeneric( java.lang.String jbossql, java.lang.Object[] args )"
                * result-type-mapping="Local"
                * method-intf="LocalHome"
                * query=""
                * @jboss.query
                * signature="java.util.Collection ejbSelectGeneric( java.lang.String jbossql, java.lang.Object[] args )"
                * dynamic="true"
                *

                despues agregás estos metodos al entity mismo:

                /**
                * @jboss.dynamic-ql
                */
                public abstract java.util.Collection ejbSelectGeneric ( String jbossQl, Object[]arguments ) throws FinderException;

                /**
                * @ejb.home-method
                * tview-type="local"
                */
                public java.util.Collection ejbHomeBusquedasGenericas(
                String node_name, String type, String alias_name, String description, String login, String passwd, String transfer_dir, String host_alias, String operational_status, String ip_address, String cv_id, String node_type ) throws javax.ejb.FinderException{

                java.util.Collection params = new java.util.ArrayList();
                int paramNumber = 1;
                boolean yaHayUno = false;

                // generate JBossQL query
                StringBuffer jbossQl = new StringBuffer();
                jbossQl.append("SELECT OBJECT(u) ");
                jbossQl.append("FROM Dyt_com_nodeSchema AS u ");

                if (
                (node_name!=null
                && !node_name.equalsIgnoreCase("todos")
                && !node_name.equalsIgnoreCase("")
                )
                || (type!=null
                && !type.equalsIgnoreCase("todos")
                && !type.equalsIgnoreCase("")
                )
                || (alias_name!=null
                && !alias_name.equalsIgnoreCase("todos")
                && !alias_name.equalsIgnoreCase("")
                )
                || (description!=null
                && !description.equalsIgnoreCase("todos")
                && !description.equalsIgnoreCase("")
                )
                || (login!=null
                && !login.equalsIgnoreCase("todos")
                && !login.equalsIgnoreCase("")
                )
                || (passwd!=null
                && !passwd.equalsIgnoreCase("todos")
                && !passwd.equalsIgnoreCase("")
                )
                || (transfer_dir!=null
                && !transfer_dir.equalsIgnoreCase("todos")
                && !transfer_dir.equalsIgnoreCase("")
                )
                || (host_alias!=null
                && !host_alias.equalsIgnoreCase("todos")
                && !host_alias.equalsIgnoreCase("")
                )
                || (operational_status!=null
                && !operational_status.equalsIgnoreCase("todos")
                && !operational_status.equalsIgnoreCase("")
                )
                || (ip_address!=null
                && !ip_address.equalsIgnoreCase("todos")
                && !ip_address.equalsIgnoreCase("")
                )
                || (cv_id!=null
                && !cv_id.equalsIgnoreCase("todos")
                && !cv_id.equalsIgnoreCase("")
                )
                || (node_type!=null
                && !node_type.equalsIgnoreCase("todos")
                && !node_type.equalsIgnoreCase("")
                )
                )
                {
                jbossQl.append("WHERE");
                }

                if (node_name != null
                && !node_name.equalsIgnoreCase("todos")
                && !node_name.equalsIgnoreCase("")
                ){
                if (yaHayUno) jbossQl.append(" AND");
                jbossQl.append(" u.node_name=?" + paramNumber++);
                params.add(node_name);
                yaHayUno=true;
                }
                if (type != null
                && !type.equalsIgnoreCase("todos")
                && !type.equalsIgnoreCase("")
                ){
                if (yaHayUno) jbossQl.append(" AND");
                jbossQl.append(" u.type=?" + paramNumber++);
                params.add(type);
                yaHayUno=true;
                }
                if (alias_name != null
                && !alias_name.equalsIgnoreCase("todos")
                && !alias_name.equalsIgnoreCase("")
                ){
                if (yaHayUno) jbossQl.append(" AND");
                jbossQl.append(" u.alias_name=?" + paramNumber++);
                params.add(alias_name);
                yaHayUno=true;
                }
                if (description != null
                && !description.equalsIgnoreCase("todos")
                && !description.equalsIgnoreCase("")
                ){
                if (yaHayUno) jbossQl.append(" AND");
                jbossQl.append(" u.description=?" + paramNumber++);
                params.add(description);
                yaHayUno=true;
                }
                if (login != null
                && !login.equalsIgnoreCase("todos")
                && !login.equalsIgnoreCase("")
                ){
                if (yaHayUno) jbossQl.append(" AND");
                jbossQl.append(" u.login=?" + paramNumber++);
                params.add(login);
                yaHayUno=true;
                }
                if (passwd != null
                && !passwd.equalsIgnoreCase("todos")
                && !passwd.equalsIgnoreCase("")
                ){
                if (yaHayUno) jbossQl.append(" AND");
                jbossQl.append(" u.passwd=?" + paramNumber++);
                params.add(passwd);
                yaHayUno=true;
                }
                if (transfer_dir != null
                && !transfer_dir.equalsIgnoreCase("todos")
                && !transfer_dir.equalsIgnoreCase("")
                ){
                if (yaHayUno) jbossQl.append(" AND");
                jbossQl.append(" u.transfer_dir=?" + paramNumber++);
                params.add(transfer_dir);
                yaHayUno=true;
                }
                if (host_alias != null
                && !host_alias.equalsIgnoreCase("todos")
                && !host_alias.equalsIgnoreCase("")
                ){
                if (yaHayUno) jbossQl.append(" AND");
                jbossQl.append(" u.host_alias=?" + paramNumber++);
                params.add(host_alias);
                yaHayUno=true;
                }
                if (operational_status != null
                && !operational_status.equalsIgnoreCase("todos")
                && !operational_status.equalsIgnoreCase("")
                ){
                if (yaHayUno) jbossQl.append(" AND");
                jbossQl.append(" u.operational_status=?" + paramNumber++);
                params.add(operational_status);
                yaHayUno=true;
                }
                if (ip_address != null
                && !ip_address.equalsIgnoreCase("todos")
                && !ip_address.equalsIgnoreCase("")
                ){
                if (yaHayUno) jbossQl.append(" AND");
                jbossQl.append(" u.ip_address=?" + paramNumber++);
                params.add(ip_address);
                yaHayUno=true;
                }
                if (cv_id != null
                && !cv_id.equalsIgnoreCase("todos")
                && !cv_id.equalsIgnoreCase("")
                ){
                if (yaHayUno) jbossQl.append(" AND");
                jbossQl.append(" u.cv_id=?" + paramNumber++);
                params.add(cv_id);
                yaHayUno=true;
                }
                if (node_type != null
                && !node_type.equalsIgnoreCase("todos")
                && !node_type.equalsIgnoreCase("")
                ){
                if (yaHayUno) jbossQl.append(" AND");
                jbossQl.append(" u.node_type=?" + paramNumber++);
                params.add(node_type);
                yaHayUno=true;
                }

                Object[] args = params.toArray(new Object[params.size()]);

                //ejecuto ejbql
                Collection col=ejbSelectGeneric ( jbossQl.toString(), args );

                //si la consulta anterior no me devolvio ningun registro, pruebo con los LIKE
                if(col.size()==0)
                {
                paramNumber = 1;
                yaHayUno = false;
                // generate JBossQL query
                jbossQl = new StringBuffer();
                jbossQl.append("SELECT OBJECT(u) ");
                jbossQl.append("FROM Dyt_com_nodeSchema AS u ");
                if (
                (node_name!=null
                && !node_name.equalsIgnoreCase("todos")
                && !node_name.equalsIgnoreCase("")
                )
                || (type!=null
                && !type.equalsIgnoreCase("todos")
                && !type.equalsIgnoreCase("")
                )
                || (alias_name!=null
                && !alias_name.equalsIgnoreCase("todos")
                && !alias_name.equalsIgnoreCase("")
                )
                || (description!=null
                && !description.equalsIgnoreCase("todos")
                && !description.equalsIgnoreCase("")
                )
                || (login!=null
                && !login.equalsIgnoreCase("todos")
                && !login.equalsIgnoreCase("")
                )
                || (passwd!=null
                && !passwd.equalsIgnoreCase("todos")
                && !passwd.equalsIgnoreCase("")
                )
                || (transfer_dir!=null
                && !transfer_dir.equalsIgnoreCase("todos")
                && !transfer_dir.equalsIgnoreCase("")
                )
                || (host_alias!=null
                && !host_alias.equalsIgnoreCase("todos")
                && !host_alias.equalsIgnoreCase("")
                )
                || (operational_status!=null
                && !operational_status.equalsIgnoreCase("todos")
                && !operational_status.equalsIgnoreCase("")
                )
                || (ip_address!=null
                && !ip_address.equalsIgnoreCase("todos")
                && !ip_address.equalsIgnoreCase("")
                )
                || (cv_id!=null
                && !cv_id.equalsIgnoreCase("todos")
                && !cv_id.equalsIgnoreCase("")
                )
                || (node_type!=null
                && !node_type.equalsIgnoreCase("todos")
                && !node_type.equalsIgnoreCase("")
                )
                )
                {
                jbossQl.append("WHERE");
                }

                if (node_name != null
                && !node_name.equalsIgnoreCase("todos")
                && !node_name.equalsIgnoreCase("")
                ){
                if (yaHayUno) jbossQl.append(" AND");
                jbossQl.append(" u.node_name LIKE '%" + node_name.toString() + "%'");
                yaHayUno=true;
                }
                if (type != null
                && !type.equalsIgnoreCase("todos")
                && !type.equalsIgnoreCase("")
                ){
                if (yaHayUno) jbossQl.append(" AND");
                jbossQl.append(" u.type LIKE '%" + type.toString() + "%'");
                yaHayUno=true;
                }
                if (alias_name != null
                && !alias_name.equalsIgnoreCase("todos")
                && !alias_name.equalsIgnoreCase("")
                ){
                if (yaHayUno) jbossQl.append(" AND");
                jbossQl.append(" u.alias_name LIKE '%" + alias_name.toString() + "%'");
                yaHayUno=true;
                }
                if (description != null
                && !description.equalsIgnoreCase("todos")
                && !description.equalsIgnoreCase("")
                ){
                if (yaHayUno) jbossQl.append(" AND");
                jbossQl.append(" u.description LIKE '%" + description.toString() + "%'");
                yaHayUno=true;
                }
                if (login != null
                && !login.equalsIgnoreCase("todos")
                && !login.equalsIgnoreCase("")
                ){
                if (yaHayUno) jbossQl.append(" AND");
                jbossQl.append(" u.login LIKE '%" + login.toString() + "%'");
                yaHayUno=true;
                }
                if (passwd != null
                && !passwd.equalsIgnoreCase("todos")
                && !passwd.equalsIgnoreCase("")
                ){
                if (yaHayUno) jbossQl.append(" AND");
                jbossQl.append(" u.passwd LIKE '%" + passwd.toString() + "%'");
                yaHayUno=true;
                }
                if (transfer_dir != null
                && !transfer_dir.equalsIgnoreCase("todos")
                && !transfer_dir.equalsIgnoreCase("")
                ){
                if (yaHayUno) jbossQl.append(" AND");
                jbossQl.append(" u.transfer_dir LIKE '%" + transfer_dir.toString() + "%'");
                yaHayUno=true;
                }
                if (host_alias != null
                && !host_alias.equalsIgnoreCase("todos")
                && !host_alias.equalsIgnoreCase("")
                ){
                if (yaHayUno) jbossQl.append(" AND");
                jbossQl.append(" u.host_alias LIKE '%" + host_alias.toString() + "%'");
                yaHayUno=true;
                }
                if (operational_status != null
                && !operational_status.equalsIgnoreCase("todos")
                && !operational_status.equalsIgnoreCase("")
                ){
                if (yaHayUno) jbossQl.append(" AND");
                jbossQl.append(" u.operational_status LIKE '%" + operational_status.toString() + "%'");
                yaHayUno=true;
                }
                if (ip_address != null
                && !ip_address.equalsIgnoreCase("todos")
                && !ip_address.equalsIgnoreCase("")
                ){
                if (yaHayUno) jbossQl.append(" AND");
                jbossQl.append(" u.ip_address LIKE '%" + ip_address.toString() + "%'");
                yaHayUno=true;
                }
                if (cv_id != null
                && !cv_id.equalsIgnoreCase("todos")
                && !cv_id.equalsIgnoreCase("")
                ){
                if (yaHayUno) jbossQl.append(" AND");
                jbossQl.append(" u.cv_id LIKE '%" + cv_id.toString() + "%'");
                yaHayUno=true;
                }
                if (node_type != null
                && !node_type.equalsIgnoreCase("todos")
                && !node_type.equalsIgnoreCase("")
                ){
                if (yaHayUno) jbossQl.append(" AND");
                jbossQl.append(" u.node_type LIKE '%" + node_type.toString() + "%'");
                yaHayUno=true;
                }
                args = params.toArray(new Object[params.size()]);
                col=ejbSelectGeneric ( jbossQl.toString(), args );
                }
                return col;
                }


                lo que te estoy pasando es un ejemplo mío, reemplazalo con lo que vos necesitas.

                Me interesa saber en donde estan laburando con jboss aca en buenos aires, pensé que era el unico...

                Saludos,
                Hernan Terzian.

                PD: che, espero aparecer en los creditos de tu codigo ;)

                • 5. Re: Checking a field against a list of values
                  Jean-Paul Huybens Newbie

                  Muchos gracias Hernan,

                  you really helped me out here, and your example worked from the first time.

                  Great stuff!!

                  Jean-Paul