Android Sqlite - Adding Only New Records To Table

I am collecting the phone's call log into a table. This works great: public long populate_Calls(String name, String phone, String type, String duration, String date, String contac

Solution 1:

You do not need to check the database for duplicates if you set it up to do that for you when you create the table.

Your table creation string might look like this:

privatestaticfinalStringCREATE_TABLE="create table "
        + YOUR_TABLE + " (_id integer primary key autoincrement, "
        + KEY_NAME + " text not null,"
        + KEY_PHONE + " text not null,"
        + KEY_TYPE + " text not null,"
        + KEY_DURATION + " text not null,"
        + KEY_DATE + " text not null,"
        + KEY_CONTACTID + " integer not null,"
        + "UNIQUE(" + KEY_PHONE + "," + KEY_DATE + ") ON CONFLICT IGNORE);";

That sets it up so that if you have a combination of phone and date that are the same the record will cause a constraint violation and the insert/update of that record will be ignored (You can change the last bit to ON CONFLICT FAIL to make it throw an error you can catch rather than silently skipping the insert/update operation).

This will make it work whether you use insert(), update(), or replace().

Solution 2:

You can also make a unique field, where you safe a generated code based on the containing date and phone number. Since the field is unique, it will not added if the code already exists.

Solution 3:

you can also try updateOrInsert. So first you can try to update the data it will usually return the row id if data exists. then you can insert the data if no row id was returned from updating. http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html

Another scenario to why you are seeing data could be because you are not clearing the application data when you are rebuilding the application. On your device go to Settings -> Applications -> Manage Applications -> " your application" clear data

Solution 4:

I guess I need to watch only the phone number and the date since that identifies the call. If a record with that number and at that date already exists, just jump to the next record.

To do an 'insert or replace':

1. Make a composite primary key for the table with the fields KEY_PHONE and KEY_DATE.

2. Use the replace (..) method on the SQLiteDatabase instead of the insert (...) that you are currently using. So your populate_Calls return statement should look like:

   return ourDatabase.replace(DATABASE_TABLE, null, cv);

Solution 5:

Edit

The query I proposed does not work, so I removed it!

Updated answer

You should do what hm. suggests. Create a unique column in you db, which you populate with something like phone+duration+date, and then do

"INSERT OR IGNORE INTO " + DATABASE_TABLE + " values(" + THE_VALUES + ");"

When inserting new values, old values will be ignored.