Erro consulta jpql e banco de dados postgres

guilhermebhte · Abril 22, 2020, 12:20am

Tenho está consulta. Mesmo o campo dataHoraMarcadaDe nulo ou com valor dá erro após gerar a consulta pelo spring-data-jpa

package br.com.ghnetsoft.pregao_presencial.core.pregao.impl;

import java.math.BigDecimal;
import java.util.Date;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import br.com.ghnetsoft.pregao_presencial.core.licitacao.enuns.LicitacaoRegimeEnum;
import br.com.ghnetsoft.pregao_presencial.core.pregao.enuns.PregaoFasesEnum;
import br.com.ghnetsoft.pregao_presencial.core.pregao.view.ViewPregaoPesquisar;
import br.com.ghnetsoft.principal.core.enuns.StatusDoRegistroEnum;

@Repository
interface ViewPregaoPesquisarRepository extends JpaRepository<ViewPregaoPesquisar, String> {

	@Query(value = "SELECT e FROM ViewPregaoPesquisar e WHERE e.idEmpresa = :idEmpresa "
			+ "AND (e.idMesAno = :idMesAno or :idMesAno is null) "
			+ "AND (e.idLicitacao = :idLicitacao or :idLicitacao is null) "
			+ "AND (e.regime = :regime or :regime is null) "
			+ "AND (e.valorEstimado = :valorEstimado or :valorEstimado is null) "
			+ "AND (e.fase = :fase or :fase is null) "
			+ "AND (e.quantidadeFornecedor = :quantidadeFornecedor or :quantidadeFornecedor is null) "
			+ "AND (e.valorMinimoPorLance = :valorMinimoPorLance or :valorMinimoPorLance is null) "
			+ "AND (e.statusDoRegistro = :statusDoRegistro or :statusDoRegistro is null) "
			+ "AND (e.dataHoraMarcada >= :dataHoraMarcadaDe or :dataHoraMarcadaDe is null) ")
	Page<ViewPregaoPesquisar> paginacao(String idEmpresa, String idLicitacao, String idMesAno, PregaoFasesEnum fase,
			LicitacaoRegimeEnum regime, BigDecimal valorMinimoPorLance, Integer quantidadeFornecedor,
			BigDecimal valorEstimado, StatusDoRegistroEnum statusDoRegistro, Date dataHoraMarcadaDe, Pageable pageable);
}

**Consulta gerada**, Se pego esta consulta é coloco no PGAdmin, do banco de dados roda com **(viewpregao0_.ts_data_hora_marcada>=null or null is null)** null ou com valor **(viewpregao0_.ts_data_hora_marcada>='2020-04-21 20:15:39' or '2020-04-21 20:15:39' is null)**.

> select viewpregao0_.pk_pregao as pk_prega1_19_, viewpregao0_.nr_ano_licitacao as nr_ano_l2_19_, viewpregao0_.nr_ano_mes_ano as nr_ano_m3_19_, viewpregao0_.ts_data_hora_abertura as ts_data_4_19_, viewpregao0_.ts_data_hora_fechou as ts_data_5_19_, viewpregao0_.ts_data_hora_marcada as ts_data_6_19_, viewpregao0_.st_fase as st_fase7_19_, viewpregao0_.fk_empresa as fk_empre8_19_, viewpregao0_.pk_licitacao as pk_licit9_19_, viewpregao0_.pk_mes_ano as pk_mes_10_19_, viewpregao0_.st_mes as st_mes11_19_, viewpregao0_.nr_numero as nr_nume12_19_, viewpregao0_.nr_quantidade_fornecedor as nr_quan13_19_, viewpregao0_.st_regime as st_regi14_19_, viewpregao0_.st_registro as st_regi15_19_, viewpregao0_.cd_login_movimentacao as cd_logi16_19_, viewpregao0_.vr_valor_estimado as vr_valo17_19_, viewpregao0_.vr_minimo_por_lance as vr_mini18_19_ from view_pregao_pesquisar viewpregao0_ where viewpregao0_.fk_empresa=? and (viewpregao0_.pk_mes_ano=? or ? is null) and (viewpregao0_.pk_licitacao=? or ? is null) and (viewpregao0_.st_regime=? or ? is null) and (viewpregao0_.vr_valor_estimado=? or ? is null) and (viewpregao0_.st_fase=? or ? is null) and (viewpregao0_.nr_quantidade_fornecedor=? or ? is null) and (viewpregao0_.vr_minimo_por_lance=? or ? is null) and (viewpregao0_.st_registro=? or ? is null) and (viewpregao0_.ts_data_hora_marcada>=? or ? is null) order by viewpregao0_.pk_licitacao asc limit ?

