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 colu开发者_StackOverflow中文版mn 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.

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
*/

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= "";
}

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.

I was able to do this:

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

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.

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);

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.

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.

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