4 Replies Latest reply on Oct 29, 2011 3:49 AM by amarpanigrahy

    A suggestion for org.jboss.seam.framework.Query

    bobyang

      Could the Query class be added a new property for specifying the appending logical operator that concatenate the basic EJBQL string with the whole restriction properties?


      The class EntityQuery is extended the class Query. When I use the EntityQuery like below:


      @Name("worklogList")
      public class WorklogList extends EntityQuery<Worklog> {
      
           private static final long serialVersionUID = 5221868912115801552L;
      
           private static final String EJBQL = 
                "select worklog from Worklog worklog "+
                "where Worklog.executor.account.userName=#{authenticatedUser.userName}";
      
           private static final String[] RESTRICTIONS = { 
                "lower(worklog.work) like lower(concat(#{worklogList.worklog.work},'%'))", 
                "lower(worklog.progress) like lower(concat(#{worklogList.worklog.progress},'%'))"
                };
      
           private Worklog worklog = new Worklog();
      
           public WorklogList() {
                setEjbql(EJBQL);
                setRestrictionExpressionStrings(Arrays.asList(RESTRICTIONS));
                setMaxResults(25);
                setRestrictionLogicOperator("and");
           }
      
           public Worklog getWorklog() {
                return worklog;
           }
      }
      



      Notice that the EJBQL contains one where clause, the Query class will append restrictions to the EJBQL string with restrictionLogicOperator. So when user choose the 'or' logical operator from the XXXList.xhtml page, the final query string will become:


      select worklog from Worklog worklog 
      where Worklog.executor.account.userName=#{authenticatedUser.userName} 
      or lower(worklog.work) like lower(concat(#{worklogList.worklog.work},'%')) 
      or lower(worklog.progress) like lower(concat(#{worklogList.worklog.progress},'%'))
      



      But most time we use the EJBQL string as the basic query condition, so the desired query string should like below, especially with the bracket enclosing the whole restriction strings :


      select worklog from Worklog worklog 
      where Worklog.executor.account.userName=#{authenticatedUser.userName} 
      and ( lower(worklog.work) like lower(concat(#{worklogList.worklog.work},'%')) 
      or lower(worklog.progress) like lower(concat(#{worklogList.worklog.progress},'%'))
      )
      



      To achieve this query string, now I have to copy EntityQuery class to MyEntityQuery and make it extends MyQuery class.


      The MyEntityQuery class is just the copy of EntityQuery but the MyQuery has little modification.


      My modified Query class snippet is


      public abstract class MyQuery<T, E> 
            extends PersistenceController<T>
      {
      ......
         private String restrictionAppendLogicOperator;
      ......
         protected String getRenderedEjbql()
         {
            StringBuilder builder = new StringBuilder().append(parsedEjbql);
            boolean isAppendLogicOpAdded = false;
            
            for (int i=0; i<getRestrictions().size(); i++)
            {
               Object parameterValue = restrictionParameters.get(i).getValue();
               if ( isRestrictionParameterSet(parameterValue) )
               {
                  if ( WHERE_PATTERN.matcher(builder).find() )
                  {
                       // added by YangBo
                     if(getRestrictionAppendLogicOperator()!=null && !isAppendLogicOpAdded)
                     {
                          sanitizeRestrictionLogicOperator(getRestrictionAppendLogicOperator());
                          builder.append(" ").append(getRestrictionAppendLogicOperator()).append(" ( ");
                          isAppendLogicOpAdded = true;
                     }
                     else
                     {
                          builder.append(" ").append(getRestrictionLogicOperator()).append(" ");
                     }
                  }
                  else
                  {
                     builder.append(" where ");
                  }
                  builder.append( parsedRestrictions.get(i) );
               }
            }
            if ( isAppendLogicOpAdded )
            {
                 builder.append(" ) ");
                 isAppendLogicOpAdded = false;
            }
            
            if (getGroupBy()!=null) {
                builder.append(" group by ").append(getGroupBy());
            }
      
            if (getOrder()!=null) {
                builder.append(" order by ").append( getOrder() );
            }
            
            return builder.toString();
         }
      ......
      }
      
      



      By specify the new restrictionAppendLogicOperator property we can controll how the restriction strings be concatenate to the basic EJBQL without the side-effect of the restrictionLogicOperator.


      In short the restrictionLogicOperator is responsible for the internal concatenation of restrictions strings while the new restrictionAppendLogicOperator is responsible for concatenation of basic EJBQL and the whole restrictions.


      If this suggestion be accept, I'd like to make a patch for it.


      Cheers!

        • 1. Re: A suggestion for org.jboss.seam.framework.Query
          josdaniel

          We are stuck in a similar issue and would definitely like to tweak the query generation mechanism and still rely on the pagination features provided by EntityQuery. What would be the best way of achieving the same?


          Appreciate any inputs on this topic

          • 2. Re: A suggestion for org.jboss.seam.framework.Query
            lcsuarezl

            Dear friend,
            I'm dealing with the same restriction, but I can't get it working ok,
            could you tell me if there are another explanation or maybe could you help me,
            this is my List:



            @Name("suIssuingcompaniesusersList")
            public class SUIssuingcompaniesusersList extends
                      EntityQuery<Issuingcompaniesusers> {
            
                 private static final long serialVersionUID = 1L;
                 private String login; 
                 private String id;
                 private String name; 
            
                 private static final String EJBQL = "select issuingcompaniesusers from Issuingcompaniesusers issuingcompaniesusers " +
                           "where issuingcompaniesusers.profiles.name = 'ADMFACT'" ;
            
                 private static final String[] RESTRICTIONS = {
                           "lower(issuingcompaniesusers.login)like lower(concat('%' ,concat(#{suIssuingcompaniesusersList.login},'%'))) ",
                           "lower(issuingcompaniesusers.identificationnumber)like lower(concat('%' ,concat(#{suIssuingcompaniesusersList.id},'%')))",
                           "lower(issuingcompaniesusers.name)like lower(concat('%' ,concat(#{suIssuingcompaniesusersList.name},'%')))", };
            
                 private Issuingcompaniesusers issuingcompaniesusers = new Issuingcompaniesusers();
            
                 public SUIssuingcompaniesusersList() {
                      setEjbql(EJBQL);
                      setRestrictionExpressionStrings(Arrays.asList(RESTRICTIONS));
                      setMaxResults(25);
                 }
            
                 public Issuingcompaniesusers getIssuingcompaniesusers() {
                      return issuingcompaniesusers;
                 }
                 
                 
                 public String getLogin() {
                      return login;
                 }
            
                 public void setLogin(String login) {
                      this.login = login;
                 }
            
                 public String getId() {
                      return id;
                 }
            
                 public void setId(String id) {
                      this.id = id;
                 }
            
                 public String getName() {
                      return name;
                 }
            
                 public void setName(String name) {
                      this.name = name;
                 }
            }



            and I'm getting the folowing queries:


            Default:



            13:13:16,441 INFO  [STDOUT] Hibernate: 
                select
                    * 
                from
                    ( select
                        issuingcom0_.LOGIN as LOGIN298_,
                        issuingcom0_.EMAIL as EMAIL298_,
                        issuingcom0_.ENABLED as ENABLED298_,
                        issuingcom0_.IDENTIFICATIONNUMBER as IDENTIFI4_298_,
                        issuingcom0_.FKISSUINGCOMPANIESNAME as FKISSUIN7_298_,
                        issuingcom0_.NAME as NAME298_,
                        issuingcom0_.PASSWORD as PASSWORD298_,
                        issuingcom0_.FKPROFILESNAME as FKPROFIL8_298_ 
                    from
                        ISSUINGCOMPANIESUSERS issuingcom0_ 
                    where
                        issuingcom0_.FKPROFILESNAME='ADMFACT' ) 
                where
                    rownum <= ?




            All properties selected in the user interface:


            select
                    * 
                from
                    ( select
                        issuingcom0_.LOGIN as LOGIN298_,
                        issuingcom0_.EMAIL as EMAIL298_,
                        issuingcom0_.ENABLED as ENABLED298_,
                        issuingcom0_.IDENTIFICATIONNUMBER as IDENTIFI4_298_,
                        issuingcom0_.FKISSUINGCOMPANIESNAME as FKISSUIN7_298_,
                        issuingcom0_.NAME as NAME298_,
                        issuingcom0_.PASSWORD as PASSWORD298_,
                        issuingcom0_.FKPROFILESNAME as FKPROFIL8_298_ 
                    from
                        ISSUINGCOMPANIESUSERS issuingcom0_ 
                    where
                        issuingcom0_.FKPROFILESNAME='ADMFACT' 
                        and (
                            lower(issuingcom0_.LOGIN) like lower('%'||?||'%')
                        ) ) 
                where
                    rownum <= ?



            OR selected in the user interface:



            select
                    * 
                from
                    ( select
                        issuingcom0_.LOGIN as LOGIN298_,
                        issuingcom0_.EMAIL as EMAIL298_,
                        issuingcom0_.ENABLED as ENABLED298_,
                        issuingcom0_.IDENTIFICATIONNUMBER as IDENTIFI4_298_,
                        issuingcom0_.FKISSUINGCOMPANIESNAME as FKISSUIN7_298_,
                        issuingcom0_.NAME as NAME298_,
                        issuingcom0_.PASSWORD as PASSWORD298_,
                        issuingcom0_.FKPROFILESNAME as FKPROFIL8_298_ 
                    from
                        ISSUINGCOMPANIESUSERS issuingcom0_ 
                    where
                        issuingcom0_.FKPROFILESNAME='ADMFACT' 
                        or lower(issuingcom0_.LOGIN) like lower('%'||?||'%') ) 
                where
                    rownum <= ?



            as you could see here, no matter if the profilesname is ADMFACT or not, if at least one value has the same ? value it will list this!


            Please, any help will be graterful

            • 3. Re: A suggestion for org.jboss.seam.framework.Query
              lcsuarezl

              Dear Bob Yang your post has been very usefull for this issue, just one little change,


              when you define the operator of the restrictions before the ( you call the user defined logic operator, this will be and or or depending if the user chose all or any in the interface.


              So instead of getRestrictionAppendLogicOperator() you should add one and
              like this:

              LOGIC_OPERATOR_AND


              by this way the sentence always will be like

              SENTENSE where and (user search filter restrictions)

              instead of:

              SENTENSE where and (user search filter restrictions)

              if getRestrictionAppendLogicOperator() equals to and

              SENTENSE where or (user search filter restrictions)

              if getRestrictionAppendLogicOperator() equals to or


              for (int i = 0; i < getRestrictions().size(); i++) {
                   Object parameterValue = restrictionParameters.get(i).getValue();
                        if (isRestrictionParameterSet(parameterValue)) {
                             if (WHERE_PATTERN.matcher(builder).find()) {
                               if(getRestrictionLogicOperator()!=null && !isAppendLogicOpAdded){ 
                                 builder.append(" ").append(LOGIC_OPERATOR_AND).append(" ( ");
                              isAppendLogicOpAdded = true;
                            }
                            else{
                                 builder.append(" ").append(getRestrictionLogicOperator()).append(" ");
                            }
                             } else {
                                  builder.append(" where ");
                        }
                        builder.append(parsedRestrictions.get(i));
                   }
              }



              I hope this result helpful for all.

              • 4. Re: A suggestion for org.jboss.seam.framework.Query
                amarpanigrahy

                Thanks a lot ...Bob Yang...i am a new biee to seam and was stuck in similar problem. i applied your suggested changes in Query class and my problem was solved. Keep Posting. Thank you once again....