Classe da view

package br.com.ghnetsoft.pregao_presencial.core.pregao.view;

import static javax.persistence.EnumType.STRING;
import static javax.persistence.TemporalType.TIMESTAMP;
import static lombok.AccessLevel.PROTECTED;

import java.math.BigDecimal;
import java.util.Date;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Enumerated;
import javax.persistence.Id;
import javax.persistence.Table;
import javax.persistence.Temporal;

import br.com.ghnetsoft.pregao_presencial.core.licitacao.enuns.LicitacaoRegimeEnum;
import br.com.ghnetsoft.pregao_presencial.core.pregao.enuns.PregaoFasesEnum;
import br.com.ghnetsoft.principal.core.enuns.MesEnum;
import br.com.ghnetsoft.principal.core.enuns.StatusDoRegistroEnum;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;

@Entity
@Setter
@Getter
@Builder
@Table(name = "VIEW_PREGAO_PESQUISAR")
@NoArgsConstructor(access = PROTECTED)
@AllArgsConstructor(access = PROTECTED)
public class ViewPregaoPesquisar {

	// Pregão
	@Id
	@Column(name = "PK_PREGAO")
	private String id;
	@Column(name = "FK_EMPRESA")
	private String idEmpresa;
	@Column(name = "CD_LOGIN_MOVIMENTACAO")
	private String usuarioPregao;
	@Enumerated(STRING)
	@Column(name = "ST_REGISTRO")
	private StatusDoRegistroEnum statusDoRegistro;
	@Column(name = "NR_QUANTIDADE_FORNECEDOR")
	private Integer quantidadeFornecedor;
	@Temporal(TIMESTAMP)
	@Column(name = "TS_DATA_HORA_ABERTURA")
	private Date dataHoraAbertura;
	@Temporal(TIMESTAMP)
	@Column(name = "TS_DATA_HORA_FECHOU")
	private Date dataHoraFechou;
	@Enumerated(STRING)
	@Column(name = "ST_FASE")
	private PregaoFasesEnum fase;
	@Column(name = "VR_MINIMO_POR_LANCE")
	private BigDecimal valorMinimoPorLance;
	// Licitação
	@Column(name = "PK_LICITACAO")
	private String idLicitacao;
	@Temporal(TIMESTAMP)
	@Column(name = "TS_DATA_HORA_MARCADA")
	private Date dataHoraMarcada;
	@Column(name = "NR_NUMERO")
	private Integer numero;
	@Column(name = "NR_ANO_LICITACAO")
	private Integer anoLicitacao;
	@Column(name = "VR_VALOR_ESTIMADO")
	private BigDecimal valorEstimado;
	@Enumerated(STRING)
	@Column(name = "ST_REGIME")
	private LicitacaoRegimeEnum regime;
	// MesAno
	@Column(name = "PK_MES_ANO")
	private String idMesAno;//
	@Column(name = "NR_ANO_MES_ANO")
	private Integer anoMesAno;
	@Enumerated(STRING)
	@Column(name = "ST_MES", length = 10)
	private MesEnum mes;
}

erro

