Tuesday, August 18, 2009

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.

12 comments:

  1. Nice and clear explanation. Does this one supports when the user type is multi set?

    ReplyDelete
  2. Nice post. Very helpful. Is this mapping applies too if the user type is multi set?

    ReplyDelete
  3. Hi Billy,

    Thank you for the appreciation. Unfortunately i am not aware of how this will work with a multi set as i have not tried. If you do try it can you kindly let me know if it worked this way? would appreciate it.

    Regards,

    Dinuka

    ReplyDelete
  4. Hi,
    Very useful about your example.Could you provide the detail about how setup and use AuditTrail with hibernate annotation.The 4th part code is not clearly for me.Thanks.

    ReplyDelete
  5. Hi,


    In the column definition using @Column, you need to give the name of the column in your table representing the oracle defined type. in my instance the column was named "AUDIT_TRAIL_DTL", hence i have used the same name in the @Column(name). The real binding happens when we tell hibernate what type of column it is. That is through the Hibernate't type annotation, to which we pass our Hibernate defined type (Code snippet 3).

    Hope that cleared your doubts. If you do have any more queries, pls do not hesitate to leave a comment and ill get back to you as soon as possible.

    Cheers

    ReplyDelete
  6. Hi Dinuka,
    Thank you for helpful post.

    One thing I need to get clarified. While retrieving data, I need to check a condition with Object type's attribute. For example, I have a object type called modifier_type, which includes status,effective_start_date, and effective_end_date.
    We used this type in on table user. I need to write hsql which looks similar to following sql. How can I write this? Please help.

    select user_id,user_name,modifier from user u where u.modifier.status='A'

    ReplyDelete
  7. Hi,

    Thx for the comment. apologies for the late reply. did not have time to check the blog y.day. Well in the last code snippet you can see i have set a column definition. You can use the same on your entity class and then use the normal named query to retrieve the data.

    Pls mail me on dinuka.arseculeratne@gmail.com if you need any help and i would be glad to help you out.

    Cheers

    Dinuka

    ReplyDelete
  8. Is the object 'connection' of type java.sql.Connection? Which ojdbc driver do you use? I tried it with ojdbc6.jar and the line

    final STRUCT struct = new STRUCT(StructDescriptor.createDescriptor(DB_OBJECT_TYPE,
    connection), connection, values);

    didn't work for me. It threw a ClassCastException, that $Proxy13 could not be cast to OracleConnection.

    ReplyDelete
  9. Nice explaination..it is working for me..!!

    Thanks,
    Nuresh.B.

    ReplyDelete
  10. Many Thanks to Dinuka..you saved my day ..

    ReplyDelete
  11. Thank you for the kind comment Devi. And i am glad i was able to help you out.

    ReplyDelete