How to consult with JPQL or CriteriaBuilder?

I share the following code, the first method I extract the information from a Datatable, in which something is searched in a search filter:

public String getListaCobrador(HttpServletRequest request, HttpServletResponse response) {
    EntityManager em = null;
    EntityManagerFactory emf = Persistence.createEntityManagerFactory("algunPU");
    em = emf.createEntityManager();  
    boolean all = false;
    String echo = (String) request.getParameter("sEcho");
    Integer paginado = new Integer((String) request.getParameter("iDisplayLength"));
    Integer paginaDesde = new Integer((String) request.getParameter("iDisplayStart"));
    String search = (String) request.getParameter("sSearch");
    int sortColumn = Integer.parseInt((String) request.getParameter("iSortCol_0"));
    String typeSort = (String) request.getParameter("sSortDir_0");

    System.out.println("Data json-->Echo:" + echo + " Paginado:" + paginado + " DisplayStart:" + paginaDesde + " Search:" + search);
    if (paginado == -1) {
        all = true;
    } 
    String msg = null;
    try { 
        CobradorJpaController cobCtrl = new CobradorJpaController ();
        List<Cobrador> lisOfCobrador = cobCtrl.findCobradorByJPQL(search, all, paginado, paginaDesde, sortColumn, typeSort);
        int registros = cobCtrl.getCobradorCount(search);
    } catch (Exception e) {
        e.printStackTrace();
    } finally{
        if(null != em){
            em.close();
        }
    }
    return msg;
}

In the above method the methods are executed:

findCounterByJPQL // Which throws the list of collectors who made match with whatever you searched for "July"

getCountCount // Which throws a Count! of how many match you found of collectors!

Then: findCounterByJPQL

public List<Cobrador> findCobradorByJPQL(String sSearch, boolean all, int maxResults, int firstResult, int sortColumn, String order) {
    EntityManager em = getEntityManager();
    StringBuilder q;
    q = new StringBuilder();

    try {
        //TEN CUIDADO!!! EN JPQL LA TABLA DEBE LLAMARSE TAL CUAL SE LLAMA EN DB OSEA Factura, NO "FACTURA" A JPQL SI LE IMPORTAL EL camelCase
        q.append("SELECT cobrador FROM Cobrador cobrador WHERE 1=1");
        //sSearch es lo que sea que hayas buscado "Juli" o "14695"
        if (sSearch != null && sSearch.length() > 0){
            String[] words = sSearch.split(" "); //por si buscas "Juli 14695"
            for (String word : words)                    
            {
              //cada q.append son en las columnas que quieres que tu filtro busque datos
              String valorParam ="'%"+word.toUpperCase()+"%'";
              q.append(" AND (cobrador.cedula LIKE ").append(valorParam).append(" ");
              q.append(" OR UPPER(cobrador.fkCobrador_CEDULA.fkPrestamo.saldo) LIKE ").append(valorParam).append(" ");
              q.append(" OR UPPER(cobrador.fkCobrador_CEDULA.fkPrestamo.fkCliente_prestamo.fkCliente.fkPersona.nombre) LIKE ").append(valorParam).append(" ) ");
            }
        }
        //las columnas de una tabla, dependiendo a que le des click es lo que vas a sortear
        switch(sortColumn){
            case 1:
                q.append(" ORDER BY cobrador.cedula ").append(order.toUpperCase()).append(" ");                    
                break;
            case 2:
                q.append(" ORDER BY cobrador.fkCobrador_CEDULA.fkPrestamo.saldo ").append(order.toUpperCase()).append(" ");                    
                break;
            case 3:
                q.append(" ORDER BY cobrador.fkCobrador_CEDULA.fkPrestamo.fkCliente_prestamo.fkCliente.fkPersona.nombre ").append(order.toUpperCase()).append(" ");                    
                break;
            default:
                q.append(" ORDER BY cobrador.cedula DESC ").append(" "); 
                break;
        }

        Query query = em.createQuery(q.toString());

        if(!all){
            query.setMaxResults(maxResults);
            query.setFirstResult(firstResult);
        }
        return query.getResultList();
    } catch(Exception ex){
        ex.printStackTrace();
        return null;
    } finally {
        em.close();
    }
}

Then: getCoradorCount

public int getCobradorCount(String sSearch) {
    EntityManager em = getEntityManager();

    StringBuilder q;
    q = new StringBuilder();

    try {          
        Number numero;
        //TEN CUIDADO!!! EN JPQL LA TABLA DEBE LLAMARSE TAL CUAL SE LLAMA EN DB OSEA Factura, NO "FACTURA" A JPQL SI LE IMPORTAL EL camelCase
        q.append("SELECT Count (cobrador.pkCobrador) FROM Cobrador cobrador WHERE 1=1");


        if (sSearch != null && sSearch.length() > 0){
            String[] words = sSearch.split(" "); 
            for (String word : words)                    
            {
              String valorParam ="'%"+word.toUpperCase()+"%'";
              q.append(" AND (cobrador.cedula LIKE ").append(valorParam).append(" ");
              q.append(" OR UPPER(cobrador.fkCobrador_CEDULA.fkPrestamo.saldo) LIKE ").append(valorParam).append(" ");
              q.append(" OR UPPER(cobrador.fkCobrador_CEDULA.fkPrestamo.fkCliente_prestamo.fkCliente.fkPersona.nombre) LIKE ").append(valorParam).append(" ) ");
            }
        }

        Query query = em.createQuery(q.toString());

        numero = ((Long)query.getSingleResult()).intValue();
        return numero.intValue();
    } finally{
        em.close();
    }
}

Summarizing the 2 methods and as a result, throws you 1 Object type List and an int with a count of how many match did!

Take into account that if you want to use JPQL your Entity must have well marked the relationships between the tables in such a way that you can extract the results as: cobrador.fkCobrador_CEDULA.fkPrestamo.saldo this has to be transparent, a "." it's like doing "JOIN" (collector JOIN Cobrador_CEDULA JOIN Prestamo.Saldo)

I hope it serves you, any doubt with pleasure we see it.

Greetings.