org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
	at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:281) ~[spring-orm-5.2.5.RELEASE.jar:5.2.5.RELEASE]
	at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:255) ~[spring-orm-5.2.5.RELEASE.jar:5.2.5.RELEASE]
	at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:528) ~[spring-orm-5.2.5.RELEASE.jar:5.2.5.RELEASE]
	at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61) ~[spring-tx-5.2.5.RELEASE.jar:5.2.5.RELEASE]
	at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:242) ~[spring-tx-5.2.5.RELEASE.jar:5.2.5.RELEASE]
	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:153) ~[spring-tx-5.2.5.RELEASE.jar:5.2.5.RELEASE]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.2.5.RELEASE.jar:5.2.5.RELEASE]
	at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:149) ~[spring-data-jpa-2.2.6.RELEASE.jar:2.2.6.RELEASE]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.2.5.RELEASE.jar:5.2.5.RELEASE]
	at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:95) ~[spring-aop-5.2.5.RELEASE.jar:5.2.5.RELEASE]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.2.5.RELEASE.jar:5.2.5.RELEASE]
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212) ~[spring-aop-5.2.5.RELEASE.jar:5.2.5.RELEASE]
	at br.com.ghnetsoft.pregao_presencial.core.pregao.impl.$Proxy213.paginacao(Unknown Source) ~[na:na]
	at br.com.ghnetsoft.pregao_presencial.core.pregao.impl.PregaoConsultaServiceImpl.paginar(PregaoConsultaServiceImpl.java:91) ~[classes/:na]
	at br.com.ghnetsoft.pregao_presencial.rest.PregaoRestController.pesquisar(PregaoRestController.java:89) ~[classes/:na]
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_241]
	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) ~[na:1.8.0_241]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) ~[na:1.8.0_241]
	at java.lang.reflect.Method.invoke(Unknown Source) ~[na:1.8.0_241]
	at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:190) [spring-web-5.2.5.RELEASE.jar:5.2.5.RELEASE]
	at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:138) [spring-web-5.2.5.RELEASE.jar:5.2.5.RELEASE]
	at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:105) [spring-webmvc-5.2.5.RELEASE.jar:5.2.5.RELEASE]
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:879) [spring-webmvc-5.2.5.RELEASE.jar:5.2.5.RELEASE]
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:793) [spring-webmvc-5.2.5.RELEASE.jar:5.2.5.RELEASE]
	at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) [spring-webmvc-5.2.5.RELEASE.jar:5.2.5.RELEASE]
	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1040) [spring-webmvc-5.2.5.RELEASE.jar:5.2.5.RELEASE]
	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:943) [spring-webmvc-5.2.5.RELEASE.jar:5.2.5.RELEASE]
	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006) [spring-webmvc-5.2.5.RELEASE.jar:5.2.5.RELEASE]
	at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:898) [spring-webmvc-5.2.5.RELEASE.jar:5.2.5.RELEASE]
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:634) [tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883) [spring-webmvc-5.2.5.RELEASE.jar:5.2.5.RELEASE]
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:741) [tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231) [tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53) [tomcat-embed-websocket-9.0.33.jar:9.0.33]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) [tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.springframework.web.filter.CorsFilter.doFilterInternal(CorsFilter.java:92) [spring-web-5.2.5.RELEASE.jar:5.2.5.RELEASE]
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) [spring-web-5.2.5.RELEASE.jar:5.2.5.RELEASE]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) [tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:209) [spring-security-web-5.2.2.RELEASE.jar:5.2.2.RELEASE]
	at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:178) [spring-security-web-5.2.2.RELEASE.jar:5.2.2.RELEASE]
	at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:358) [spring-web-5.2.5.RELEASE.jar:5.2.5.RELEASE]
	at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:271) [spring-web-5.2.5.RELEASE.jar:5.2.5.RELEASE]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) [tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100) [spring-web-5.2.5.RELEASE.jar:5.2.5.RELEASE]
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) [spring-web-5.2.5.RELEASE.jar:5.2.5.RELEASE]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) [tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93) [spring-web-5.2.5.RELEASE.jar:5.2.5.RELEASE]
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) [spring-web-5.2.5.RELEASE.jar:5.2.5.RELEASE]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) [tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201) [spring-web-5.2.5.RELEASE.jar:5.2.5.RELEASE]
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) [spring-web-5.2.5.RELEASE.jar:5.2.5.RELEASE]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) [tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202) [tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96) [tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:541) [tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139) [tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92) [tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74) [tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343) [tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:373) [tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65) [tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:868) [tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1594) [tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) [tomcat-embed-core-9.0.33.jar:9.0.33]
	at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) [na:1.8.0_241]
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) [na:1.8.0_241]
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) [tomcat-embed-core-9.0.33.jar:9.0.33]
	at java.lang.Thread.run(Unknown Source) [na:1.8.0_241]
Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
	at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:103) ~[hibernate-core-5.4.12.Final.jar:5.4.12.Final]
	at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42) ~[hibernate-core-5.4.12.Final.jar:5.4.12.Final]
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113) ~[hibernate-core-5.4.12.Final.jar:5.4.12.Final]
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99) ~[hibernate-core-5.4.12.Final.jar:5.4.12.Final]
	at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:67) ~[hibernate-core-5.4.12.Final.jar:5.4.12.Final]
	at org.hibernate.loader.Loader.getResultSet(Loader.java:2292) ~[hibernate-core-5.4.12.Final.jar:5.4.12.Final]
	at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2050) ~[hibernate-core-5.4.12.Final.jar:5.4.12.Final]
	at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2012) ~[hibernate-core-5.4.12.Final.jar:5.4.12.Final]
	at org.hibernate.loader.Loader.doQuery(Loader.java:953) ~[hibernate-core-5.4.12.Final.jar:5.4.12.Final]
	at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:354) ~[hibernate-core-5.4.12.Final.jar:5.4.12.Final]
	at org.hibernate.loader.Loader.doList(Loader.java:2838) ~[hibernate-core-5.4.12.Final.jar:5.4.12.Final]
	at org.hibernate.loader.Loader.doList(Loader.java:2820) ~[hibernate-core-5.4.12.Final.jar:5.4.12.Final]
	at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2652) ~[hibernate-core-5.4.12.Final.jar:5.4.12.Final]
	at org.hibernate.loader.Loader.list(Loader.java:2647) ~[hibernate-core-5.4.12.Final.jar:5.4.12.Final]
	at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:506) ~[hibernate-core-5.4.12.Final.jar:5.4.12.Final]
	at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:396) ~[hibernate-core-5.4.12.Final.jar:5.4.12.Final]
	at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:219) ~[hibernate-core-5.4.12.Final.jar:5.4.12.Final]
	at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1404) ~[hibernate-core-5.4.12.Final.jar:5.4.12.Final]
	at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1562) ~[hibernate-core-5.4.12.Final.jar:5.4.12.Final]
	at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1530) ~[hibernate-core-5.4.12.Final.jar:5.4.12.Final]
	at org.hibernate.query.Query.getResultList(Query.java:165) ~[hibernate-core-5.4.12.Final.jar:5.4.12.Final]
	at org.springframework.data.jpa.repository.query.JpaQueryExecution$PagedExecution.doExecute(JpaQueryExecution.java:177) ~[spring-data-jpa-2.2.6.RELEASE.jar:2.2.6.RELEASE]
	at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:88) ~[spring-data-jpa-2.2.6.RELEASE.jar:2.2.6.RELEASE]
	at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:154) ~[spring-data-jpa-2.2.6.RELEASE.jar:2.2.6.RELEASE]
	at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:142) ~[spring-data-jpa-2.2.6.RELEASE.jar:2.2.6.RELEASE]
	at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:618) ~[spring-data-commons-2.2.6.RELEASE.jar:2.2.6.RELEASE]
	at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:605) ~[spring-data-commons-2.2.6.RELEASE.jar:2.2.6.RELEASE]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.2.5.RELEASE.jar:5.2.5.RELEASE]
	at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:80) ~[spring-data-commons-2.2.6.RELEASE.jar:2.2.6.RELEASE]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.2.5.RELEASE.jar:5.2.5.RELEASE]
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:366) ~[spring-tx-5.2.5.RELEASE.jar:5.2.5.RELEASE]
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:118) ~[spring-tx-5.2.5.RELEASE.jar:5.2.5.RELEASE]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.2.5.RELEASE.jar:5.2.5.RELEASE]
	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139) ~[spring-tx-5.2.5.RELEASE.jar:5.2.5.RELEASE]
	... 69 common frames omitted
