Check an int variable is null or is empty from the Database

Question

I am having a variable which is:

nocustomers = rs.getInt("CUST_TRAN_COUNT");

I would like to perform if it is null or not.

I tried

if (nocustomers ==null)

It showed an error.

How do I solve this?

My new modified code is:

try{

    query=" select * from SS_summary where txn_date = to_date ('"+asatdate+"','YYYY-MM-DD') ";
    st = conn.createStatement();
    rs = st.executeQuery(query);

    if (rs.next())
    {   


    nocustomers = rs.getInt("CUST_TRAN_COUNT");

    nocheques =rs.getInt("CHEQ_DELIVERED_MAIN");
    }

    if (rs.wasNull()) {
        out.println("NO DATA FOUND");
        }

%>

you can't check primitive datatypes for null (= null pointer), only pointers to objects. what is `rs` ? you have to check your ORM / SQL adapter for such a functionality — Marian Theisen, Sep 14 '11 at 06:43
Related: http://stackoverflow.com/questions/2254435/can-an-int-be-null-in-java — Joachim Sauer, Sep 14 '11 at 06:50
@maas, you don't need to do a `rs.wasNull()` as `rs.getInt()` will return a `0` if data was null by default. Rather say, `if (nocustomers == 0) { out.println("NO DATA FOUND"); }`. — Buhake Sindi, Sep 14 '11 at 11:09

score 7 · Answer 1 · answered Sep 14 '11 at 06:48

7

int can't be null, because it's a primitive type.

For the same reason ResultSet.getInt() can't return null.

You will need to call ResultSet.wasNull() right after your getInt() call to check if the value was null.

Note that since int can't be null, nocustomer will have a value even if the database value is null. getInt() is defined to return 0 when the DB value is null, so nocustomers will be 0.

answered Sep 14 '11 at 06:48

Joachim Sauer

302,674
57
556
614

resultSet.wasNull() did not work and showed an error "java.sql.SQLException: No data read " – maas Sep 14 '11 at 07:56
1

@maas: when did you call it? You **must** call `getInt()` *before* calling `wasNull()`. Please show us your code. – Joachim Sauer Sep 14 '11 at 07:57
if (rs.next()) { nocustomers = rs.getInt("CUST_TRAN_COUNT"); } if (rs.wasNull()) { out.println("NO DATA FOUND"); } – maas Sep 14 '11 at 08:03

Buhake Sindi · Answer 2 · 2011-09-14T07:38:18.463

3

At compile time, the java compiler will complain with the following message:

incomparable types: int and <nulltype>
if (nocustomers  == null) {
      ^

That is because you can never do a null check on primitive types. Primitive types are assigned with default values (zero, for integers), if unassigned.

If you want to know if the value read was null, use the ResultSet.wasNull() method instead (after reading your integer value, see JavaDoc link provided):

nocustomers = rs.getInt("CUST_TRAN_COUNT");
if (rs.wasNull()) {
    nocustomers = -1; //Assuming, -1 means NULL.
}

edited Sep 14 '11 at 07:38

answered Sep 14 '11 at 06:51

Buhake Sindi

87,898
29
167
228

Assigning `0` to `nocustomers` if `wasNull()` returns `true` is redundant, because that's already what `getInt()` will have returned in that case. – Joachim Sauer Sep 14 '11 at 07:15
@Joachim Sauer, that's true indeed. The OP shouldn't worry about checking a null, unless the OP wants to define `-1` as `null`. I changed my assignment value from `0` to `-1` for this example of using `ResultSet.wasNull()`. – Buhake Sindi Sep 14 '11 at 07:37
if (rs.next()) { nocustomers = rs.getInt("CUST_TRAN_COUNT"); } if (rs.wasNull()) { out.println("NO DATA FOUND"); } it showed an error – maas Sep 14 '11 at 08:12
1

@maas: please post your **full** code and the **full** stack trace (edit the question or post a new one). – Joachim Sauer Sep 14 '11 at 08:20

score 2 · Answer 3 · answered Sep 14 '11 at 06:50

2

If the value was NULL, then 0 will be returned by getInt, and then you could call wasNull to check if it held a 0 or if it was a NULL.

See also: wasNull()

answered Sep 14 '11 at 06:50

Eran Zimmerman Gonen

4,375
1
19
31

it showed an error "java.sql.SQLException: No data read " – maas Sep 14 '11 at 07:56
@maas: make sure you called it only after `getInt` was called. – Eran Zimmerman Gonen Sep 14 '11 at 07:58
resultSet.wasNull() did not work and showed an error "java.sql.SQLException: No data read " – maas Sep 14 '11 at 08:12

Low Flying Pelican · Answer 4 · 2014-10-01T00:07:08.553

1

you can use,

object obj = rs.getObject("CUST_TRAN_COUNT");
if (obj != null)
{
rs.getInt("CUST_TRAN_COUNT");
}

but in some cases (very rare) you can't call the getInt once you called the getObject, in that case you can simply use

int.parse(obj.toString())

Also, I think better way to do it is,

  int value = rs.getInt("CUST_TRAN_COUNT");
  boolean nullValue = rs.wasNull();

so if the db returned null, value would be 0 however nullValue will be true so that you can do the needful

edited Oct 01 '14 at 00:07

answered Sep 14 '11 at 08:07

Low Flying Pelican

5,974
1
32
43

1

What are those 'rare' cases? – Jus12 Sep 30 '14 at 15:18
The issue happened, with certain version of mysql driver where once I call getObject, the getInt for the same column throws an exception – Low Flying Pelican Oct 01 '14 at 00:04

score 0 · Answer 5 · answered Sep 14 '11 at 06:49

There is a wasNull method on your ResultSet or RowSet:

Reports whether the last column read had a value of SQL NULL. 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 wasNull to see if the value read was SQL NULL.

Check an int variable is null or is empty from the Database

5 Answers5