org.hibernate.hql.internal.ast.QuerySyntaxException: Invalid path on EntityManager createQuery

Hibernate ORM
1

I am sending from the frontend a value to search on two properties of my entity Producto. That properties are Codigo and Descripcion.

The issue is that when the line TypedQuery<Long> typedQuery = em.createQuery(queryCount); hits, this exception is thrown:

queryString= org.hibernate.hql.internal.ast.QuerySyntaxException: Invalid path: ‘generatedAlias1._codigo’ [select count(generatedAlias0) from com.its.entidades.db.Producto as generatedAlias0 where ( generatedAlias1._codigo like :param0 ) and ( generatedAlias1._descripcion like :param1 )]

detailMessage= Invalid path: ‘generatedAlias1._codigo’

The weird thing is that if I comment the quoted line, and in consequence the two lines below, everything runs as expected. But I need to get the total of the registers filtered, so I need to count them.

ProductoService.java

@Override
public ServiceResponse<List<Producto>> ObtenerListaPaginada(ParametrosListadoModelo parametros) {

    ServiceResponse<List<Producto>> ret = new ServiceResponse<>();
    ret.setListadoModelo(parametros);

    try {
        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<Producto> query = cb.createQuery(Producto.class);

        CriteriaQuery<Long> queryCount = cb.createQuery(Long.class);
        queryCount.select(cb.count(queryCount.from(Producto.class)));

        Root<Producto> entity = query.from(Producto.class);
        TypedQuery<Producto> tq = null;

        if (parametros.getBusqueda() != null && !parametros.getBusqueda().isEmpty()) {
            String queryFilter = "%" + parametros.getBusqueda() + "%";

            List<Predicate> predicates = new ArrayList<>();
            predicates.add(cb.like(entity.<String>get("_codigo"), queryFilter));
            predicates.add(cb.like(entity.<String>get("_descripcion"), queryFilter));

            query.where(predicates.toArray(new Predicate[]{}));
            queryCount.where(predicates.toArray(new Predicate[]{}));
        }

        // Count for total
        TypedQuery<Long> typedQuery = em.createQuery(queryCount);
        Long count = typedQuery.getSingleResult();
        ret.getListadoModelo().setTotalRegistros(count);

        // Order by
        if (parametros.getCampoOrdenamiento().equals("codigo"))
            parametros.setCampoOrdenamiento("_codigo");
        if (parametros.getCampoOrdenamiento().equals("descripcion"))
            parametros.setCampoOrdenamiento("_descripcion");

        query.orderBy(parametros.getDireccionOrdenamiento().equals("ASC") ? cb.asc(entity.get(parametros.getCampoOrdenamiento())) : cb.desc(entity.get(parametros.getCampoOrdenamiento())));

        // Paginator
        tq = em.createQuery(query);
        tq.setFirstResult((int) ((parametros.getNumeroPagina() - 1) * parametros.getCantidadElementos()));
        tq.setMaxResults((int) (parametros.getCantidadElementos()));
        ret.setData(tq.getResultList());
    } catch (Exception ex) {
        ret.getErrores().add(new ServicioError(ex));
    }

    return ret;
}

Producto.java

@Entity(name = "Producto")
public class Producto {

    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(name = "ProductoID")
    private int _productoID;

    @Column(name = "cCodigo")
    private String _codigo;

    @Column(name="cDescripcion")
    private String _descripcion;

    @JsonProperty("codigo")
    public String getCodigo() {
        return _codigo;
    }

    @JsonProperty("codigo")
    public void setCodigo(String _codigo) {
        this._codigo = _codigo;
    }

    @JsonProperty("descripcion")
    public String getDescripcion() {
        return _descripcion;
    }

    @JsonProperty("descripcion")
    public void setDescripcion(String _descripcion) {
        this._descripcion = _descripcion;
    }
}

How can I solve this and why is it happening?

2

Why do you create an extra count query when you can just get that from the size of the Product list you are fetching?

1 Like
3

If you have 2 aliases in the generated query, it’s because the Query analyser thinks you are using 2 times the same entity, and I don’t see another suspect to create that kind of problem that the fact you reuse the predicates.
Guessing: when you add predicates using query.where it is probably introducing a reference to the root of the query somewhere in the predicate, then when you add the same predicate to another query you “pollute” it with another root entity.

(if my theory is correct, reverse the 2 query….where()and the exception should be on the other query…)

1 Like
4

If I reverse the 2 query…where, the issue persist on the same line. Very weird.

5

But I am fetching only the 10 first results (or 25 or 100 if I selected it in the front end).
And what I need if to count all the records in my DB with that conditions.

6

Try to replicate it with this test case first which wwe can take a look on.

7

Well, try to NOT share the predicates between the 2 queries… and this means creating them twice.

8

@vlad, @p3consulting

Finally solved refactoring:

try{
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Producto> query = cb.createQuery(Producto.class);

    CriteriaQuery<Long> queryCount = cb.createQuery(Long.class);
    Root<Producto> entityRoot = queryCount.from(query.getResultType());
    queryCount.select(cb.count(entityRoot));

    Root<Producto> entity = query.from(Producto.class);
    TypedQuery<Producto> tq;

    //And the rest of the code is the same as the original one.
    //...
}