Let me preface this by saying I am a HUGE Hibernate fan. You guys are doing an awesome job (and I am reaping the benefit). I am using Hibernate 3.1.2 accessing a Sybase database.

I write financial applications and have a lot of number fields that represent Money. If I just use a BigDecimal mapping I have to set the scale on every data value or else I will get a NumericOverflow exception. Rather than dealing with the precision issues all over my code I tried to build a UserType that will adapt the values when the move between the database and my application. However, I am having difficulty building that mapping. I have no doubt that I am making a bonehead move here but I cannot for the life of me see the problem.

I have recreated the whole problem in a very small test case that shows the problem. I have the following table:

CREATE TABLE Debug
(
id int NOT NULL,
non_cash_int numeric(8,3) NULL,
non_cash_prin numeric(8,3) NULL
)

The POJO is:

public class Debug
{

private Integer _id;
private BigDecimal _name;
private BigDecimal _nonCashPrincipal;

<standard getters and setters>
}

The mapping is
<hibernate-mapping>
<class name="test.Debug" table="Debug">

<id name="id" type="int" column="id">
<generator class="increment"/>
</id>

<property name="nonCashInterest" type="test.MoneyDataType" column="non_cash_int"/>
<property name="nonCashPrincipal" type="test.MoneyDataType" column="non_cash_prin"/>
</class>
</hibernate-mapping>


The test.MoneyDataType is:

public class MoneyDataType implements UserType
{

private static final int[] SQL_TYPES = {Types.NUMERIC};

public int[] sqlTypes() { return SQL_TYPES; }

public Class returnedClass() { return BigDecimal.class; }

public boolean equals(Object x, Object y) throws HibernateException
{ ... }
public int hashCode(Object x) throws HibernateException
{ ... }

public Object nullSafeGet(ResultSet rs, String[] names, Object owner)
throws HibernateException, SQLException
{
Object answer = null;
LOGGER.debug("nullSafeGet(rs, " + names[0] + ", owner)");
if (!rs.wasNull())
{
LOGGER.debug(" NOT NULL");
BigDecimal d = rs.getBigDecimal(names[0]);
LOGGER.debug(" {" + (d==null?"NULL":d.toString()) + "}");
answer = d;
} else
{
LOGGER.debug(" NULL");
}
return answer;
}

public void nullSafeSet(PreparedStatement st, Object value, int index)
throws HibernateException, SQLException
{
if (value == null)
{
st.setNull(index, Types.NUMERIC);
} else
{
BigDecimal v = null;
try
{
v = ((BigDecimal) value).setScale(2, BigDecimal.ROUND_HALF_UP);
} catch (ClassCastException e)
{
try
{
v = new BigDecimal(((Double) value).doubleValue()).setScale(2, BigDecimal.ROUND_HALF_UP);
} catch (ClassCastException e2)
{
LogFactory.getLog(MoneyDataType.class).error("MoneyDataType->nullSafeSet {" + value.getClass().getName() + "} " + index);
throw e;
}
}
}
}

I wrote a junitTest that creates 4 new Debug objects and stores them. The resulting rows in the DB are:
1 24.54 56.42
2 null 53212.25
3 20808.34 null
4 null null


I wrote a junitTest that reads the rows from the DB and the resulting object has the following values:
1 24.54 56.42
2 null null
3 20808.34 null
4 null null

If I change my Mapping file back to use java.math.BigDecimal the values are loaded into the domain object correctly.

Those log statements in my MoneyDataType nullSafeGet method yield:
16:52:39:040|DEBUG|test.MoneyDataType|nullSafeGet(rs, non2_0_, owner)
16:52:39:040|DEBUG|test.MoneyDataType| NOT NULL
16:52:39:040|DEBUG|test.MoneyDataType| {24.540}
16:52:39:040|DEBUG|test.MoneyDataType|nullSafeGet(rs, non3_0_, owner)
16:52:39:040|DEBUG|test.MoneyDataType| NOT NULL
16:52:39:040|DEBUG|test.MoneyDataType| {56.420}
16:52:39:040|DEBUG|test.MoneyDataType|nullSafeGet(rs, non2_0_, owner)
16:52:39:071|DEBUG|test.MoneyDataType| NOT NULL
16:52:39:071|DEBUG|test.MoneyDataType| {NULL}
16:52:39:071|DEBUG|test.MoneyDataType|nullSafeGet(rs, non3_0_, owner)
16:52:39:071|DEBUG|test.MoneyDataType| NULL
16:52:39:071|DEBUG|test.MoneyDataType|nullSafeGet(rs, non2_0_, owner)
16:52:39:071|DEBUG|test.MoneyDataType| NOT NULL
16:52:39:071|DEBUG|test.MoneyDataType| {20808.340}
16:52:39:071|DEBUG|test.MoneyDataType|nullSafeGet(rs, non3_0_, owner)
16:52:39:071|DEBUG|test.MoneyDataType| NOT NULL
16:52:39:071|DEBUG|test.MoneyDataType| {NULL}
16:52:39:071|DEBUG|test.MoneyDataType|nullSafeGet(rs, non2_0_, owner)
16:52:39:071|DEBUG|test.MoneyDataType| NOT NULL
16:52:39:071|DEBUG|test.MoneyDataType| {NULL}
16:52:39:071|DEBUG|test.MoneyDataType|nullSafeGet(rs, non3_0_, owner)
16:52:39:071|DEBUG|test.MoneyDataType| NULL


If this is information overload please send me an email and I can mail you the jar file with this working example. Like I said - I am sure I am doing something wrong in the MoneyDataType class, but I just don't know what it is.


Any help would be appreciated.

Marc Luce
marc.luce@bankofamerica.com

You should change your nullSafeGet to this

public Object nullSafeGet(ResultSet rs, String[] names, Object owner)
throws HibernateException, SQLException {
Object answer = null;
LOGGER.debug("nullSafeGet(rs, " + names[0] + ", owner)");
BigDecimal d = rs.getBigDecimal(names[0]);
if (!rs.wasNull()) {
LOGGER.debug(" NOT NULL");
LOGGER.debug(" {" + (d == null ? "NULL" : d.toString()) + "}");
answer = d;
} else {
LOGGER.debug(" NULL");
}
return answer;
}


note that BigDecimal d = rs.getBigDecimal(names[0]); has migrated before the was null test.

Your problem caused by the way JDBC works, previous retrieval of value returned null and since that JDBD returns was null, that refers to the last invocation of resultSet.getSomething()

Duh! That is exactly it. I knew I was doing something wrong. Thanks Konstantin.