[RESOLVIDO] - Pesquisar com data inicio e fim e retornar um total

WAO · Novembro 12, 2012, 7:08pm

olá, quero fazer um filtro com data inicio e a data final

atualmente ele mostra tudo o total

no meu bean ta

[code] public Long getTotalDeAlunosCursando() {
TotalDeAlunosCursando = new RelatorioDAO().TotalDeAlunosCursando();
return TotalDeAlunosCursando;
}

public void setTotalDeAlunosCursando(Long TotalDeAlunosCursando) {
    this.TotalDeAlunosCursando = TotalDeAlunosCursando;
}[/code]

meu dao:

public Long TotalDeAlunosCursando() { Session s = HibernateUtil.getSession(); Query q = s.createQuery("select count(*) from Matricula ag where ag.situacao = 'C'"); return (Long) q.uniqueResult();

certo… eu ja inserir um campo na minha entidade

@Temporal(TemporalType.DATE) private Date dataCadastro;

como eu faria pra ele retornar todos os alunos que estão no intervalo ex: 10:/11/2012 á 12/11/2012.

eu tentei aqui mas deu erro na hora de comparar eu usei isso no xhtml

[code]
Data inicial:

                    <p:calendar pattern="dd/MM/yyyy"
                                locale="pt" maxlength="0" 
                                showButtonPanel="true" navigator="true"
                                size="20" required="true" requiredMessage="O Campo 'Data' é obrigatório" >

                    Data final:

                    <p:calendar pattern="dd/MM/yyyy"
                                locale="pt" maxlength="0"
                                showButtonPanel="true" navigator="true" 
                                size="20" required="true" requiredMessage="O Campo 'Data' é obrigatório">
                    </p:calendar>[/code]

Hebert_Coelho · Novembro 12, 2012, 7:12pm

Com HQL/JPQL não se usa count(*) mas sim count©, caso c fosse o apelido da query.

Aqui mostra como utilizar o count e parâmetro por data: JPA Consultas e Dicas.

WAO · Novembro 12, 2012, 7:17pm

do jeito que ta ai ele ta funcionando certo pra retornar um valor total de tudo, agora queria montar pra receber 2 parametros que são 2 data

charleston10 · Novembro 12, 2012, 7:24pm

Oxi implementa um AND com BETWEEN…

charleston10 · Novembro 12, 2012, 7:25pm

select count(*) from Matricula ag where ag.situacao = 'C' and between '2012-11-01' and '2012-11-30'

algo desse tipo…

WAO · Novembro 12, 2012, 7:39pm

nao deu certo

WAO · Novembro 12, 2012, 8:00pm

sentença de SQL, seria

Select count(*) From Matricula Where  situacao = "C" and dataCadastro >= '2012/11/12' And dataCadastro <= '2012/11/15'

o problema e no xhtml e pra passar pro metado que ta dando erro

AVISO: #{relatoriosBean.totalDeAlunosCursandoData()}: javax.el.MethodNotFoundException: /relatorios/relatorioalunostotal.xhtml @43,67 action="#{relatoriosBean.totalDeAlunosCursandoData()}": Method totalDeAlunosCursandoData not found javax.faces.FacesException: #{relatoriosBean.totalDeAlunosCursandoData()}: javax.el.MethodNotFoundException: /relatorios/relatorioalunostotal.xhtml @43,67 action="#{relatoriosBean.totalDeAlunosCursandoData()}": Method totalDeAlunosCursandoData not found at com.sun.faces.application.ActionListenerImpl.processAction(ActionListenerImpl.java:110) at javax.faces.component.UICommand.broadcast(UICommand.java:315) at javax.faces.component.UIViewRoot.broadcastEvents(UIViewRoot.java:794) at javax.faces.component.UIViewRoot.processApplication(UIViewRoot.java:1259) at com.sun.faces.lifecycle.InvokeApplicationPhase.execute(InvokeApplicationPhase.java:81) at com.sun.faces.lifecycle.Phase.doPhase(Phase.java:101) at com.sun.faces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:118) at javax.faces.webapp.FacesServlet.service(FacesServlet.java:593) at org.apache.catalina.core.StandardWrapper.service(StandardWrapper.java:1542) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:343) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:217) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:343) at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:109) at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:83) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355) at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:97) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355) at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:100) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355) at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:78) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355) at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:54) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355) at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:35) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355) at org.springframework.security.web.authentication.www.BasicAuthenticationFilter.doFilter(BasicAuthenticationFilter.java:177) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355) at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:187) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355) at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:105) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355) at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:79) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355) at org.springframework.security.web.session.ConcurrentSessionFilter.doFilter(ConcurrentSessionFilter.java:109) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355) at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:149) at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:237) at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:167) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:256) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:217) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:279) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175) at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:655) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:595) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:161) at org.apache.catalina.connector.CoyoteAdapter.doService(CoyoteAdapter.java:331) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:231) at com.sun.enterprise.v3.services.impl.ContainerMapper$AdapterCallable.call(ContainerMapper.java:317) at com.sun.enterprise.v3.services.impl.ContainerMapper.service(ContainerMapper.java:195) at com.sun.grizzly.http.ProcessorTask.invokeAdapter(ProcessorTask.java:849) at com.sun.grizzly.http.ProcessorTask.doProcess(ProcessorTask.java:746) at com.sun.grizzly.http.ProcessorTask.process(ProcessorTask.java:1045) at com.sun.grizzly.http.DefaultProtocolFilter.execute(DefaultProtocolFilter.java:228) at com.sun.grizzly.DefaultProtocolChain.executeProtocolFilter(DefaultProtocolChain.java:137) at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:104) at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:90) at com.sun.grizzly.http.HttpProtocolChain.execute(HttpProtocolChain.java:79) at com.sun.grizzly.ProtocolChainContextTask.doCall(ProtocolChainContextTask.java:54) at com.sun.grizzly.SelectionKeyContextTask.call(SelectionKeyContextTask.java:59) at com.sun.grizzly.ContextTask.run(ContextTask.java:71) at com.sun.grizzly.util.AbstractThreadPool$Worker.doWork(AbstractThreadPool.java:532) at com.sun.grizzly.util.AbstractThreadPool$Worker.run(AbstractThreadPool.java:513) at java.lang.Thread.run(Thread.java:662) Caused by: javax.faces.el.MethodNotFoundException: javax.el.MethodNotFoundException: /relatorios/relatorioalunostotal.xhtml @43,67 action="#{relatoriosBean.totalDeAlunosCursandoData()}": Method totalDeAlunosCursandoData not found at javax.faces.component.MethodBindingMethodExpressionAdapter.invoke(MethodBindingMethodExpressionAdapter.java:92) at com.sun.faces.application.ActionListenerImpl.processAction(ActionListenerImpl.java:102) ... 62 more Caused by: javax.el.MethodNotFoundException: /relatorios/relatorioalunostotal.xhtml @43,67 action="#{relatoriosBean.totalDeAlunosCursandoData()}": Method totalDeAlunosCursandoData not found at com.sun.faces.facelets.el.TagMethodExpression.invoke(TagMethodExpression.java:109) at javax.faces.component.MethodBindingMethodExpressionAdapter.invoke(MethodBindingMethodExpressionAdapter.java:88) ... 63 more

angeliski · Novembro 12, 2012, 8:03pm

Cara, da uma olhada no link q o Hebert Coelho passou, ele tem uns tutoriais FANTASTICOS…

    select count(ag.situacao) from Matricula ag where ag.situacao = 'C' and ag.DATA_DA_SUA_CLASSE_MATRICULA between :dataInicio and :dataFinal

Vai ser algo parecido com isso, vc tem de inserir os parametros dataInicio e dataFinal,
lembresse que as datas injetadas devem ser compativeis com a data da sua classe matricula, se for date la em matricula tem de “injetar” date, se for String, de de ser String.
Isso porq o hibernate trabalha com “OBJETOS” na hora de suas consultas… se surgir uma duvida, vai lá no link do Hebert q vc vai matar todas.

angeliski · Novembro 12, 2012, 8:04pm

[quote=WAO] sentença de SQL, seria

Select count(*) From Matricula Where  situacao = "C" and dataCadastro >= '2012/11/12' And dataCadastro <= '2012/11/15'

o problema e no xhtml e pra passar pro metado que ta dando erro

AVISO: #{relatoriosBean.totalDeAlunosCursandoData()}: javax.el.MethodNotFoundException: /relatorios/relatorioalunostotal.xhtml @43,67 action="#{relatoriosBean.totalDeAlunosCursandoData()}": Method totalDeAlunosCursandoData not found javax.faces.FacesException: #{relatoriosBean.totalDeAlunosCursandoData()}: javax.el.MethodNotFoundException: /relatorios/relatorioalunostotal.xhtml @43,67 action="#{relatoriosBean.totalDeAlunosCursandoData()}": Method totalDeAlunosCursandoData not found at com.sun.faces.application.ActionListenerImpl.processAction(ActionListenerImpl.java:110) at javax.faces.component.UICommand.broadcast(UICommand.java:315) at javax.faces.component.UIViewRoot.broadcastEvents(UIViewRoot.java:794) at javax.faces.component.UIViewRoot.processApplication(UIViewRoot.java:1259) at com.sun.faces.lifecycle.InvokeApplicationPhase.execute(InvokeApplicationPhase.java:81) at com.sun.faces.lifecycle.Phase.doPhase(Phase.java:101) at com.sun.faces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:118) at javax.faces.webapp.FacesServlet.service(FacesServlet.java:593) at org.apache.catalina.core.StandardWrapper.service(StandardWrapper.java:1542) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:343) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:217) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:343) at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:109) at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:83) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355) at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:97) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355) at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:100) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355) at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:78) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355) at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:54) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355) at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:35) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355) at org.springframework.security.web.authentication.www.BasicAuthenticationFilter.doFilter(BasicAuthenticationFilter.java:177) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355) at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:187) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355) at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:105) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355) at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:79) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355) at org.springframework.security.web.session.ConcurrentSessionFilter.doFilter(ConcurrentSessionFilter.java:109) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355) at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:149) at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:237) at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:167) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:256) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:217) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:279) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175) at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:655) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:595) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:161) at org.apache.catalina.connector.CoyoteAdapter.doService(CoyoteAdapter.java:331) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:231) at com.sun.enterprise.v3.services.impl.ContainerMapper$AdapterCallable.call(ContainerMapper.java:317) at com.sun.enterprise.v3.services.impl.ContainerMapper.service(ContainerMapper.java:195) at com.sun.grizzly.http.ProcessorTask.invokeAdapter(ProcessorTask.java:849) at com.sun.grizzly.http.ProcessorTask.doProcess(ProcessorTask.java:746) at com.sun.grizzly.http.ProcessorTask.process(ProcessorTask.java:1045) at com.sun.grizzly.http.DefaultProtocolFilter.execute(DefaultProtocolFilter.java:228) at com.sun.grizzly.DefaultProtocolChain.executeProtocolFilter(DefaultProtocolChain.java:137) at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:104) at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:90) at com.sun.grizzly.http.HttpProtocolChain.execute(HttpProtocolChain.java:79) at com.sun.grizzly.ProtocolChainContextTask.doCall(ProtocolChainContextTask.java:54) at com.sun.grizzly.SelectionKeyContextTask.call(SelectionKeyContextTask.java:59) at com.sun.grizzly.ContextTask.run(ContextTask.java:71) at com.sun.grizzly.util.AbstractThreadPool$Worker.doWork(AbstractThreadPool.java:532) at com.sun.grizzly.util.AbstractThreadPool$Worker.run(AbstractThreadPool.java:513) at java.lang.Thread.run(Thread.java:662) Caused by: javax.faces.el.MethodNotFoundException: javax.el.MethodNotFoundException: /relatorios/relatorioalunostotal.xhtml @43,67 action="#{relatoriosBean.totalDeAlunosCursandoData()}": Method totalDeAlunosCursandoData not found at javax.faces.component.MethodBindingMethodExpressionAdapter.invoke(MethodBindingMethodExpressionAdapter.java:92) at com.sun.faces.application.ActionListenerImpl.processAction(ActionListenerImpl.java:102) ... 62 more Caused by: javax.el.MethodNotFoundException: /relatorios/relatorioalunostotal.xhtml @43,67 action="#{relatoriosBean.totalDeAlunosCursandoData()}": Method totalDeAlunosCursandoData not found at com.sun.faces.facelets.el.TagMethodExpression.invoke(TagMethodExpression.java:109) at javax.faces.component.MethodBindingMethodExpressionAdapter.invoke(MethodBindingMethodExpressionAdapter.java:88) ... 63 more[/quote]

cara, vc ta trabalhando com SQL nativo ou com o HQL? (JQPL e afins)?

Editado:

cara, vc tentou tirar o parenteses do metodo, E ver se ele esta publico na sua classe? coloca o codigo da Classe ai pra gente dar uma olhada.

WAO · Novembro 12, 2012, 8:21pm

cara uso SQL normal e tbm Hql

angeliski · Novembro 12, 2012, 8:23pm

tá, mas NESSE caso em especifico q esta dando problema? porq usar os dois não tem crise, mas misturar os dois na mesma query…bom, eu nunca vi.XD

WAO · Novembro 12, 2012, 9:11pm

rsrs, obrigado pela ajuda conseguir aqui ficou assim:

public Long TotalDeAlunosAprovado(Date inicio, Date fim) { Session s = HibernateUtil.getSession(); Query q = s.createQuery("select count(*) from Matricula de where de.situacao = 'AP' and de.dataCadastro >= :inicio and de.dataCadastro <= :fim"); q.setParameter("inicio", inicio); q.setParameter("fim", fim); return (Long) q.uniqueResult(); }

angeliski · Novembro 13, 2012, 2:09am

só um conselho cara, faça esse Count pela chave primaria, tipo assim, vamos supor q seu atributo chama idMatricula, coloca o count assim:

select count(de.idMatricula) ....

porq?

quando vc coloca esse *, vc carrega TODOS os objetos em memoria, e não só um campo, se vc tiver mil registros ja complica…duzentos mil? manjou?

Abraço!

WAO · Novembro 13, 2012, 2:45am

[quote=angeliski]só um conselho cara, faça esse Count pela chave primaria, tipo assim, vamos supor q seu atributo chama idMatricula, coloca o count assim:

select count(de.idMatricula) ....

porq?

quando vc coloca esse *, vc carrega TODOS os objetos em memoria, e não só um campo, se vc tiver mil registros ja complica…duzentos mil? manjou?

Abraço![/quote]

entendi irmao brigadao ai pela dica :D, mudei aqui ja

system · Dezembro 30, 2015, 1:01pm

[RESOLVIDO] - Pesquisar com data inicio e fim e retornar um total

Cursos de Mobile

Cursos de Programação

Cursos de Front-end

Cursos de DevOps

Cursos de Design & UX

Cursos de Business

Cursos de Data & BI