Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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.

like image 619
Nick Fortescue Avatar asked Jul 09 '09 12:07

Nick Fortescue


People also ask

How do you check for null in ResultSet?

The wasNull() method of the ResultSet interface determines whether the last column read had a Null value. i.e. whenever you read the contents of a column of the ResultSet using the getter methods (getInt(), getString etc...) you can determine whether it (column) contains null values, using the wasNull() method.

How do you retrieve values in a ResultSet?

Invoke the Statement. executeQuery method to obtain the result table from the SELECT statement in a ResultSet object. In a loop, position the cursor using the next method, and retrieve data from each column of the current row of the ResultSet object using getXXX methods.

What is ResultSet getString ()?

getString(String columnLabel) Retrieves the value of the designated column in the current row of this ResultSet object as a String in the Java programming language. Time. getTime(int columnIndex) Retrieves the value of the designated column in the current row of this ResultSet object as a java.

What is ResultSet in JDBC with example?

A ResultSet object is a table of data representing a database result set, which is usually generated by executing a statement that queries the database. For example, the CoffeeTables. viewTable method creates a ResultSet , rs , when it executes the query through the Statement object, stmt .


3 Answers

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.

like image 156
skaffman Avatar answered Sep 22 '22 02:09

skaffman


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.

like image 22
artbristol Avatar answered Sep 23 '22 02:09

artbristol


Use:

rs.getObject(1)==null?null:rs.getBigDecimal(1).doubleValue()
like image 37
Nemo Avatar answered Sep 20 '22 02:09

Nemo