I am working on functionality where I need to fetch the data from DB based on the date. The date format in DB is yyyy-MM-dd and in my application, I am passing the date in the same form.
The day column in TempTable entity class is Date(java.util.Date). I used Temporal as well but no success.
Below is the java code:
EntityManagerFactory emf= Persistence.createEntityManagerFactory("punit");
EntityManager em = emf.createEntityManager();
Query query;
query = em.createQuery("select r from TempTable r where r.uId = :uid and r.day = :day group by r.mId, r.rId");
query.setParameter("uid", 5l);
SimpleDateFormat simpleDateFormat= new SimpleDateFormat("yyyy-MM-dd");
Date d = simpleDateFormat.parse("2018-10-12");
query.setParameter("day", d);
query.getResultList();
After execution of the code, found the exception as below:
Jan 13, 2020 3:11:20 PM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
ERROR: Unsupported conversion from TIMESTAMP to java.lang.Long
Exception in thread "main" javax.persistence.PersistenceException: org.hibernate.exception.DataException: could not execute query
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:154)
at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1535)
at org.hibernate.query.Query.getResultList(Query.java:165)
at com.abc.XXXXX.main(XXXXX.java:30)
Caused by: org.hibernate.exception.DataException: could not execute query
at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:52)
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
at org.hibernate.loader.Loader.doList(Loader.java:2818)
at org.hibernate.loader.Loader.doList(Loader.java:2797)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2629)
at org.hibernate.loader.Loader.list(Loader.java:2624)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:506)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:396)
at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:219)
at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1396)
at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1558)
at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1526)
... 2 more
Caused by: java.sql.SQLDataException: Unsupported conversion from TIMESTAMP to java.lang.Long
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:114)
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:96)
at com.mysql.cj.jdbc.result.ResultSetImpl.getObject(ResultSetImpl.java:1382)
at com.mysql.cj.jdbc.result.ResultSetImpl.getLong(ResultSetImpl.java:812)
at com.mysql.cj.jdbc.result.ResultSetImpl.getLong(ResultSetImpl.java:818)
at org.hibernate.type.descriptor.sql.BigIntTypeDescriptor$2.doExtract(BigIntTypeDescriptor.java:63)
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:3041)
at org.hibernate.loader.Loader.loadFromResultSet(Loader.java:1866)
at org.hibernate.loader.Loader.hydrateEntityState(Loader.java:1794)
at org.hibernate.loader.Loader.instanceNotYetLoaded(Loader.java:1767)
at org.hibernate.loader.Loader.getRow(Loader.java:1615)
at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:745)
at org.hibernate.loader.Loader.processResultSet(Loader.java:1008)
at org.hibernate.loader.Loader.doQuery(Loader.java:964)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:354)
at org.hibernate.loader.Loader.doList(Loader.java:2815)
... 11 more
Caused by: com.mysql.cj.exceptions.DataConversionException: Unsupported conversion from TIMESTAMP to java.lang.Long
at com.mysql.cj.result.DefaultValueFactory.unsupported(DefaultValueFactory.java:70)
at com.mysql.cj.result.DefaultValueFactory.createFromTimestamp(DefaultValueFactory.java:82)
at com.mysql.cj.protocol.a.MysqlTextValueDecoder.decodeTimestamp(MysqlTextValueDecoder.java:79)
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)
at com.mysql.cj.jdbc.result.ResultSetImpl.getObject(ResultSetImpl.java:1290)
... 29 more
The configuration is:
- Java8
- Mysql8
- JPA2.1
- Spring5
LocalDate.SimpleDateFormatandDate. Those classes are poorly designed and long outdated, the former in particular notoriously troublesome. Instead use classes from java.time, the modern Java date and time API. If your datatype in MySQL istimestamp(which in MySQL defines a point int time) you should be able to retrieve it into anOffsetDateTimeorInstant. See my answer here.