CODEWITHSUNDEEP
androidsqlite
BaSSa
BaSSa

Reputation: 53

Check duplicate's before inserting data in SQLite database

Im inserting 3 values into a database on a click of a button, 1 is the insert date, 2 is a number from a editText and the 3rd is the value assigned to the button being clicked(there are six different buttons that can be clicked)

What I want to do is check if the date and number are already in the database the 3rd value does not matter. I cant use unique on any of the columns as I want the number from the editText to be able to be entered again but just not on the same day.

I have got this far... and it does not enter any data to database

From MainActivity

    public void onClick_30(View v) {

    counter++;
    cnt_bcs30++;

    String getCowNum = CowNum.getText().toString();

    Cursor C = myDB.dubUpCheck();
    String ddate = C.getString(DBAdapter.COL_DATE);
    String cow = C.getString(DBAdapter.COL_COW);


    if(C.moveToFirst()){

        do {

            if(ddate == dateFormat.format(date) && cow == getCowNum){
        Toast.makeText(getBaseContext(), getCowNum + "Has already been scored today ",
                Toast.LENGTH_SHORT).show();
            }
        }while (C.moveToNext());


    }else{



    myDB.insertRow(dateFormat.format(date), getCowNum, 3.0);

    Toast.makeText(getBaseContext(), getCowNum + " " + score30,
            Toast.LENGTH_SHORT).show();
    CowNum.clearFocus();
    }
}

From my DBAdapter

            public Cursor dubUpCheck() {

    Cursor c = db.query(DATABASE_TABLE, new String []{KEY_DATE, KEY_COW}, null, null,
            null, null, null, null);
    if (c != null) {
        c.moveToFirst();
    }

    return c;

}

I need to check if there is a row with dateFormat.format(date) and getCowNum but i cant seem to get it to work, not sure if it is due to the way I am storing the date which is a string in the format of dd-MM-yyyy

    SimpleDateFormat dateFormat = new SimpleDateFormat("dd-MM-yyyy");
    Date date = new Date();

like this, enters data but still allows dupicates

    public void onClick_30(View v) {

    counter++;
    cnt_bcs30++;

    String getCowNum = CowNum.getText().toString();

    Cursor C = myDB.dubUpCheck();
    String ddate = C.getString(DBAdapter.COL_DATE);
    String cow = C.getString(DBAdapter.COL_COW);


    if(ddate == dateFormat.format(date) && cow == getCowNum){
        Toast.makeText(getBaseContext(), getCowNum + "Has already been scored today ",
                Toast.LENGTH_SHORT).show();
        }else{



    myDB.insertRow(dateFormat.format(date), getCowNum, 3.0);

    Toast.makeText(getBaseContext(), getCowNum + " " + score30,
            Toast.LENGTH_SHORT).show();
    CowNum.clearFocus();
    }

UPD: Another attempt... the else statement is in the wrong place I know but thats not all thats wrong Im guessing

        public void onClick_30(View v) {

    counter++;
    cnt_bcs30++;

    String getCowNum = CowNum.getText().toString();

    Cursor C = myDB.dubUpCheck();
    String ddate = C.getString(DBAdapter.COL_DATE);
    String cow = C.getString(DBAdapter.COL_COW);

    if (C.moveToFirst()) {
        do {
            if (ddate.equals(dateFormat.format(date)) && cow.equals(getCowNum)) {

                Toast.makeText(getBaseContext(),
                        getCowNum + "Has already been scored today ",
                        Toast.LENGTH_SHORT).show();

            }

        } while (C.moveToNext());
    } else {

        myDB.insertRow(dateFormat.format(date), getCowNum, 3.0);

        Toast.makeText(getBaseContext(), getCowNum + " " + score30,
                Toast.LENGTH_SHORT).show();
        CowNum.clearFocus();
    }
}

Upvotes: 1

Views: 3785

Answers (1)

Ivan Bartsov
Ivan Bartsov

Reputation: 21066

Why not just a simple WHERE?

/** @return true if given date/cowNum combination is already in the db, false otherwise */
public boolean dubUpCheck(Date date, String cowNum) {
  /* The 3rd parameter is treated as SQL WHERE clause, ? are replaced by strings
   * from the 4th parameter */
  Cursor cur = db.query(DATABASE_TABLE, null, "DATE = ? AND COW_NUM = ?", new String[] {dateFormat.format(date), getCowNum}, null, null, null, null);
  if (cur != null && cur.getCount()>0) {
      // duplicate found
      return true;
  }
  return false;
}

UPD In your approach where you manually compare strings you've made a classic Java rookie mistake. You're comparing String objects using the operator == : it will return false for different [in terms of instances] objects that might actually be identical content-wise (e.g. this "foo" == new String("foo") will give you false). You have to use .equals() like so:

ddate.equals(dateFormat.format(date)) && cow.equals(getCowNum)

But really, try the approach above, it creates less objects and will spare you all the string comparison stuff.

It seems you could benefit from reading a good book on Java -- e.g. Thinking in Java by Bruce Eckel is hands down one of the most awesome reads for both novice and intermediate Java devs

Upvotes: 1

Related Questions