support two table versions with one set of code?

softweave · **Joined:** Sat May 06, 2006 9:08 am **Posts:** 8

Hibernate version: 3.0.5
Name and version of the database you are using: Oracle 9+

We would like to support two versions of the same table with the same hibernate mapping files and Java classes. The old version of the table defines a column as numeric that is a varchar in the new version. The corresponding Java class will have a String property for this column and will only be used for read only access.

Can this be handled by simply providing two setters - one taking a BigDecimal, the other a String? If not, would a custom type be able to handle the column type change?

Thanks in advance,
Jane Eisenstein

LaLiLuna · **Posted:** Sat May 06, 2006 6:38 pm

Hallo,

you could try a custom type.

Regards Sebastian

softweave · **Joined:** Sat May 06, 2006 9:08 am **Posts:** 8

I implemented a custom type

Code:

 * StringOrBigDecimalUserType supports access to String properties whose column
 * values may be numeric or varchar. Varchar values are expected to contain
 * non-numeric characters.

(see bottom of message for complete class code)

It works fine when running a select ("from com.med.resourcehelpers.pcsdata.EdfRule")
against hibernate mappings for some classes:

Code:

 
<hibernate-mapping>
<class name="com.med.resourcehelpers.pcsdata.EdfRule" table="EDF_RULE">
    <composite-id>
        <key-property name="sourceId" column="SOURCE_ID" type="long"/>
        <key-property name="ruleId" column="RULE_ID"  
                type="com.med.resourcehelpers.custom.StringOrBigDecimalUserType"/>
        <key-property name="edfDescription" column="EDF_DESCRIPTION" type="string" length="240"/>
        <key-property name="cmdmType" column="CMDM_TYPE" type="long"/>
...

And bombs when running a select ("from com.med.resourcehelpers.pcsdata.Condition")
against hibernate mappings for other classes:

Code:

<hibernate-mapping>
<class name="com.med.resourcehelpers.pcsdata.Condition" table="CONDITION"
    <composite-id>
        <key-property name="condCode" column="COND_CODE" 
                type="com.med.resourcehelpers.custom.StringOrBigDecimalUserType"/>
        <key-property name="description" column="DESCRIPTION" type="string" length="60"/>
 ...

The target database stores the rule_id and cond_code columns as NUMBER with length 22 and IS_NULLABLE=NO.

The versions of the pojo classes I am using have String properties for these columns and also have both default and full constructors.

Running this test code:

Code:

   Query query;
   List list;
 
   query = session.createQuery("from com.med.resourcehelpers.pcsdata.EdfRule");
   list = query.list();
   System.out.println("retrieved EdfRule list with size=" + list.size());
   EdfRule rule;
 
   query = session.createQuery("from com.med.resourcehelpers.pcsdata.Condition");
   list = query.list();
   System.out.println("retrieved Condition list with size=" + list.size());
   Condition condition;

gives a console trace like this:

Code:

 
retrieved EdfRule list with size=69
 
org.hibernate.exception.GenericJDBCException: could not execute query
 at org.hibernate.exception.ErrorCodeConverter.handledNonSpecificException(ErrorCodeConverter.java:92)
 at org.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:80)
 at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
 at org.hibernate.loader.Loader.doList(Loader.java:1596)
 at org.hibernate.loader.Loader.list(Loader.java:1577)
 at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:395)
 at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:271)
 at org.hibernate.impl.SessionImpl.list(SessionImpl.java:844)
 at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)
 at com.med.resourcehelpers.custom.StringOrBigDecimalUserTypeTest.testRetrieveNumericValues(StringOrBigDecimalUserTypeTest.java:79)
 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:85)
 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:58)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:60)
 at java.lang.reflect.Method.invoke(Method.java:391)
 at junit.framework.TestCase.runTest(TestCase.java:154)
 at junit.framework.TestCase.runBare(TestCase.java:127)
 at junit.framework.TestResult$1.protect(TestResult.java:106)
 at junit.framework.TestResult.runProtected(TestResult.java:124)
 at junit.framework.TestResult.run(TestResult.java:109)
 at junit.framework.TestCase.run(TestCase.java:118)
 at junit.framework.TestSuite.runTest(TestSuite.java:208)
 at junit.framework.TestSuite.run(TestSuite.java:203)
 at junit.textui.TestRunner.doRun(TestRunner.java:116)
 at junit.textui.TestRunner.doRun(TestRunner.java:109)
 at junit.textui.TestRunner.run(TestRunner.java:72)
 at junit.textui.TestRunner.run(TestRunner.java:57)
 at com.med.resourcehelpers.custom.StringOrBigDecimalUserTypeTest.main(StringOrBigDecimalUserTypeTest.java:35)
