New DataTable with Lazy Loading. Sort and filter.

[jgenchik]

It looks like the new (PrimeFaces-2.2.M1-SNAPSHOT) DataTable with lazy loading supports sorting and filtering. The showcase does not use these features. Is there an example that someone could point me to? I am guessing that the sort column that is passed to the load() method could be passed to JPA, but i am not sure how to handle filtering.

[wikisky]

Something like:

Code: Select all

public List<Car> load(int first, int pageSize, String sortField, boolean sortOrder, Map<String,String> filters) {  
    StringBuilder where = null;
    Map<String,Object> params=new HashMap<String, Object>();
    if (filters!=null) {
        for(Map.Entry<String,String> e:filters.entrySet()) {
            if (e.getValue()!=null && !e.getValue().trim().isEmpty()) {
                if (where==null) where=new StringBuilder(" WHERE "); else where.append(" AND ");
                where.append("t.").append(e.getKey()).append(" LIKE :).append(e.getKey());
                params.put(e.getKey(), e.getValue()+'%');
            }
        }
        StringBuilder sb=new StringBuilder("SELECT t FROM MyTable t");
        if (where!=null) sb.append(where);
        if (sortField!=null && !sortField.isEmpty()) {
            sb.append(" ORDER BY t.").append(sortField);
            if (!sortOrder) sb.append(" DESC");
        }
        Query q=getEntityManager().createQuery(sb.toString());
        for(Map.Entry<String,Object> e:params.entrySet()) q.setParameter(e.getKey(), e.getValue());
        return q.setFirstResult(first).setMaxResults(pageSize).getResultList();

}

[cagatay.civici]

We'll add a JPA based example and a sample implementation to the moviecollector app which is jsf-spring-jpa based.

As in wikisky answer you just need to build your query depending on the state passed to you from datatable. If the column sortBy is #{car.year} sortField will be year and if filterBy is #{car.year} entry in map will be year-filterValue.

[jgenchik]

Thank you very much for your answers. Can't wait till Monday to try to implement it.

Thanks again.

[cagatay.civici]

Since p:dataTable is implemented from scratch any feedback on making it more stable is appreciated.

[jgenchik]

When I am typing filtering data, back end is not being called.

The p:datatable is lazy loading, has both sort and filtering enabled. I tried with

Code: Select all

dynamic="true"

and

Code: Select all

dynamic="false"

with the same result.

The page is:

Code: Select all

<p:dataTable id="rolesDataTable" value="#{primeFacesBean.lazyRolesModel}" var="role" 
					paginator="true" rows="10" paginatorPosition="bottom"
					dynamic="true" lazy="true">
					
				<p:column sortBy="#{role.id}" parser="number" filterBy="#{role.id}">
					<f:facet name="header">id</f:facet>
					<h:outputText value="#{role.id}" />
				</p:column>

				<p:column sortBy="#{role.name}" filterBy="#{role.name}">
					<f:facet name="header">name</f:facet>
					<h:outputText value="#{role.name}" />
				</p:column>
				
				<p:column sortBy="#{role.description}" filterBy="#{role.description}">
					<f:facet name="header">description</f:facet>
					<h:outputText value="#{role.description}" />
				</p:column>
			</p:dataTable>

and the back end CDI Bean is:

Code: Select all

lazyRolesModel = new LazyDataModel<RoleEntity>(){
			@Override  
            public List<RoleEntity> load(int first, int pageSize, String sortField, boolean sortOrder, Map<String, String> filters) {  
                log.info(" ************ Loading the lazy role data. first: "+first+" pageSize: "+pageSize+" sortField: "+sortField+" sortOrder: "+sortOrder+" filters: "+filters);  
                List<RoleEntity> lazyCars = new ArrayList<RoleEntity>(); 
                StringBuilder where = null;
                Map<String,Object> params=new HashMap<String, Object>();
                
                if (filters!=null) {
                	log.info(" *************** filters is not null");
                    for(Map.Entry<String,String> e : filters.entrySet()) {
                        if (e.getValue()!=null && !e.getValue().trim().isEmpty()){
                        	log.info(" *********** key: "+e.getKey()+" value: "+e.getValue());
                        	
                        	if (where==null){
                        		where=new StringBuilder(" WHERE ");
                        	}else{
                        		where.append(" AND ");
                        		where.append("t.").append(e.getKey()).append(" LIKE :").append(e.getKey());
                                params.put(e.getKey(), e.getValue()+'%');
                        	}
                            
                        }
                    }
                }
                
                
                StringBuilder sb = new StringBuilder("SELECT r FROM RoleEntity r");
                if (where!=null){
                	sb.append(where);
                }
                
                if(sortField!=null && !sortField.isEmpty()) {
                    sb.append(" ORDER BY r.").append(sortField);
                    if (!sortOrder){
                    	sb.append(" DESC");
                    }
                }
                
                
                Query q = em.createQuery(sb.toString());
                for(Map.Entry<String,Object> e:params.entrySet()){
                	q.setParameter(e.getKey(), e.getValue());
                }
                
                return q.setFirstResult(first).setMaxResults(pageSize).getResultList();
            }  
		};
		
	}

When data is typed into filter, table is being filtered, but incorrectly. Nothing in the back end is being called.
Note that instead of JSF managed bean I am using a CDI bean. I am not sure if that is relevant.

[jgenchik]

Because p:dataTable component is new, i would like to report the following problems:

PrimeFaces 2.2 M1 SNAPSHOT that I downloaded on 8/27/2010 (so i think it was built on 8/26): lazy loaded datatable filtering did not work. Nothing was triggered on the back end when user typed in a filter.

PrimeFaces 2.2 M1 SNAPSHOT that I downloaded on 8/31/2010: p:datatable works fine but p:calendar and p:autoComplete stopped working.

[cagatay.civici]

Does paging and sorting invoke your lazy load method? Is it just filter that has an issue?

[cagatay.civici]

I also can't replicate autocomplete and calendar issues you've faced.

[jgenchik]

I am using a small demo app. If you like i could send you the page ad the managed bean. If you need me to do any specific tests, i would be happy to do that too.