Zero date value prohibited -- hibernate SQL JPA

Question

I am trying to execute a service with postman, but it is generating the next error

The table does not have information yet

public List<TestEntity> getFilterTest() {
        List<TestEntity> test= this.testRepositoryJPA.findAll();
        if(!stateBus.isEmpty()) {
            return test.stream()
                    .filter(x -> x.getState().toLowerCase().equals("PRUEBA"))
                    .collect(Collectors.toList());
        }
        return test;
    }

The line marks the error is the next

List<TestEntity> test= this.testRepositoryJPA.findAll();

JPA Entity

public interface TestRepositoryJPA extends JpaRepository<TestEntity, Integer> {
    final static Logger logger = Logger.getLogger(TestRepositoryJPA .class);
}

The pom

<!-- Conector/libreria de MYSQL para java -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>

The Entity is

import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.apache.log4j.Logger;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
import java.io.Serializable;
import java.util.Date;

@Data
@AllArgsConstructor
@NoArgsConstructor
@Entity
@Table(name = "test")
public class TestEntity implements Serializable {

    final static Logger logger = Logger.getLogger(TestEntity.class);
    private static final long serialVersionUID = -2463354084291480284L;

    @Id
    private Integer id;

    @Column(nullable = false , name = "a")
    private String  a;

    @JsonFormat(pattern="yyyy-MM-dd hh:mm:ss")
    @Column(nullable = false , name = "b")
    private Date b;

    @Column(nullable = false , name = "c")
    private Integer  c;

    @JsonFormat(pattern="yyyy-MM-dd hh:mm:ss")
    @Column(nullable = false , name = "d")
    private Date  d;

}

The table has the next data

CREATE TABLE IF NOT EXISTS Test.test ( id INT NOT NULL, a VARCHAR(45) NULL DEFAULT NULL, b DATETIME NULL DEFAULT NULL, c INT NULL DEFAULT NULL, d DATETIME NULL DEFAULT NULL, PRIMARY KEY (id)) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8;

The Datetime format is the next

2021-08-12 10:15:00

The query result is the next

The stacktrace is the next

rg.springframework.orm.jpa.JpaSystemException: could not execute query; nested exception is org.hibernate.exception.GenericJDBCException: could not execute query at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:353) 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:153) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:178) 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.$Proxy112.findAll(Unknown Source) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344) at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:205) at com.sun.proxy.$Proxy77.findAll(Unknown Source) at com.co.umb.data.management.adapter.database.repository.StateBusDatabaseRepository.getActiveBus(StateBusDatabaseRepository.java:27) at com.co.umb.data.management.adapter.database.repository.StateBusDatabaseRepository$$FastClassBySpringCGLIB$$ad1e98c4.invoke() at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218) at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:771) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749) at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749) at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:691) at com.co.umb.data.management.adapter.database.repository.StateBusDatabaseRepository$$EnhancerBySpringCGLIB$$d0bb5e3b.getActiveBus() at com.co.umb.data.management.domain.service.ManagementService.executeHome(ManagementService.java:39) at com.co.umb.data.management.adapter.api.facade.ManagementFacade.getHomeContent(ManagementFacade.java:24) at com.co.umb.data.management.adapter.api.controller.ManagementController.getContentHome(ManagementController.java:44) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) 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:879) at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:793) 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.doPost(FrameworkServlet.java:909) at javax.servlet.http.HttpServlet.service(HttpServlet.java:660) at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883) at javax.servlet.http.HttpServlet.service(HttpServlet.java:741) 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.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.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 org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:541) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343) at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:373) 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.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) at java.lang.Thread.run(Thread.java:745) Caused by: org.hibernate.exception.GenericJDBCException: could not execute query at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113) at org.hibernate.loader.Loader.doList(Loader.java:2834) at org.hibernate.loader.Loader.doList(Loader.java:2813) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2645) at org.hibernate.loader.Loader.list(Loader.java:2640) 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:1412) at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1565) at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1533) at org.hibernate.query.Query.getResultList(Query.java:165) at org.hibernate.query.criteria.internal.compile.CriteriaQueryTypeQueryAdapter.getResultList(CriteriaQueryTypeQueryAdapter.java:76) at org.springframework.data.jpa.repository.support.SimpleJpaRepository.findAll(SimpleJpaRepository.java:355) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.springframework.data.repository.core.support.ImplementationInvocationMetadata.invoke(ImplementationInvocationMetadata.java:72) at org.springframework.data.repository.core.support.RepositoryComposition$RepositoryFragments.invoke(RepositoryComposition.java:382) at org.springframework.data.repository.core.support.RepositoryComposition.invoke(RepositoryComposition.java:205) at org.springframework.data.repository.core.support.RepositoryFactorySupport$ImplementationMethodExecutionInterceptor.invoke(RepositoryFactorySupport.java:549) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:155) 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) ... 78 more Caused by: java.sql.SQLException: Zero date value prohibited at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129) at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89) at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63) at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:73) at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:99) at com.mysql.cj.jdbc.result.ResultSetImpl.getTimestamp(ResultSetImpl.java:914) at com.mysql.cj.jdbc.result.ResultSetImpl.getTimestamp(ResultSetImpl.java:959) at com.zaxxer.hikari.pool.HikariProxyResultSet.getTimestamp(HikariProxyResultSet.java) at org.hibernate.type.descriptor.sql.TimestampTypeDescriptor$2.doExtract(TimestampTypeDescriptor.java:84) at org.hibernate.type.descriptor.sql.BasicExtractor.extract(BasicExtractor.java:47) at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:257) at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:253) at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:243) at org.hibernate.type.AbstractStandardBasicType.hydrate(AbstractStandardBasicType.java:329) at org.hibernate.persister.entity.AbstractEntityPersister.hydrate(AbstractEntityPersister.java:3087) at org.hibernate.loader.Loader.loadFromResultSet(Loader.java:1851) at org.hibernate.loader.Loader.hydrateEntityState(Loader.java:1779) at org.hibernate.loader.Loader.instanceNotYetLoaded(Loader.java:1752) at org.hibernate.loader.Loader.getRow(Loader.java:1600) at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:743) at org.hibernate.loader.Loader.processResultSet(Loader.java:1006) at org.hibernate.loader.Loader.doQuery(Loader.java:962) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:352) at org.hibernate.loader.Loader.doList(Loader.java:2831) ... 108 more Caused by: com.mysql.cj.exceptions.DataReadException: Zero date value prohibited at com.mysql.cj.result.SqlTimestampValueFactory.localCreateFromTimestamp(SqlTimestampValueFactory.java:101) at com.mysql.cj.result.SqlTimestampValueFactory.localCreateFromTimestamp(SqlTimestampValueFactory.java:50) at com.mysql.cj.result.AbstractDateTimeValueFactory.createFromTimestamp(AbstractDateTimeValueFactory.java:87) at com.mysql.cj.protocol.a.MysqlTextValueDecoder.decodeTimestamp(MysqlTextValueDecoder.java:81) at com.mysql.cj.protocol.result.AbstractResultsetRow.decodeAndCreateReturnValue(AbstractResultsetRow.java:87) at com.mysql.cj.protocol.result.AbstractResultsetRow.getValueFromBytes(AbstractResultsetRow.java:241) at com.mysql.cj.protocol.a.result.ByteArrayRow.getValue(ByteArrayRow.java:91) ... 127 more

