CODEWITHSUNDEEP
javaandroidandroid-sqlite
Kitereative Indonesia
Kitereative Indonesia

Reputation: 1207

android - prevent duplicate insert data in SQLite

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

Upvotes: 1

Views: 3201

Answers (3)

Narayana Reddy
Narayana Reddy

Reputation: 1

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;

}

Upvotes: 0

John Joe
John Joe

Reputation: 12803

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;
  }

Upvotes: 3

Upendra Shah
Upendra Shah

Reputation: 2301

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

Upvotes: 0

Related Questions