Prevent repetitive value from being inserted into database android

Question

Here is my code:

    final String[] names_list = { "apple", "banana", "orange" };
    final int[] preview_list = { "R.drawable.apple", "R.drawable.banana", "R.drawable.orange" };
    final int[] colors_list = { "red", "yellow", "orange" };
    int random = (int) (Math.random() * 3);
    String name= names_list[random];
    int preview= preview_list[random];
    int color= colors_list[random];
    helper = new DatabaseHelper(getApplicationContext());
    helper.insertIntoDB(name, preview, color);

As it turns out ( int random = (int) (Math.random() * 3); ) code generates a random number from 1 to 3 ( depends on the count off the arrays in each list ) and then for example if variable random is 1 DatabaseHelper inserts first arrays of array list into the database ( "apple", "R.drawable.apple" and "red" ) whilst the array "apple" from names_list array list will be inserted as a primary unique value. My question is that what can I do to prevent my program insert repetitive data into database for example if variable random becomes 1 for the second time program ignore it and generate a new variable random number.

here is DatabaseModel:

public class DatabaseModel {
private String name;
private int preview;
private int color;

public String getName() {
    return name;
}
public void setName(String name) {
    this.name = name;
}
public int getPreview() {
    return preview;
}
public void setPreview(int preview) {
    this.preview = preview;
}
public int getColor() {
    return color;
}
public void setColor(int color) {
    this.color = color;
}

and DatabaseHelper

public class DatabaseHelper extends SQLiteOpenHelper {
private static final String DATABASE_NAME="fruits";
private static final int DATABASE_VERSION = 1;
private static final String FRUITS_TABLE = "fruitsreg";
private static final String FS_TABLE = "create table "+FRUITS_TABLE +"(name TEXT primary key,preview TEXT, color TEXT)";
Context context;
public DatabaseHelper(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
    this.context = context;
}
@Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL(FS_TABLE);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL("DROP TABLE IF EXISTS " + FRUITS_TABLE);
    // Create tables again
    onCreate(db);
}
/* Insert into database*/
public void insertIntoDB(String name, int preview, int color){
    Log.d("insert", "before insert");
    // 1. get reference to writable DB
    SQLiteDatabase db = this.getWritableDatabase();
    // 2. create ContentValues to add key "column"/value
    ContentValues values = new ContentValues();
    values.put("name", name);
    values.put("preview", String.valueOf(preview));
    values.put("color", color);
    // 3. insert
    db.insert(FRUITS_TABLE, null, values);
    // 4. close
    db.close();
    Toast.makeText(context, "insert value", Toast.LENGTH_LONG);
    Log.i("insert into DB", "After insert");
}
/* Retrive  data from database */
public List<DatabaseModel> getDataFromDB(){
    List<DatabaseModel> modelList = new ArrayList<DatabaseModel>();
    String query = "select * from "+FRUITS_TABLE;
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor cursor = db.rawQuery(query,null);
    if (cursor.moveToFirst()){
        do {
            DatabaseModel model = new DatabaseModel();
            model.setName(cursor.getString(0));
            model.setPreview(cursor.getInt(1));
            model.setColor(cursor.getInt(2));
            modelList.add(model);
        }while (cursor.moveToNext());
    }
    Log.d("Fruits database data", modelList.toString());
    return modelList;
}

im not if is a good solution but you can create a random number using a temporal list and the size. When you insert one item, then remove the item for the temporal list and repeat the process, now the size is 2 and finally 1. — Manuel Mato, Dec 28 '21 at 09:53
@Luca Pizzini hey friend I updated the post Can you help if you dont mind? — Abolfazl SM, Dec 28 '21 at 18:26

MikeT · Answer 1 · 2021-12-28T20:50:12.687

My question is that what can I do to prevent my program insert repetitive data into database for example if variable random becomes 1 for the second time program ignore it .

To check for the existence you would have to query the database to determine what does and doesn't exist. via a query such as

SELECT * FROM fruitsreg WHERE name = 'name';

However. I would suggest that instead you don't prevent the attempt to insert BUT instead check the result of the insert method. The insert method returns a long as per long the row ID of the newly inserted row, or -1 if an error occurred.

if an attempt is made to insert an existing PRIMARY KEY value then the error will result in -1 being returned.
You may question along the lines of but my primary value is a string or I haven't declared an Id column. SQLite unless a table is defined using the WITHOUT ROWID clause ALWAYS has a column name rowid which is a uniue integer value (1, then likely 2, then likely 3 .... ). It is this value that is returned or -1 if the row could not be inserted.

As such with a small change to your insertIntoDB method this can be utilised to detect if the row was inserted or not. Thus avoiding the need to query before inserting.

So perhaps consider :-

/* Insert into database*/
public long insertIntoDB(String name, int preview, int color) {
    Log.d("insert", "before insert");
    // 1. get reference to writable DB
    SQLiteDatabase db = this.getWritableDatabase();
    // 2. create ContentValues to add key "column"/value
    ContentValues values = new ContentValues();
    values.put("name", name);
    values.put("preview", String.valueOf(preview));
    values.put("color", color);
    // 3. insert
    long rv = db.insert(FRUITS_TABLE, null, values);
    // 4. close
    db.close();
    Toast.makeText(context, "insert value", Toast.LENGTH_LONG);
    Log.i("insert into DB", "After insert");
    return rv;
}

and generate a new variable random number

The above could then be utilised perhaps according to this adaptation of your code (in an activity for demonstration) :-

public class MainActivity extends AppCompatActivity {

