Continuous Incrementing of Rows

Question

I'm trying to build a Table of buttons in SQLite in android, everytime I open my app and looking at the DBBrowser the number of rows continuously incrementing, the total rows should be 47

//Menu.class
conn =  new SQLiteDatabase(this); 
        if(conn.setInitializeTableButtons()){
            Toast.makeText(getApplicationContext(), "DB READY", Toast.LENGTH_SHORT).show();
        }
        else {
            Toast.makeText(getApplicationContext(), "DB FAILED", Toast.LENGTH_SHORT).show();
        }

//SQLiteDatabase.class
   public boolean setInitializeTableButtons() {
        android.database.sqlite.SQLiteDatabase conn = this.getWritableDatabase();
        contentValues = new ContentValues();
        for (int i = 0; i < buttonNames.length; i++) {
            contentValues.put(BUTTON_NAME, buttonNames[i]);
            if (i == 0) {
                contentValues.put(IS_UNLOCK, 1);
            }
            else{
                contentValues.put(IS_UNLOCK, 0);
            }
            conn.insert(TABLE_BUTTONS, null, contentValues);
        }
        return true;
    }

Answer 1

The issue

A Database persists, i.e. it is saved as a file and any data added (inserted) will remain forever unless deleted.

So you are opening a database, if it doesn't exist it will be created, and then you add some data (47 rows).

The next time the app is run, you open it and add another 47 rows and so on.

How to Fix it

You need to do one of the following:-

• delete all the existing rows before adding the rows

  • this is relatively expensive resource wise.

• check if the data already exists and not add any rows

  • this isn't suitable (can be a little complicated) if the data to be added increases.

• have a schema, along with code that will not allow duplicate data.

  • this is perhaps the better/simplest option.

Suggested Fix So assuming that your table is something like :-

CREATE TABLE your_table (btnIds INTEGER PRIMARY KEY, btnNames TEXT, btnIsUnlock INTEGER);

Then you could make the btnNames column UNIQUE which means that a button name could not be duplicated. e.g.

CREATE TABLE your_table (btnIds INTEGER PRIMARY KEY, btnNames TEXT UNIQUE, btnIsUnlock INTEGER)

Typically a failure (conflict) would occur BUT the SQLiteDatabase insert method is actually INSERT OR IGNORE (i.e. it will trap and ignore the conflict).

So coding UNIQUE will allow the repeated insertions to run without adding duplicates BUT to add any new ones (according to name).

Suggested Improvement

You may wish to consider placing the loop within a transaction by using :-

//SQLiteDatabase.class
public boolean setInitializeTableButtons() {
    android.database.sqlite.SQLiteDatabase conn = this.getWritableDatabase();
    conn.beginTransaction();
    contentValues = new ContentValues();
    for (int i = 0; i < buttonNames.length; i++) {
        contentValues.put(BUTTON_NAME, buttonNames[i]);
        if (i == 0) {
            contentValues.put(IS_UNLOCK, 1);
        }
        else{
            contentValues.put(IS_UNLOCK, 0);
        }
        conn.insert(TABLE_BUTTONS, null, contentValues);
    }
    conn.setTransactionSuccessful();
    conn.endTransaction();
    return true;
 }

• https://developer.android.com/reference/android/database/sqlite/SQLiteDatabase#beginTransaction()
• https://developer.android.com/reference/android/database/sqlite/SQLiteDatabase#setTransactionSuccessful()
• https://developer.android.com/reference/android/database/sqlite/SQLiteDatabase#endTransaction()

Instead of writing to disk 47 times (a disk write for each insert), all inserts will be done and then written to disk (so just the 1 disk write). Writing to the disk is relatively resource hungry.