Android - SQlite check if value in row exists

Question

This question is similar to this and this.

I have 4 columns in my database (id, name, surname, image). I only want to check if the name that is being entered already exists in "name" column.

here is my SQLiteOpenHelper:

public class SQLiteHelper extends SQLiteOpenHelper {


public SQLiteHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
    super(context, name, factory, version);
}

public void queryData(String sql){
    SQLiteDatabase database = getWritableDatabase();
    database.execSQL(sql);
}

@Override
public void onCreate(SQLiteDatabase sqLiteDatabase) {

}

public void insertData(String name, String surname, byte[] image){
    SQLiteDatabase database = getWritableDatabase();
    String sql = "INSERT INTO STUDENTS VALUES (NULL, ?, ?, ?)";

    SQLiteStatement statement = database.compileStatement(sql);
    statement.clearBindings();

    statement.bindString(1, name);
    statement.bindString(2, surname);
    statement.bindBlob(3, image);

    statement.executeInsert();

}

@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {

}

/*public void removeSingleContact(String name, String price, byte[] image) {

    SQLiteDatabase dbb = getWritableDatabase();
    dbb.delete(name, surname, image);

}*/

public Cursor getData(String sql){
    SQLiteDatabase database = getReadableDatabase();
    return database.rawQuery(sql, null);
}

}

and this is how I add to SQlite;

public class AddEditStudent extends AppCompatActivity {

    public static SQLiteHelper sqLiteHelper;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_addedit_student);

        sqLiteHelper = new SQLiteHelper(this, "StudentsDB.sqlite", null, 1);

        sqLiteHelper.queryData("CREATE TABLE IF NOT EXISTS STUDENTS(Id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR, surname VARCHAR, image BLOB)");


            btnAdd.setOnClickListener(new View.OnClickListener() {

                    @Override
                    public void onClick(View view) {

                        if (edtName.getText().toString().trim().length() == 0) {
                            Toast.makeText(getApplicationContext(), "Please provide your students name", Toast.LENGTH_SHORT).show();

                        } else
                            try {

                                    //THIS IS WHERE I WANT TO CHECK---------

                                    sqLiteHelper.insertData(
                                            edtName.getText().toString().trim(),
                                            edtPrice.getText().toString().trim(),
                                            imageViewToByte(imageView)

                                    );

                                        Toast.makeText(getApplicationContext(), "Added successfully!", Toast.LENGTH_SHORT).show();

                                        finish();
                                    }

                                }
                            } catch (Exception e) {
                            e.printStackTrace();
                            }

                    }
                });

EDIT---- Thank you for the help so far. I have 2 buttons, one for update and one for insert, the problem I am facing is how will I know what row to update without using an ID. I have a gridview that is being populated by cardviews, in the cardview there is a button to edit info, please see image.

https://i.stack.imgur.com/aFJTl.png

If I click on the exit sign I do;

holder.dit.setOnClickListener(new View.OnClickListener() {

            @Override
            public void onClick(View v) {
                // Do something
                Intent editintent = new Intent(context, AddEditStudent.class);
                editintent.putExtra("studentname", finalHolder.txtName.getText().toString());
                editintent.putExtra("studentsurname", finalHolder.txtPrice.getText().toString());

                context.startActivity(editintent);
            }
        });

then in AddEditStudent Class is where the insert and update is.

@TimBiegeleisen Display a toast saying student already exists — , Jun 08 '17 at 08:53

score 3 · Accepted Answer · answered Jun 08 '17 at 09:48

3

Update you Insert Query

    String name = edtName.getText().toString().trim();
        String query = "Select * From STUDENTS where name = '"+name+"'";
        if(sqLiteHelper.getData(query).getCount()>0){
            Toast.makeText(getApplicationContext(), "Already Exist!", Toast.LENGTH_SHORT).show();
        }else{
            sqLiteHelper.insertData(
                    name,
                    edtPrice.getText().toString().trim(),
                    imageViewToByte(imageView)

            );
Toast.makeText(getApplicationContext(), "Added successfully!", Toast.LENGTH_SHORT).show();

        }

