1

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
Mandar Dharurkar
  • 267
  • 1
  • 5
  • 16
Sunil Chaphekar
  • 23
  • 1
  • 1
  • 4
  • Please describe table TempTable if day is stored as timestamp then you need to change hql – Mandar Dharurkar Jan 13 '20 at 10:01
  • no, a column is defined as a Date column type. – Sunil Chaphekar Jan 13 '20 at 10:26
  • 2
    You are using the terrible date-time classes that were years ago supplanted by the *java.time* classes defined in JSR 310. For a date-only value without time-of-day and without time zone, use `LocalDate`. – Basil Bourque Jan 13 '20 at 16:55
  • 1
    I recommend you don’t use `SimpleDateFormat` and `Date`. 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](https://docs.oracle.com/javase/tutorial/datetime/). If your datatype in MySQL is `timestamp` (which in MySQL defines a point int time) you should be able to retrieve it into an `OffsetDateTime` or `Instant`. See [my answer here](https://stackoverflow.com/a/54907501/5772882). – Ole V.V. Jan 14 '20 at 03:26

1 Answers1

1

Please check the TempTable entity class and db table structure. If you have anything miss match related to it then you have to map it accordingly. If there is anything the things will not work.

E.g.- java.util.Date column type must be match with Date type column in database.

Atul
  • 3,043
  • 27
  • 39
  • 2
    Actually, the `java.util.Date` class represents a moment in UTC, so it matches the standard SQL type of `TIMESTAMP WITH TIME ZONE`. – Basil Bourque Jan 13 '20 at 16:52