How do I in JDBC read a possibly null double value from resultSet?

Question

I have a column in my database that is typed double and I want to read the value from it using a JDBC ResultSet, but it may be null. What is the best way of doing this? I can think of three options none of which seem very good.

Option 1: Bad because exception handling verbose and smelly

double d;
try {
   d = rs.getDouble(1);
   // do something
} catch(SQLException ex) {
   if(rs.wasNull()) {
      // do something else
   } else {
     throw ex;
   }
}

Option 2: Bad because two fetches

s = rs.getString(1); // or getObject()
if(s == null) {
  // do something else
} else {
  double d = rs.getDouble(1);
  // do something
}

Option 3: Bad because Java rather than SQL conversion

s = rs.getString(1); // or getObject()
if(s == null) {
  // do something else
} else {
  double d = Double.parseDouble(s);
  // do something
}

Any suggestions on which way is better, or is there another superior way? And please don't say "Use Hibernate", I'm restricted to JDBC code only here.

score 61 · Accepted Answer · answered Jul 09 '09 at 12:17

61

Option 1 is closest:

double d = rs.getDouble(1);
if (rs.wasNull()) {
  // do something
} else {
  // use d
}

It's not very nice, but that's JDBC. If the column was null, the double value is considered "bad", so you should check using wasNull() every time you read a primitive that is nullable in the database.

answered Jul 09 '09 at 12:17

skaffman

398,947
96
818
769

2

note that there's no sqlexception when retrieving null primitives from a result set. the default value for that primitive is returned in such cases (0 for int, 0.0 for double, etc....). Hence why .wasNull() is a necessary evil. – Matt Aug 31 '12 at 12:42
1

I had the exact same reaction as @JosefPfleger once I realized how vanilla JDBC works with regards to nulls. If you want to continue using plain SQL, though (as opposed to adopting an ORM) and avoid this debacle altogether, I suggest using Spring's `JdbcTemplate`, which takes care of all those pesky null checks for you. – Priidu Neemre Oct 15 '14 at 17:43

artbristol · Answer 2 · 2017-11-24T09:03:32.590

19

Depending on your JDBC driver and database, you may be able to use a boxed type and cast:

Double doubleValueOrNull = (Double)rs.getObject(1); // or .getObject("columnName")

It will be null if the column was NULL.

Be careful to check this still works if you change database.

edited Nov 24 '17 at 09:03

answered Dec 12 '12 at 13:40

artbristol

32,010
5
70
103

This is not working for me when querying HSQLDB with oracle syntax for tests. – sravanreddy001 Jul 12 '13 at 20:39
This worked fine for me for MySQL, and nicer on the eyes than .wasNull() – Hugo Zaragoza Oct 10 '14 at 15:29

score 9 · Answer 3 · edited Mar 02 '14 at 17:19

9

Use:

rs.getObject(1)==null?null:rs.getBigDecimal(1).doubleValue()

edited Mar 02 '14 at 17:19

LaurentG

11,128
9
51
66

answered Mar 02 '14 at 17:01

Nemo

145
1
7

score 8 · Answer 4 · answered Jul 07 '16 at 11:35

8

Or with java 8 you can do this:

Double dVal = Optional.ofNullable(resultSet.getBigDecimal("col_name"))
                .map(BigDecimal::doubleValue).orElse(null));

answered Jul 07 '16 at 11:35

George

7,206
8
33
42

score 0 · Answer 5 · answered Apr 30 '19 at 04:45

0

Kotlin version to retrieve a nullable field:

val parentId = resultSet.getObject("parent_id") as Double?

answered Apr 30 '19 at 04:45

vovahost

34,185
17
113
116

How do I in JDBC read a possibly null double value from resultSet?

5 Answers5

Linked

Related