Caused by: org.postgresql.util.PSQLException: ERROR: could not determine data type of parameter $19
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2578) ~[postgresql-42.2.11.jar:42.2.11]
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2313) ~[postgresql-42.2.11.jar:42.2.11]
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:331) ~[postgresql-42.2.11.jar:42.2.11]
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:448) ~[postgresql-42.2.11.jar:42.2.11]
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:369) ~[postgresql-42.2.11.jar:42.2.11]
	at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:159) ~[postgresql-42.2.11.jar:42.2.11]
	at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:109) ~[postgresql-42.2.11.jar:42.2.11]
	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52) ~[HikariCP-3.4.2.jar:na]
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java) ~[HikariCP-3.4.2.jar:na]
	at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:57) ~[hibernate-core-5.4.12.Final.jar:5.4.12.Final]
	... 98 common frames omitted

Entendi que o erro é este Caused by: org.postgresql.util.PSQLException: ERROR: could not determine data type of parameter $19, só não consegui ajustar.

O que pode ser ?

Jonathan_Medeiros · Abril 22, 2020, 12:33am

Imagino que você deveria comparar se as colunas são nulas e não os parâmetros da query, vi que todas as suas comparações estão neste padrão, já tentou mudar?

AND (e.dataHoraMarcada >= :dataHoraMarcadaDe or e.dataHoraMarcada is null)

