Hibernate And Oracle User Defined Types

I came across a situation recently where i had to use hibernate to read an Oracle defined object type which was used as a column type in the database. A friend of mine shared a useful link which explained how to do this using hibernate 2. But as we were using hibernate 3 I had to do a few adjustments to get it working. Following I share the procedures you need to follow in order to get hibernate 3 working with Oracle objects.
First if you look at the Oracle object it self, it will look like something as given below;

TYPE audit_trail as object

(

UPDATED_BY VARCHAR2(30),

UPDATED_ON DATE,

DML_ACTION VARCHAR2(10)

)

Now to map this to a hibernate object first you need to create a DTO type class to hold the variables defined in the Oracle object. For this example i create a class called AuditTrail which represents the Oracle object.

public class AuditTrail implements Serializable{

private String updatedBy;

private Date updatedOn;

private String dmlAction;

public AuditTrail(){

}

/**

* @param updatedBy the updatedBy to set

*/

public void setUpdatedBy(String updatedBy) {

this.updatedBy = updatedBy;

}

/**

* @return the updatedBy

*/

public String getUpdatedBy() {

return updatedBy;

}

/**

* @param updatedOn the updatedOn to set

*/

public void setUpdatedOn(Date updatedOn) {

this.updatedOn = updatedOn;

}

/**

* @return the updatedOn

*/

public Date getUpdatedOn() {

return updatedOn;

}

/**

* @param dmlAction the dmlAction to set

*/

public void setDmlAction(String dmlAction) {

this.dmlAction = dmlAction;

}

/**

* @return the dmlAction

*/

public String getDmlAction() {

return dmlAction;

}

}

Then moving on you need to tell hibernate how to map the following class to the Oracle user defined type. We do this by implementing the interafce UserType which is provided by Hibernate.

package com.test;

public class AuditTrailUserType implements UserType {

private static final int SQL_TYPE = Types.STRUCT;

private static final String DB_OBJECT_TYPE = "AUDIT_TRAIL";

public int[] sqlTypes() {

return new int[] { SQL_TYPE };

}

public Class returnedClass() {

return AuditTrail.class;

}

public boolean equals(Object o1, Object o2) throws HibernateException {

if (o1 == o2) {

return true;

}

if (o1 == null || o2 == null) {

return false;

}

return true;

}

private boolean equals(final String str1, final String str2) {

return true;

}

private boolean equals(final Date date1, final Date date2) {

if (date1 == date2) {

return true;

}

if (date1 != null && date2 != null) {

return date1.equals(date2);

}

return false;

}

public Object nullSafeGet(ResultSet resultSet, String[] names, Object owner)

throws HibernateException, SQLException {

//assert names.length == 1;

final Struct struct = (Struct) resultSet.getObject(names[0]);

if (resultSet.wasNull()) {

return null;

}

final AuditTrail user = new AuditTrail();

user.setUpdatedBy((String) struct.getAttributes()[0]);

user.setUpdatedOn((Date) struct.getAttributes()[1]);

user.setDmlAction((String) struct.getAttributes()[2]);

return user;

}

public void nullSafeSet(PreparedStatement statement, Object value, int index)

throws HibernateException, SQLException {

if (value == null) {

statement.setNull(index, SQL_TYPE, DB_OBJECT_TYPE);

} else {

final AuditTrail user = (AuditTrail) value;

final Object[] values = new Object[] { user.getUpdatedOn(),

convertDate(user.getUpdatedOn()), user.getDmlAction()};

final Connection connection = statement.getConnection();

final STRUCT struct = new STRUCT(StructDescriptor.createDescriptor(DB_OBJECT_TYPE,

connection), connection, values);

statement.setObject(index, struct, SQL_TYPE);

}

}

public java.sql.Date convertDate(Date date) {

return date == null ? null : new java.sql.Date(date.getTime());

}

public Object deepCopy(Object value) throws HibernateException {

if (value == null) {

return null;

}

final AuditTrail user = (AuditTrail) value;

final AuditTrail clone = new AuditTrail();

clone.setUpdatedBy(user.getUpdatedBy());

clone.setUpdatedOn(user.getUpdatedOn());

clone.setDmlAction(user.getDmlAction());

return clone;

}

public boolean isMutable() {

return true;

}

@Override

public Object assemble(Serializable arg0, Object arg1) throws HibernateException {

return null;

}

@Override

public Serializable disassemble(Object arg0) throws HibernateException {

return null;

}

@Override

public int hashCode(Object arg0) throws HibernateException {

return 0;

}

@Override

public Object replace(Object arg0, Object arg1, Object arg2) throws HibernateException {

return null;

}

}

Then you need to define in your entity class how to map this class. You do this by using the columnDefinition tag in the @Column annotation. Following shows how you should map the Oracle user Defined type in your entity class.

@Column(name="AUDIT_TRAIL_DTL",columnDefinition="AUDIT_TRAIL")

@org.hibernate.annotations.Type(type="com.test.AuditTrailUserType")

private AuditTrail auditTrail;

Well thats about it. You can seamlessly integrate Oracle object handling with hibernate by following the few simple steps described above.