Discussoes de Programacao e Tecnologia

Duvida com Joins HQL

5 respostas
maxmustang
maxmustang
boa galera,entao to tentando reproduzir essa consulta 
select dr.dataRealizacao, sum(preco.valor) from tb_preco as preco
     inner join tb_despesa_realizada as dr on dr.preco_id = preco.id 
     inner join tb_plano as plano on plano.plano_id = preco.plano_id 
where dr.dataRealizacao between '2011-08-10' and '2011-08-19' group by (dr.dataRealizacao)
no hql, só que.. nao consigo por nada essa é a query q o hql gera 
SELECT despesa.dataRealizacao, sum(preco.valor) from Preco as preco inner join DespesaRealizada as despesa  despesa.preco.id = preco.id inner join Plano as plano plano.id = preco.plano.id where despesa.dataRealizacao between :inicio and :fim group by (despesa.dataRealizacao)
código 
String hql = "SELECT despesa.dataRealizacao, sum(preco.valor) from Preco as preco "
                + "inner join DespesaRealizada as despesa  despesa.preco.id = preco.id "
                + "inner join Plano as plano plano.id = preco.plano.id "
                + "where despesa.dataRealizacao between :inicio and :fim group by (despesa.dataRealizacao)";
        System.out.println(hql);
        Query query = hbSession.createQuery(hql);
essa sao as exceptions 
java.lang.reflect.UndeclaredThrowableException
	at $Proxy175.createQuery(Unknown Source)
	at br.com.tecnoarte.drtools.controller.relatorio.ConsultaRelatorioFonteFaturamentoAction.buscarPrimeiraLista(ConsultaRelatorioFonteFaturamentoAction.java:57)
	at br.com.tecnoarte.drtools.controller.relatorio.ConsultaRelatorioFonteFaturamentoAction.processa(ConsultaRelatorioFonteFaturamentoAction.java:38)
	at br.com.tecnoarte.drtools.controller.AbstractSecureAction.execute(AbstractSecureAction.java:61)
	at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:425)
	at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:228)
	at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1913)
	at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:449)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:617)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at br.com.onbudget.hibernate.HibernateSessionRequestFilter.doFilter(HibernateSessionRequestFilter.java:49)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
	at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:859)
	at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:588)
	at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
	at java.lang.Thread.run(Thread.java:662)
Caused by: java.lang.reflect.InvocationTargetException
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	at org.hibernate.context.ThreadLocalSessionContext$TransactionProtectionWrapper.invoke(ThreadLocalSessionContext.java:301)
	... 25 more
Caused by: java.lang.NoSuchMethodError: org.hibernate.hql.antlr.HqlBaseParser.recover(Lantlr/RecognitionException;Lantlr/collections/impl/BitSet;)V
	at org.hibernate.hql.antlr.HqlBaseParser.fromJoin(HqlBaseParser.java:1802)
	at org.hibernate.hql.antlr.HqlBaseParser.fromClause(HqlBaseParser.java:1420)
	at org.hibernate.hql.antlr.HqlBaseParser.selectFrom(HqlBaseParser.java:1130)
	at org.hibernate.hql.antlr.HqlBaseParser.queryRule(HqlBaseParser.java:702)
	at org.hibernate.hql.antlr.HqlBaseParser.selectStatement(HqlBaseParser.java:296)
	at org.hibernate.hql.antlr.HqlBaseParser.statement(HqlBaseParser.java:159)
	at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:248)
	at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:157)
	at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:111)
	at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:77)
	at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:56)
	at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:72)
	at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:133)
	at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:112)
	at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1623)
	... 30 more
17/08/2011 12:37:47 org.hibernate.hql.ast.ErrorCounter reportError
GRAVE: line 1:152: unexpected token: despesa
17/08/2011 12:37:47 br.com.tecnoarte.drtools.controller.AbstractSecureAction execute
INFO: java.lang.reflect.UndeclaredThrowableException
javax.servlet.ServletException: java.lang.reflect.UndeclaredThrowableException
	at org.apache.struts.action.RequestProcessor.processException(RequestProcessor.java:520)
	at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:427)
	at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:228)
	at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1913)
	at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:449)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:617)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at br.com.onbudget.hibernate.HibernateSessionRequestFilter.doFilter(HibernateSessionRequestFilter.java:49)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
	at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:859)
	at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:588)
	at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
	at java.lang.Thread.run(Thread.java:662)
Caused by: java.lang.reflect.UndeclaredThrowableException
	at $Proxy175.createQuery(Unknown Source)
	at br.com.tecnoarte.drtools.controller.relatorio.ConsultaRelatorioFonteFaturamentoAction.buscarPrimeiraLista(ConsultaRelatorioFonteFaturamentoAction.java:57)
	at br.com.tecnoarte.drtools.controller.relatorio.ConsultaRelatorioFonteFaturamentoAction.processa(ConsultaRelatorioFonteFaturamentoAction.java:38)
	at br.com.tecnoarte.drtools.controller.AbstractSecureAction.execute(AbstractSecureAction.java:61)
	at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:425)
	... 20 more
Caused by: java.lang.reflect.InvocationTargetException
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	at org.hibernate.context.ThreadLocalSessionContext$TransactionProtectionWrapper.invoke(ThreadLocalSessionContext.java:301)
	... 25 more