javaflex · Abril 22, 2020, 12:54am

Isso é comum pra deixar o parâmetro opcional. O mais otimizado seria testar primeiro se o parâmetro é nulo, se for nulo o banco nem se preocupa em fazer a busca na tabela pelo outro lado da condição.

@guilhermebhte voce fez toda essa query e só testou no final? O ideal é começar simples e testar a evolução aos poucos, senão vai ficar perdido no que exatamente causou o erro.

guilhermebhte · Abril 22, 2020, 1:31am

@javaflex
Estou fazendo aos poucos e testando.
Só está faltando implementar os campos data/hora na pesquisa. Ai começou dar o erro.

guilhermebhte · Abril 22, 2020, 1:33am

Com o JPA, do Spring, que implemento um reposityory, não consegui fazer desta forma que vc está falando.

Em outros projetos , eu fazia isso, quando tinha se não me engano o persistence

@PersistenceContext(name="PersistentUnitName")
private EntityManager entityManager;

Sempre fazia if nestas condições.

guilhermebhte · Abril 22, 2020, 1:34am

Vou testar também

javaflex · Abril 22, 2020, 2:24am

Voce já está fazendo, a ideia seria só inverter.
(:parametro is null or campo = :parametro)
ao inves de
(campo = :parametro or :parametro is null)

guilhermebhte · Abril 22, 2020, 10:12am

@javaflex

Desta maneira não funcionou.

package br.com.ghnetsoft.pregao_presencial.core.view.pregao.impl;

import java.math.BigDecimal;
import java.util.Date;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import br.com.ghnetsoft.pregao_presencial.core.licitacao.enuns.LicitacaoRegimeEnum;
import br.com.ghnetsoft.pregao_presencial.core.pregao.enuns.PregaoFasesEnum;
import br.com.ghnetsoft.pregao_presencial.core.view.pregao.ViewPregaoPesquisar;
import br.com.ghnetsoft.principal.core.enuns.StatusDoRegistroEnum;

@Repository
interface ViewPregaoPesquisarRepository extends JpaRepository<ViewPregaoPesquisar, String> {

	@Query(value = "SELECT e FROM ViewPregaoPesquisar e WHERE e.idEmpresa = :idEmpresa "
			+ "AND (e.idMesAno = :idMesAno or :idMesAno is null) "
			+ "AND (e.idLicitacao = :idLicitacao or :idLicitacao is null) "
			+ "AND (e.regime = :regime or :regime is null) "
			+ "AND (e.valorEstimado = :valorEstimado or :valorEstimado is null) "
			+ "AND (e.fase = :fase or :fase is null) "
			+ "AND (e.quantidadeFornecedor = :quantidadeFornecedor or :quantidadeFornecedor is null) "
			+ "AND (e.valorMinimoPorLance = :valorMinimoPorLance or :valorMinimoPorLance is null) "
			+ "AND (e.statusDoRegistro = :statusDoRegistro or :statusDoRegistro is null) "
			+ "AND (:dataHoraMarcadaDe is null or e.dataHoraMarcada >= :dataHoraMarcadaDe) ")
	Page<ViewPregaoPesquisar> paginacao(String idEmpresa, String idLicitacao, String idMesAno, PregaoFasesEnum fase,
			LicitacaoRegimeEnum regime, BigDecimal valorMinimoPorLance, Integer quantidadeFornecedor,
			BigDecimal valorEstimado, StatusDoRegistroEnum statusDoRegistro, Date dataHoraMarcadaDe, Pageable pageable);
}

Caused by: org.postgresql.util.PSQLException: ERROR: could not determine data type of parameter $18

guilhermebhte · Abril 22, 2020, 10:39am

@Jonathan_Medeiros

