help with nativeQuery

0

I tried to do the query uasndo expression constructors and jpql, but jpql does not eceita subqueryes and I'm trying to do with nativeQuery. Is it possible?

I'm getting an error message. Would anyone know why?

The query is as follows:

StringBuilder jpql = new StringBuilder();
        jpql.append("select a2.codigoProduto , a2.descricao, a2.quantidade_recente, Soma ,a2.valor, a2.valor * Soma from")
                .append("(select p.codigoProduto,  sum(p.quantidade_recente) Soma   from Produto p ,Usuario u ")
                .append("where u.codigo = p.codigo_filial and u.codigo_chefe = 3  ) as a1,")
                .append(" (SELECT p1.codigoProduto, p1.descricao, p1.quantidade_recente , p1.valor from Produto aS p1,  Usuario u ")
                .append("where u.codigo = p1.codigo_filial and u.codigo_chefe = 3 ) as a2 ")
                .append("where a2.codigoProduto = a1.codigoProduto");

        // metodo buscarPorPaginacao
        Query query = manager.createNativeQuery(jpql.toString(), Filtro.class);
        // query.setParameter("codigo", 3L);
        @SuppressWarnings("unchecked")
        List<Filtro> resultado = query.getResultList();
        for (Filtro prod : resultado) {
            System.out.println(" Impressão Filtro da Empresa: ");
            System.out.println(" CodigoProduto : " + prod.getCodigoProduto() + " Produto " + prod.getDescricao()
                    + " Quantidade " + prod.getQuantidade() + " Total " + prod.getSoma() + " Receita " + prod.getReceita() );

        }

The query that exits on the console is as follows:

 select
    a2.codigoProduto ,
    a2.descricao,
    a2.quantidade_recente,
    Soma ,
    a2.valor,
    a2.valor * Soma 
from
    (select
        p.codigoProduto,
        sum(p.quantidade_recente) Soma   
    from
        Produto p ,
        Usuario u 
    where
        u.codigo = p.codigo_filial 
        and u.codigo_chefe = 3  ) as a1,
    (SELECT
        p1.codigoProduto,
        p1.descricao,
        p1.quantidade_recente ,
        p1.valor 
    from
        Produto aS p1,
        Usuario u 
    where
        u.codigo = p1.codigo_filial 
        and u.codigo_chefe = 3 ) as a2 
where
    a2.codigoProduto = a1.codigoProduto

It runs in mysql and returns results correctly, but in the class I'm testing the following error occurs:

Caused by: java.sql.SQLException: Column 'codigo' not found.
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:996)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:935)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:924)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:870)
at com.mysql.jdbc.ResultSetImpl.findColumn(ResultSetImpl.java:1064)
at com.mysql.jdbc.ResultSetImpl.getLong(ResultSetImpl.java:2743)
at com.mchange.v2.c3p0.impl.NewProxyResultSet.getLong(NewProxyResultSet.java:2478)
at org.hibernate.type.descriptor.sql.BigIntTypeDescriptor$2.doExtract(BigIntTypeDescriptor.java:74)
at org.hibernate.type.descriptor.sql.BasicExtractor.extract(BasicExtractor.java:64)
at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:267)
at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:263)
at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:253)
at org.hibernate.type.AbstractStandardBasicType.hydrate(AbstractStandardBasicType.java:338)
at org.hibernate.loader.Loader.extractKeysFromResultSet(Loader.java:785)
at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:721)
at org.hibernate.loader.Loader.processResultSet(Loader.java:953)
at org.hibernate.loader.Loader.doQuery(Loader.java:921)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:355)
at org.hibernate.loader.Loader.doList(Loader.java:2554)
... 10 more
    
asked by anonymous 07.07.2017 / 18:23

1 answer

0

The problem occurs because the Filter entity is waiting for the code field. As this field is not located in the query result it is displaying this message. If this query can not be changed, I suggest creating a POJO to solve the problem. So the code looks like this:

Query query = manager.createNativeQuery(jpql.toString(), PojoCriado.class);

If you did not understand the answer, I suggest that you post the Filter entity. :)

    
11.07.2017 / 19:28