9 Replies Latest reply on Aug 20, 2009 9:18 AM by Nicolas U.

    Pagination in SQL for a dataTable and datascoller

    Nicolas U. Newbie

      hello all.

      i have a big performance probleme.
      I have a very large table (logging table).

      i display this sql table in a <rich:dataTable rows=50> and a <rich:datascroller>

      I use sortBy and filterBy on some colum.

      It's work but it's sooo slow.

      When i request 50 row in a manual SQL request it's go fast but in the command i see SQL generated by richfaces.
      And he request everytime all row.

      How limit the SQL request to the row of the datatable and not all row??

      Thx

        • 2. Re: Pagination in SQL for a dataTable and datascoller
          Nicolas U. Newbie

          oh great. i bit more complicated but it's possible.

          thx

          enjoy with richfaces 4 @JSF2

          • 3. Re: Pagination in SQL for a dataTable and datascoller
            Nicolas U. Newbie

            i have look in demo before ask it but i have look in datatable et datascoller component.

            Not in colum...

            • 4. Re: Pagination in SQL for a dataTable and datascoller
              Nicolas U. Newbie

              i cant make it work...

              my list is always empty.

              My xhtml simplify:

              <rich:dataTable value="#{logSpeosDataModel}" var="log" rows="20" reRender="ds" id="simpletable">
               <f:facet name="header">
               <rich:columnGroup>
               <rich:column>
               <h:outputText value="ID" />
               </rich:column>
               <rich:column>
               <h:outputText value="Application" />
               </rich:column>
               </rich:columnGroup>
               </f:facet>
               <rich:column>
               <h:outputText value="#{log.id}" />
               </rich:column>
               <rich:column>
               <h:outputText value="#{log.url.application.name}" />
               </rich:column>
               <f:facet name="footer">
               <rich:datascroller id="ds" renderIfSinglePage="false" limitToList="true" />
               </f:facet>
              </rich:dataTable>
              


              and my class for input data (sample in demo modify):

              package be.speos.action;
              
              import java.io.IOException;
              import java.util.ArrayList;
              import java.util.HashMap;
              import java.util.List;
              import java.util.Map;
              
              import javax.faces.context.FacesContext;
              import javax.persistence.EntityManager;
              import javax.persistence.Query;
              
              import org.ajax4jsf.model.DataVisitor;
              import org.ajax4jsf.model.Range;
              import org.ajax4jsf.model.SequenceRange;
              import org.ajax4jsf.model.SerializableDataModel;
              import org.jboss.seam.annotations.In;
              import org.jboss.seam.annotations.Name;
              
              import be.speos.entity.LogSpeos;
              
              /**
               *
               * @author ias This is example class that intended to demonstrate use of
               * ExtendedDataModel and SerializableDataModel. This implementation
               * intended to be used as a request scope bean. However, it actually
               * provides serialized state, so on a post-back we do not load data from
               * the data provider. Instead we use data that was used during
               * rendering. This data model must be used together with Data Provider,
               * which is responsible for actual data load from the database using
               * specific filtering and sorting. Normally Data Provider must be in
               * either session, or conversation scope.
               */
              @Name("logSpeosDataModel")
              public class LogSpeosDataModel extends SerializableDataModel {
              
               private Long currentPk;
               private Map<Long, LogSpeos> wrappedData = new HashMap<Long, LogSpeos>();
               private ArrayList<Long> wrappedKeys = null;
               @In
               EntityManager entityManager;
              
               private static final long serialVersionUID = -1956179896877538628L;
              
               /**
               * This method never called from framework. (non-Javadoc)
               *
               * @see org.ajax4jsf.model.ExtendedDataModel#getRowKey()
               */
               @Override
               public Object getRowKey() {
               return currentPk;
               }
              
               /**
               * This method normally called by Visitor before request Data Row.
               */
               @Override
               public void setRowKey(Object key) {
               this.currentPk = (Long) key;
              
               }
              
               /**
               * This is main part of Visitor pattern. Method called by framework many
               * times during request processing.
               */
               @Override
               public void walk(FacesContext context, DataVisitor visitor, Range range, Object argument) throws IOException {
               int firstRow = ((SequenceRange) range).getFirstRow();
               int numberOfRows = ((SequenceRange) range).getRows();
               wrappedKeys = new ArrayList<Long>();
               /*
               * for (AuctionItem item:dataProvider.getItemsByrange(new
               * Integer(firstRow), numberOfRows, null, true)) {
               * wrappedKeys.add(item.getPk()); wrappedData.put(item.getPk(), item);
               * visitor.process(context, item.getPk(), argument); }
               */
               System.out.println("***************in walk - " + range + " - " + firstRow + " - " + numberOfRows);
               Query query = entityManager.createQuery("SELECT c FROM LogSpeos AS c ORDER BY ID DESC");
               query.setFirstResult(firstRow);
               query.setMaxResults(20);
               List<LogSpeos> logList = query.getResultList();
               for (LogSpeos log : logList) {
               wrappedKeys.add(log.getId());
               wrappedData.put(log.getId(), log);
               visitor.process(context, log.getId(), argument);
               }
              
               }
              
               /**
               * This method must return actual data rows count from the Data Provider. It
               * is used by pagination control to determine total number of data items.
               */
               @Override
               public int getRowCount() {
               System.out.println("************************in getRowCount()");
               Query query = entityManager.createQuery("SELECT COUNT(c) FROM LogSpeos AS c");
               Long tmp = (Long) query.getSingleResult();
               System.out.println("long: " + tmp);
               return tmp.intValue();
               }
              
               /**
               * This is main way to obtain data row. It is intensively used by framework.
               * We strongly recommend use of local cache in that method.
               */
               @Override
               public Object getRowData() {
               System.out.println("************************in getRowData() " + currentPk);
               if (currentPk == null) {
               return null;
               } else {
               LogSpeos ret = wrappedData.get(currentPk);
               if (ret == null) {
               Query query = entityManager.createQuery("SELECT c FROM LogSpeos AS c WHERE c.id=:pk");
               query.setParameter("pk", currentPk);
               ret = (LogSpeos) query.getSingleResult();
               wrappedData.put(currentPk, ret);
               return ret;
               } else {
               return ret;
               }
               }
               }
              
               /**
               * Unused rudiment from old JSF staff.
               */
               @Override
               public int getRowIndex() {
               throw new UnsupportedOperationException();
               }
              
               /**
               * Unused rudiment from old JSF staff.
               */
               @Override
               public Object getWrappedData() {
               throw new UnsupportedOperationException();
               }
              
               /**
               * Never called by framework.
               */
               @Override
               public boolean isRowAvailable() {
               if (currentPk == null) {
               return false;
               } else {
               Query query = entityManager.createQuery("SELECT c FROM LogSpeos AS c WHERE c.id=:pk");
               query.setParameter("pk", currentPk);
               if (query.getSingleResult() != null)
               return false;
               else
               return true;
               }
               }
              
               /**
               * Unused rudiment from old JSF staff.
               */
               @Override
               public void setRowIndex(int rowIndex) {
               throw new UnsupportedOperationException();
               }
              
               /**
               * Unused rudiment from old JSF staff.
               */
               @Override
               public void setWrappedData(Object data) {
               throw new UnsupportedOperationException();
               }
              
               /**
               * This method suppose to produce SerializableDataModel that will be
               * serialized into View State and used on a post-back. In current
               * implementation we just mark current model as serialized. In more
               * complicated cases we may need to transform data to actually serialized
               * form.
               */
               public SerializableDataModel getSerializableModel(Range range) {
               if (wrappedKeys != null) {
               return this;
               } else {
               return null;
               }
               }
              
               @Override
               public void update() {
               System.out.println("**************************** in UPDATE");
              
               }
              
              }
              


              any idea why my list empty?

              thx

              • 5. Re: Pagination in SQL for a dataTable and datascoller
                Nicolas U. Newbie

                and i use seam 2.2.0 with richfaces 3.3.1 on jboss 4.2.3.

                jboss, what else? ;)

                • 6. Re: Pagination in SQL for a dataTable and datascoller
                  Nicolas U. Newbie

                  Someone make a article on the wiki for this? :D

                  • 7. Re: Pagination in SQL for a dataTable and datascoller
                    Nicolas U. Newbie

                    I have make a DataProvider like in the richfaces sample.

                    but the function getItemsByrange() don't receive good argument. why?

                    public List<LogSpeos> getItemsByrange(Integer startPk, int numberOfRows, String sortField, boolean ascending) {
                     List<LogSpeos> ret = new ArrayList<LogSpeos>();
                     System.out.println("********************** IN getItemsByrange - " + startPk + " - " + numberOfRows + " - " + sortField + " - " + ascending);
                     Query q = entityManager.createQuery("SELECT l FROM LogSpeos l");
                     q.setFirstResult(startPk);
                     q.setMaxResults(numberOfRows);
                     ret = q.getResultList();
                     return ret;
                     }


                    display:

                    11:50:30,893 INFO [STDOUT] ********************** IN getItemsByrange - 0 - -1 - null - true


                    • 8. Re: Pagination in SQL for a dataTable and datascoller
                      Nicolas U. Newbie

                      the number of row is -1 but un my rich:datatable in xhtml i have:

                      rows="20"

                      If i force 20 in numberOfRows i have only the 20 first row, no datascroller...

                      • 9. Re: Pagination in SQL for a dataTable and datascoller
                        Nicolas U. Newbie

                        ok it's work without filterBy in the rich:column...

                        now how filter...