Search code examples
javajdbcpersistence

Java DAO: null and Integer object or negative number and int in mapped object for optional number column?

Currently im working in a project where we had recently a discussion about this. So lets have any database table with a numeric column which is allowed to have "no value" in the source code.

Some of my colleagues prefer to use the primitive type int in their mapped objects and set the value in this case to -1; i prefer to make this column nullable, using the Integer object in source instead of the primitive type and set this value to null.

Now im searching for the pro's and con's of both variants but couldn't find anything so far.

The only article i found so far is this one: http://www.javapractices.com/topic/TopicAction.do?Id=134 ...

There is a very common exception to this rule. For Model Objects, which map roughly to database records, it's often appropriate to use null to represent optional fields stored as NULL in the database

...

@EDIT I need to add that such kind of columns are used in this project as a kind of fake foreign keys. They are not real database constraints but used as such. Dont ask me why...i know i know :-) -1 in this case means it has no relationship to the primay key/id of another table. So its not needed for any calculations or such stuff...

Solution

  • For Nullable columns, it is preferred to have null value, when you don't want to store a value. Your approach of using Null Integer object is far better than -1

    Consider this, your numeric column is being used in calculation and adding -1 (instead of null) could change the calculation. But, if you have Null your calculations would be correct

    Pros of using Null Integer:

    1. If numeric column is used in calculation, your output is not effected.
    2. When using ORM, the underlying framework doesn't have to insert value in that column, if it's an insert query
    3. Another benefit is that your DB would have less number of rows with -1 as value in nullable columns. (Think of 10 billion rows with -1 as a value for nullable column where you wanted to put null)
    4. Since, both Integer and Null require 4 bytes of memory space, memory benefit can be claimed by using static null Integer object.
    5. You don't have to check for -1 everywhere

    The reason those guys still use int - primitive is may be they have not yet realized the benefit of using Objects over primitives or are still used to JDK 1.4 style coding

    I hope I was able to answer your question...