    DatabaseHelper helper;
    final String[] names_list = { "apple", "banana", "orange" };
    final int[] preview_list = { /*"R.drawable.apple"*/ 100, /*"R.drawable.banana"*/ 200, /*"R.drawable.orange"*/ 300};
    final int[] colors_list = { /*"red"*/ 0xff0000, /*"yellow"*/ 0xFFFF0, /*"orange"*/ 0xFFa500};

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        helper = new DatabaseHelper(getApplicationContext());

        /* Try inserting 5 times */
        for(int i=0;i < 5; i++) {
            myCode();
        }

        for(DatabaseModel d: helper.getDataFromDB()) {
            Log.d("APPLOG","Name is " + d.getName() + " Preview is " + d.getPreview() + " Color is " + d.getColor() );
        }
    }

    private void myCode() {
        //Here is my code:
        //int random = (int) (Math.random() * 3);
        int random = (int) (Math.random() * names_list.length);
        int limit =0;
        while (
                helper.insertIntoDB(
                        names_list[random],
                        preview_list[random],
                        colors_list[random]
                )
                        < 0 /*( if insert is ok will be 1 or greater)*/
                        && limit++ < names_list.length /* limit the number of attempts */
        ) {
            random = (int) (Math.random() * names_list.length); /* next random value */
        }
        Log.d("APPLOG"," tried " + limit + " retires to insert.");
    }
}

note made up values for resource id's

So running the above, from a new install, there will be 5 attempts to insert the 3 possible rows as there are 3 colors.

There is no guarantee that all 3 rows will inserted as fewer may be inserted due to the attempt to use a random number which may be as before.

An example run could produce the following in the log:-

D/APPLOG:  tried 0 times to insert.
D/APPLOG:  tried 2 times to insert.
D/APPLOG:  tried 0 times to insert.
D/APPLOG:  tried 4 times to insert.
D/APPLOG:  tried 4 times to insert.
D/APPLOG: Name is orange Preview is 300 Color is 16753920
D/APPLOG: Name is banana Preview is 200 Color is 1048560
D/APPLOG: Name is apple Preview is 100 Color is 16711680

So

attempt 1 of the 5 a row was inserted at the first attempt (0 retries).
attempt 2 of the 5 a row was inserted at the third attempt (2 retries).
- so try 1 and try 2 both generated the same random number as was used in attempt 1
attempt 3 of the 5 a row was inserted at the first attempt (0 retries).
- so now all three rows have been inserted.
attempt 4 of the 5 did not insert a row before the limit of retries was exceeded.
attempt 5 of the 5 did not insert a row before the limit of retries was exceeded.

In addition to the above, using Android Studio's App Inspection the database is :-

Prevent repetitive value from being inserted into database android

1 Answers1