Hibernate - .ast.QuerySyntaxException: unexpected AST node error?

Hibernate ORM
1

My JPQL Query is as below -

@Query("SELECT p.packageName FROM Packages p WHERE CASE WHEN p.packageId.packageId IS NULL THEN TRUE WHEN p.packageId.packageId=:packageId THEN TRUE ELSE FALSE END")
List<String> getPackageByAccountIdAndPackageId(Long packageId);

I am facing below error on compiling the above code -

Caused by: java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected AST node: CASE near line 1, column 80 [SELECT p.packageName FROM com.entities.Packages p WHERE CASE WHEN p.packageId.packageId IS NULL THEN TRUE WHEN p.packageId.packageId=:packageId THEN TRUE ELSE FALSE END]
	at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:138) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
	at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:181) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
	at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:188) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
	at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:729) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
	at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:104) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:na]
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
	at java.base/java.lang.reflect.Method.invoke(Method.java:566) ~[na:na]
	at org.springframework.orm.jpa.ExtendedEntityManagerCreator$ExtendedEntityManagerInvocationHandler.invoke(ExtendedEntityManagerCreator.java:350) ~[spring-orm-5.1.9.RELEASE.jar:5.1.9.RELEASE]
	at com.sun.proxy.$Proxy137.createQuery(Unknown Source) ~[na:na]
	at org.springframework.data.jpa.repository.query.SimpleJpaQuery.validateQuery(SimpleJpaQuery.java:87) ~[spring-data-jpa-2.1.10.RELEASE.jar:2.1.10.RELEASE]
	... 58 common frames omitted
Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected AST node: CASE near line 1, column 80 [SELECT p.packageName FROM com.entities.Packages p WHERE CASE WHEN p.packageId.packageId IS NULL THEN TRUE WHEN p.packageId.packageId=:packageId THEN TRUE ELSE FALSE END]
	at org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:74) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
	at org.hibernate.hql.internal.ast.ErrorTracker.throwQueryException(ErrorTracker.java:93) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
	at org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:277) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
	at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:191) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
	at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:143) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
	at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:119) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
	at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:80) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
	at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:153) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
	at org.hibernate.internal.AbstractSharedSessionContract.getQueryPlan(AbstractSharedSessionContract.java:611) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
	at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:720) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
	... 66 common frames omitted

Any guidance/help on how to resolve this will be greatly appreciated.

I am using spring-data-jpa - 2.1.7.RELEASE and hibernate core - 5.3.10.FINAL

2

That kind of predicate is simply not supported. Just use it like this:

SELECT p.packageName 
FROM Packages p 
WHERE p.packageId.packageId IS NULL 
OR p.packageId.packageId=:packageId
3

I have a similar error in my query. It is my Enum class:

    FILTER_PERFIL_POR_NOME("perfil", "nome", "p.nome"),
    FILTER_PERFIL_POR_QUANTIDADE_USUARIO("perfil","usuario", "(SELECT COUNT(ppu.id) FROM PerfilUsuario ppu JOIN ppu.perfil pf where pf.id = p.id )"),
    FILTER_PERFIL_POR_SETOR("perfil","setor", "p.setor.nome"),
    FILTER_PERFIL_POR_SITUACAO("perfil","ativo", "p.ativo"),
    ;

    private String nomeEntidade;
    private String coluna;
    private String orderBy;

    private OrderByEnum(String nomeEntidade, String coluna, String orderBy) {
        this.nomeEntidade = nomeEntidade;
        this.coluna = coluna;
        this.orderBy = orderBy;
    }

    public String getNomeEntidade() {
        return nomeEntidade;
    }
    public void setNomeEntidade(String nomeEntidade) {
        this.nomeEntidade = nomeEntidade;
    }
    public String getColuna() {
        return coluna;
    }
    public void setColuna(String coluna) {
        this.coluna = coluna;
    }
    public String getOrderBy() {
        return orderBy;
    }

    public void setOrderBy(String orderBy) {
        this.orderBy = orderBy;
    }

    public static OrderByEnum get(String nomeEntidade, String coluna) {
        for (OrderByEnum orderBy: OrderByEnum.values()) {
            if (orderBy.getNomeEntidade().equals(nomeEntidade)
                    && orderBy.getColuna().equals(coluna)) {
                return orderBy;
            }
        }
        throw new NegocioException("Coluna para ordenação não reconhecida");
    }

And I am doing the sorting from my repository file

	public PanacheQuery<PerfilEntity> findAllByFilters(int pageIndex, Integer size, String nomePerfil, Boolean situacao,
			String setor, String ordenacao, Boolean ordenacaoDsc) {
		if ( (nomePerfil == null || nomePerfil.isBlank())
				&& situacao == null
				&& (setor == null || setor.isBlank()) && ordenacao == null && ordenacaoDsc == null) {
			return findAll(Sort.by("nome").ascending()).page(pageIndex, size);
		}
		
		StringBuilder query = new StringBuilder();
		query.append(" SELECT p from Perfil p ");
		query.append(" where 1 = 1 ");

		Map<String, Object> params = new HashMap<>();
		
		if (nomePerfil != null && !nomePerfil.isBlank()) {
			query.append(" and UPPER(p.nome) like :nomePerfil ");
			params.put("nomePerfil", "%" + nomePerfil.toUpperCase() + "%");
		}
		if (situacao != null) {
			query.append(" and p.ativo = :ativo ");
			params.put("ativo", situacao);
		}
		if (setor != null && !setor.isBlank()) {
			query.append(" and p.setor.sigla = :setor ");
			params.put("setor", setor);
		}

		// implementacao da ordenacao de registro por colunas
		if(ordenacao!=null && ordenacaoDsc != null){
			Sort sort = ordenacaoDsc	? Sort.descending(OrderByEnum.get("perfil", ordenacao).getOrderBy())
										: Sort.ascending(OrderByEnum.get("perfil", ordenacao).getOrderBy());
			PanacheQuery<PerfilEntity> pQuery = find(query.toString(), sort, params);
			if (size != null) {
				pQuery.page(pageIndex, size);
			}
			return pQuery;
		}

		return find(query.toString(), Sort.by("p.nome").ascending(), params).page(pageIndex, size);
	}

the result is the picture below

image
image1863×293 54.3 KB

What is wrong on my code?

4

You can’t order by a subquery in HQL in older versions, and in fact, some databases also don’t support that. Generally, the workaround is to create a select item with an alias and order by the alias.