android - prevent duplicate insert data in SQLite

Question

I just learned to use sqlite on android. how to prevent duplicate data when it will be inserted .. so, when there is same data entry, it will overwrite the data?

here I attach the code snippet:

@Override
    public void onCreate(SQLiteDatabase db) {
        String CREATE_FAVORIT_TABLE = "CREATE TABLE " + Constant.favoritDBtable + "("
                + Constant.id_postFav + " INTEGER PRIMARY KEY AUTOINCREMENT," + Constant.titleFav + " TEXT," + Constant.namaPerusahaanFav + " TEXT,"
                + Constant.lokasiFav + " TEXT," + Constant.kriteria_1Fav + " TEXT," + Constant.kriteria_2Fav
                + " TEXT," + Constant.kriteria_3Fav + " TEXT," + Constant.gajiFav + " TEXT," + Constant.img_logoFav
                + " TEXT," + Constant.tanggalFav + " TEXT);";

public String addFavorit(Favorit favorit){
        SQLiteDatabase db = this.getWritableDatabase();
//        long rows = 0;

        ContentValues values = new ContentValues();
        values.put(Constant.titleFav, favorit.getTitleFav());
        values.put(Constant.namaPerusahaanFav, favorit.getNamaPerusahaanFav());
        values.put(Constant.lokasiFav, favorit.getLokasiFav());
        values.put(Constant.kriteria_1Fav, favorit.getKriteria_1Fav());
        values.put(Constant.kriteria_2Fav, favorit.getKriteria_2Fav());
        values.put(Constant.kriteria_3Fav, favorit.getKriteria_3Fav());
        values.put(Constant.gajiFav, favorit.getGajiFav());
        values.put(Constant.img_logoFav, favorit.getImg_logoFav());
        values.put(Constant.tanggalFav, favorit.getTanggalFav());

      db.insert(Constant.favoritDBtable, null, values,);

Log.d("Favorit saved: ", "Success 200 OK");
        return null;
    }

MainActivity.java

@Override
    public void onClick(View v) {

        if (job.getTitle() != null && job.getLokasi() != null){
        saveToFavoritDB();
        }
    }

    private void saveToFavoritDB() {
        Favorit favorit = new Favorit();

        favorit.setTitleFav(job.getTitle());
        favorit.setGajiFav(job.getGaji());

        Log.d(TAG, "gaji " + job.getGaji());

        db.addFavorit(favorit);

        List<Favorit> favList = db.getAllFavorit();

        for (Favorit each : favList) {
            String log = "ID: " + each.getTitleFav() + ", Name: " + each.getLokasiFav() + ", Phone: " + each.getGajiFav();
            Log.d(TAG, "saveToFavoritDB: " + String.valueOf(db.getCountFavorit()));
            Log.d(TAG, "Hasil: " + log);
        }
    }

hope you can help me

when the same data goes into the favorite list. @VladMatvienko — Kitereative Indonesia
– Kitereative Indonesia, Commented Jan 25, 2018 at 13:16
how do you check that the data is the same? What would the if condition be? — Vladyslav Matviienko
– Vladyslav Matviienko, Commented Jan 25, 2018 at 13:17
I have tried it, but the incoming data is still duplicate @JohnJoe — Kitereative Indonesia
– Kitereative Indonesia, Commented Jan 25, 2018 at 13:26

John Joe · Accepted Answer · 2018-02-05 06:30:23Z

3

Before go through addFavorit method, you can add one method to check whether the data is already exists to prevent duplicate.

  boolean check;

  check = checkDuplicate(...,...,...,id_post); // check whether data exists
    if(check == true)  // if exists
    {
       Toast.makeText(MainActivity.this, " Data Already Exists", Toast.LENGTH_LONG).show();
    }else{
        db.addFavorit(favorit);
  }

  public static boolean checkDuplicate(String TableName,String dbfield, String fieldValue, int id_post) {
  String Query = ".... WHERE "+ Constant.id_postFav +"="+ id_post; // your query 
  Cursor cursor = db.rawQuery(Query, null);
      if(cursor.getCount() <= 0){
      cursor.close();
      return false;
     }
      cursor.close();
      return true;
  }

edited Feb 5, 2018 at 6:30

answered Jan 25, 2018 at 14:35

John Joe

12.8k19 gold badges79 silver badges142 bronze badges

Sign up to request clarification or add additional context in comments.

30 Comments

Kitereative Indonesia Over a year ago

Wow, thank you so much friends. this work, but there are little constraints. I have created a unique ID (primary key) in sqlite (id_favList) and took id_post other data on JSON. when I fill the fieldValue with getId_post from JSON and then when to enter to favList, the data is defined as "Data already exists". how to make the comparison is by comparing id_post. if id_post (from json) has been in sqlite then "Data already exists", if id_post is not there then i will add it into db.

John Joe Over a year ago

you mean id_post is same with id_favList?

Kitereative Indonesia Over a year ago

no, id_favlist it for sqlite with autoincrement key primary key. However, on this id_post I took from JSON

Kitereative Indonesia Over a year ago

so that determines whether or not I want id_post that I take from the json

4xMafole Over a year ago

@JohnJoe Amazing answer. You probably save me time for messing around in the internet.

|

Upendra Shah · Accepted Answer · 2018-01-25 14:20:21Z

0

Crate a function to check row is in db or not

private static boolean CheckIsInDBorNot(String titleFav) {
        String selectQuery = "SELECT  * FROM " + Constant.favoritDBtable + " WHERE " + Constant.titleFav +"'"+titleFav "'";
        final SQLiteDatabase db = open();
        Cursor cursor = db.rawQuery(selectQuery, null);
        if (cursor.getCount() <= 0) {
            cursor.close();
            return false;
        }
        cursor.close();
        return true;
    }

than check

if (!CheckIsInDBorNot(commentOrderId, commentId)) {
                db.insertOrThrow(Constant.favoritDBtable, null, cVal);
            }

It will check and insert

answered Jan 25, 2018 at 14:20

Upendra Shah

2,3511 gold badge20 silver badges31 bronze badges

2 Comments

Kitereative Indonesia Over a year ago

what is the purpose of open() function is this.getReadableDB() ?

Upendra Shah Over a year ago

it returns writable sqlitedatbase

sanastasiadis · Accepted Answer · 2018-06-18 13:24:25Z

0

Create method IsItemExist() in your DatabaseHelper class and call this method in you activity class like IsItemExist(name,mobile)

 public boolean IsItemExist(String name,String mobile) {
    try
    {
        SQLiteDatabase db=this.getReadableDatabase();
        Cursor cursor=db.rawQuery("SELECT "+NAME+" FROM "+TABLE+" WHERE "+NAME+"=?",new String[]{name});
        Cursor cursor1=db.rawQuery("SELECT "+MOBILE+" FROM "+TABLE+" WHERE "+MOBILE+"=?",new String[]{mobile});
        if (cursor.moveToFirst() && cursor1.moveToFirst())
        {
            db.close();
            Log.d("Record  Already Exists", "Table is:"+TABLE+" ColumnName:"+NAME);
            return true;//record Exists

        }
        Log.d("New Record  ", "Table is:"+TABLE+" ColumnName:"+NAME+" Column Value:"+NAME);
        db.close();
    }
    catch(Exception errorException)
    {
        Log.d("Exception occured", "Exception occured "+errorException);
        // db.close();
    }

        return false;

}

edited Jun 18, 2018 at 13:24

sanastasiadis

1,2001 gold badge16 silver badges24 bronze badges

answered Jun 18, 2018 at 12:44

Narayana Reddy

1

2 Comments

sanastasiadis Over a year ago

Please write a few words explaining your answer and how your approach works.

Narayana Reddy Over a year ago

Create method IsItemExist() in your DatabaseHelper class and call this method in you activity class like databaseHelper.IsItemExist(name,mobile)

Collectives™ on Stack Overflow

android - prevent duplicate insert data in SQLite

3 Answers 3

30 Comments

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

30 Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related