Commons DbUtils
  1. Commons DbUtils
  2. DBUTILS-42

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

    Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 1.1
    • Fix Version/s: 1.2
    • Labels:
      None
    • Environment:

      JDK 5.0, MSSQL 2000

      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.

      1. BeanProcessor-fixed.diff
        2 kB
        Julien Aymé
      2. BeanProcessorAlternativePatch.diff
        0.5 kB
        Brandon Atkinson
      3. BeanProcessor.diff
        2 kB
        Julien Aymé

        Activity

        Hide
        Henri Yandell added a comment -

        What part of the DbUtils API are you using?

        Show
        Henri Yandell added a comment - What part of the DbUtils API are you using?
        Hide
        Matt Jiang added a comment -

        Hi Henri

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

        Matt

        Show
        Matt Jiang added a comment - Hi Henri I update the content on JIRA, please check it out. Thanks a lot. Matt
        Hide
        Rick Cromer added a comment -

        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

        Show
        Rick Cromer added a comment - 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
        Hide
        Rick Cromer added a comment -

        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?

        Show
        Rick Cromer added a comment - 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?
        Hide
        Julien Aymé added a comment -

        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

        Show
        Julien Aymé added a comment - 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
        Hide
        Julien Aymé added a comment -

        Attaching proposed patch.

        Show
        Julien Aymé added a comment - Attaching proposed patch.
        Hide
        Brandon Atkinson added a comment - - edited

        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.

        Show
        Brandon Atkinson added a comment - - edited 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.
        Hide
        Brandon Atkinson added a comment -

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

        Show
        Brandon Atkinson added a comment - Fix to BeanProcessor which fixes processColumn. Returns null if the bean property is an Object type, and the resultSet returns null.
        Hide
        Julien Aymé added a comment -

        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

        Show
        Julien Aymé added a comment - 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
        Hide
        Julien Aymé added a comment -

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

        Show
        Julien Aymé added a comment - A slightly modified version of my original patch, which check if the bean property is not a primitive type before returning null.
        Hide
        Dan Fabulich added a comment -

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

        Show
        Dan Fabulich added a comment - Fixed using Brandon's patch in "bugfixing" branch revision 742701.
        Hide
        Henri Yandell added a comment -

        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.

        Show
        Henri Yandell added a comment - 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.

          People

          • Assignee:
            Dan Fabulich
            Reporter:
            Matt Jiang
          • Votes:
            1 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development