Attribute Converter not successfully called with Timestamp type #2101

oramattkosem · 2024-03-18T21:58:41Z

Describe the bug
It appears to be impossible to work with java.time.Instant fields on Oracle TIMESTAMP(6) WITH TIME ZONE fields. The following occurs when the converter is called because a nonstandard object is read from the driver.

jakarta.persistence.PersistenceException: An exception occurred while calling convertToEntityAttribute on converter class test.TestObjectConverter with value oracle.sql.TIMESTAMPTZ@7d0d91a1
	at org.eclipse.persistence.mappings.converters.ConverterClass.convertDataValueToObjectValue(ConverterClass.java:166)
	at org.eclipse.persistence.mappings.foundation.AbstractDirectMapping.getObjectValue(AbstractDirectMapping.java:626)
	at org.eclipse.persistence.mappings.foundation.AbstractDirectMapping.valueFromRow(AbstractDirectMapping.java:1244)
	at org.eclipse.persistence.mappings.foundation.AbstractDirectMapping.buildCloneFromRow(AbstractDirectMapping.java:211)
	at org.eclipse.persistence.internal.descriptors.ObjectBuilder.buildAttributesIntoWorkingCopyClone(ObjectBuilder.java:2111)
	at org.eclipse.persistence.internal.descriptors.ObjectBuilder.buildWorkingCopyCloneFromRow(ObjectBuilder.java:2364)
	at org.eclipse.persistence.internal.descriptors.ObjectBuilder.buildObjectInUnitOfWork(ObjectBuilder.java:958)
	at org.eclipse.persistence.internal.descriptors.ObjectBuilder.buildObjectInternal(ObjectBuilder.java:844)
	at org.eclipse.persistence.internal.descriptors.ObjectBuilder.buildObject(ObjectBuilder.java:799)
	at org.eclipse.persistence.internal.descriptors.ObjectBuilder.buildObject(ObjectBuilder.java:777)
	at org.eclipse.persistence.queries.ObjectLevelReadQuery.buildObject(ObjectLevelReadQuery.java:863)
	at org.eclipse.persistence.queries.ReadObjectQuery.registerResultInUnitOfWork(ReadObjectQuery.java:896)
	at org.eclipse.persistence.queries.ReadObjectQuery.executeObjectLevelReadQuery(ReadObjectQuery.java:570)
	at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeDatabaseQuery(ObjectLevelReadQuery.java:1236)
	at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:913)
	at org.eclipse.persistence.queries.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:1195)
	at org.eclipse.persistence.queries.ReadObjectQuery.execute(ReadObjectQuery.java:448)
	at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:1283)
	at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:3025)
	at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1841)
	at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1823)
	at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1773)
	at org.eclipse.persistence.internal.jpa.EntityManagerImpl.executeQuery(EntityManagerImpl.java:999)
	at org.eclipse.persistence.internal.jpa.EntityManagerImpl.refresh(EntityManagerImpl.java:1138)
	at org.eclipse.persistence.internal.jpa.EntityManagerImpl.refresh(EntityManagerImpl.java:1029)
	at test.ConverterTest.testConverter(ConverterTest.java:24)
	at java.base/java.lang.reflect.Method.invoke(Method.java:580)
	at java.base/java.util.ArrayList.forEach(ArrayList.java:1596)
	at java.base/java.util.ArrayList.forEach(ArrayList.java:1596)
Caused by: java.lang.ClassCastException: class oracle.sql.TIMESTAMPTZ cannot be cast to class java.sql.Timestamp (oracle.sql.TIMESTAMPTZ is in unnamed module of loader 'app'; java.sql.Timestamp is in module java.sql of loader 'platform')
	at test.TestObjectConverter.convertToEntityAttribute(TestObjectConverter.java:1)
	at org.eclipse.persistence.mappings.converters.ConverterClass.convertDataValueToObjectValue(ConverterClass.java:164)
	... 28 more

To Reproduce
Create a persistence unit like this:

<?xml version="1.0" encoding="UTF-8"?>
<!-- Persistence deployment descriptor for dev profile -->
<persistence xmlns="https://jakarta.ee/xml/ns/persistence" 
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="https://jakarta.ee/xml/ns/persistence https://jakarta.ee/xml/ns/persistence/persistence_3_0.xsd" version="3.0">
  <persistence-unit name="test" transaction-type="RESOURCE_LOCAL">
    <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider> 
    <exclude-unlisted-classes>false</exclude-unlisted-classes>
    <properties>
      <property name="eclipselink.logging.level" value="FINE"/>
      <property name="eclipselink.logging.level.sql" value="FINE"/>
      <property name="eclipselink.logging.parameters" value="true"/>
      <property name="eclipselink.ddl-generation" value="drop-and-create-tables" />
      <property name="eclipselink.id-validation" value="NEGATIVE" />
      <property name="eclipselink.cache.shared.default" value="false"/>
      
      <property name="jakarta.persistence.jdbc.driver" value="oracle.jdbc.OracleDriver" />
      <property name="jakarta.persistence.jdbc.url" value="jdbc:oracle:thin:@localhost:1521/my_pdb" />
      <property name="jakarta.persistence.jdbc.user" value="user" />
      <property name="jakarta.persistence.jdbc.password" value="password" />
    </properties>
  </persistence-unit>