Só funcionou quando a data é preenchida. Se a data for nulo, a consulta retorna vazia.

javaflex · Abril 22, 2020, 10:41am

Acho que voce não entendeu ainda. Isso foi só uma dica sobre otimização.

O erro pode estar relacionado a mapeamento ou driver jdbc desatualizado. Se o campo com problema é TIMESTAMP, tente mapear como LocalDateTime ou ZonedDateTime.

guilhermebhte · Abril 22, 2020, 11:07am

@javaflex

Alterei o campo de timestamp with time zone para timestamp without time zone, mas não adiantou mesmo erro.

O campo está mapeado assim:

@Temporal(TIMESTAMP)
Column(name = "TS_DATA_HORA_ABERTURA")
private Date dataHoraAbertura;

Acho que não estou entendendo.

guilhermebhte · Abril 22, 2020, 11:09am

Estou usando uma das últimas versões

	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.2.6.RELEASE</version>
		<relativePath /> <!-- lookup parent from repository -->
	</parent>

javaflex · Abril 22, 2020, 11:12am

Continua como Date.

guilhermebhte · Abril 22, 2020, 12:15pm

@javaflex - não entendi …

Jonathan_Medeiros · Abril 22, 2020, 12:35pm

O que o @javaflex sugeriu é alterar o tipo do atributo!

Exemplo:

@Column(name = "TS_DATA_HORA_ABERTURA")
private LocalDateTime dataHoraAbertura;

guilhermebhte · Abril 28, 2020, 4:49pm

@Jonathan_Medeiros e @javaflex

Jonathan_Medeiros:

@Temporal(TIMESTAMP) Column(name = “TS_DATA_HORA_ABERTURA”) private Date dataHoraAbertura;

O que o @javaflex sugeriu é alterar o tipo do atributo!

Exemplo:
@Column(name = "TS_DATA_HORA_ABERTURA")
private LocalDateTime dataHoraAbertura;

Fiz conforme vc falou. Mesmo erro.

Caused by: org.postgresql.util.PSQLException: ERROR: could not determine data type of parameter $19

Jonathan_Medeiros · Abril 28, 2020, 5:22pm

Como está ficando o SQL gerado?

guilhermebhte · Abril 28, 2020, 5:54pm

`

select viewpregao0_.pk_pregao as pk_prega1_20_, viewpregao0_.nr_ano_licitacao as nr_ano_l2_20_, viewpregao0_.nr_ano_mes_ano as nr_ano_m3_20_, viewpregao0_.ts_data_hora_abertura as ts_data_4_20_, viewpregao0_.ts_data_hora_fechou as ts_data_5_20_, viewpregao0_.ts_data_hora_marcada as ts_data_6_20_, viewpregao0_.st_fase as st_fase7_20_, viewpregao0_.fk_empresa as fk_empre8_20_, viewpregao0_.pk_licitacao as pk_licit9_20_, viewpregao0_.pk_mes_ano as pk_mes_10_20_, viewpregao0_.st_mes as st_mes11_20_, viewpregao0_.nr_numero as nr_nume12_20_, viewpregao0_.nr_quantidade_fornecedor as nr_quan13_20_, viewpregao0_.st_regime as st_regi14_20_, viewpregao0_.st_registro as st_regi15_20_, viewpregao0_.cd_login_movimentacao as cd_logi16_20_, viewpregao0_.vr_valor_estimado as vr_valo17_20_, viewpregao0_.vr_minimo_por_lance as vr_mini18_20_ from view_pregao_pesquisar viewpregao0_ where viewpregao0_.fk_empresa=? and (viewpregao0_.pk_mes_ano=? or ? is null) and (viewpregao0_.pk_licitacao=? or ? is null) and (viewpregao0_.st_regime=? or ? is null) and (viewpregao0_.vr_valor_estimado=? or ? is null) and (viewpregao0_.st_fase=? or ? is null) and (viewpregao0_.nr_quantidade_fornecedor=? or ? is null) and (viewpregao0_.vr_minimo_por_lance=? or ? is null) and (viewpregao0_.st_registro=? or ? is null) and (viewpregao0_.ts_data_hora_marcada>=? or ? is null) order by viewpregao0_.pk_licitacao asc limit ?

`

Erro consulta jpql e banco de dados postgres

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