Android sqlite how to check if a record exists

Question

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

User · Answer

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 · Answer

Here s a simple solution based on a combination of what dipali and Piyush Gupta posted     public boolean dbHasData String searchTable  String searchColumn  String searchKey        String query    Select   from     searchTable     where     searchColumn               return getReadableDatabase   rawQuery query  new String   searchKey   moveToFirst

User · Answer

Raw queries are more vulnerable to SQL Injection  I will suggest using query   method instead   public boolean Exists String searchItem         String   columns     COLUMN NAME        String selection   COLUMN NAME              String   selectionArgs     searchItem        String limit    1        Cursor cursor   db query TABLE NAME  columns  selection  selectionArgs  null  null  null  limit       boolean exists    cursor getCount    gt  0       cursor close        return exists      Source  here

User · Answer

Code     private String   allPushColumns     MySQLiteHelper COLUMN PUSH ID          MySQLiteHelper COLUMN PUSH TITLE  MySQLiteHelper COLUMN PUSH CONTENT  MySQLiteHelper COLUMN PUSH TIME          MySQLiteHelper COLUMN PUSH TYPE  MySQLiteHelper COLUMN PUSH MSG ID     public boolean checkUniqueId String msg id        Cursor cursor   database query MySQLiteHelper TABLE PUSH              allPushColumns  MySQLiteHelper COLUMN PUSH MSG ID         new String      msg id    null  null  MySQLiteHelper COLUMN PUSH ID    DESC         if cursor getCount    lt   0           return false            return true

User · Answer

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    lt   0   cursorr close      cursor close

User · Answer

Try to use cursor isNull method  Example    song isFavorite   cursor isNull cursor getColumnIndex  favorite

User · Answer

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 · Answer

you can also see this   if  cursor moveToFirst         record exists   else      record not found     OR  You just check Cursor not null after that why you check count not 0   So  that you try this     DBHelper getReadableDatabase     Cursor mCursor   db rawQuery  SELECT   FROM     DATABASE TABLE     WHERE    yourKey   AND yourKey1     new String   keyValue keyvalue1     if  mCursor    null                return true     record exist       else           return false     record not exist

User · Answer

because of possible data leaks best solution via cursor    Cursor cursor   null      try             cursor         some query  raw or not your choice            return cursor moveToNext          finally           if  cursor    null                cursor close                      1  From API KITKAT u can use resources try     try  cursor      some query    2  if u query against VARCHAR TYPE use        eg  COLUMN NAME  string to search   3  dont use moveToFirst   is used when you need to start iterating from beggining  4  avoid getCount   is expensive - it iterates over many records to count them  It doesn t return a stored variable  There may be some caching on a second call  but the first call doesn t know the answer until it is counted

User · Answer

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 · Answer

SQLiteDatabase sqldb   MyProvider db  String Query    Select   from     TABLE NAME   Cursor cursor   sqldb rawQuery Query  null   cursor moveToLast      if you not place this cursor getCount   always give same integer  1  or current position of cursor   if cursor getCount   lt  0       Log v  tag   if 1   cursor getCount         return false    Log v  tag   2 else    cursor getCount      return true    if you not use cursor moveToLast       cursor getCount   always give same integer  1  or current position of cursor

User · Answer

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    lt   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 · Answer

I have tried all methods mentioned in this page  but only below method worked well for me   Cursor c db rawQuery  SELECT   FROM user WHERE idno    txtID getText        null   if c moveToFirst       showMessage  Error    Record exist      else       Inserting record

User · Answer

These are all good answers  however many forget to close the cursor and database  If you don t close the cursor or database you may run in to memory leaks   Additionally  You can get an error when searching by String that contains non alpha numeric characters  For example   1a5f9ea3-ec4b-406b-a567-e6927640db40   Those dashes  -  will cause an unrecognized token error  You can overcome this by putting the string in an array  So make it a habit to query like this    public boolean hasObject String id        SQLiteDatabase db   getWritableDatabase        String selectString    SELECT   FROM      TABLE     WHERE      ID                  Add the String you are searching by here          Put it in an array to avoid an unrecognized token error      Cursor cursor   db rawQuery selectString  new String    id          boolean hasObject   false      if cursor moveToFirst             hasObject   true             region if you had multiple records to check for  use this region            int count   0          while cursor moveToNext               count                        here  count is records found         Log d TAG  String format   d records found   count               endregion              cursor close                Dont forget to close your cursor     db close                   AND your Database      return hasObject

[android] Android sqlite how to check if a record exists

Examples related to android

Examples related to sqlite