Erro em consulta com Spring data

guilhermebhte · Dezembro 23, 2021, 11:26pm

Estou com este erro

query

@Query(value = 
  "SELECT SUM(e.litros) " +
  "FROM NotaFiscal e " +
  "WHERE (e.usuario.id = :idUsuario) " +
  "AND (e.metadadoAuditoria.statusDoRegistro = :statusDoRegistro) " +
  "AND (e.vencimento >= :vencimento or :vencimento is null) "
)
BigDecimal somaNotasPorStatus(
  @Param("idUsuario") String idUsuario, 
  @Param("statusDoRegistro") StatusDoRegistroEnum statusDoRegistro, 
  @Param("vencimento") LocalDate vencimento);

Erro

Hibernate: select sum(notafiscal0_.nr_litros) as col_0_0_ from tab_nota_fiscal notafiscal0_ where notafiscal0_.fk_usuario=? and notafiscal0_.st_registro=? and (notafiscal0_.dt_vencimento>=? or ? is null)
2021-12-23 20:17:17 - o.h.e.jdbc.spi.SqlExceptionHelper - SQL Error: 0, SQLState: 42P18
2021-12-23 20:17:17 - o.h.e.jdbc.spi.SqlExceptionHelper - ERROR: could not determine data type of parameter $4
2021-12-23 20:17:17 - b.c.g.p.resource.MensagensRetorno - could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
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)
	at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:255)
	at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:528)
	at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61)
	at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:242)
	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:154)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:149)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:95)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)

Para chamar a auery

final LocalDate minusDays = now().minusDays(30);
        final BigDecimal pontosExpirar = repository.somaNotasPorStatus(principal.getName(), ATIVO, minusDays);

Campo no model está assim

    @Column(name = "DT_VENCIMENTO")
    private LocalDate vencimento;

Todos são importados desta import java.time.LocalDate;

O que pode ser este erro ?

Banco de dados postgres

Lucas_Camara · Dezembro 23, 2021, 11:43pm

Qual o tipo da coluna litros no banco de dados?

Jonathan_Medeiros · Dezembro 24, 2021, 1:50am

O @Query por padrão espera JPQL e você parece estar informando SQL nativo, nesse caso você precisa configurar a anotação da seguinte forma: @Query(" … ", nativeQuery = true.

guilhermebhte · Dezembro 24, 2021, 11:02am

@Lucas_Camara

o model completo

package br.com.ghnetsoft.gestaovendasatendimento.model;

import static javax.persistence.EnumType.STRING;
import static lombok.AccessLevel.PROTECTED;
import static org.hibernate.annotations.CacheConcurrencyStrategy.NONSTRICT_READ_WRITE;

import java.math.BigDecimal;
import java.time.LocalDate;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Enumerated;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.Table;

import org.hibernate.annotations.Cache;
import org.hibernate.annotations.GenericGenerator;

import br.com.ghnetsoft.principal.auditoria.AuditableBase;
import br.com.ghnetsoft.principal.enuns.SimNaoEnum;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.EqualsAndHashCode;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;

@EqualsAndHashCode(callSuper = false)
@Setter
@Getter
@Entity
@Builder
@Table(name = "TAB_NOTA_FISCAL")
@NoArgsConstructor(access = PROTECTED)
@AllArgsConstructor(access = PROTECTED)
@Cache(usage = NONSTRICT_READ_WRITE)
public class NotaFiscal extends AuditableBase {

    private static final long serialVersionUID = 7531211143476980946L;
    @Id
    @Column(name = "PK_NOTA_FISCAL")
    @GeneratedValue(generator = "UUID")
    @GenericGenerator(name = "UUID", strategy = "org.hibernate.id.UUIDGenerator")
    private String id;
    @ManyToOne(targetEntity = Usuario.class)
    @JoinColumn(name = "FK_USUARIO")
    private Usuario usuario;
    @Column(name = "DS_NUMERO_NOTA")
    private String numeroNota;
    @Column(name = "DS_CHAVE_ACESSO")
    private String chaveAcesso;
    @Column(name = "NR_LITROS")
    private BigDecimal litros;
    @Column(name = "DT_NOTA")
    private LocalDate data;
    @Column(name = "DT_VENCIMENTO")
    private LocalDate vencimento;
    @Enumerated(STRING)
    @Column(name = "ST_VALIDO")
    private SimNaoEnum valido;
    @Column(name = "NR_TEMPO")
    private Long tempo;
    @Column(name = "NR_PONTOS_POR_LITRO")
    private BigDecimal pontosPorLitro;
    @Column(name = "NR_LITROS_USADOS")
    private BigDecimal litrosUsados;
}

guilhermebhte · Dezembro 24, 2021, 11:06am

@Jonathan_Medeiros

Deu este erro, mas o que fiz porque não é JPQL ?

Caused by: org.postgresql.util.PSQLException: ERROR: relation "notafiscal" does not exist
  Posição: 27
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2285)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:323)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:473)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:393)
	at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:164)
	at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:114)
	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
	at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:57)
	... 128 common frames omitted

