Group_concat in JPQL

Question

I created the following`query:

SELECT 
o.codigoChamado,
o.codigoOrcamento,
group_concat(o.conteudoOrcamento),
o.observacaoOrcamento
FROM 
Orcamentos  as o
group by
o.codigoChamado,
o.codigoOrcamento,
o.observacaoOrcamento
order by 
o.codigoOrcamento

When executed directly in the base I can get the expected result normally, but this query must be executed via JPQL and group_concat is not native to JPQL, there is something I can replace the group_concat but still get the same return?

Answer 1

You can use the FUNCTION JPQL to call native functions or functions created in the database.

To call it, you must provide the name of the function in the first parameter and in the other parameters you fill them with the parameters that go to the database function.

In your example, it would be:

FUNCTION(group_concat, o.conteudoOrcamento)

Your whole appointment looks like this:

String jpql = "SELECT o.codigoChamado, o.codigoOrcamento, FUNCTION(group_concat, 
    o.conteudoOrcamento), o.observacaoOrcamento FROM  Orcamentos o
    group by o.codigoChamado, o.codigoOrcamento, o.observacaoOrcamento order by o.codigoOrcamento"
Query query = em.createQuery(jpql);