Date comparison under RESTRICTIONS.

[nagendra_singh_krishnawat]

Hi Guys,
My application has a requirement of displaying dataTable rows which lies between particular dates.
I basically put two date comparison under RESTRICTIONS clause of EntityQuery class.
Here is the code:

{(book.bookPublishedDate > #{book.startDate),
(book.bookPublishedDate > #{book.endDate),
}

But it didn’t work this way. Is it that only string comparisons are supported under restrictions clause.

If it does not work this way then I have to use EntityManager class and create my own query but I frankly speaking don’t want to do using EntityManager.

Kindly update.
Thanks

[rhills]

How didn't it work?  Did it throw an exception, or just not filter correctly.

I note that the restrictions you've included aren't quoted, but that wouldn't compile, so I am guessing that's just a typo in your message.

I haven't tried this, but maybe a between expression would work, eg:

{"book.bookPublishedDate between #{book.startDate} and #{book.endDate}"}

HTH,

[svadu]

Not sure if EntityQuery will allow 2 EL expressions.
The following restrictions worked for me:

"myEntity.insertionDate >= #{myEntityList.insertionDateFrom}",
"myEntity.insertionDate <= #{myEntityList.insertionDateTo}"

myEntity is the entity bean

myEntityList is the entity query

[nagendra_singh_krishnawat]

Thanks a lot for the reply,
I dig more into it, I looked at the server console and found that there are two queries, One with the proper restriction and other without any restrictions, actually only the first one should get fired, so what happens is that the list doesn’t get refreshed, I really don’t know form there the second query comes form, Here is my server console:

10:44:55,776 INFO  [STDOUT] Restrictions are 
book.bookPublishedEndDate >= #{bookList.book.bookPublishedEndDate}
10:44:56,370 INFO  [STDOUT] Hibernate
    select
        *
    from
        ( select
            book0_.BOOKNUMBER as BOOK1_0_,
            book0_.BOOKNAME as BOOK2_0_,
            book0_.BOOKPUBLISHED_ENDDATE as BOOK3_0_,
            book0_.BOOKPRICE as BOOK4_0_,
            book0_.BOOKPUBLISHEDDATE as BOOK5_0_
        from
            calendar.BOOK book0_
        where
            book0.BOOKPUBLISHEDDATE<=?
            and book0.BOOKPUBLISHEDENDDATE>=? )
    where
        rownum <= ?
10:44:56,386 INFO  [STDOUT] 1
10:44:56,386 INFO  [STDOUT] 1
10:44:56,401 INFO  [STDOUT] 1

10:44:56,714 INFO  [STDOUT] Hibernate:
    select
        *
    from
        ( select
            book0_.BOOKNUMBER as BOOK1_0_,
            book0_.BOOKNAME as BOOK2_0_,
            book0_.BOOKPUBLISHEDEND_DATE as BOOK3_0_,
            book0_.BOOKPRICE as BOOK4_0_,
            book0_.BOOKPUBLISHED_DATE as BOOK5_0_
        from
            calendar.BOOK book0_ )
    where
        rownum <= ?
10:44:56,714 INFO  [STDOUT] 4

I override the getResultList method to see the size of the list, if you noticed the server console size is displayed as 1 and 4 respectively.

10:44:56,386 INFO  [STDOUT] 1
10:44:56,714 INFO  [STDOUT] 4

And the result is that I am getting displayed all the four rows on the browser instead of one (Result when the restrictions are applied). Does the refresh of list have to do something with the .xhtml page; I am using exactly the same page generated by seamgen
Thanks

[svadu]

Restrictions are applied depending on whether your EL expressions are set. I have an impression you loose your request parameters that are passed via page actions (you do that don't you?)...

[nagendra_singh_krishnawat]

Thanks,
My parameters are going to backend thats the reason why I am getting the list size as 1 for the first query, but that query is overridded by another query without any restrictions and gives me 4 rows,

Here is my .xhtml page:

<h:form id="bookSearch" styleClass="edit">
   
        <rich:simpleTogglePanel label="Book search parameters" switchType="ajax">
       
            <s:decorate template="layout/display.xhtml">
                <ui:define name="label">bookPrice</ui:define>
                <h:inputText id="bookPrice" value="#{bookList.book.bookPrice}"/>
            </s:decorate>
           
             <s:decorate template="layout/display.xhtml">
                <ui:define name="label">bookName</ui:define>
                <h:inputText id="bookName" value="#{bookList.book.bookName}"/>
            </s:decorate>
           
              <s:decorate id="bookPublishedDateDecoration" template="layout/display.xhtml">
                <ui:define name="label">Book Published Date</ui:define>
                    <rich:calendar id="bookPublishedDate" value="#{bookList.book.bookPublishedDate}" datePattern="MM/dd/yyyy" />
            </s:decorate>
           
              <s:decorate id="bookPublishedDateDecoration1" template="layout/display.xhtml">
                <ui:define name="label">Book Published Date</ui:define>
                    <rich:calendar id="bookPublishedEndDate" value="#{bookList.book.bookPublishedEndDate}" datePattern="MM/dd/yyyy" />
            </s:decorate>
           
                   
        </rich:simpleTogglePanel>

<div class="actionButtons">
            <h:commandButton id="search" value="Search" action="/BookList.xhtml"/>
            <h:commandButton id="navigate" value="navigation" action="/nagendra.seam"/>
        </div>
       
    </h:form>
   
    <rich:panel>
        <f:facet name="header">Book search results</f:facet>
    <div class="results" id="bookList">

    <h:outputText value="The book search returned no results."
               rendered="#{empty bookList.resultList}"/>
              
    <rich:dataTable id="bookList"
                var="book"
              value="#{bookList.resultList}"
           rendered="#{not empty bookList.resultList}">
        <h:column>
            <f:facet name="header">
                <s:link styleClass="columnHeader"
                             value="bookNumber #{bookList.order=='bookNumber asc' ? messages.down : ( bookList.order=='bookNumber desc' ? messages.up : '' )}">
                    <f:param name="order" value="#{bookList.order=='bookNumber asc' ? 'bookNumber desc' : 'bookNumber asc'}"/>
                </s:link>
            </f:facet>
            #{book.bookNumber}
        </h:column>
.....
....

I have just added three more s:decorate for the id and two date fields(start and end date) to put restrictions. Other then that I havent changed much code. Am i missing something ..
Thanks
Nagendra

[svadu]

Please use code block to format the code. It's unreadable...

[svadu]

Did you define request parameters in your page action to move your search parameters across requests?

[nagendra_singh_krishnawat]

Not readable, sorry abt that,
Here is my xhtml page:

<!DOCTYPE composition PUBLIC "-//W3C//DTD XHTML 1.0Transitional//EN" 

                             "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">



<ui:composition xmlns="http://www.w3.org/1999/xhtml"

                xmlns:s="http://jboss.com/products/seam/taglib"

                xmlns:ui="http://java.sun.com/jsf/facelets"

                xmlns:f="http://java.sun.com/jsf/core"

                xmlns:h="http://java.sun.com/jsf/html"

                xmlns:rich="http://richfaces.org/rich"

                template="layout/template.xhtml">

                       

<ui:define name="body">

    

    <h:messages globalOnly="true" styleClass="message" id="globalMessages"/>

    

    <h:form id="bookSearch" styleClass="edit">

    

        <rich:simpleTogglePanel label="Book search parameters" switchType="ajax">

        

            <s:decorate template="layout/display.xhtml">

                <ui:define name="label">bookPrice</ui:define>

                <h:inputText id="bookPrice" value="#{bookList.book.bookPrice}"/>

            </s:decorate>

            

             <s:decorate template="layout/display.xhtml">

                <ui:define name="label">bookName</ui:define>

                <h:inputText id="bookName" value="#{bookList.book.bookName}"/>

            </s:decorate>

            

              <s:decorate id="bookPublishedDateDecoration" template="layout/display.xhtml">

                <ui:define name="label">Book Published Date</ui:define>

                    <rich:calendar id="bookPublishedDate" value="#{bookList.book.bookPublishedDate}" datePattern="MM/dd/yyyy" />

            </s:decorate>

            

              <s:decorate id="bookPublishedDateDecoration1" template="layout/display.xhtml">

                <ui:define name="label">Book Published Date</ui:define>

                    <rich:calendar id="bookPublishedEndDate" value="#{bookList.book.bookPublishedEndDate}" datePattern="MM/dd/yyyy" />

            </s:decorate> 

            

                    

        </rich:simpleTogglePanel>

        

        <div class="actionButtons">

            <h:commandButton id="search" value="Search" action="/BookList.xhtml"/>

            <h:commandButton id="navigate" value="navigation" action="/nagendra.seam"/>

        </div>

        

    </h:form>

    

    <rich:panel>

        <f:facet name="header">Book search results</f:facet>

    <div class="results" id="bookList">


    <h:outputText value="The book search returned no results." 

               rendered="#{empty bookList.resultList}"/>

               

    <rich:dataTable id="bookList" 

                var="book"

              value="#{bookList.resultList}" 

           rendered="#{not empty bookList.resultList}">

        <h:column>

            <f:facet name="header">

                <s:link styleClass="columnHeader"

                             value="bookNumber #{bookList.order=='bookNumber asc' ? messages.down : ( bookList.order=='bookNumber desc' ? messages.up : '' )}">

                    <f:param name="order" value="#{bookList.order=='bookNumber asc' ? 'bookNumber desc' : 'bookNumber asc'}"/>

                </s:link>

            </f:facet>

            #{book.bookNumber}

        </h:column>

        <h:column>

            <f:facet name="header">

                <s:link styleClass="columnHeader"

                             value="bookName #{bookList.order=='bookName asc' ? messages.down : ( bookList.order=='bookName desc' ? messages.up : '' )}">

                    <f:param name="order" value="#{bookList.order=='bookName asc' ? 'bookName desc' : 'bookName asc'}"/>

                </s:link>

            </f:facet>

            #{book.bookName}

        </h:column>

        <h:column>

            <f:facet name="header">

                <s:link styleClass="columnHeader"

                             value="bookPrice #{bookList.order=='bookPrice asc' ? messages.down : ( bookList.order=='bookPrice desc' ? messages.up : '' )}">

                    <f:param name="order" value="#{bookList.order=='bookPrice asc' ? 'bookPrice desc' : 'bookPrice asc'}"/>

                </s:link>

            </f:facet>

            #{book.bookPrice}

        </h:column>

        <h:column>

            <f:facet name="header">

                <s:link styleClass="columnHeader"

                             value="bookPublishedDate #{bookList.order=='bookPublishedDate asc' ? messages.down : ( bookList.order=='bookPublishedDate desc' ? messages.up : '' )}">

                    <f:param name="order" value="#{bookList.order=='bookPublishedDate asc' ? 'bookPublishedDate desc' : 'bookPublishedDate asc'}"/>

                </s:link>

            </f:facet>

            #{book.bookPublishedDate}

        </h:column>

        <h:column>

            <f:facet name="header">action</f:facet>

            <s:link view="/#{empty from ? 'Book' : from}.xhtml" 

                   value="Select" 

                      id="book">

                <f:param name="bookBookNumber" 

                        value="#{book.bookNumber}"/>

            </s:link>

        </h:column>

    </rich:dataTable>


    </div>

    </rich:panel>

    

    <div class="tableControl">

      

        <s:link view="/BookList.xhtml" 

            rendered="#{bookList.previousExists}" 

               value="#{messages.left}#{messages.left} First Page"

                  id="firstPage">

          <f:param name="firstResult" value="0"/>

        </s:link>

        

        <s:link view="/BookList.xhtml" 

            rendered="#{bookList.previousExists}" 

               value="#{messages.left} Previous Page"

                  id="previousPage">

            <f:param name="firstResult" 

                    value="#{bookList.previousFirstResult}"/>

        </s:link>

        

        <s:link view="/BookList.xhtml" 

            rendered="#{bookList.nextExists}" 

               value="Next Page #{messages.right}"

                  id="nextPage">

            <f:param name="firstResult" 

                    value="#{bookList.nextFirstResult}"/>

        </s:link>

        

        <s:link view="/BookList.xhtml" 

            rendered="#{bookList.nextExists}" 

               value="Last Page #{messages.right}#{messages.right}"

                  id="lastPage">

            <f:param name="firstResult" 

                    value="#{bookList.lastFirstResult}"/>

        </s:link>

        

    </div>

    

    <s:div styleClass="actionButtons" rendered="#{empty from}">

        <s:button view="/BookEdit.xhtml"

                    id="create" 

                 value="Create book">

            <f:param name="bookBookNumber"/>

        </s:button>

    </s:div>

    

</ui:define>

</ui:composition>

Here is my java page:

package com.westernasset.calendar.action;


import com.westernasset.calendar.*;

import org.jboss.seam.annotations.Name;

import org.jboss.seam.framework.EntityQuery;

import java.util.List;

import java.util.Arrays;


@Name("bookList")

public class BookList extends EntityQuery {


     //private static final String[] RESTRICTIONS = {" book.bookPrice = (#{bookList.book.bookPrice})"};

    //private static final String[] RESTRICTIONS = {"book.bookPublishedDate between #{bookList.book.bookPublishedDate} and #{bookList.book.bookPublishedEndDate}",};

    

    private static final String[] RESTRICTIONS = {"book.bookPublishedDate <= #{bookList.book.bookPublishedDate}",

      "book.bookPublishedEndDate >= #{bookList.book.bookPublishedEndDate}",};

    

   // private static final String[] RESTRICTIONS = {" book.bookName = (#{bookList.book.bookName})"};


    


     private Book book = new Book();


     @Override

     public String getEjbql() {

          return "select book from Book book";

     }


     @Override

     public Integer getMaxResults() {

          return 25;

     }


     public Book getBook() {

          return book;

     }

    @Override

    public List getResultList() {

        // TODO Auto-generated method stub

        List list =super.getResultList(); 

        System.out.println(list.size());

        return list;

    }


     @Override

     public List<String> getRestrictions() {

        for(int i=0;i<RESTRICTIONS.length;i++){

            System.out.println("Restrictions are : " +RESTRICTIONS[i].toString() );    

        }

          return Arrays.asList(RESTRICTIONS);

     }

}

If I add components in my form all the form element values goes to the backing bean, Right!
I didnt get what you wanted to say.
Looking at the code you might figure out where is the problem.
Thanks in advance.

[nagendra_singh_krishnawat]

If I keep

 private static final String[] RESTRICTIONS = {" book.bookName = (#{bookList.book.bookName})"};

and then press the "search" button, it works fine.
When I change it to

 private static final String[] RESTRICTIONS = {"book.bookPublishedDate <= #{bookList.book.bookPublishedDate}",

      "book.bookPublishedEndDate >= #{bookList.book.bookPublishedEndDate}",};

and press "search" list doenst get updated, Actually two queries gets fired in single request, one with restrictions and other without restrictions.
Server console:

10:44:55,776 INFO [STDOUT] Restrictions are book.bookPublishedEndDate >= #{bookList.book.bookPublishedEndDate} 10:44:56,370 INFO [STDOUT] Hibernate select * from ( select book0_.BOOKNUMBER as BOOK1_0_, book0_.BOOKNAME as BOOK2_0_, book0_.BOOKPUBLISHED_ENDDATE as BOOK3_0_, book0_.BOOKPRICE as BOOK4_0_, book0_.BOOKPUBLISHEDDATE as BOOK5_0_ from calendar.BOOK book0_ where book0.BOOKPUBLISHEDDATE<=? and book0.BOOKPUBLISHEDENDDATE>=? ) where rownum <= ? 

10:44:56,386 INFO [STDOUT] 1 

10:44:56,386 INFO [STDOUT] 1 

10:44:56,401 INFO [STDOUT] 1 


10:44:56,714 INFO [STDOUT] Hibernate: select * from ( select book0_.BOOKNUMBER as BOOK1_0_, book0_.BOOKNAME as BOOK2_0_, book0_.BOOKPUBLISHEDEND_DATE as BOOK3_0_, book0_.BOOKPRICE as BOOK4_0_, book0_.BOOKPUBLISHED_DATE as BOOK5_0_ from calendar.BOOK book0_ ) where rownum <= ? 

10:44:56,714 INFO [STDOUT] 4 

I override the getResultList method to see the size of the list, if you noticed the server console size is displayes the return list size as 1 and 4 respectively.

10:44:56,386 INFO [STDOUT] 1 

10:44:56,714 INFO [STDOUT] 4 

Thanks

[svadu]

Do you have this for page parameters? If not this is your problem, if yes - show it to me.

[nagendra_singh_krishnawat]

Server console for single click of "Search" button.
On the second and third line there is book start date and end date properly set:

13:38:31,413 INFO  [STDOUT] bookPrice is:: null

13:38:31,429 INFO  [STDOUT] bookPublishedDate is:: Sat May 31 00:00:00 PDT 2008

13:38:31,429 INFO  [STDOUT] bookPublishedEndDate is:: Sun Jun 01 00:00:00 PDT 2008

13:38:31,444 INFO  [STDOUT] Restrictions are : book.bookPublishedDate <= #{bookList.book.bookPublishedDate}

13:38:31,444 INFO  [STDOUT] Restrictions are : book.bookPublishedEndDate >= #{bookList.book.bookPublishedEndDate}

13:38:31,444 INFO  [STDOUT] Restrictions are : book.bookPublishedDate <= #{bookList.book.bookPublishedDate}

13:38:31,460 INFO  [STDOUT] Restrictions are : book.bookPublishedEndDate >= #{bookList.book.bookPublishedEndDate}

13:38:31,460 INFO  [STDOUT] Restrictions are : book.bookPublishedDate <= #{bookList.book.bookPublishedDate}

13:38:31,460 INFO  [STDOUT] Restrictions are : book.bookPublishedEndDate >= #{bookList.book.bookPublishedEndDate}

13:38:32,085 INFO  [STDOUT] Hibernate:

    select

        *

    from

        ( select

            book0_.BOOK_NUMBER as BOOK1_0_,

            book0_.BOOK_NAME as BOOK2_0_,

            book0_.BOOK_PUBLISHED_END_DATE as BOOK3_0_,

            book0_.BOOK_PRICE as BOOK4_0_,

            book0_.BOOK_PUBLISHED_DATE as BOOK5_0_

        from

            calendar.BOOK book0_

        where

            book0_.BOOK_PUBLISHED_DATE<=?

            and book0_.BOOK_PUBLISHED_END_DATE>=? )

    where

        rownum <= ?

13:38:32,194 INFO  [STDOUT] 1

13:38:32,194 INFO  [STDOUT] 1

13:38:32,210 INFO  [STDOUT] 1

13:38:32,569 INFO  [STDOUT] Restrictions are : book.bookPublishedDate <= #{bookList.book.bookPublishedDate}

13:38:32,585 INFO  [STDOUT] Restrictions are : book.bookPublishedEndDate >= #{bookList.book.bookPublishedEndDate}

13:38:32,585 INFO  [STDOUT] Restrictions are : book.bookPublishedDate <= #{bookList.book.bookPublishedDate}

13:38:32,585 INFO  [STDOUT] Restrictions are : book.bookPublishedEndDate >= #{bookList.book.bookPublishedEndDate}

13:38:32,585 INFO  [STDOUT] Restrictions are : book.bookPublishedDate <= #{bookList.book.bookPublishedDate}

13:38:32,585 INFO  [STDOUT] Restrictions are : book.bookPublishedEndDate >= #{bookList.book.bookPublishedEndDate}

13:38:32,601 INFO  [STDOUT] Restrictions are : book.bookPublishedDate <= #{bookList.book.bookPublishedDate}

13:38:32,601 INFO  [STDOUT] Restrictions are : book.bookPublishedEndDate >= #{bookList.book.bookPublishedEndDate}

13:38:32,601 INFO  [STDOUT] Hibernate:

    select

        *

    from

        ( select

            book0_.BOOK_NUMBER as BOOK1_0_,

            book0_.BOOK_NAME as BOOK2_0_,

            book0_.BOOK_PUBLISHED_END_DATE as BOOK3_0_,

            book0_.BOOK_PRICE as BOOK4_0_,

            book0_.BOOK_PUBLISHED_DATE as BOOK5_0_

        from

            calendar.BOOK book0_ )

    where

        rownum <= ?

13:38:32,648 INFO  [STDOUT] 4

13:38:32,663 INFO  [STDOUT] 4

13:38:32,663 INFO  [STDOUT] 4

I dont think I have to change anything in pages.xml, but you can have a look:

<?xml version="1.0" encoding="UTF-8"?>

<pages xmlns="http://jboss.com/products/seam/pages"

     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

     xsi:schemaLocation="http://jboss.com/products/seam/pages http://jboss.com/products/seam/pages-2.0.xsd"

     no-conversation-view-id="/home.xhtml" login-view-id="/login.xhtml">


     <page view-id="*">

          <navigation>

               <rule if-outcome="home">

                    <redirect view-id="/home.xhtml" />

               </rule>

          </navigation>

     </page>


     <exception

          class="org.jboss.seam.framework.EntityNotFoundException">

          <redirect view-id="/error.xhtml">

               <message>Not found</message>

          </redirect>

     </exception>


     <exception class="javax.persistence.EntityNotFoundException">

          <redirect view-id="/error.xhtml">

               <message>Not found</message>

          </redirect>

     </exception>


     <exception class="javax.persistence.OptimisticLockException">

          <end-conversation />

          <redirect view-id="/error.xhtml">

               <message>

                    Another user changed the same data, please try again

               </message>

          </redirect>

     </exception>


     <exception class="org.jboss.seam.security.AuthorizationException">

          <redirect view-id="/error.xhtml">

               <message>You don't have permission to do this</message>

          </redirect>

     </exception>


     <exception class="org.jboss.seam.security.NotLoggedInException">

          <redirect view-id="/login.xhtml">

               <message>Please log in first</message>

          </redirect>

     </exception>


     <exception class="javax.faces.application.ViewExpiredException">

          <redirect view-id="/error.xhtml">

               <message>

                    Your session has timed out, please try again

               </message>

          </redirect>

     </exception>


     <exception>

          <redirect view-id="/error.xhtml">

               <message>Unexpected error, please try again</message>

          </redirect>

     </exception>


</pages>

Thanks

[svadu]

You need to define page parameters so that when MVC-pull happens the value of your search query parameters will be restored.

See 6.7. Fine-grained files for definition of navigation, page actions and parameters and 6.3. Page parameters

If your EntityQuery class is generated with seam-gen you should have examples of 'partial' navigation page with some page parameters.

[nagendra_singh_krishnawat]

I got some success by defining pages.xml, but I get the following exception:

15:22:44,729 ERROR [SeamPhaseListener] uncaught exception

javax.el.ELException: java.lang.IllegalArgumentException: java.lang.ClassCastException@26b13c

        at javax.el.BeanELResolver.setValue(BeanELResolver.java:116)

        at javax.el.CompositeELResolver.setValue(CompositeELResolver.java:68)

        at com.sun.faces.el.FacesCompositeELResolver.setValue(FacesCompositeELResolver.java:93)

        at org.jboss.el.parser.AstPropertySuffix.setValue(AstPropertySuffix.java:73)

What could be the cause of this exception.

Booklist.pages.xml :

<?xml version="1.0" encoding="UTF-8"?>

<page xmlns="http://jboss.com/products/seam/pages"

     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

     xsi:schemaLocation="http://jboss.com/products/seam/pages http://jboss.com/products/seam/pages-2.0.xsd">


     <param name="firstResult" value="#{bookList.firstResult}" />

     <param name="order" value="#{bookList.order}" />

     <param name="from" />

     <param name="bookName" value="#{bookList.book.bookName}" />

     <param name="bookPrice" value="#{bookList.book.bookPrice}" />

     <param name="bookPublishedDate"     value="#{bookList.book.bookPublishedDate}" />

</page>

Thanks

[nagendra_singh_krishnawat]

Do i need to define date convertor ?