lucas.sampaio · Dezembro 24, 2021, 11:48am

O erro é esse :vencimento is null. Deve ver ser a coluna que é null e não o parâmetro. O correto é sem os : em vencimento:

or vencimento is null

guilhermebhte · Dezembro 24, 2021, 11:53am

Tenho consultas assim e funcionam

O problema acho que é o date

Esta consulta funciona

@Query(value = "SELECT e FROM Usuario e WHERE (e.email = :email) AND (e.tipoUsuario = :tipoUsuario or :tipoUsuario is null) AND (e.metadadoAuditoria.statusDoRegistro = 'ATIVO') ")
Usuario existeUsuarioComEmailAndTipoUsuario(@Param("email") String email, @Param("tipoUsuario") TipoUsuarioEnum tipoUsuario);

lucas.sampaio · Dezembro 24, 2021, 11:58am

Se funciona eu não sei o porque (vou procurar entender). Mas, veja que na saída ele vê se um parâmetro é null

 ? is null

E isso é errado. A coluna 2021-12-24 não existe no banco. E sim vencimento. A mesma coisa aqui

Supondo que tenha um enum ADMIN. Não é ADMIN que é coluna no banco e sim tipoUsuario.

Tanto que no restante do erro ele diz

ERROR: could not determine data type of parameter $4

guilhermebhte · Dezembro 24, 2021, 12:00pm

Eu faço este tipo de consulta, porque em nem toda consulta este parametro pode estar preenchido.

O que vc está dizendo, seria assim:

@Query(value = 
  "SELECT SUM(e.litros) " +
  "FROM NotaFiscal e " +
  "WHERE (e.usuario.id = :idUsuario) " +
  "AND (e.metadadoAuditoria.statusDoRegistro = :statusDoRegistro) " +
  "AND (e.vencimento >= :vencimento or vencimento is null) "
)
BigDecimal somaNotasPorStatus(
  @Param("idUsuario") String idUsuario, 
  @Param("statusDoRegistro") StatusDoRegistroEnum statusDoRegistro, 
  @Param("vencimento") LocalDate vencimento);

lucas.sampaio · Dezembro 24, 2021, 12:03pm

Entendo. Mas, como tu vai saber se ele tá preenchido? Tu tem que garantir que está ou não está. Isso é feito antes de passar o dado pra query.

Isso. Vencimento existe como coluna da tabela. Se por exemplo o teu valor for null a query vai ficar assim

AND (e.vencimento >= :vencimento or null is null)

Ou se tivesse valor e não fosse true na primeira condição a query ficaria assim

AND (e.vencimento >= :vencimento or 2021-12-24 is null)

E 2021-12-24 não é coluna do banco.

guilhermebhte · Dezembro 24, 2021, 12:05pm

Sim. Esta validação é feita antes de passar para query.

A questão é que o erro só acontece quando o parâmetro é LocalDate. E quando a data é preenchida

lucas.sampaio · Dezembro 24, 2021, 12:08pm

A data preenchida é satisfaz a primeira condição ou seja a data que tu tá passando é maior do que tem no banco?
Faz o seguinte… pega a query formatada que sai no console e executa direto no banco pra ver se funciona. Talvez vai fazer mais sentido o que eu estou te falando.

