Android sqlite how to check if a record exists

user_1912165 0
Viewed 129901

I would like to check whether a record exists or not.

Here is what I've tried:

MainActivity.class

    public void onTextChanged(CharSequence s, int start, int before, int count) {

        System.out.println("Ontext changed " + new String(s.toString()));
        strDocumentFrom = s.toString();         

        if(s.toString().isEmpty()){

        } else {

             try{
                 strTransactionDate = dbHelper.getTransactionDateByDocumentNumber(strDocumentFrom);
                 //strTotalAmount = dbHelper.getTotalAmountByDocumentNumber(strDocumentFrom);
                 //strVan = dbHelper.getVanByDocumentNumber(strDocumentFrom);


                 //etTransactionDate.setText(strTransactionDate);
                 //etTotalAmount.setText(strTotalAmount);
                 //Log.d("Van", "" + strVan);
                 //etVan.setText(strVan);

             } catch (SQLiteException e) {
                 e.printStackTrace();
                 Toast.makeText(ReceivingStocksHeader.this, 
                         "Document number does not exist.", Toast.LENGTH_SHORT).show();
             }

        }

DBHelper.class

            // TODO DISPLAYING RECORDS TO TRANSRCVHEADER
        public String getTransactionDateByDocumentNumber(String strDocumentNumber){
            String[] columns = new String[]{KEY_TRANSACTIONDATE};

            Cursor c = myDataBase.query(TBL_INTRANS, 
                    columns, null, 
                    null, null, null, null, null);

            if(c != null){
                c.moveToFirst();
                String date = c.getString(0);
                return date;
            } else {
                Log.d("Error", "No record exists");
            }


            return null;
        }

But it doesn't get it to the catch block to display the toast.

What am I doing wrong in here?

sqlite android
14 Answers
public static boolean CheckIsDataAlreadyInDBorNot(String TableName,
        String dbfield, String fieldValue) {
    SQLiteDatabase sqldb = EGLifeStyleApplication.sqLiteDatabase;
    String Query = "Select * from " + TableName + " where " + dbfield + " = " + fieldValue;
    Cursor cursor = sqldb.rawQuery(Query, null);
        if(cursor.getCount() <= 0){
            cursor.close();
            return false;
        }
    cursor.close();
    return true;
}

I hope this is useful to you... This function returns true if record already exists in db. Otherwise returns false.

user_12232910

SELECT EXISTS with LIMIT 1 is much faster.

Query Ex: SELECT EXISTS (SELECT * FROM table_name WHERE column='value' LIMIT 1);

Code Ex:

public boolean columnExists(String value) {
    String sql = "SELECT EXISTS (SELECT * FROM table_name WHERE column='"+value+"' LIMIT 1)";
    Cursor cursor = database.rawQuery(sql, null);
    cursor.moveToFirst();

    // cursor.getInt(0) is 1 if column with value exists
    if (cursor.getInt(0) == 1) { 
        cursor.close();
        return true;
    } else {
        cursor.close();
        return false;
    }
}
user_50452010

You can use SELECT EXISTS command and execute it for a cursor using a rawQuery, from the documentation

The EXISTS operator always evaluates to one of the integer values 0 and 1. If executing the SELECT statement specified as the right-hand operand of the EXISTS operator would return one or more rows, then the EXISTS operator evaluates to 1. If executing the SELECT would return no rows at all, then the EXISTS operator evaluates to 0.

user_26368870

One thing the top voted answer did not mention was that you need single quotes, 'like this', around your search value if it is a text value like so:

public boolean checkIfMyTitleExists(String title) {
    String Query = "Select * from " + TABLE_NAME + " where " + COL1 + " = " + "'" + title + "'";
    Cursor cursor = database.rawQuery(Query, null);
    if(cursor.getCount() <= 0){
        cursor.close();
        return false;
    }
    cursor.close();

    return true;
}

Otherwise, you will get a "SQL(query) error or missing database" error like I did without the single quotes around the title field.

If it is a numeric value, it does not need single quotes.

Refer to this SQL post for more details

user_46561380

You can use like this:

String Query = "Select * from " + TABLE_NAME + " where " + Cust_id + " = " + cust_no;

Cursor cursorr = db.rawQuery(Query, null);
if(cursor.getCount() <= 0){
cursorr.close();
}
cursor.close();
user_45295880 
private boolean checkDataExistOrNot(String columnName, String value) {
    SQLiteDatabase sqLiteDatabase = getReadableDatabase();
    String query = "SELECT * FROM" + TABLE_NAME + " WHERE " + columnName + " = " + value;
    Cursor cursor = sqLiteDatabase.rawQuery(query, null);
    if (cursor.getCount() <= 0) {
        cursor.close();
        return false;  // return false if value not exists in database
    }
    cursor.close();
    return true;  // return true if value exists in database
}
user_128335510
Related