javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query

Hibernate JIRA | Pawel Solarski | 2 months ago
  1. 0

    Given the code and testcase below: {code} /** * Created by Pawel_Solarski on 21.09.2016. */ package org.hibernate.bugs; import org.junit.After; import org.junit.Before; import org.junit.Test; import javax.persistence.*; import java.util.Date; import java.util.List; /** * This template demonstrates how to develop a test case for Hibernate ORM, using the Java Persistence API. */ public class JPAUnitFieldResultMappingTestCase { private EntityManagerFactory entityManagerFactory; @Before public void init() { entityManagerFactory = Persistence.createEntityManagerFactory("templatePU"); } @After public void destroy() { entityManagerFactory.close(); } // Entities are auto-discovered, so just add them anywhere on class-path // Add your tests, using standard JUnit. @Test public void shouldMapNativeQueryUsingFieldResultMapping() throws Exception { EntityManager entityManager = entityManagerFactory.createEntityManager(); entityManager.getTransaction().begin(); entityManager.persist(new AuditLog(1L, "foo", new Date())); entityManager.persist(new AuditLog(2L, "bar", new Date())); entityManager.getTransaction().commit(); entityManager.close(); entityManager = entityManagerFactory.createEntityManager(); // new EntityManager instance is being created entityManager.getTransaction().begin(); List<AuditLog> list = entityManager.createNamedQuery("getAuditLogByUsername", AuditLog.class).setParameter("username", "foo").getResultList(); list.stream().forEach(System.out::println); entityManager.getTransaction().commit(); entityManager.close(); } } @Entity @SqlResultSetMapping( name = "auditLogResultSet", entities = @EntityResult( entityClass = AuditLog.class, fields = { @FieldResult(name = "id", column = "id"), @FieldResult(name = "ts", column = "insert_date"), @FieldResult(name = "username", column = "username") } ) ) @NamedNativeQuery( name = "getAuditLogByUsername", query = "select id, username, ts as insert_date from auditlog where username = :username", resultSetMapping = "auditLogResultSet" ) class AuditLog { @Id private Long id; private String username; @Temporal(TemporalType.TIMESTAMP) @Column(name = "ts") private Date date; public AuditLog() { } public AuditLog(Long id, String username, Date date) { this.id = id; this.username = username; this.date = date; } public AuditLog(Long id, String username) { this(id, username, new Date()); } public Long getId() { return id; } public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } public Date getDate() { return date; } public void setDate(Date date) { this.date = date; } @Override public String toString() { return "AuditLog{" + "id=" + id + ", username='" + username + '\'' + ", date=" + date + '}'; } } {code} The following exception is thrown: {code} Hibernate: insert into AuditLog (ts, username, id) values (?, ?, ?) Hibernate: insert into AuditLog (ts, username, id) values (?, ?, ?) Hibernate: select id, username, ts as insert_date from auditlog where username = ? wrz 21, 2016 11:49:27 AM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions WARN: SQL Error: 0, SQLState: S0022 wrz 21, 2016 11:49:27 AM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions ERROR: Column 'ts2_2_0_' not found. wrz 21, 2016 11:49:27 AM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl stop INFO: HHH10001008: Cleaning up connection pool [jdbc:mysql://localhost:3306/test] javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:147) at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:155) at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1383) at org.hibernate.Query.getResultList(Query.java:417) at org.hibernate.bugs.JPAUnitFieldResultMappingTestCase.shouldMapNativeQueryUsingFieldResultMapping(JPAUnitFieldResultMappingTestCase.java:47) 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.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50) at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12) at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47) at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17) at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26) at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27) at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325) at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78) at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57) at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290) at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71) at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288) at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58) at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268) at org.junit.runners.ParentRunner.run(ParentRunner.java:363) at org.junit.runner.JUnitCore.run(JUnitCore.java:137) at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:117) at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:42) at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:262) at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:84) 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 com.intellij.rt.execution.application.AppMain.main(AppMain.java:147) Caused by: org.hibernate.exception.SQLGrammarException: could not execute query at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:106) at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111) at org.hibernate.loader.Loader.doList(Loader.java:2612) at org.hibernate.loader.Loader.doList(Loader.java:2592) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2424) at org.hibernate.loader.Loader.list(Loader.java:2419) at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:335) at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:2119) at org.hibernate.internal.AbstractSharedSessionContract.list(AbstractSharedSessionContract.java:952) at org.hibernate.query.internal.NativeQueryImpl.doList(NativeQueryImpl.java:147) at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1374) ... 31 more Caused by: java.sql.SQLException: Column 'ts2_2_0_' not found. at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:957) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:896) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:885) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860) at com.mysql.jdbc.ResultSetImpl.findColumn(ResultSetImpl.java:1076) at com.mysql.jdbc.ResultSetImpl.getTimestamp(ResultSetImpl.java:5647) at org.hibernate.type.descriptor.sql.TimestampTypeDescriptor$2.doExtract(TimestampTypeDescriptor.java:76) at org.hibernate.type.descriptor.sql.BasicExtractor.extract(BasicExtractor.java:47) at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:258) at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:254) at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:244) at org.hibernate.type.AbstractStandardBasicType.hydrate(AbstractStandardBasicType.java:327) at org.hibernate.persister.entity.AbstractEntityPersister.hydrate(AbstractEntityPersister.java:2775) at org.hibernate.loader.Loader.loadFromResultSet(Loader.java:1735) at org.hibernate.loader.Loader.instanceNotYetLoaded(Loader.java:1661) at org.hibernate.loader.Loader.getRow(Loader.java:1550) at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:734) at org.hibernate.loader.Loader.processResultSet(Loader.java:979) at org.hibernate.loader.Loader.doQuery(Loader.java:937) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:343) at org.hibernate.loader.Loader.doList(Loader.java:2609) ... 39 more {code} The problem is gone when using same EntityManager instance throughout all transactions

    Hibernate JIRA | 2 months ago | Pawel Solarski
    javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
  2. 0

    SQL exception launching RDFizer

    GitHub | 2 years ago | scanbit
    org.springframework.orm.hibernate3.HibernateJdbcException: JDBC exception on Hibernate data access: SQLException for SQL [n/a]; SQL state [S1009]; error code [0]; Cannot convert value '0000-00-00 00:00:00' from column 4 to TIMESTAMP.; nested exception is org.hibernate.exception.GenericJDBCException: Cannot convert value '0000-00-00 00:00:00' from column 4 to TIMESTAMP.
  3. 0

    Getting the exception like "Cannot convert value '0000-00-00 00:00:00' from column 12 to TIMESTAMP"

    Stack Overflow | 6 years ago | Rakesh Sabbani
    java.sql.SQLException: Cannot convert value '0000-00-00 00:00:00' from column 12 to TIMESTAMP.
  4. Speed up your debug routine!

    Automated exception search integrated into your IDE

  5. 0

    How to deal with "Value '0000-00-00' can not be represented as java.sql.Timestamp" error at application level?

    Stack Overflow | 2 years ago | Dave A
    javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: Could not read entity state from ResultSet : EntityKey[org.mainco.subco.mypackage.domain.mypackage#BELVUEADMINTESTLP1]
  6. 0

    GitHub comment 1#135685107

    GitHub | 1 year ago | chatainsim
    java.sql.SQLException: Value '1022iny Tiny RSS: New Releases�http://tt-rss.org/releases.rss000000-00-00 00:00:00�0001000�2014-12-01 13:37:420000000�0�' can not be represented as java.sql.Timestamp

    1 unregistered visitors
    Not finding the right solution?
    Take a tour to get the most out of Samebug.

    Tired of useless tips?

    Automated exception search integrated into your IDE

    Root Cause Analysis

    1. java.sql.SQLException

      Column 'ts2_2_0_' not found.

      at com.mysql.jdbc.SQLError.createSQLException()
    2. MySQL jdbc
      ResultSetImpl.getTimestamp
      1. com.mysql.jdbc.SQLError.createSQLException(SQLError.java:957)
      2. com.mysql.jdbc.SQLError.createSQLException(SQLError.java:896)
      3. com.mysql.jdbc.SQLError.createSQLException(SQLError.java:885)
      4. com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860)
      5. com.mysql.jdbc.ResultSetImpl.findColumn(ResultSetImpl.java:1076)
      6. com.mysql.jdbc.ResultSetImpl.getTimestamp(ResultSetImpl.java:5647)
      6 frames
    3. Hibernate
      AbstractSharedSessionContract.list
      1. org.hibernate.type.descriptor.sql.TimestampTypeDescriptor$2.doExtract(TimestampTypeDescriptor.java:76)
      2. org.hibernate.type.descriptor.sql.BasicExtractor.extract(BasicExtractor.java:47)
      3. org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:258)
      4. org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:254)
      5. org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:244)
      6. org.hibernate.type.AbstractStandardBasicType.hydrate(AbstractStandardBasicType.java:327)
      7. org.hibernate.persister.entity.AbstractEntityPersister.hydrate(AbstractEntityPersister.java:2775)
      8. org.hibernate.loader.Loader.loadFromResultSet(Loader.java:1735)
      9. org.hibernate.loader.Loader.instanceNotYetLoaded(Loader.java:1661)
      10. org.hibernate.loader.Loader.getRow(Loader.java:1550)
      11. org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:734)
      12. org.hibernate.loader.Loader.processResultSet(Loader.java:979)
      13. org.hibernate.loader.Loader.doQuery(Loader.java:937)
      14. org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:343)
      15. org.hibernate.loader.Loader.doList(Loader.java:2609)
      16. org.hibernate.loader.Loader.doList(Loader.java:2592)
      17. org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2424)
      18. org.hibernate.loader.Loader.list(Loader.java:2419)
      19. org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:335)
      20. org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:2119)
      21. org.hibernate.internal.AbstractSharedSessionContract.list(AbstractSharedSessionContract.java:952)
      21 frames
    4. org.hibernate.query
      AbstractProducedQuery.list
      1. org.hibernate.query.internal.NativeQueryImpl.doList(NativeQueryImpl.java:147)
      2. org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1374)
      2 frames
    5. Hibernate
      Query.getResultList
      1. org.hibernate.Query.getResultList(Query.java:417)
      1 frame
    6. org.hibernate.bugs
      JPAUnitFieldResultMappingTestCase.shouldMapNativeQueryUsingFieldResultMapping
      1. org.hibernate.bugs.JPAUnitFieldResultMappingTestCase.shouldMapNativeQueryUsingFieldResultMapping(JPAUnitFieldResultMappingTestCase.java:47)
      1 frame
    7. Java RT
      Method.invoke
      1. sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      2. sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
      3. sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      4. java.lang.reflect.Method.invoke(Method.java:498)
      4 frames
    8. JUnit
      JUnitCore.run
      1. org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
      2. org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
      3. org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
      4. org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
      5. org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
      6. org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
      7. org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
      8. org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
      9. org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
      10. org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
      11. org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
      12. org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
      13. org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
      14. org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
      15. org.junit.runners.ParentRunner.run(ParentRunner.java:363)
      16. org.junit.runner.JUnitCore.run(JUnitCore.java:137)
      16 frames
    9. IntelliJ junit4 module
      JUnit4IdeaTestRunner.startRunnerWithArgs
      1. com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:117)
      2. com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:42)
      2 frames
    10. IDEA
      JUnitStarter.main
      1. com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:262)
      2. com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:84)
      2 frames
    11. Java RT
      Method.invoke
      1. sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      2. sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
      3. sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      4. java.lang.reflect.Method.invoke(Method.java:498)
      4 frames
    12. IDEA
      AppMain.main
      1. com.intellij.rt.execution.application.AppMain.main(AppMain.java:147)
      1 frame