Caused by: java.lang.NoSuchMethodError: org.hibernate.hql.antlr.HqlBaseParser.recover(Lantlr/RecognitionException;Lantlr/collections/impl/BitSet;)V
	at org.hibernate.hql.antlr.HqlBaseParser.fromJoin(HqlBaseParser.java:1802)
	at org.hibernate.hql.antlr.HqlBaseParser.fromClause(HqlBaseParser.java:1420)
	at org.hibernate.hql.antlr.HqlBaseParser.selectFrom(HqlBaseParser.java:1130)
	at org.hibernate.hql.antlr.HqlBaseParser.queryRule(HqlBaseParser.java:702)
	at org.hibernate.hql.antlr.HqlBaseParser.selectStatement(HqlBaseParser.java:296)
	at org.hibernate.hql.antlr.HqlBaseParser.statement(HqlBaseParser.java:159)
	at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:248)
	at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:157)
	at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:111)
	at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:77)
	at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:56)
	at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:72)
	at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:133)
	at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:112)
	at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1623)
	... 30 more
Alura Git Flow: entenda o que é, como e quando utilizar Entenda o que é Git Flow, como funciona seu fluxo com branches como Master, Develop, Feature, Release e Hotfix, além de vantagens e desvantagens.

5 Respostas

henriqueluz
henriqueluz

Amigão, como está seu mapeamento? Você usa annotations ou mapeia pelo xml?
Cole o código de suas classes Preco, DespesaRealizada e Plano.
Abs,

maxmustang
maxmustang 
@Entity
@Table(name = "tb_preco")
public class Preco implements Serializable {

    @Id
    @GeneratedValue
    private Long id;
    @ManyToOne
    @JoinColumn(name = "plano_id")
    private Plano plano;
    @ManyToOne
    @JoinColumn(name = "cliente_id")
    private Cliente cliente;
    @ManyToOne
    @JoinColumn(name = "item_faturamento_id")
    private ItemFaturamento itemFaturamento;
    
    @Temporal(TemporalType.DATE)
    private Date dtInicioVigencia;
    
    @Transient
    private String dtInicioVigenciaString;

    private BigDecimal valor;

    @Transient
    private String valorStr;

@Entity
@Table(name = "tb_plano")
public class Plano implements Serializable {

    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "plano_id")
    @Id
    private Integer id;

    @Column(length = 50)
    private String nome;

    private Boolean desativado;
    
    @ManyToOne
    @JoinColumns ({
        @JoinColumn(name="convenio_id", referencedColumnName="convenio_id"),
        @JoinColumn(name="cliente_id", referencedColumnName="cliente_id")
        
    })
    private ConvenioCliente convenioCliente;

@Entity
@Table(name = "tb_despesa_realizada")
public class DespesaRealizada implements Serializable {

    private Long id;
    private Date dataRealizacao;
    private Date horaInicial;
    private Date horaFinal;
    private Integer quantidade;
    private Double percReducaoOuAcrescimo;
    private Preco preco = new Preco();
    private SimpleDateFormat time = new SimpleDateFormat("HH:mm");
    private SimpleDateFormat date = new SimpleDateFormat("dd/MM/yyyy");
    private NumberFormat nf = NumberFormat.getInstance(Locale.getDefault());
    private ItemFaturamento itemFaturamento;
henriqueluz
henriqueluz

E as annotations na classe DespesaRealizada?
Faltou só isso.

maxmustang
maxmustang 
@Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    public Long getId() {
        return id;
    }

 @Temporal(value = javax.persistence.TemporalType.DATE)
    public Date getDataRealizacao() {
        return dataRealizacao;
    }


 @Temporal(value = javax.persistence.TemporalType.TIME)
    public Date getHoraFinal() {
        return horaFinal;
    }

@Temporal(value = javax.persistence.TemporalType.TIME)
    public Date getHoraInicial() {
        return horaInicial;
    }

@ManyToOne
    @JoinColumn(name="preco_id")
    public Preco getPreco() {
        return preco;
    }

 @ManyToOne
    @JoinColumn(name="item_faturamento_id")
    public ItemFaturamento getItemFaturamento() {
        if(itemFaturamento.getId()!=null){
            return itemFaturamento;
        }else{
            itemFaturamento = new ItemFaturamento();
            itemFaturamento.setCodigoDeDespesa(new CodigoDeDespesa());
            return itemFaturamento;
        }

    }
henriqueluz
henriqueluz

Beleza, tenta assim:

SELECT despesa.dataRealizacao, sum(preco.valor)
FROM br.com.seupacote.DespesaRealizada as despesa,
         br.com.seupacote.Preco as preco,
         br.com.seupacote.Plano as plano
WHERE despesa.dataRealizacao between :inicio and :fim
GROUP BY despesa.dataRealizacao

Lembrando que caso você tenha mapeado suas classes com seus atributos(colunas) e joincolumns corretamente, o hibernate se responsabiliza por fazer suas junções. Não é necessário escreve-las.
Espero ter ajudado.

Abraço

Criado 17 de agosto de 2011
Ultima resposta 17 de ago. de 2011
Respostas 5
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
Lumina Lumina: a IA que te traz resultados Prompts prontos por especialistas. Resolva seus problemas de verdade.
Fiap Graduacao em Tecnologia — FIAP Analise e Desenvolvimento de Sistemas, Engenharia de Software e mais