Object with Long or Decimal got initial zero value while database field is null

• Key: DBUTILS-42
• Type: Improvement
• Status: Closed
• Resolution: Fixed
• Priority: Major
• Assignee: Dan Fabulich
• Reporter: Matt Jiang
• Affects Version/s: 1.1
• Fix Version/s: 1.2

Description

While I use dbutil1.1, I got a big different implementation betweeb 1.0 and 1.1.
Given a Java object, it has a property with Long data type; mapping to database, its table field datatype is bigint.
If it has a record and its value is null.

In 1.0 implementation, if I load entity, then we can see the property in Java object is also null.
But in 1.1 implementation, the Java object will got a Long object with 0 inside.

This behavior change does big impact if I upgrade from 1.0 to 1.1. It might make application logic fail because origional null status now become a Long(0) value to map to null value in database.

I suggest to change it back. If null value in database, then mapped Java object should be null as well, not new a Long(0) to be a initial value.

Below is the code snapshot I used to execute query, and I use jTDS 1.2 as JDBC driver

public List<E> executePreparedQuery(String sql, Object[] params, Class clazz) throws SQLException {
    
    Connection cnct = getConnection();
    QueryRunner qRunner = new QueryRunner();
    ResultSetHandler rsHandler = new BeanListHandler(clazz);
    List<E> entities = null;
    try {
    	convertDateIn(params);
    	entities = (List<E>) qRunner.query(cnct, sql, params, rsHandler);
    }
    catch (SQLException e) {
    	e.printStackTrace();
    	throw e;
    }
    finally {
    	closeConnection();
    }
    return entities;
  }

Hope this helps.

Comments

[Henri Yandell]

What part of the DbUtils API are you using?

[Matt Jiang]

Hi Henri

I update the content on JIRA, please check it out.
Thanks a lot.

Matt

[Rick Cromer]

I've run into the same issue. My beanClass that I pass to this method has fields that are Long types when database fields are NUMBER NULLABLE in Oracle 9i using ojdbc14.jar jdbc driver.

public static Object doBeanQuery(String sql, Class beanClass, DbConnectionPool dbConnectionPool) {
ResultSetHandler handler = new BeanHandler(beanClass);
QueryRunner run = new QueryRunner();
Connection conn = null;
Object obj = null;
try { conn = dbConnectionPool.getConnection(); obj = run.query(conn, sql, handler); } catch (SQLException e) { e.printStackTrace(); } finally { dbConnectionPool.free(conn); }
return obj;
}

Thanks
Rick

[Rick Cromer]

I've traced down what I believe is happening,

in class BeanProcessor

protected Object processColumn(ResultSet rs, int index, Class propType) { .... } else if (propType.equals(Long.TYPE) || propType.equals(Long.class)) {
return new Long(rs.getLong(index));
....

The rs.getLong(index) returns zero when the column is null. This is really returning a primative type long not Long, so it has to have a value and can't be null.

Per the Java Documentation

getLong

long getLong(int columnIndex)
throws SQLException

Retrieves the value of the designated column in the current row of this ResultSet object as a long in the Java programming language.

Parameters:
columnIndex - the first column is 1, the second is 2, ...
Returns:
the column value; if the value is SQL NULL, the value returned is 0 <<<<----
Throws:
SQLException - if a database access error occurs

Does anyone have any ideas on how it worked before?

[Julien Aymé]

By looking through the subversion repository, I found that in DbUtils 1.0 only rs.getObject(index) was used, which effectively returned <code>null</code> when the value is SQL NULL.

The problem is that some database, when using getObject, will return a BigInteger instead of a Long (for example), so I assume that this was the (a) reason for which the method getLong has been used instead of getObject.

A simple fix could be:

Object res = new Long(rs.getLong(index));
return rs.wasNull() ? null : res;

(And generalize the given code for all the different types in the method processColumn).

I will provide the corresponding patch this evening (cannot use svn:checkout at my current place, only have a web browser).

Julien

[Julien Aymé]

Attaching proposed patch.

[Brandon Atkinson]

I disagree the the classification of this as an 'Improvement' issue (sorry if this is just poor naming by JIRA, I'm not too familiar...).

The comments clearly state that null will be returned if the column value was null. This is a bug, either in comments or the code.

Looked at the patch that Julien submitted in January. The problem with the fix he proposes is that null is returned even if the bean property is a primitive type.
This could cause problems upon assignment , unless there is some smarts higher up that do some maneuvering.

The fix I propose is less code, and only returns null if the bean property is an Object type.

[Brandon Atkinson]

Fix to BeanProcessor which fixes processColumn.
Returns null if the bean property is an Object type, and the resultSet returns null.

[Julien Aymé]

First, I agree with Brandon, my patch would definitely return null even if the bean property is a primitive type, so my patch would break some code later.
I will submit a corrected version of mine, which will not return null when the bean property is a primitive type.

Then, I looked at Brandon patch:
it fixes this issue, it is simpler/smaller than mine, so the choice is up to the commiter

[Julien Aymé]

A slightly modified version of my original patch, which check if the bean property is not a primitive type before returning null.

Fixed using Brandon's patch in "bugfixing" branch revision 742701.

[Henri Yandell]

svn ci -m "Merging in Dab Fabulich's work on https://svn.apache.org/repos/asf/commons/sandbox/dbutils/bugfixing from -r741987:747723. Resolving DBUTILS-34 - DBUTILS-37 - DBUTILS-29 - DBUTILS-14 - DBUTILS-31 - DBUTILS-39 - DBUTILS-41 - DBUTILS-44 - DBUTILS-33 - DBUTILS-42 - DBUTILS-40"

Sending pom.xml
Sending src/java/org/apache/commons/dbutils/BasicRowProcessor.java
Sending src/java/org/apache/commons/dbutils/BeanProcessor.java
Sending src/java/org/apache/commons/dbutils/QueryRunner.java
Adding src/java/org/apache/commons/dbutils/handlers/AbstractListHandler.java
Sending src/java/org/apache/commons/dbutils/handlers/ArrayListHandler.java
Sending src/java/org/apache/commons/dbutils/handlers/BeanListHandler.java
Sending src/java/org/apache/commons/dbutils/handlers/ColumnListHandler.java
Deleting src/java/org/apache/commons/dbutils/handlers/GenericListHandler.java
Sending src/java/org/apache/commons/dbutils/handlers/MapListHandler.java
Sending src/test/org/apache/commons/dbutils/BaseTestCase.java
Adding src/test/org/apache/commons/dbutils/QueryRunnerTest.java
Transmitting file data .........
Committed revision 747724.