Cannot set microsecond value from Java to the Oracle

Question

I have a date which created from current time and the object type is XMLGregorianCalendar. In Java, I can see that my date has microseconds but in the SQL, it is set as 0. What is the exact problem here?

    public static XMLGregorianCalendar initializeTime(LocalDateTime localDateTime) {
        try {
            XMLGregorianCalendar time= DatatypeFactory.newInstance()
              .newXMLGregorianCalendar(String.valueOf
              (LocalDateTime.now().truncatedTo(ChronoUnit.MICROS)));
            
        return time;
        } catch (DatatypeConfigurationException var3) {
            throw new RuntimeException("Execution time could not created.");
        }
    }

In Java it shows: 2023-05-24T15:50:07.931456

But in the SQL (the type is TIMESTAMP(6)) it shows like this : 24-MAY-23 15.50.07.931000000 PM

SQL calling, you can find it down below. dummytest is a procedure basically taking a param and inserting this param into a table.

<select id="add_time" statementType="CALLABLE" parameterType="java.util.Map">
        {call dummytest(
                #{time, mode=IN, jdbcType=TIMESTAMP}
            )}
    </select>

procedure code:

create or replace procedure dummytest(p_timestamp TIMESTAMP) is
begin
  insert into testtimestamp values (p_timestamp);
  commit;
end ;

I am losing the microsecond precision. I have to use XMLGregorianCalendar because all code designed for this API.

Answer 1

The problem is fixed by custom handler in java side.

<select id="add_time" statementType="CALLABLE" parameterType="java.util.Map">
        {call dummytest(
                #{time, mode=IN, jdbcType=TIMESTAMP, typeHandler = com.project.yunus.XMLGregorianCalendarWithFractionalTypeHandler}
            )}
    </select>

Content of XMLGregorianCalendarWithFractionalTypeHandler:

public class XMLGregorianCalendarWithFractionalTypeHandler implements TypeHandler {
    public XMLGregorianCalendarWithFractionalTypeHandler() {
    }

    public void setParameter(PreparedStatement paramPreparedStatement, int paramInt, Object paramObject, JdbcType paramJdbcType) throws SQLException {
        if (paramObject == null) {
            paramPreparedStatement.setNull(paramInt, 93);
        } else {
            if (!(paramObject instanceof XMLGregorianCalendar)) {
                throw new IllegalArgumentException("Excepted XMLGregorianCalendar, but have: " + paramObject);
            }

            XMLGregorianCalendar objectCalendar = ((XMLGregorianCalendar)paramObject);
            Timestamp timestamp = Timestamp.valueOf(objectCalendar.toString().replace("T", " "));
            paramPreparedStatement.setTimestamp(paramInt, timestamp);
        }

    }

    public Object getResult(ResultSet rs, String columnLabel) throws SQLException {
        Timestamp value = rs.getTimestamp(columnLabel);
        return rs.wasNull() ? null : stringToXMLGregorianCalendar(value);
    }

    public Object getResult(ResultSet rs, int columnIndex) throws SQLException {
        Timestamp value = rs.getTimestamp(columnIndex);
        return rs.wasNull() ? null : stringToXMLGregorianCalendar(value);
    }

    public Object getResult(CallableStatement rs, int columnNb) throws SQLException {
        Timestamp value = rs.getTimestamp(columnNb);
        return rs.wasNull() ? null : stringToXMLGregorianCalendar(value);
    }

    private static XMLGregorianCalendar stringToXMLGregorianCalendar(Timestamp value) {
        try {
            return dateToXMLGregorianCalendar(value);
        } catch (Exception var2) {
            return null;
        }
    }

    public static XMLGregorianCalendar dateToXMLGregorianCalendar(Timestamp timestamp) throws DatatypeConfigurationException {
        return  DatatypeFactory.newInstance().newXMLGregorianCalendar(timestamp.toString().replace(" ", "T"));

    }