Android SQLite adding a record only if data doesn't exist

Question

I have 3 tables in my sqlite database. They are games, movies, and books. I want to create a new item in the table only if there's another with the same name doesn't exist. This is the current code I have. When I want to check for duplicate entry before adding a new item in the table, the app crashes.

// Adds an item to our database
public void createItem(Item item, String table) {
    SQLiteDatabase db = this.getWritableDatabase(); //reference the database
    ContentValues values = new ContentValues();
    values.put("name", item.getName());
    values.put("creator", item.getCreator());
    values.put("genre", item.getGenre());
    db.insert(table, null, values);
    db.close();
}

The current method I was using to check if one already existed is:

// Checks if an item exists before we add it to the database more than once
private boolean doesExist(String TableName, String fieldValue) {
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db.rawQuery("SELECT * FROM " +TableName+"WHERE NAME = " + fieldValue +"",null);

    if (cursor == null) {
        db.close();
        return false;
    } else {
        return true;
    }
}

And here is my code for the button clicked to add the record:

public void clickAdd(View view) {
    ImageView image = (ImageView) view;
    String table = TableAssistant.getTableName(image);
    Database db = new Database(getApplicationContext());
    Item item = new Item();
    item.setName(txtName.getText().toString());
    item.setGenre(txtGenre.getText().toString());
    item.setCreator(txtCreator.getText().toString());
    db.createItem(item, table);
}

Any help to get this to validate without crashing would be appreciated.

Possible duplicate of [INSERT IF NOT EXISTS ELSE UPDATE?](https://stackoverflow.com/questions/3634984/insert-if-not-exists-else-update) — ADM, Feb 25 '18 at 02:38
I've already looked through that before posting, app still crashed. — Carson1995, Feb 25 '18 at 02:50
How we suppose to know that app crash there is no crash log in your question . Add the crash logs if you need any help . — ADM, Feb 25 '18 at 03:21
@ADM, this is not the same problem, that you are indicating to. I have see the sql query is built wrongly. — Reaz Murshed, Feb 25 '18 at 03:49
I have seen that too . What about the title of question ? and Crash logs — ADM, Feb 25 '18 at 03:55

score 0 · Accepted Answer · answered Feb 25 '18 at 03:42

0

You are running a wrong SQL query while checking for the duplicate entry in your table. The query should be like this.

Cursor cursor = db.rawQuery("SELECT * FROM " + TableName + " WHERE NAME = "' + fieldValue +"'", null);

If you look very closely, you can see that I have added a ' to the both sides of the fieldValue to indicate this as a string. I have added a space before the WHERE as well.

answered Feb 25 '18 at 03:42

Reaz Murshed

23,691
13
78
98

Glad to know that helped. – Reaz Murshed Feb 25 '18 at 04:45

score 0 · Answer 2 · answered Feb 25 '18 at 05:17

You will also find that the following code will, once the query works, return true irrespective of whether a row is found or not. Any of the SQLiteDatabase methods that return a Cursor will never return a null Cursor. They will always return a Cursor. Furthermore, if the Cursor were null you would not be able to close it as there is no Cursor. However you should close the Cursor before returning true.

// Checks if an item exists before we add it to the database more than once
private boolean doesExist(String TableName, String fieldValue) {
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db.rawQuery("SELECT * FROM " +TableName+" WHERE NAME = " + fieldValue +"",null);

    if (cursor == null) { //<<<< Will not be null.
        db.close();  //<<<< would fail.
        return false;
    } else {
        return true; //<<<< Cursor not closed.
    }
}

The following would overcome the issues:-

// Checks if an item exists before we add it to the database more than once
private boolean doesExist(String TableName, String fieldValue) {
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db.rawQuery("SELECT * FROM " +TableName+" WHERE NAME = " + fieldValue +"",null);

    if (cursor.getCount() < 0) {
        db.close();
        return false;
    } else {
        db.close();
        return true;
    }
}

I appreciate your response, I actually noticed that when testing from the other answer and realized that it does indeed need to be cursor.getCount() < 0. thank you! — Carson1995, Feb 25 '18 at 05:22

Android SQLite adding a record only if data doesn't exist

2 Answers2