Android SQL - Check if whole row already exists in database

Question

I'm trying to create a simple favorites application, where you can keep your favorites pages (from the web for example).

Let's say I have the following data in my database

Title    | URL     | TAG      |
-------------------------------
Hey       Ho.com    Site
Jo        Jo.com    Image
Jo        Mo.com    Image

Now I want to make sure, the user does not add the same page twice, so I will check if the value already exists. But I need to that the user does not add a site to the table that has already been added.

So let's say I would try to add:

Title    | URL     | TAG      |
-------------------------------
Jo        Mo.com    Image

This it would return true (for "check if row exist", because an identical row already exists).

But if I would try to add:

Title    | URL     | TAG      |
-------------------------------
Jo        Go.com    Image

It would return false (though the title already exists), because there is no identical row.

This is the code where I add my data to the database with:

public long createNote(String title, String url, String rn) {
    ContentValues initialValues = new ContentValues();
    initialValues.put(KEY_TITLE, title);
    initialValues.put(KEY_URL, url);
    initialValues.put(KEY_RN, rn);

    return mDb.insert(DATABASE_TABLE, null, initialValues);

How can I check if a row already exists in a database?

Use INTEGER PRIMARY KEY AUTOINCREMENT ID. Always use INTEGER PRIMARY KEY AUTOINCREMENT ID. To prevent duplicate rows you just need a UNIQUE constraint. See this related answer stackoverflow.com/a/2701903/792932 — foibs
– foibs, Commented Dec 19, 2013 at 16:56

Mdlc · Accepted Answer · 2013-12-19 17:50:52Z

7

You can check using cursor:

public boolean checkEvent(String title, String URL, String tag) 
{
    SQLiteDatabase db = this.getReadableDatabase();

    Cursor cursor = db.query(TABLES, 
            new String[] { KEY_TITLE,KEY_URL,KEY_TAG }, 
            KEY_TITLE + " = ? and "+ KEY_URL + " = ? and " + KEY_TAG + " = ?" , 
            new String[] {title,url,tag}, 
            null, null, null, null);

    if(cursor.moveToFirst())

     return true; //row exists
    else 
     return false;

}

Edit: Code now copy & paste ready

edited Dec 19, 2013 at 17:50

Mdlc

7,29812 gold badges58 silver badges100 bronze badges

answered Dec 19, 2013 at 16:55

Aniket Gupte

1221 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

bsautner · Accepted Answer · 2013-12-19 16:54:53Z

5

There is a very graceful (imho) way to do this at the database level. Add this to your create table sql when you create your database:

 UNIQUE(TITLE, URL) ON CONFLICT REPLACE

Then you can insert all you want without checking, it will either be added or updated if the row is already there.

answered Dec 19, 2013 at 16:54

bsautner

4,9032 gold badges41 silver badges53 bronze badges

2 Comments

Mdlc Over a year ago

Creating a unique identifier by combining various data is certainly an Idea!

Varundroid Over a year ago

This is a far better and superior solution than the one which has been accepted. Definitely deserves an up vote. Thank :)

Rakeeb Rajbhandari · Accepted Answer · 2013-12-19 17:03:28Z

1

You can query to see if the a unique identifier is there in the database, in your case the title maybe the unique identifier of the row.

public boolean isEntry(int title){
    String queryString = "SELECT * FROM "+TABLE_NAME+" WHERE "+KEY_PROJECT_ID+" = "+"'"+project_id+"'";
    Cursor c = db.rawQuery(queryString, null);
    if(c.getCount() > 0){
        Log.i("CHECK", "true");
        return true;
    }
    else{ 
        return false;
    }
}

Here true would mean that there is a row like this, false would mean that there is no such entry.

Now let's say are about to insert an entry into the database. Even if the method returns a true value what you can do is you could compare the other two values as in your example. After that you could call a function to return a single row from the database, like

public String getCursor(int project_id){
    String queryString = "SELECT * FROM "+TABLE_NAME+" WHERE "+KEY_PROJECT_ID+" = "+"'"+project_id+"'";
    Cursor cursor = db.rawQuery(queryString, null);
    return cursor;
}

Once you have the function you can select the TAG and URL from it and see if it matches the one in your database.

Like this:

Cursor cursor = db.rawQuery(queryString, null);
String tag = "";
String url = "";

cursor.moveToFirst();
for (int i = 0; i < cursor.getCount(); i++) {
    like_state = cursor.getString(cursor.getColumnIndex(KEY_TAG));
    url = cursor.getString(cursor.getColumnIndex(KEY_URL));
    cursor.moveToNext();
}
cursor.close();

if(!ins_tag.equalsIgnoreCase(tag) || !ins_url.equalsIgnoreCase(url)) {
    //insert function here
}

And as mentioned in the comments you should use the an AUTOINCREAMENT integer as a Primary Key.

edited Dec 19, 2013 at 17:03

answered Dec 19, 2013 at 16:49

Rakeeb Rajbhandari

5,0636 gold badges45 silver badges74 bronze badges

2 Comments

Mdlc Over a year ago

Thank you for your answer, but I am really looking for a way to find identical rows

Mdlc Over a year ago

Thank you, I'll try and let you know

Collectives™ on Stack Overflow

Android SQL - Check if whole row already exists in database

3 Answers 3

Comments

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related