</persistence>

And an entity like this:

package test;

import java.time.Instant;

import jakarta.persistence.*;

@Entity
public class TestingEntity {
  private long id;
  private Instant timestamp;

  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  public long getId() {
    return id;
  }

  @Column(name = "timestamp", columnDefinition = "TIMESTAMP(6) WITH TIME ZONE")
  @Convert(converter = TestObjectConverter.class)
  public Instant getTimestamp() {
    return timestamp;
  }

  public void setId(long argId) {
    id = argId;
  }

  public void setTimestamp(Instant argStamp) {
    timestamp = argStamp;
  }
}

And a converter like this:

package test;

import java.sql.Timestamp;
import java.time.Instant;

import jakarta.persistence.AttributeConverter;
import jakarta.persistence.Converter;

@Converter
public class TestObjectConverter
    implements AttributeConverter<Instant, Timestamp> {
  @Override
  public Timestamp convertToDatabaseColumn(Instant argAttribute) {
    return argAttribute == null ? null : Timestamp.from(argAttribute);
  }

  @Override
  public Instant convertToEntityAttribute(Timestamp argDbData) {
    return argDbData == null ? null : argDbData.toInstant();
  }
}

And attempt to retrieve it from the database:

// Copyright (c) 1998, 2022, Oracle and/or its affiliates. All rights reserved.
// $Id$
package test;

import java.time.Instant;

import org.junit.jupiter.api.Test;

import jakarta.persistence.*;

public class ConverterTest {
  @Test
  public void testConverter() {
    try (EntityManagerFactory emf = Persistence.createEntityManagerFactory("test")) {

      long id;
      try (EntityManager em = emf.createEntityManager()) {
        EntityTransaction tx = em.getTransaction();
        tx.begin();
        TestingEntity entity = new TestingEntity();
        entity.setTimestamp(Instant.now());
        em.persist(entity);
        em.flush();
        em.refresh(entity);
        tx.commit();
        id = entity.getId();
      }

      try (EntityManager em = emf.createEntityManager()) {
        // this fails
        em.find(TestingEntity.class, id);

        // this also fails
        em.createQuery("FROM TestingEntity t where t.id = :id", TestingEntity.class).setParameter("id", id)
            .getResultStream().forEach(e -> System.out.println(e.getTimestamp()));
      }
    }
  }
}

EclipseLink version 4.0.2
Java/JDK version 21
Entity source (mainly applied annotations) - See above

For problem in persistence (JPA)
- JPA context like persistence.xml settings or related system properties (in case of JPA) - See above
- Database provider/version Oracle DB 19c
- JDBC driver provider/version (it should be useful if bug is related with some "specific" datatype e.g. JSON) Oracle JDBC 19.22

Expected behavior
The attribute converter should be supplied with a reasonable JDBC object type that corresponds with the converter's database value type, like java.sql.Timestamp.

Additional context
The same occurs when working with java.time datatypes. This happens when the records are read from the database, and I'm not seeing an apparent way to work around it.

The text was updated successfully, but these errors were encountered:

rfelcman · 2024-03-19T09:41:42Z

Sorry but in this case for TIMESTAMP(6) WITH TIME ZONE is JDBC driver return type oracle.sql.TIMESTAMPTZ not java.sql.Timestamp so correct converter is:

