CODEWITHSUNDEEP
javajdbc
javaNOob
javaNOob

Reputation: 481

Handling the null value from a resultset

I currently have a result set returned, and in one of the columns the string value may be null (I mean no values at all). I have a condition to implement like following

rs = st.executeQuery(selectSQL);
output = rs.getString("column");

Since the column may be null in the database, the rs.getString() will throw a NullPointerException when the column is null. If column is null, I want the output to be an empty string like output = "";. I can't check if(rs.getString("column) != null either. How can I tackle this situation?

My real problem:

try {
    rs = st.executeQuery(sql);
    int i = 0;
    while (rs.next()) {
        output[i] = rs.getString(column);
        // column field in the database contains multiple results, but sometimes
        // may be null
        i++;
    }
} catch (SQLException e) {
    e.printStackTrace();
    // other than tracing the exception i want to fill the array too
}
return output;

Now, if one of the column values contains no value, i.e. null, I want output[i] defined as N/A. This problem stems from the fact that the column field is NULL allowed in the database. And sorry for telling you that it's a NPE, while in fact it's a SQLException.

Upvotes: 47

Views: 155450

Answers (9)

iamfnizami
iamfnizami

Reputation: 193

You can simply use-

rs = st.executeQuery(selectSQL);
output = rs.getString("column");
if(!output.isEmpty()) {
 //
}

In MySQL faced issue with-

output!=null
output!=""

But output.isEmpty() worked for rs.getString().

Upvotes: -1

Fav-loper PHP
Fav-loper PHP

Reputation: 1

I came across with the same issue. But I believe , handling null in the sql is not a good option. such things should be handled in java program for better performance. secondly , rs.getString("column") != NULL is also not a good option as you are comparing string's reference not value. better to use .equals() method while checking null or isEmpty() method. Again, with this you can use null check, that is fine.

Upvotes: -1

nikelio
nikelio

Reputation: 1

The code should be like given below

String selectSQL = "SELECT IFNULL(tbl.column, \"\") AS column FROM MySQL_table AS tbl";
Statement st = ...;
Result set rs = st.executeQuery(selectSQL);

Upvotes: 0

d-live
d-live

Reputation: 8036

Since the column may be null in the database, the rs.getString() will throw a NullPointerException()

No.

rs.getString will not throw NullPointer if the column is present in the selected result set (SELECT query columns) For a particular record if value for the 'comumn is null in db, you must do something like this - 

String myValue = rs.getString("myColumn");
if (rs.wasNull()) {
    myValue = ""; // set it to empty string as you desire.
}

You may want to refer to wasNull() documentation - 

From java.sql.ResultSet
boolean wasNull() throws SQLException;

* Reports whether
* the last column read had a value of SQL <code>NULL</code>.
* Note that you must first call one of the getter methods
* on a column to try to read its value and then call
* the method <code>wasNull</code> to see if the value read was
* SQL <code>NULL</code>.
*
* @return <code>true</code> if the last column value read was SQL
*         <code>NULL</code> and <code>false</code> otherwise
* @exception SQLException if a database access error occurs or this method is 
*            called on a closed result set
*/

Upvotes: 71

joselitoo
joselitoo

Reputation: 59

To treat validation when a field is null in the database, you could add the following condition.

String name = (oRs.getString ("name_column"))! = Null? oRs.getString ("name_column"): "";

with this you can validate when a field is null and do not mark an exception.

Upvotes: -1

Barath Sankar
Barath Sankar

Reputation: 383

The String being null is a very good chance, but when you see values in your table, yet a null is printed by the ResultSet, it might mean that the connection was closed before the value of ResultSet was used.

Class.forName("org.sqlite.JDBC");
con = DriverManager.getConnection("jdbc:sqlite:My_db.db");
String sql = ("select * from cust where cust_id='" + cus + "'");
pst = con.prepareStatement(sql);
rs = pst.executeQuery();
con.close();
System.out.println(rs.getString(1));

Would print null even if there are values.

Class.forName("org.sqlite.JDBC");
con = DriverManager.getConnection("jdbc:sqlite:My_db.db");
String sql = ("select * from cust where cust_id='" + cus + "'");
pst = con.prepareStatement(sql);
rs = pst.executeQuery();
System.out.println(rs.getString(1));
con.close();

Wouldn't print null if there are values in the table.

Upvotes: 2

SedJ601
SedJ601

Reputation: 13859

I was able to do this: 

String a;
if(rs.getString("column") != null)
{
    a = "Hello world!";
}
else
{
    a = "Bye world!";
}

Upvotes: 4

Chris
Chris

Reputation: 91

The description of the getString() method says the following:

 the column value; if the value is SQL NULL, the value returned is null

That means your problem is not that the String value is null, rather some other object is, perhaps your ResultSet or maybe you closed the connection or something like this. Provide the stack trace, that would help.

Upvotes: 8

Jigar Joshi
Jigar Joshi

Reputation: 240918

output = rs.getString("column");// if data is null `output` would be null, so there is no chance of NPE unless `rs` is `null`

if(output == null){// if you fetched null value then initialize output with blank string
  output= "";
}

Upvotes: 28

Related Questions