Android - How to check if item exists in database?

Question

I want to add a new item (spot) in my database, but before doing this, I want to check if this item already exists.

Here is my code :

for (Spot spot : spotsList)
{
                if (dbHelper.getSpotWithDistantId(spot.getDistantId()) == null)
                {
                    dbHelper.addSpot(spot);
                }
}

And here is my getSpotWithDistantId method :

public Spot getSpotWithDistantId(int distantId)
    {
        Cursor cursor =
                db.query(TABLE_SPOTS,
                        SPOTS_COLUMNS,
                        " " + KEY_DISTANTID + " = ?",
                        new String[]{String.valueOf(distantId)}, // Selections args
                        null, // Group by
                        null, // Having
                        null, // Order by
                        null); // Limit

        if (cursor != null)
        {
            cursor.moveToFirst();
        }

        Spot spot = buildSpotWithCursor(cursor);

        if (cursor != null)
        {
            cursor.close();
        }

        db.close();

        return spot;
    }

The problem is when I want to check if a spot exists, the following error appears:

android.database.CursorIndexOutOfBoundsException: Index 0 requested, with a size of 0

What's wrong with my code ?

Thanks in advance.

Answer 1

I have the similar problem and I use this simple function:

public boolean exists_the_ColumnParameter(String query){
    //Declaration of variables
    Cursor a2 = null;

    try{
        OpenDB();
        a2 = database.rawQuery(query,null);

        if(a2 != null) {
            a2.moveToFirst();

            if (a2.getString(0).equals("")) {
                a2.close();
                database.close();
                return false;
            } else {
                a2.close();
                database.close();
                return true;
            }
        }else{
            a2.close();
            database.close();
            return false;
        }

    }
    catch (CursorIndexOutOfBoundsException ex){
        return false;
    }
    catch (NullPointerException ex){
        return false;
    }
    catch (Exception ex){
        Log.e("-- BDD.exists_the_ColumnParameter --","Exception",ex);
        return false;
    }
}

The reason which I can't pass a single ID because it isn't a generic function, the big difference is which with the ID you can only view one SQLite table and with the query you can view multiple ID's from diferent tables.

Tell me if I can helps you, good programming!!

Answer 2

You need to check if your cursor contains any records. You can do it like this

if(cursor != null && cursor.getCount() > 0)
{
    ...
}
else
{
    cursor.close();
    return false;
}