Integer variable is zero when is it null in database

Question

I have declared variable as

private Integer projectId;

   public void setProjectId(Integer projectId) {
    this.projectId= projectId;
}

public Integer getProjectId() {
    return projectId;
}

While retrieving values from database, if projectId is null in database table, it is shown as 0 e.g.

log.info("?? "+projectList.get(1).getProjectId());

the result of the above is 0.

Why it is shown as 0 although it is null in table and how can I make this is as null when it is null in table?

Edit 1

while (rs.next()) {
projectList.add(mapProjects(resultSet));

}

private static Project mapProjects(ResultSet rs) 
throws SQLException {
return new Project ((rs.getInt("ID")), 
(rs.getInt("PROJECT_ID")),

Are either your getter or setters using primitive variables? — ug_, Oct 02 '13 at 07:55
@ns47731, even if the getter/setter used `int` instead of `Integer` you'd just get a NullReferenceException when trying to return it. — Joey, Oct 02 '13 at 07:57

Joey · Accepted Answer · 2013-10-02T08:11:55.083

You have to handle that case yourself. The ResultSet documentation clearly states (emphasis mine):

getInt int getInt(int columnIndex)

Retrieves the value of the designated column in the current row of this ResultSet object as an int 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

You have to call wasNull() after reading a column value and set your field to null if that was the case. So something like the following:

Integer projectId = rs.getInt("PROJECT_ID");
if (rs.wasNull()) projectId = null;
return new Project (rs.getInt("ID"), projectId), ...);

For sanity reasons it's probably nicer if you move that code into a new method:

/**
 * Retrieves the value from the designated column as an {@link Integer}
 * object.
 * 
 * @param rs
 *            The ResultSet to read from.
 * @param columnName
 *            The column name to read.
 * @return {@code null} if the column value was SQL NULL; its value as an
 *         integer otherwise.
 */
public static Integer getInteger(ResultSet rs, String columnName) {
    int v = rs.getInt(columnName);
    return rs.wasNull() ? null : v;
}

Joey I have included my retrieving database code snippet in question, how can I check `wasNull`? — Jacob, Oct 02 '13 at 08:04
It's a bit annoying because you *first* have to retrieve the value and *then* check for `NULL` (which makes a conditional operator useless here). I added some code. — Joey, Oct 02 '13 at 08:06
Well, as a developer you know that abstraction can be a powerful tool in reducing mindless chores to calling aptly-named methods ;-) — Joey, Oct 02 '13 at 08:08

Integer variable is zero when is it null in database

1 Answers1