Discussoes de Programacao e Tecnologia

[Duvida] Join com OneToMany HQL

1 resposta
GabrielCardelli
GabrielCardelli

Opa, beleza pessoal?

Estou com uma query a fazer e estou tendo problemas com HQL.

Tenho

1 Funcionário que pode ter N Números de Telefones.

Tenho que fazer uma busca do funcionário com seus telefones ou com nome ou com telefone como chave de pesquisa.

Partindo deste principio fiz a seguinte Query:

String hqlQuery = "FROM Cliente c, Telefone t " +
                          "WHERE c.telefones.id = t.id  " +
                          "AND ";

        
         if(!(nome == null || nome.equals(""))){
             hqlQuery += "c.nome like '%" + nome + "%'" ;
         }else
         if(!(telefone == null || telefone.equals(""))){
           
             hqlQuery += " t.numero = " + telefone;
         }
isso me retornou a seguinte exception: 
1562 [AWT-EventQueue-0] INFO org.hibernate.impl.SessionFactoryObjectFactory - Not binding factory to JNDI, no JNDI name configured
Exception in thread "AWT-EventQueue-0" org.hibernate.QueryException: illegal attempt to dereference collection [cliente0_.id.telefones] with element property reference [id] [FROM br.com.pizzaria.entity.Cliente c, br.com.pizzaria.entity.Telefone t WHERE c.telefones.id = t.id  AND c.nome like '%Gabriel%']
        at org.hibernate.hql.ast.tree.DotNode$1.buildIllegalCollectionDereferenceException(DotNode.java:69)
        at org.hibernate.hql.ast.tree.DotNode.checkLhsIsNotCollection(DotNode.java:536)
        at org.hibernate.hql.ast.tree.DotNode.resolve(DotNode.java:244)
        at org.hibernate.hql.ast.tree.FromReferenceNode.resolve(FromReferenceNode.java:117)
        at org.hibernate.hql.ast.tree.FromReferenceNode.resolve(FromReferenceNode.java:113)
        at org.hibernate.hql.ast.HqlSqlWalker.resolve(HqlSqlWalker.java:750)
        at org.hibernate.hql.antlr.HqlSqlBaseWalker.expr(HqlSqlBaseWalker.java:1216)
        at org.hibernate.hql.antlr.HqlSqlBaseWalker.exprOrSubquery(HqlSqlBaseWalker.java:4041)
        at org.hibernate.hql.antlr.HqlSqlBaseWalker.comparisonExpr(HqlSqlBaseWalker.java:3525)
        at org.hibernate.hql.antlr.HqlSqlBaseWalker.logicalExpr(HqlSqlBaseWalker.java:1762)
        at org.hibernate.hql.antlr.HqlSqlBaseWalker.logicalExpr(HqlSqlBaseWalker.java:1687)
        at org.hibernate.hql.antlr.HqlSqlBaseWalker.whereClause(HqlSqlBaseWalker.java:776)
        at org.hibernate.hql.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:577)
        at org.hibernate.hql.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:281)
        at org.hibernate.hql.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:229)
        at org.hibernate.hql.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:251)
        at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:183)
        at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:134)
        at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:101)
        at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:80)
        at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:94)
        at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:156)
        at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:135)
        at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1650)
        at br.com.pizzaria.dao.ClienteDAO.load(ClienteDAO.java:50)
        at br.com.pizzaria.service.ClienteService.getClientFormList(ClienteService.java:230)
        at br.com.pizzaria.service.ClienteService.loadClientFormInClienteGUI(ClienteService.java:187)
        at br.com.pizzaria.gui.ClienteGUI.btnPesquisarActionPerformed(ClienteGUI.java:570)
        at br.com.pizzaria.gui.ClienteGUI.access$1200(ClienteGUI.java:29)
        at br.com.pizzaria.gui.ClienteGUI$14.actionPerformed(ClienteGUI.java:441)
        at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1995)
        at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2318)
        at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:387)
        at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:242)
        at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:236)
        at java.awt.Component.processMouseEvent(Component.java:6263)
        at javax.swing.JComponent.processMouseEvent(JComponent.java:3267)
        at java.awt.Component.processEvent(Component.java:6028)
        at java.awt.Container.processEvent(Container.java:2041)
        at java.awt.Component.dispatchEventImpl(Component.java:4630)
        at java.awt.Container.dispatchEventImpl(Container.java:2099)
        at java.awt.Component.dispatchEvent(Component.java:4460)
        at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4574)
        at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4238)
        at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4168)
        at java.awt.Container.dispatchEventImpl(Container.java:2085)
        at java.awt.Window.dispatchEventImpl(Window.java:2478)
        at java.awt.Component.dispatchEvent(Component.java:4460)
        at java.awt.EventQueue.dispatchEvent(EventQueue.java:599)
        at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:269)
        at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:184)
        at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:174)
        at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:169)
        at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:161)
        at java.awt.EventDispatchThread.run(EventDispatchThread.java:122)

Fiz algumas alterações e deixei a query deste modo:

String hqlQuery = "FROM Cliente c " +
                          "WHERE c.id = (SELECT tsub0.cliente.id FROM c.telefones tsub0 )  " +
                          "AND ";


        
         if(!(nome == null || nome.equals(""))){
             hqlQuery += "c.nome like '%" + nome + "%'" ;
         }else
         if(!(telefone == null || telefone.equals(""))){
             hqlQuery += " (SELECT tsub1.numero FROM c.telefones tsub1) = " + telefone;
         }
        
        Query query = session.createQuery(hqlQuery);

Isto me retorna a seguinte exception:

1597 [AWT-EventQueue-0] INFO org.hibernate.impl.SessionFactoryObjectFactory - Not binding factory to JNDI, no JNDI name configured
Hibernate: 
    select
        cliente0_.id as id1_,
        cliente0_.Bairro as Bairro1_,
        cliente0_.complemento as compleme3_1_,
        cliente0_.dataNascimento as dataNasc4_1_,
        cliente0_.endereco as endereco1_,
        cliente0_.nome as nome1_,
        cliente0_.numero as numero1_,
        cliente0_.referencia as referencia1_ 
    from
        Cliente cliente0_ 
    where
        cliente0_.id=(
            select
                telefones1_.cliente_id 
            from
                Telefone telefones1_ 
            where
                cliente0_.id=telefones1_.cliente_id
        ) 
        and (
            cliente0_.nome like '%Rosângela%'
        )
14846 [AWT-EventQueue-0] WARN org.hibernate.util.JDBCExceptionReporter - SQL Error: 1242, SQLState: 21000
14847 [AWT-EventQueue-0] ERROR org.hibernate.util.JDBCExceptionReporter - Subquery returns more than 1 row
Exception in thread "AWT-EventQueue-0" org.hibernate.exception.DataException: could not execute query
        at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:100)
        at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
        at org.hibernate.loader.Loader.doList(Loader.java:2231)
        at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2125)
        at org.hibernate.loader.Loader.list(Loader.java:2120)
        at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:401)
        at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:361)
        at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196)
        at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1148)
        at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102)
        at br.com.pizzaria.dao.ClienteDAO.load(ClienteDAO.java:46)
        at br.com.pizzaria.service.ClienteService.getClientFormList(ClienteService.java:230)
        at br.com.pizzaria.service.ClienteService.loadClientFormInClienteGUI(ClienteService.java:187)
        at br.com.pizzaria.gui.ClienteGUI.btnPesquisarActionPerformed(ClienteGUI.java:570)
        at br.com.pizzaria.gui.ClienteGUI.access$1200(ClienteGUI.java:29)
        at br.com.pizzaria.gui.ClienteGUI$14.actionPerformed(ClienteGUI.java:441)
        at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1995)
        at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2318)
        at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:387)
        at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:242)
        at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:236)
        at java.awt.Component.processMouseEvent(Component.java:6263)
        at javax.swing.JComponent.processMouseEvent(JComponent.java:3267)
        at java.awt.Component.processEvent(Component.java:6028)
        at java.awt.Container.processEvent(Container.java:2041)
        at java.awt.Component.dispatchEventImpl(Component.java:4630)
        at java.awt.Container.dispatchEventImpl(Container.java:2099)
        at java.awt.Component.dispatchEvent(Component.java:4460)
        at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4574)
        at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4238)
        at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4168)
        at java.awt.Container.dispatchEventImpl(Container.java:2085)
        at java.awt.Window.dispatchEventImpl(Window.java:2478)
        at java.awt.Component.dispatchEvent(Component.java:4460)
        at java.awt.EventQueue.dispatchEvent(EventQueue.java:599)
        at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:269)
        at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:184)
        at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:174)
        at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:169)
        at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:161)
        at java.awt.EventDispatchThread.run(EventDispatchThread.java:122)
Caused by: java.sql.SQLException: Subquery returns more than 1 row
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1075)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3562)
        at com.mysql.jdbc.MysqlIO.nextRowFast(MysqlIO.java:1554)
        at com.mysql.jdbc.MysqlIO.nextRow(MysqlIO.java:1410)
        at com.mysql.jdbc.MysqlIO.readSingleRowSet(MysqlIO.java:2876)
        at com.mysql.jdbc.MysqlIO.getResultSet(MysqlIO.java:477)
        at com.mysql.jdbc.MysqlIO.readResultsForQueryOrUpdate(MysqlIO.java:2582)
        at com.mysql.jdbc.MysqlIO.readAllResults(MysqlIO.java:1758)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2172)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2696)
        at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2105)
        at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2264)
        at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
        at org.hibernate.loader.Loader.getResultSet(Loader.java:1808)
        at org.hibernate.loader.Loader.doQuery(Loader.java:697)
        at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
        at org.hibernate.loader.Loader.doList(Loader.java:2228)
        ... 38 more

Alguem pode me ajudar?

Abraço, Gabriel Cardelli

Alura POO: o que é programação orientada a objetos? Aprenda os conceitos básicos da programação orientada a objetos, como classes, objetos, herança, encapsulamento e polimorfismo, com exemplos.

1 Resposta

G
gustavorozolin

Seguinte amigo acho que assim resolve o seu problema.

String hqlQuery = "FROM Cliente c inner join fetch c.telefones t " +
                          "WHERE c.id is not null  ";

        
         if(!(nome == null || nome.equals(""))){
             hqlQuery += "and c.nome like  :nome " ;
         }

         if(!(telefone == null || telefone.equals(""))){
           
             hqlQuery += " and t.numero = :telefone";
         }
         Query query = session.createQuery(hqlQuery);
        if(!(nome == null || nome.equals(""))){
             query.setString("nome","%"+nome+"%") ;
         }

         if(!(telefone == null || telefone.equals(""))){
           
          query.setString("telefone",telefone);
         }

Espero ter ajudado.
Abraço

Criado 25 de junho de 2010
Ultima resposta 25 de jun. de 2010
Respostas 1
Participantes 2

Topicos relacionados

Componente de forum 4 respostas Consultar CPF pelo nome na Receita Federal 5 respostas Pegadinhas que se fazem com os pobres estagiários 62 respostas [Resolvido] Como comparar Strings em C? 6 respostas Converter pixels para centímetros 9 respostas
Fiap Graduacao em Tecnologia — FIAP Analise e Desenvolvimento de Sistemas, Engenharia de Software e mais
Casa do Codigo Casa do Codigo — Livros de tecnologia Livros de programacao, infraestrutura e inovacao