guilhermebhte · Dezembro 24, 2021, 12:19pm

A data é final LocalDate minusDays = now().minusDays(30);, data do dia - 30 dias.

Vou fazer

guilhermebhte · Dezembro 24, 2021, 12:20pm

    public RewardDTO reward(final Principal principal) {
        final BigDecimal pontosValido = repository.somaNotasPorStatus(principal.getName(), SIM, null);
        final BigDecimal pontosValidar = repository.somaNotasPorStatus(principal.getName(), NAO, null);
        final LocalDate minusDays = now().minusDays(30);
        final BigDecimal pontosExpirar = repository.somaNotasPorStatus(principal.getName(), SIM, minusDays);
        return preencher.dto(pontosValido, pontosValidar, pontosExpirar);
    }

A consulta para pontosValido, funciona
A consulta para pontosValidar, funciona
A consulta para pontosExpirar, não funciona

guilhermebhte · Dezembro 24, 2021, 12:22pm

Caused by: org.postgresql.util.PSQLException: ERROR: could not determine data type of parameter $4
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2285)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:323)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:473)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:393)
	at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:164)
	at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:114)
	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
	at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:57)
	... 129 common frames omitted

Erro

guilhermebhte · Dezembro 24, 2021, 12:29pm

Consulta gerada pelo jpa/Hibernate

select sum(notafiscal0_.nr_litros-notafiscal0_.nr_litros_usados) as col_0_0_ from tab_nota_fiscal notafiscal0_ where notafiscal0_.fk_usuario=? and notafiscal0_.st_valido=? and (notafiscal0_.dt_vencimento>=? or ? is null)

Testando no banco
Com usuário, valido = SIM e data = null

select sum(notafiscal0_.nr_litros-notafiscal0_.nr_litros_usados) as col_0_0_ 
from tab_nota_fiscal notafiscal0_ 
where notafiscal0_.fk_usuario='f34e21bb-163b-4899-b89c-6c8553fb5b69' 
and notafiscal0_.st_valido='SIM' 
and (notafiscal0_.dt_vencimento>=null or null is null)

Retorno

Com usuário, valido = NAO e data = null

select sum(notafiscal0_.nr_litros-notafiscal0_.nr_litros_usados) as col_0_0_ 
from tab_nota_fiscal notafiscal0_ 
where notafiscal0_.fk_usuario='f34e21bb-163b-4899-b89c-6c8553fb5b69' 
and notafiscal0_.st_valido='NAO' 
and (notafiscal0_.dt_vencimento>=null or null is null)

Com usuário, valido = SIM e data = 2021-11-24

select sum(notafiscal0_.nr_litros-notafiscal0_.nr_litros_usados) as col_0_0_ 
from tab_nota_fiscal notafiscal0_ 
where notafiscal0_.fk_usuario='f34e21bb-163b-4899-b89c-6c8553fb5b69' 
and notafiscal0_.st_valido='NAO' 
and (notafiscal0_.dt_vencimento>='2021-11-24' or '2021-11-24' is null)

Com usuário, valido = SIM e data = 20211124

select sum(notafiscal0_.nr_litros-notafiscal0_.nr_litros_usados) as col_0_0_ 
from tab_nota_fiscal notafiscal0_ 
where notafiscal0_.fk_usuario='f34e21bb-163b-4899-b89c-6c8553fb5b69' 
and notafiscal0_.st_valido='NAO' 
and (notafiscal0_.dt_vencimento>='20211124' or '20211124' is null)

lucas.sampaio · Dezembro 24, 2021, 1:23pm

Tá usando o postgresql né?
Tenta converter o LocalDate na query assim

CAST( ? AS date) IS NULL

guilhermebhte · Dezembro 24, 2021, 2:36pm

Fiz assim

@Query(value = "SELECT SUM(e.litros - e.litrosUsados) FROM NotaFiscal e WHERE (e.usuario.id = :idUsuario) AND (e.valido = :valido) AND (e.vencimento >= CAST(:vencimento AS date) or :vencimento is null) ")
    BigDecimal somaNotasPorStatus(@Param("idUsuario") String idUsuario, @Param("valido") SimNaoEnum valido, @Param("vencimento") LocalDate vencimento);

