vashu11

There is very nice web component for tabular data - jqGrid http://www.trirand.com/blog/

When I had to use it with Spring and Hibernate, I found several tutorials on how to do that.

http://krams915.blogspot.com.au/2012/01/spring-mvc-31-jqgrid-and-spring-data_8761.html
http://krams915.blogspot.com.au/2010/12/jqgrid-and-spring-3-mvc-integration.html
http://nisargpathak.blogspot.com.au/2014/02/spring-mvc-with-jqgrid-simplified.html

But neither of them can make use of filtering jqGrid feature. So I will decribe how to add filtering to abovementioned example of krams915.

First, we introduce JqGridSearchField class. It is fairly simple POJO that represents one filtering condition and has method criterion() that returns condition as org.hibernate.criterion.Criterion

import org.hibernate.criterion.*;
 
public class JqGridSearchField {
    private String field;
    private String op;
    private String data;
    
    public JqGridSearchField() {
    }
    
    public void setField(String field) {
        this.field = field;
    }
    
    public String getField() {
        return field;
    }
    
    public void setOp(String op) {
        this.op = op;
    }
    
    public String getOp() {
        return op;
    }
    
    public void setData(String data) {
        this.data = data;
    }
    
    public String getData() {
        return data;
    }
    
    public Criterion criterion() {
        if(op==null)
            return null;
        if(op.equals("cn") && data.equals("none"))
            return Restrictions.sqlRestriction(" ("+field+" IS NULL) ");
        if(op.equals("eq"))//we cast to varchar(255) only because there are problems with string and text in test mode - used in postgresql
            return Restrictions.sqlRestriction(" CAST("+field+" AS varchar(255) ) LIKE '"+data+"' ");
        if(op.equals("ne"))
            return Restrictions.sqlRestriction(" CAST("+field+" AS varchar(255) ) NOT LIKE '"+data+"' ");
        if(op.equals("lt"))
            return Restrictions.sqlRestriction(" CAST("+field+" AS varchar(255) ) < '"+data+"' ");
        if(op.equals("gt"))
            return Restrictions.sqlRestriction(" CAST("+field+" AS varchar(255) ) > '"+data+"' ");
        if(op.equals("cn"))
            return Restrictions.sqlRestriction(" CAST("+field+" as varchar(255) )  LIKE '%"+data+"%' ");
        if(op.equals("bw"))
            return Restrictions.sqlRestriction(" CAST("+field+" AS varchar(255) ) LIKE '"+data+"%' ");
        if(op.equals("ew"))
            return Restrictions.sqlRestriction(" CAST("+field+" AS varchar(255) ) LIKE '%"+data+"' ");  
        return null;
    }
}

Now, we can add JqGridSearch template.

import java.beans.IntrospectionException;
import java.beans.PropertyDescriptor;
import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.*;

import org.hibernate.criterion.*;

import org.hibernate.*;

import java.util.logging.Level;
import java.util.logging.Logger;

public class JqGridSearch {

    static protected Class clazz;
    private String groupOp;
    private List rules;
    private List transientFields;
    private List transientRules;
    
    public void setGroupOp(String groupOp) {
        this.groupOp = groupOp;
    }
    
    public String getGroupOp() {
        return groupOp;
    }
    
    public void setRules(List rules) {
        this.rules = rules;
    }
    
    public List getRules() {
        return rules;
    }

    private void initTransientData() throws NoSuchFieldException {
        if(rules == null)
            return;
        if(transientFields != null)
            return;

        transientFields = new ArrayList();
        transientRules = new ArrayList();;
        Iterator iterator1 = rules.iterator();// @Formula bean fields are accessed through formula's value
        while (iterator1.hasNext()) {
            JqGridSearchField jqGridSearchField = iterator1.next();
            Field field = clazz.getDeclaredField( jqGridSearchField.getField() );
            Annotation formulaAnnotation = field.getAnnotation(org.hibernate.annotations.Formula.class);
            if( formulaAnnotation != null )
                jqGridSearchField.setField(((org.hibernate.annotations.Formula)formulaAnnotation).value());
            Annotation transientAnnotation = field.getAnnotation(javax.persistence.Transient.class);
            if( transientAnnotation != null ) {
                transientFields.add(jqGridSearchField.getField());
                transientRules.add(jqGridSearchField);
            }
        }
    }

    public void addFiltersToCriteria(Criteria criteria) throws NoSuchFieldException {
        if(groupOp==null)
            return;
        initTransientData();

        Junction junction = null;
        if(groupOp.equals("OR"))
            junction = Restrictions.disjunction();
        if(groupOp.equals("AND"))
            junction = Restrictions.conjunction();
        if(junction==null)
            return;
        Iterator iterator = rules.iterator();
        while (iterator.hasNext()) {
                JqGridSearchField jqGridSearchField = iterator.next();
                if( !transientFields.contains(jqGridSearchField.getField()) )
                    junction = junction.add(jqGridSearchField.criterion());
            }
            
        criteria.add(junction);
    }
    
