Duplicate data issue android sqlite

Question

I am creating an app in which people give scores to other persons. For that, I want to insert names of some person (say 5) to a database during installation of the app. Now the when I run the app first time everything goes fine. But when I run it second time, the data are inserted again making it 10, i.e. duplicate data. The problem is whenever I run this activity, it adds the data. What is the solution to this problem? Here's my code:

@TargetApi(Build.VERSION_CODES.HONEYCOMB)
     public class GamePlay extends ActionBarActivity {
     //Some code-----------

@Override
protected void onCreate(Bundle savedInstanceState) {

    super.onCreate(savedInstanceState);
    setContentView(R.layout.gameplay);
    //Some code----

    db=openOrCreateDatabase("MyGameData", MODE_PRIVATE, null);

    try{
        db.execSQL("create table teachers(_id integer primary key autoincrement , name varchar(30) , gender varchar(2) , score integer)");
        Toast.makeText(this, "Table created", Toast.LENGTH_SHORT).show();
    }

    catch(Exception e){
        Toast.makeText(this, "Exception 1", Toast.LENGTH_SHORT).show();
        Log.i("Create table", e.getMessage());
    }

    // Insert values in table-------


    String str_name[]={"arun", "dhanraj", "decoder", "sanjana"};
    String str_gender[]={"m", "m", "m", "f"};

    ContentValues cv = new ContentValues();

    for(int i=0; i<str_name.length; i++){

        cv.put("name", str_name[i]);
        cv.put("gender", str_gender[i]);
        db.insert("teachers", null, cv);

    }

   //Some code------

For sure. You shoud **UPDATE** the existing data, if already present. You only **INSERT** new records, duplicating the information in your db. — Phantômaxx, Dec 02 '14 at 21:52
@DerGolem : I want to insert new data during installation. No data is present at first. That's why I use INSERT. Updating is another process. So how should I insert the names and gender? — Decoder, Dec 02 '14 at 21:57
I'd do another query to see if user exists, then do what Der Golem says, and update the data. — Kristy Welsh, Dec 02 '14 at 22:02
You **could** also do **without** another query, and always use the **REPLACE INTO** command (although slower, because it always does a check if data is existing and decides whether to perform an INSERTion or an UPDATE). But that is the concept. You could also try to do an UPDATE and manage the returned error, this time performing an UPDATE. — Phantômaxx, Dec 02 '14 at 22:05
@KristyWelsh : Yeah this is a solution. But running query would be a long process for say if I have 100 names and genders to insert. It would take unnecessary time. What I want is to insert the data only once and it should not be inserted when I run it the next time. The update work is separate. I have no problem with that. Is there any other method that can be used? — Decoder, Dec 02 '14 at 22:12
@DerGolem : I think you are not getting what I am trying to do. Let me be clear. I am creating the app for people to give scores to some specific names. So I want to insert the names during installation only ONCE. Now after the names are given I want when the app is run second time, it should NOT enter the names again. The scores will be updated. I have done that part. Yes, checking OR REPLACE INTO is a solution, but it will make the app slow if the no. of names are increased say 100. So what's the solution? — Decoder, Dec 02 '14 at 22:26
I understood perfectly. The slution, as indicated by others, is to include your INSERTs in the onCreate event of your DBHelper class. Then only perform UPDATEs. — Phantômaxx, Dec 03 '14 at 08:01

score 2 · Answer 1 · answered Dec 02 '14 at 22:54

2

If I get your problem right, you could use a helper class and extend SQLiteOpenHelper.

In the onCreate method you can insert the needed data. This method is called only once.

Hope this helps.

answered Dec 02 '14 at 22:54

Hanelore Ianoseck

594
1
5
12

score 1 · Accepted Answer · answered Dec 03 '14 at 00:31

You should have a SQLiteOpenHelper class which contains some methods for database management and lifecycle like

onCreate(SQLiteDatabase db) // Called when the database is created for the first time.
onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) //Called when the database needs to be downgraded.
onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) // Called when the database needs to be upgraded.

And other ones, you can have a look here: http://developer.android.com/reference/android/database/sqlite/SQLiteOpenHelper.html

Following this tutorial should keep you on the good path about using this helper: http://developer.android.com/training/basics/data-storage/databases.html

My advice is to create some Querys like the ones that will populate and execute them after the creation of the Tables which should be there too. This way, your app will only make those inserts the first time, and keep them even if you update your database (if you don't state something else on the onUpgrade() method).

This should fix your problem.

Duplicate data issue android sqlite

2 Answers2

Linked