help with nativeQuery

Asked

Viewed 153 times

0

I tried to query uasndo Expression constructors and jpql , but jpql does not subqueryes and I am , so I try to do with nativeQuery . It is possible?

I am getting error message. Would anyone know why?

The query is the following :

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 comes out in 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
java hibernate jpql

  • So, if you copy the console query and run it in Mysql it works perfectly? Because in the console it says that the source column does not exist in the database. It must be the'u.codigo 'of the subquery.

  • I also thought so I took the u.code, but the error message continues

  • Complicated then. Just try to run the main select. If you pass, add the first subquery and so on. Maybe this way you can identify where exactly the problem might be.

  • It would be better to do with Criteria?

  • I can’t say, it depends on your knowledge with Criteria.

1 answer

0

The problem occurs because the entity Filter is waiting for the code field. As this field is not located in the query result is presenting this message. If this query cannot be changed, I suggest creating a POJO to solve the problem. Therefore, the code would look similar to this:

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

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

Browser other questions tagged java hibernate jpql

You are not signed in. Login or sign up in order to post.