public class TestObjectConverter
            implements AttributeConverter<java.time.Instant, oracle.sql.TIMESTAMPTZ> {
        @Override
        public TIMESTAMPTZ convertToDatabaseColumn(Instant argAttribute) {
            try {
//            return argAttribute == null ? null : Timestamp.from(argAttribute);
//Oracle JDBC 23.3.0.23.09 API
                return argAttribute == null ? null : TIMESTAMPTZ.of(ZonedDateTime.ofInstant(argAttribute, ZoneId.of("UTC")));
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }

        }

        @Override
        public Instant convertToEntityAttribute(TIMESTAMPTZ argDbData) {
            try {
//            return argDbData == null ? null : argDbData.toInstant();
//Oracle JDBC 23.3.0.23.09 API
                return argDbData == null ? null : argDbData.toZonedDateTime().toInstant();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
}

In case of java.time.Instant You will loose info about time zone and (not sure for 100%) maybe (nano)seconds precision.

Correct converter (test case) is attached. There two pure JDBC tests com.oracle.jpa.bugtest.TestBug#bugTestPureJDBCInsert com.oracle.jpa.bugtest.TestBug#bugTestPureJDBCSelect (check there which datatype is returned by rSet.getObject(2).
jpa-bug-2101-AttributeConverterOracleTimestamp.tar.gz

Oracle JDBC driver 19.x has JDK and API TIMESTAMPTZ limits. I'd like recommend use latest one 23.3.0.23.09 .

oramattkosem · 2024-03-19T13:34:08Z

Thanks for the tip. I'll give the 23c JDBC driver a try.

We can't switch to the TIMESTAMPTZ object because it's not portable across DBMSs. We'd be locked to Oracle JDBC so our unit tests on HSQLDB wouldn't work (or any other use case on any other DBMS). Our prior JPA provider considers the JDBC object types used in converters when reading from ResultSets, and calls the appropriate types getters, which helps in cases like this since it reads those types transparently.

The JPA specification suggests that conversions should be supported for all basic types [1], so it seems like a case where the converter accepts Timestamp (or even OffsetDatetime, which fails similarly if used instead of Timestamp) should work.

oramattkosem · 2024-03-19T14:03:46Z

Upgrading to 23.3.0.23.09 doesn't seem to help this use case. The converter is still fed with a TIMESTAMPTZ object that doesn't implement Timestamp or OffsetDateTime.

rfelcman · 2024-03-19T14:08:04Z

Upgrading to 23.3.0.23.09 doesn't seem to help this use case. The converter is still fed with a TIMESTAMPTZ object that doesn't implement Timestamp or OffsetDateTime.

It was mentioned for provided test case due this: TIMESTAMPTZ.of(ZonedDateTime.ofInstant(argAttribute, ZoneId.of("UTC")).

oramattkosem · 2024-03-19T15:27:25Z

I appreciate the clarification. Unfortunately, I don't see a portable way to do this apart from customizing the platform. The converter is meant to take a JDBC-supported type, like java.sql.Timestamp or java.time.OffsetDatetime, but the trip through org.eclipse.persistence.platform.database.OraclePlatform.getObjectFromResultSet(ResultSet, int, int, AbstractSession) doesn't account for that like it does for the older temporal types.

lukasj · 2024-03-19T15:37:45Z

Try to use org.eclipse.persistence.platform.database.oracle.Oracle23Platform instead

oramattkosem · 2024-03-19T15:41:53Z

That's what it does automatically. There's nothing special in there in 4.0: https://github.com/eclipse-ee4j/eclipselink/blob/4.0.2/foundation/org.eclipse.persistence.core/src/main/java/org/eclipse/persistence/platform/database/Oracle23Platform.java

It looks like #1969 made some changes here in master, but it doesn't look like they'd help offset types - only local ones.

lukasj · 2024-03-19T15:48:10Z

I did not write org.eclipse.persistence.platform.database.Oracle23Platform, I wrote org.eclipse.persistence.platform.database.oracle.Oracle23Platform

oramattkosem · 2024-03-19T17:26:20Z

I see. I didn't even have that jar on the classpath, and don't see that platform mentioned in the main documentation, but that does seem to help with this scenario.

Is this the one we're meant to use? Why are there 2? What are the differences between the one in the core jar and the one in the Oracle jar?

oramattkosem · 2024-03-21T12:44:07Z

Closing this based on the solution provided, It would be helpful for those working toward uptake of this library if there wasn't a need for an additional jar, and a code path that isn't as robust for lack of it.

oramattkosem closed this as completed Mar 21, 2024

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Attribute Converter not successfully called with Timestamp type #2101

Attribute Converter not successfully called with Timestamp type #2101

oramattkosem commented Mar 18, 2024

rfelcman commented Mar 19, 2024 •

edited

Loading

oramattkosem commented Mar 19, 2024 •

edited

Loading

oramattkosem commented Mar 19, 2024

rfelcman commented Mar 19, 2024

oramattkosem commented Mar 19, 2024 •

edited

Loading

lukasj commented Mar 19, 2024

oramattkosem commented Mar 19, 2024

lukasj commented Mar 19, 2024

oramattkosem commented Mar 19, 2024

oramattkosem commented Mar 21, 2024

Attribute Converter not successfully called with Timestamp type #2101

Attribute Converter not successfully called with Timestamp type #2101

Comments

oramattkosem commented Mar 18, 2024

rfelcman commented Mar 19, 2024 • edited Loading

oramattkosem commented Mar 19, 2024 • edited Loading

oramattkosem commented Mar 19, 2024

rfelcman commented Mar 19, 2024

oramattkosem commented Mar 19, 2024 • edited Loading

lukasj commented Mar 19, 2024

oramattkosem commented Mar 19, 2024

lukasj commented Mar 19, 2024

oramattkosem commented Mar 19, 2024

oramattkosem commented Mar 21, 2024

rfelcman commented Mar 19, 2024 •

edited

Loading

oramattkosem commented Mar 19, 2024 •

edited

Loading

oramattkosem commented Mar 19, 2024 •

edited

Loading