Caused by: java.sql.SQLException: No data read
 at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
 at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
 at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:269)
 at oracle.jdbc.driver.OracleStatement.wasNullValue(OracleStatement.java:3174)
 at oracle.jdbc.driver.OracleResultSetImpl.wasNull(OracleResultSetImpl.java:127)
 at com.med.resourcehelpers.custom.StringOrBigDecimalUserType.nullSafeGet(StringOrBigDecimalUserType.java:71)
 at org.hibernate.type.CustomType.nullSafeGet(CustomType.java:100)
 at org.hibernate.type.AbstractType.hydrate(AbstractType.java:80)
 at org.hibernate.type.ComponentType.hydrate(ComponentType.java:423)
 at org.hibernate.type.ComponentType.nullSafeGet(ComponentType.java:182)
 at org.hibernate.loader.Loader.getKeyFromResultSet(Loader.java:759)
 at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:292)
 at org.hibernate.loader.Loader.doQuery(Loader.java:412)
 at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:218)
 at org.hibernate.loader.Loader.doList(Loader.java:1593)
 ... 24 more

Any ideas why this conversion works for one table and not another?

============================================

Code:

package com.med.resourcehelpers.custom;

import java.io.Serializable;
import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

import org.hibernate.HibernateException;
import org.hibernate.usertype.UserType;

/**
 * StringOrBigDecimalUserType supports access to String properties whose column
 * values may be numeric or varchar. Varchar values are expected to contain
 * non-numeric characters.
 * 
 * @author Jane Eisenstein
 */
public class StringOrBigDecimalUserType implements UserType, Serializable {
   private static final int[] SQL_TYPES = { Types.VARCHAR };

   /**
    * Return the SQL type codes for the columns mapped by this type. Tells
    * Hibernate what SQL column types to use for DDL schema generation.
    * 
    * @see org.hibernate.usertype.UserType#sqlTypes()
    */
   public int[] sqlTypes() {
      return SQL_TYPES;
   }

   /**
    * The class returned by nullSafeGet().
    * 
    * @see org.hibernate.usertype.UserType#returnedClass()
    */
   public Class returnedClass() {
      return java.lang.String.class;
   }

   /**
    * @see org.hibernate.usertype.UserType#equals(java.lang.Object,
    *      java.lang.Object)
    */
   public boolean equals(Object x, Object y) throws HibernateException {
      return (x == y) || !(x == null || y == null) || x.equals(y);
   }

   /**
    * @see org.hibernate.usertype.UserType#hashCode(java.lang.Object)
    */
   public int hashCode(Object x) throws HibernateException {
      return x.hashCode();
   }

   /**
    * Retrieve an instance of the mapped class from a JDBC resultset.
    * 
    * @param rs -
    *            a JDBC result set
    * @param names -
    *            the column names
    * @param owner -
    *            the containing entity
    * @return Object
    */
   public Object nullSafeGet(ResultSet rs, String[] names, Object owner)
         throws HibernateException, SQLException {
      String value = null;
      if (!rs.wasNull()) {
         try {
            BigDecimal bd = rs.getBigDecimal(names[0]);
            if (bd != null)
               value = bd.toString();
         } catch (SQLException sqle) {
            value = rs.getString(names[0]);
         }
      }
      return value;
   }

   /**
    * Write an instance of the mapped class to a prepared statement.
    * 
    * @param st -
    *            a JDBC prepared statement
    * @param value -
    *            the object to write
    * @param index -
    *            statement parameter index
    */
   public void nullSafeSet(PreparedStatement st, Object value, int index)
         throws HibernateException, SQLException {
      if (value == null) {
         st.setNull(index, Types.VARCHAR); // will fail with older schemas
      } else {
         try {
            BigDecimal bd = new BigDecimal(value.toString());
            st.setBigDecimal(index, bd);
         } catch (java.lang.NumberFormatException nfe) {
            st.setString(index, value.toString());
         }
      }
   }

   /**
    * Return a deep copy of the persistent state, stopping at entities and at
    * collections.
    * 
    * @see org.hibernate.usertype.UserType#deepCopy(java.lang.Object)
    */
   public Object deepCopy(Object value) throws HibernateException {
      return value;
   }

   /**
    * Are objects of this type mutable?
    * 
    * @see org.hibernate.usertype.UserType#isMutable()
    */
   public boolean isMutable() {
      return false;
   }

   /**
    * Transform the object into its cacheable representation.
    * 
    * @see org.hibernate.usertype.UserType#disassemble(java.lang.Object)
    */
   public Serializable disassemble(Object value) throws HibernateException {
      return (Serializable) value;
   }

   /**
    * Reconstruct an object from the cacheable representation
    * 
    * @see org.hibernate.usertype.UserType#assemble(java.io.Serializable,
    *      java.lang.Object)
    */
   public Object assemble(Serializable value, Object owner)
         throws HibernateException {
      return (Serializable) value;
   }

   /**
    * During merge, replace the existing (target) value in the entity we are
    * merging to with a new (original) value from the detached entity we are
    * merging.
    * 
    * @see org.hibernate.usertype.UserType#replace(java.lang.Object,
    *      java.lang.Object, java.lang.Object)
    */
   public Object replace(Object original, Object target, Object owner)
         throws HibernateException {
      return original;
   }
}