answered Jun 08 '17 at 09:48

Aniruddh Parihar

3,072
3
21
39

I would also want to update an existing, could you please help me with that as well? or should I create a new question? – Jun 08 '17 at 10:06
apply Update query for it, by using where Clause with name = 'yourName' under if statement. – Aniruddh Parihar Jun 08 '17 at 10:10
Can you please update your answer, sorry I'm new to sqlite – Jun 08 '17 at 10:11
yeah, sure but you have to wait for some time. – Aniruddh Parihar Jun 08 '17 at 10:12
I am still waiting – Jun 08 '17 at 12:02
now see my new answer, follow it and let me know if you face any problem with this. – Aniruddh Parihar Jun 08 '17 at 12:27
You changed it again? – Jun 08 '17 at 12:49
I think I will just ask a new question – Jun 08 '17 at 12:53
no not needed...you can again manage it with voteup and Flag i will not mind. – Aniruddh Parihar Jun 08 '17 at 12:58

Tim Biegeleisen · Answer 2 · 2017-06-08T09:04:53.127

One approach would be to let a unique constraint on the name column handle the possibility of duplicate names. Then, attemtps to insert a duplicate name would fail at the database level.

CREATE TABLE IF NOT EXISTS STUDENTS (
    Id INTEGER PRIMARY KEY AUTOINCREMENT,
    name VARCHAR,
    surname VARCHAR,
    image BLOB,
    CONSTRAINT constraint_name UNIQUE (name)
)

Note that it is not possible to add a unique constraint to an already existing table. Instead, you will have to create a new one.

From the documentation for SQLiteStatement#executeInsert(), this method returns:

the row ID of the last row inserted, if this insert is successful. -1 otherwise.

In other words, with a unique constraint on the name column, this call to insert data should return -1. You can update your code to check this return value and act accordingly (you are not currently checking the return value at all).

Can you please elaborate on how I can implement this in my code? — , Jun 08 '17 at 08:59
@NewUser After adding the unique constraint, you can check for failure of the insert. It would not be efficient for me to refactor your entire code here, but my answer at least gives you one option. — Tim Biegeleisen, Jun 08 '17 at 09:06

score 0 · Answer 3 · answered Jun 08 '17 at 12:25

As you Requested me to Help for Update of that record which Exist the process will be follow

First of all add this Method for update in class SQLiteHelper

public void updateData(ContentValues contentValues, String whereClause, String[] whereArgs){
    SQLiteDatabase database = getReadableDatabase();
    database.update(DATABASE_TABLE, contentValues, whereClause, whereArgs);
}

Now Apply Changes in your If else Statement

String name = edtName.getText().toString().trim();
    String query = "Select * From STUDENTS where name = '"+name+"'";
    if(sqLiteHelper.getData(query).getCount()>0){
        Toast.makeText(getApplicationContext(), "Already Exist!", Toast.LENGTH_SHORT).show();
        ContentValues contentValues = new ContentValues();
        contentValues.put("name", name);
        contentValues.put("surname", edtPrice.getText().toString().trim());
        contentValues.put("image", imageViewToByte(imageView));

        String whereClause = "name=?";
        String[] whereArgs = new String[] {"'"+name+"'"};

        sqLiteHelper.updateData(contentValues, whereClause, whereArgs);
    }else{
        sqLiteHelper.insertData(
                name,
                edtPrice.getText().toString().trim(),
                imageViewToByte(imageView)

        );
    }

score -2 · Answer 4 · answered Jun 08 '17 at 09:30

i think this will help you.

boolean isAdded(String name) {
    SQLiteDatabase sqLiteDatabase = this.getReadableDatabase();
    String query="SELECT * FROM "+TABLE_NAME+" WHERE "+NAME+" = "+name;
    Cursor cursor = sqLiteDatabase.rawQuery(query,null);
    if(cursor.getCount()>0)
        return true;
    else
        return false;
}

Android - SQlite check if value in row exists

4 Answers4