    static public boolean isTransient(String fieldname) {
        if(fieldname == null)
            return false;
        Field field = null;
        try {
            field = clazz.getDeclaredField( fieldname );
        } catch (NoSuchFieldException ex) {} 
          catch (SecurityException ex)    {}
        Annotation transientAnnotation = (field == null)? null : field.getAnnotation(javax.persistence.Transient.class);
        return transientAnnotation != null;
    }

    public void sortTransientField(List list, String sidx, String sord) {
        if(list == null)
            return;
        if(!isTransient(sidx))
            return;
        if( (sord==null) || (!sord.equals("asc") && !sord.equals("desc")) )
            return;
        Collections.sort(list, new UniversalComparator(clazz, sidx));
        if(sord.equals("desc"))
            Collections.reverse(list);
    }
    
    public List filterTransientFields(List list) {
        try {
            initTransientData();
        } catch (NoSuchFieldException e) {}

        if(transientFields == null)
            return null;
        if(transientFields.size() == 0)
            return list;
        List result = new ArrayList();
        Iterator iterator = list.iterator();
        while (iterator.hasNext()) {
                T elem = iterator.next();
                Iterator iterator2 = transientRules.iterator();
                while (iterator2.hasNext()) {
                    JqGridSearchField field = iterator2.next();
                    String fieldValue;
                    Method getter = null;
                    try {
                        getter = new PropertyDescriptor(field.getField(), clazz).getReadMethod();
                    } catch (IntrospectionException ex) {
                        Logger.getLogger(JqGridSearch.class.getName()).log(Level.SEVERE, null, ex);
                    }
                    try {
                        if(getter.invoke(elem) == null)
                            break;
                        if(Date.class == getter.getReturnType()) {
                            fieldValue = (new SimpleDateFormat("yyyy-MM-dd")).format((Date) getter.invoke(elem));
                        } else {
                            fieldValue = (String) getter.invoke(elem);
                        }
                    } catch (Exception ex) {
                        break;
                    }
                    if(fieldValue.contains(field.getData())) {
                        result.add(elem);
                        break;
                    }
                }
            }
        
        return result;
    }
}

We use it as parent for classes like JqGridSearchUser.

import com.griddynamics.pmo.model.User;

public class JqGridSearchUser extends JqGridSearch[Unknown site tag]{
    static {
        clazz = User.class;
    }
}

Now JqGridSearchStaffPositionElem has class info on User. User is hibernate Entity and we can easily map it on DB.

JqGridSearch will take care of both transient and non transient fields.

Now all filtering is done by this snippet

In controller 

    @RequestMapping(value = "/ccenter/all", method = RequestMethod.GET)
    @ResponseBody
    public JqGridResponce listAllUsers(
                @RequestParam(value="sidx", required=false) String sidx,
                @RequestParam(value="sord", required=false) String sord,
                @RequestParam(value="_search", required=false) boolean search,
                @RequestParam(value="filters", required=false) String filters) { 
        List[Unknown site tag] users = userDaoDb.listUsersInJsGrig(sidx, sord, filters);
        return new JqGridResponce(users);
    }

In DB service(extends AbstractHibernateDaoImpl[Unknown site tag])

    @SuppressWarnings("unchecked")
    @Override
    public List[Unknown site tag] listUsersInJsGrig(String sidx, String sord, String filters) {
        return em.execute(new HibernateCallback>() {
            private String sidx;
            private String sord;
            private String filters;
            private HibernateCallback> init(String sidx, String sord, String filters){
                this.sidx = sidx;
                this.sord = sord;
                this.filters = filters;
                return this;
            }            
            public List[Unknown site tag] doInHibernate(Session session) throws HibernateException, SQLException {
                Criteria usersCriteria = session.createCriteria(User.class);
                
                ObjectMapper mapper = new ObjectMapper();
                JqGridSearchUser jqGridSearch = null;
                try {
                    if( (filters != null) && !filters.isEmpty() )
                        jqGridSearch = mapper.readValue(filters, JqGridSearchUser.class);// load filtering conditions
                } catch (IOException ex) {
                    return null;
                }
                
                if(jqGridSearch != null)
                    try {    // SQL fiter
                        jqGridSearch.addFiltersToCriteria(usersCriteria);// filter
                    } catch (NoSuchFieldException ex) {
                        java.util.logging.Logger.getLogger(UserDaoImpl.class.getName()).log(Level.SEVERE, null, ex);
                    }
                
                if( !JqGridSearchUser.isTransient(sidx) && (sidx != null) ) { // SQL sort
                    if( (sord != null) && (sord.equals("asc")) )
                        usersCriteria.addOrder(Order.asc(sidx));
                    if( (sord != null) && (sord.equals("desc")) )
                        usersCriteria.addOrder(Order.desc(sidx));
                }
                
                List[Unknown site tag] result = usersCriteria.list();
                if(jqGridSearch != null) {
                    result = jqGridSearch.filterTransientFields(result); // Transient fiter
                    if( JqGridSearchUser.isTransient(sidx) )
                        jqGridSearch.sortTransientField(result, sidx, sord); // Transient sort
                }
                

                return result;
            }
        }.init(sidx, sord, filters) );
    }    
}