1

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...

StephanM
  • 1,350
  • 2
  • 24
  • 50
  • I am not happy with -1 becouse of unnecessary memory usage. Many database systems have a very effective memory management which presupposes nullable fields. – nano_nano Sep 02 '15 at 07:07

1 Answers1

5

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...

Sategroup
  • 955
  • 13
  • 29
  • About point number 4: is this really correct? Just was searching for the memory usuage off null and int and found the following: http://stackoverflow.com/questions/2430655/java-does-null-variable-require-space-in-memory for null and about int i read so far that they use 4 bytes (32bit system)...so my understanding is the null reference needs at least the same memory usuage like the primitive int with the value -1...or am i wrong? – StephanM Sep 03 '15 at 05:28
  • int is 32-bit which means it would occupy 4 bytes of memory and same is the case with Integer being a wrapper of int. However, Java caches number up to 127 for Integer wrapper class and in that case, it will always use the cache to represent that value. You can do a check for this in Java Integer i1 = new Integer("1"); Integer i2 = new Integer("1"); Though both different objects when you do `==` it would return true. *As for null, it would also consume 4 bytes of memory.* To overcome point 4 conflict, you can create a static reference to null Integer object and use that everywhere – Sategroup Sep 03 '15 at 05:34