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

Hibernate JIRA | Pawel Solarski | 5 months ago
tip
Your exception is missing from the Samebug knowledge base.
Here are the best solutions we found on the Internet.
Click on the to mark the helpful solution and get rewards for you help.
  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 | 5 months ago | Pawel Solarski
    javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
  2. 0

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

    Stack Overflow | 7 years ago | Rakesh Sabbani
    java.sql.SQLException: Cannot convert value '0000-00-00 00:00:00' from column 12 to TIMESTAMP.
  3. 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.
  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 | 2 years 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. DoktorDoener666 1 times, last 4 weeks ago
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