How to map Oracle Object type in an entity using Spring Data JPA?

Question

This question is particularly related to mapping Oracle object type in an entity using Spring Data JPA. The other question did not help explain the problem in detail.

So here below are details:

I could not find information related to mapping Oracle object type in entity objects with Spring Data JPA neither in Spring Data JPA Reference nor in any other search results.

Scenario:

Let's say I have the following date_typ Oracle type.

CREATE TYPE date_typ AS OBJECT (
  month         VARCHAR2(2),
  day           VARCHAR2(2),
  year          VARCHAR2(4),
  extension     VARCHAR2(10),
  MEMBER FUNCTION getFullDate RETURN VARCHAR2
);

CREATE TYPE BODY date_typ AS
  MAP MEMBER FUNCTION getFullDate RETURN VARCHAR2 AS
  BEGIN
    return month || day || year;
 END getFullDate;
END;

Now in the oracle database table, say REGISTRATION, one of the columns is of the above date_typ

Column      Type
REG_ID      VARCHAR2(25)  primary key
FIRSTNAME   VARCHAR2(30)  not null
LASTNAME    VARCHAR2(30)  not null
MIDDLEINIT  VARCHAR2(10) 
REQUESTEDDATE DATE_TYP
...
...

Now, I need to map the above columns in the entity

@Entity
@Table(name = "REGISTRATION", schema = "accounts")
public class RegistrationEntity {
    @Id
    @Column(name = "REG_ID")
    private String registrationId;

    @Column(name = "FIRSTNAME", nullable = false)
    private String firstName;

    @Column(name = "LASTNAME", nullable = false)
    private String lastName;

    @Column(name = "MIDDLEINIT")
    private String middleInitial;

    requestedDate ???
}

How do I map the requestedDate to the REQUESTEDDATE column of the DATE_TYP (oracle object type) in Spring JPA?

Notes: am using Hibernate as the persistence provider.

Answer 1

This answer states, that there is no official support in JPA for Oracles Object Types, but support for it in Eclipse Link. I guess that means there is no out of the box support in Hibernate for this.

But this blog post describes how to implement a UserType to map a Java class to an Oracle Object type.

Spring Data is not really involved in this because it just uses the JPA implementation to map enities to the database. Conversions and projections offered by Spring Data only convert input parameters/output values before passing them to/ after getting them from the JPA provider.

Answer 2

Here's my implementation with an updated version from the link provided by Jens Schauder. The implementation described in the link uses the deprecated type STRUCT and wasn't working for me as is. This worked for me.

Custom Object type

create type CUSTOMER_ADDRESS as object
(
  STREET_ADDRESS   VARCHAR2(50),
  CITY             VARCHAR2(20)
)



import lombok.Data;

@Data
public class Address {
    String streetAddress;
    String city;
}

Custom type:

import org.hibernate.HibernateException;
import org.hibernate.engine.spi.SharedSessionContractImplementor;
import org.hibernate.usertype.UserType;

import java.io.Serializable;
import java.sql.*;

import static java.sql.Types.STRUCT;

/**
 * **
 * Usage in Entity
 *  @Column(name="TABLE_NAME", columnDefinition="CUSTOMER_ADDRESS")
 *  @Type(type= "com.example.demo.AddressType")
 *  private Address address;
 */


public class AddressType implements UserType {

    private static final int SQL_TYPE = STRUCT;
    private static final String DB_OBJECT_TYPE = "CUSTOMER_ADDRESS"; //DB custom type name

    @Override
    public int[] sqlTypes() {
        return new int[]{SQL_TYPE};
    }

    @Override
    public Class returnedClass() {
        return Address.class;
    }

    @Override
    public Address nullSafeGet(ResultSet rs, String[] names, SharedSessionContractImplementor session, Object owner) throws HibernateException, SQLException {
        final Struct struct = (Struct) rs.getObject(names[0]);

        if (rs.wasNull()) {
            return null;
        }

        final Address address = new Address();
        address.streetAddress = (String) struct.getAttributes()[0];
        address.city = (String) struct.getAttributes()[1];

        return address;
    }

    @Override
    public void nullSafeSet(PreparedStatement st, Object value, int index, SharedSessionContractImplementor session) throws HibernateException, SQLException {
        if (value != null) {
            final Address address = (Address) value;
            final Struct address =  st.getConnection().createStruct(DB_OBJECT_TYPE,new Object[]{
                    address.streetAddress,
                    address.city
            });
            st.setObject(index, address, SQL_TYPE);
        } else {
            st.setNull(index, SQL_TYPE, DB_OBJECT_TYPE);
        }
    }

    @Override
    public boolean equals(Object x, Object y) throws HibernateException {
        return ((x == y) || (x != null && y != null && x.equals(y)));
    }

    @Override
    public int hashCode(Object x) throws HibernateException {
        return x != null ? x.hashCode() : 0;
    }

    @Override
    public Object deepCopy(Object value) throws HibernateException {
        return value == null ? null : value;
    }

    @Override
    public boolean isMutable() {
        return false;
    }

    @Override
    public Serializable disassemble(Object value) throws HibernateException {
        Object deepCopy = deepCopy(value);
        if (!(deepCopy instanceof Serializable)) {
            return (Serializable) deepCopy;
        }
        return null;
    }

    @Override
    public Object assemble(Serializable cached, Object owner) throws HibernateException {
        return deepCopy(cached);
    }

    @Override
    public Object replace(Object original, Object target, Object owner) throws HibernateException {
        return deepCopy(original);
    }
}