Este erro

2021-12-24 11:30:59 - o.h.e.jdbc.spi.SqlExceptionHelper - SQL Error: 0, SQLState: 42846
2021-12-24 11:30:59 - o.h.e.jdbc.spi.SqlExceptionHelper - ERROR: cannot cast type bytea to date
  Posição: 208
2021-12-24 11:30:59 - b.c.g.p.resource.MensagensRetorno - could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
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)
	at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:255)
	at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:528)
	at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61)
	at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:242)
	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:154)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:149)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:95)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)
	at com.sun.proxy.$Proxy638.somaNotasPorStatus(Unknown Source)
	at br.com.ghnetsoft.gestaovendasatendimento.site.service.RewardService.reward(RewardService.java:29)
	at br.com.ghnetsoft.gestaovendasatendimento.site.resource.RewardResource.reward(RewardResource.java:33)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:566)
	at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:190)
	at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:138)
	at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:105)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:878)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:792)
	at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)
	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1040)
	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:943)
	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006)
	at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:898)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:626)
	at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:733)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:320)
	at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:126)
	at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:90)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
	at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:118)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
	at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:137)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
	at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:111)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
	at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:158)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
	at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:63)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
	at org.springframework.security.oauth2.provider.authentication.OAuth2AuthenticationProcessingFilter.doFilter(OAuth2AuthenticationProcessingFilter.java:176)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
	at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:116)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
	at org.springframework.security.web.header.HeaderWriterFilter.doHeadersAfter(HeaderWriterFilter.java:92)
	at org.springframework.security.web.header.HeaderWriterFilter.doFilterInternal(HeaderWriterFilter.java:77)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
	at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:105)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
	at org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:56)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
	at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:215)
	at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:178)
	at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:358)
	at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:271)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.springframework.boot.actuate.metrics.web.servlet.WebMvcMetricsFilter.doFilterInternal(WebMvcMetricsFilter.java:93)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at br.com.ghnetsoft.gestaovendasatendimento.site.config.SmpleCORSFilter.doFilter(SmpleCORSFilter.java:48)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:542)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:143)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343)
	at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:374)
	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:868)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1590)
	at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
	at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
	at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:103)
	at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99)
	at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:67)
	at org.hibernate.loader.Loader.getResultSet(Loader.java:2304)
	at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2057)
	at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2019)
	at org.hibernate.loader.Loader.doQuery(Loader.java:948)
	at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:349)
	at org.hibernate.loader.Loader.doList(Loader.java:2850)
	at org.hibernate.loader.Loader.doList(Loader.java:2832)
	at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2664)
	at org.hibernate.loader.Loader.list(Loader.java:2659)
	at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:506)
	at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:400)
	at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:219)
	at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1414)
	at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1565)
	at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1533)
	at org.hibernate.query.internal.AbstractProducedQuery.getSingleResult(AbstractProducedQuery.java:1581)
	at org.springframework.data.jpa.repository.query.JpaQueryExecution$SingleEntityExecution.doExecute(JpaQueryExecution.java:196)
	at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:88)
	at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:154)
	at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:142)
	at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor$QueryMethodInvoker.invoke(QueryExecutorMethodInterceptor.java:195)
	at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:152)
	at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:130)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:80)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:367)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:118)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139)
	... 99 common frames omitted
Caused by: org.postgresql.util.PSQLException: ERROR: cannot cast type bytea to date
  Posição: 208
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2285)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:323)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:473)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:393)
	at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:164)
	at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:114)
	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
	at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:57)
	... 129 common frames omitted

Bloco de Citação

javaflex · Dezembro 24, 2021, 9:35pm

Usa query nativa, em SQL diretamente é bem mais prático.

@Query(value = "diretamente o SQL sem engessamento de modelagem OO", nativeQuery = true)

pmlm · Dezembro 27, 2021, 9:44am

O postgres não tem nenhum tipo de dados que mapeie com o LocalDate. Se usares Instant deverá funcionar.

Erro em consulta com Spring data

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