The code that you marked as Entity (in your question) is actually the repository. Can you include your `Entity` definition as well, please? - it seems like an annotation/definition is missing or misaligned — blurfus, Jul 12 '21 at 20:48
Does this answer your question? ['0000-00-00 00:00:00' can not be represented as java.sql.Timestamp error](https://stackoverflow.com/questions/11133759/0000-00-00-000000-can-not-be-represented-as-java-sql-timestamp-error) — blurfus, Jul 12 '21 at 20:49
In the `Date` field, the annotation indicates it cannot accept null values... check your DB for any records with this field either `NULL` or zeroed out.. — blurfus, Jul 12 '21 at 20:51
the definition of the table, allows NULL but the Entity does not - try changing this `@Column(nullable = false , name = "b") private Date b;` to this `@Column(nullable = true...)` — blurfus, Jul 12 '21 at 20:56
@AlejandroGonzalez a solution work for me. You can also try it. Please see my answer below. — Shailesh Vikram Singh, Jul 18 '22 at 15:23

score 6 · Answer 1 · answered Jan 21 '22 at 08:35

6

Please check the database table columns with date type if any row contains zero value something like 0000-00-00.

answered Jan 21 '22 at 08:35

blueDexter

977
11
14

This is an issue that I am facing, but I cannot change the data in the database, how can I work around this? – flash Jan 21 '22 at 20:27
Ok!! Will post if I found something. – blueDexter Jan 24 '22 at 06:24

Shailesh Vikram Singh · Answer 2 · 2022-07-20T06:18:12.570

6

I faced same issue in a column namely modified_date type TIMESTAMP in MYSQL DB. I just added zeroDateTimeBehavior=convertToNull as query string parameter with datasource URL in application.properties file in springboot application and it worked for me.

spring.datasource.url=jdbc:mysql://localhost:3306/testdb?zeroDateTimeBehavior=convertToNull

Here is more details available.

edited Jul 20 '22 at 06:18

answered Jul 18 '22 at 14:56

Shailesh Vikram Singh

1,433
17
26

score 1 · Answer 3 · answered May 25 '23 at 21:33

It is an exception that is returned when we have date field with zeros like:

0000-00-00

In my case, for example, removing/editing the existing field with zeros solved my problem. By depending on your case, you can filter invalid date fields like this and handle them to avoid this kind of exception.

score -1 · Answer 4 · edited Jul 21 '21 at 15:32

In my case, the error was because my entity has a null field, then I changed the fields

import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.apache.log4j.Logger;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
import java.io.Serializable;
import java.util.Date;

@Data
@AllArgsConstructor
@NoArgsConstructor
@Entity
@Table(name = "test")
public class Test implements Serializable {

    final static Logger logger = Logger.getLogger(StateDriverEntity.class);
    private static final long serialVersionUID = -2463354084291480284L;

    @Id
    private Integer id;

    @Column(nullable = false , name = "a")
    private String  a;

    @Column(nullable = false , name = "b")
    private Date b;

    @Column(nullable = false , name = "c")
    private Integer  c;

    @Column(nullable = false , name = "d")
    private Date  d;

}

The field c has this name

stateActive

The error was generated because when the JPA generates the mapping, it converts in camel case

state_active

The columns does not have data, as consequence the nullable generates an error.

The solution was

Changed the column's name from stateActive to state_active

Note: it was not necessary to change the spring.datasource.url

spring.datasource.url=jdbc:mysql://a:a@local/c?reconnect=true

Zero date value prohibited -- hibernate SQL JPA

4 Answers4