NullPointerException when using String from SQL database

Question

I am trying to use a String read from a row in an SQL database, but when I do, I get the following NullPointerException :

Exception in thread "main" java.lang.NullPointerException
    at ReadCol.data(ReadCol.java:37)
    at ReadCol.main(ReadCol.java:50)

My code is shown below...

public String[] array;    
ResultSet rs=st.executeQuery("select * from ATTENDANCE");

while(rs.next()){
    File file = new File("E:\\eclipse\\workspace\\AutoAttendanceSystem\\res\\AttendanceData.csv");
    List<String> lines = Files.readAllLines(file.toPath(), StandardCharsets.UTF_8);
    for (String line : lines) {
        array = line.split(",");
        // This is line 37 :
        if(rs.getString(1).equals(array[0]) && rs.getString(7).equals(array[6])){
            JOptionPane.showMessageDialog(null, "Can't Update Because record already exist");
        }
    }

Here is the SQL table's structure :

CREATE TABLE "ATTENDANCE" ( 
   "ATTENDANT_NAME" VARCHAR2(4000), 
   "ATTENDANT_AGE" NUMBER, 
   "ATTENDANT_CONTACT_NO" NUMBER, 
   "ATTENDANT_DEPARTMENT_NAME" VARCHAR2(4000), 
   "REGISTRATION_NUM" VARCHAR2(50), 
   "ABSENT_PRESENT" VARCHAR2(4000) DEFAULT 'Absent', 
   "ATTENDANCE_TIME_DATE" VARCHAR2(4000) 
)

And here is an example of a row in that table :

Sun 2016.08.14 at 11:21:43 PM PDT, null, null, Thu 2016.08.18 at 01:58:34 AM PDT, null, Thu 2016.08.18 at 02:13:26 AM PDT, null

What is the problem ?

Answer 1

What is going on

It appears that the problem is when you call rs.getString(7).equals(...) or rs.getString(1).equals : this means that getString has returned null.

This can happen for 2 reasons :

1. The column that you ask (in that case column 1 or 7) doesn't exist

   • (doesn't seem to be the case here given your SQL table structure)

2. a NULL value must have been stored in the column, and getString returns null, which is why calling equals on null throws an exception.

Readings about this :

• Information about ResultSet.getString's return value: ResultSet's documentation.

• Why calling a method on a reference that points to null throws a NullPointerException

How to solve the problem

First off, to know which column is causing the problem, separate the conditions on 2 lines and see which one throws an exception :

if(rs.getString(1).equals(array[0]) && 
   rs.getString(7).equals(array[6])){

If it is ok for your database to hold NULL values, then what you should do, is test for it before applying equals :

if( ( rs.getString(1) == null && array[0] == null
    || rs.getString(1) != null && rs.getString(1).equals(array[0]) )
    && ... // same for 7th column

Side notes

1. You should refer to columns using their names rather their index.

2. To make the code easier to understand, you could store the strings you want to compare in variables, and only later test the conditions on these variables.

End result

String databaseResult1, databaseResult2, fileResult1, fileResult2;


// Start looping
// ...

   databaseResult1 = rs.getString("ATTENDANT_NAME");
   fileResult1 = array[0];
   databaseResult2 = rs.getString("ATTENDANCE_TIME_DATE");
   fileResult2 = array[6];

   if(
       ( databaseResult1 == null && fileResult1 == null 
         || databaseResult1 != null && databaseResult1.equals(fileResult1) )
       &&
       ( databaseResult2 == null && fileResult2 == null 
         || databaseResult2 != null && databaseResult2.equals(fileResult2) )
      ...