Storing duplicate records in SQLite database table

Question

I am storing data into SQLite table, and if data already exist(s) then updating it, and i am able to do that but whenever i update my existing record it saves another copy of that record...

DatabaseDataHelper.java:-

public class DatabaseDataHelper extends SQLiteOpenHelper {

    // Database Version
    private static final int DATABASE_VERSION = 1;
    // Database Name
    private static final String DATABASE_NAME = "Data";
    // Table Name
    private static final String TABLE_MEMBER = "Data";

    public DatabaseDataHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {

        db.execSQL("CREATE TABLE " + TABLE_MEMBER + 
                  "(Name VARCHAR(50) UNIQUE,"
                  " Title VARCHAR(50))");

        Log.d("CREATE TABLE","Create Table Successfully - classs");
    }

    public long InsertData(String strName, String strTitle) {

        try {
            SQLiteDatabase db;
            db = this.getWritableDatabase();

            ContentValues Val = new ContentValues();
            Val.put("Name", strName); 
            Val.put("Title", strTitle);

            long rows = db.insert(TABLE_MEMBER, null, Val);

            db.close();
            return rows;

        } catch (Exception e) {
            return -1;
        }
    }

    public long UpdateData(String strName, String strTitle) {

        try {
            SQLiteDatabase db;
            db = this.getWritableDatabase();

            ContentValues Val = new ContentValues();
            Val.put("Title", strTitle);

            long rows = db.update(TABLE_MEMBER, Val, "Name=?",
                    new String[] { String.valueOf(strName) });

            db.close();
            return rows;

        } catch (Exception e) {
            return -1;
        }   
    }

    public String[] SelectData(String strName) {

        try {
            String arrData[] = null;    

             SQLiteDatabase db;
             db = this.getReadableDatabase();

             Cursor cursor = db.query(TABLE_MEMBER, new String[] { "*" }, 
                     "Name=?",
                     new String[] { String.valueOf(strName) }, null, null, null, null);

                if(cursor != null) {
                    if (cursor.moveToFirst()) {
                        arrData = new String[cursor.getColumnCount()];
                        arrData[0] = cursor.getString(0);
                        arrData[1] = cursor.getString(1);
                    }
                }
                cursor.close();
                db.close();
                return arrData;
         } catch (Exception e) {
            return null;
         }
    }

    public boolean Exists(String strName) {
        SQLiteDatabase db;
        db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery("select 1 from Data where Name= ?", new String[] { strImageName });
        boolean exists = (cursor.getCount() > 0);
        cursor.close();
        return exists;
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_MEMBER);
        onCreate(db);
    }

    public Cursor getAllData() {
         String selectQuery = "Select * from "+ TABLE_MEMBER; 
         SQLiteDatabase db = this.getReadableDatabase();
         Cursor cursor = db.rawQuery(selectQuery, null);
         return cursor;
    }

}

InsertDataActivity.java:-

public boolean saveData() {
    long storeData = databaseDataHelper.InsertData(txtName.getText().toString(), editTitle.getText().toString());

    if (storeData <=  0) {
    } else
        storeData = databaseDataHelper.UpdateData(txtName.getText().toString(),
            editTitle.getText().toString());
    return true;
}

before you edit it, query in database that if same data is there or not. — Shvet, Mar 23 '15 at 13:05
You are inserting the datum **anyway** (and storeData is **1**). — Phantômaxx, Mar 23 '15 at 13:30
Did you [check this](http://stackoverflow.com/a/3635038/2591002) ? — SweetWisher ツ, Mar 24 '15 at 07:24

score 2 · Answer 1 · edited Mar 24 '15 at 07:35

In your InsertDataActivity where you are inserting and updating data in database, you are inserting data first and then you are updating data. The approach is wrong.

You should update first and check the affected row. if you get 0 row affected then only you need to insert that row.

Try Following :

public boolean saveData() {

    // This will Update record in the database if it is available and return row id of the updated database.
    long mNoRowsEffected = databaseDataHelper.UpdateData(txtName.getText().toString(), editTitle.getText().toString());

    // If we don't get any updated row id or if row id is null it mean the current record is not available in database 
    // so we need to insert this record to the database.
    if (mNoRowsEffected != null && mNoRowsEffected <= 0) {
        storeData = databaseDataHelper.InsertData(txtName.getText().toString(), editTitle.getText().toString());
    }

}

thanks i have checked your answer as useful, but i am feeling comfortable by using Abhishek's answer — Sophie, Mar 24 '15 at 07:51

Abhishek · Accepted Answer · 2015-03-24T07:46:28.883

1

EDIT - as SilentKiller said, this method will increase database overhead as we're making one extra call. The recommended method to solve this is by using constraints in the SQLite database, which is better IMHO.

in InsertDataActivity you are calling InsertData() without checking if the entry is already present. You should check whether the data is present or not, and then call InsertData() or UpdateData() accordingly.

Something like this should work.

  public boolean saveData() {
    String[] data = databaseDataHelper.SelectData(txtName.getText().toString());

    if (data != null) {
        storeData = databaseDataHelper.UpdateData(txtName.getText().toString(),
                editTitle.getText().toString());
    } else {
        storeData = databaseDataHelper.InsertData(txtName.getText().toString(),
                editTitle.getText().toString());
    }
}

edited Mar 24 '15 at 07:46

answered Mar 24 '15 at 07:18

Abhishek

473
3
12

by using your code it will increase one more action on database. – SilentKiller Mar 24 '15 at 07:24
where you want me to do change in my sqlite ? and secondly why i am getting .journal file (and how can i control on making of it ?) – Sophie Mar 24 '15 at 07:52
You can apply the UNIQUE constraint on the `ImageName` variable in the database. That will ensure that no two rows will have the same `ImageName`. No idea about the .journal thing, never encountered such a file. – Abhishek Mar 24 '15 at 07:55

Storing duplicate records in SQLite database table

2 Answers2