How to check if an item is in a database before trying to add it-CodePudding

I have an android app that is adding to and updating a database. The student ID number is the primary key. I'm trying to have the app display a message when the user attempts to add a duplicate. This is my latest attempt; it's triggering the catch clause.

In my main activity, I have this conditional statement:

 if (!dbHandler.checkStudentID(studentID)){
    Toast.makeText(MainActivity.this, "Student ID is a Duplicate",
         Toast.LENGTH_SHORT).show();
 }
 dbHandler.addNewStudent(studentID, firstName, lastName, grade);

In my handler the method is this:

public boolean checkStudentID (String studentID) {
   SQLiteDatabase db = this.getReadableDatabase();
   Cursor checkID = db.rawQuery("SELECT student_id FROM "   TABLE_NAME  
                   " where student_id=?", new String[]{studentID});
   String x = checkID.getString(0);
   checkID.close();
   return x.isEmpty();
}

Logcat isn't showing any issues and there aren't any syntax problems shown, so I'm at a loss as to what's not correct.

CodePudding user response：

The problem is if the studentID could not be found, String x = checkID.getString(0) contains null. And with that, calling x.isEmpty() will throw a NullPointerException. So you should intercept that by:

public static boolean checkStudentID (String studentID) {
   SQLiteDatabase db = this.getReadableDatabase();
   Cursor checkID = db.rawQuery("SELECT student_id FROM "   TABLE_NAME  
                   " where student_id=?", new String[]{studentID});
   String x = checkID.getString(0);
   checkID.close();
   if (x == null || x.isEmpty()) {
       return false;
   }
   else {
       return true;
   }
}

I think you could even leave x.isEmpty() out altogether.

If the Toast.makeText().show() doesn't terminate the program, you should better wrap that addNewStudent part in an else-case. Otherwise, it will still be executed!

if (!dbHandler.checkStudentID(studentID)){
   Toast.makeText(MainActivity.this, "Student ID is a Duplicate",
        Toast.LENGTH_SHORT).show();
}
else{
   dbHandler.addNewStudent(studentID, firstName, lastName, grade);
}

CodePudding user response：

When working with a Cursor rawQuery will return a Cursor that is positioned before the first row (as do any of the SQLiteDatabase methods that return a Cursor). To extract data you need to position the Cursor accordingly.

In your case all you need to know is whether or not any data was extracted, as such you can use the moveToFirst method. This will return true if the move could be made (if there is any data) or false (if there is no data).

So you could use something like :-

public static boolean checkStudentID (String studentID) {
   boolean rv = false;
   SQLiteDatabase db = this.getReadableDatabase();
   Cursor checkID = db.rawQuery("SELECT student_id FROM "   TABLE_NAME  
                   " where student_id=?", new String[]{studentID});
   if (checkID.moveToFirst()) {
       rv = true;
   }
   checkID.close();
   return rv;
}

This is my latest attempt; it's triggering the catch clause.

Because you are trying to access data when the Cursor is before the first row when you execute String x = checkID.getString(0);, hence an exception being caught/trapped.