SQLite Query in Android for row counting

Question

I am trying to create a simple login form where I compare the login ID and password entered on the login screen with what is stored in the database.

I am using the following query:

final String DATABASE_COMPARE = "select count(*) from users where uname=" + loginname + "and pwd=" + loginpass + ");" ; 

The problem is that I do not know how I can fulfill the above request and save the returned invoice.

This is what the database table looks like (I managed to create the database successfully using the execSQl method)

 private static final String DATABASE_CREATE = "create table users (_id integer autoincrement, " + "name text not null, uname primary key text not null, " + "pwd text not null);";//+"phoneno text not null);"; 

Can someone kindly explain to me how I can achieve this? If possible, provide a sample snippet to complete the above task.

Answer 1

DatabaseUtils.queryNumEntries (since api: 11) is a useful alternative that negates raw SQL (yay!).

 SQLiteDatabase db = getReadableDatabase(); DatabaseUtils.queryNumEntries(db, "users", "uname=? AND pwd=?", new String[] {loginname,loginpass}); 

Answer 2

@scottyab parameterized DatabaseUtils.queryNumEntries (db, table, whereparams) exists in API 11 +, one without whereparams exists with API 1 . The answer would be to create a cursor with db.rawQuery:

 Cursor mCount= db.rawQuery("select count(*) from users where uname='" + loginname + "' and pwd='" + loginpass +"'", null); mCount.moveToFirst(); int count= mCount.getInt(0); mCount.close(); 

I also like @Dre's answer with a parameterized query.

Answer 3

Use SQLiteStatement .

eg.

  SQLiteStatement s = mDb.compileStatement( "select count(*) from users where uname='" + loginname + "' and pwd='" + loginpass + "'; " ); long count = s.simpleQueryForLong(); 

Answer 4

See rawQuery (String, String []) and the documentation for Cursor

Your DADABASE_COMPARE SQL statement is currently invalid, loginname and loginpass will not be escaped, there is no space between loginname and and , and you end the statement); instead; - If you logged in as bob with a password, this statement would end as

 select count(*) from users where uname=boband pwd=password); 

In addition, you should probably use the selectionArgs function, and not combine the login and login.

To use selectionArgs, you would do something like

 final String SQL_STATEMENT = "SELECT COUNT(*) FROM users WHERE uname=? AND pwd=?"; private void someMethod() { Cursor c = db.rawQuery(SQL_STATEMENT, new String[] { loginname, loginpass }); ... } 

Answer 5

Assuming you already have a database connection ( db ), I think the most elegant way is to stick with the Cursor class and do something like:

 String selection = "uname = ? AND pwd = ?"; String[] selectionArgs = {loginname, loginpass}; String tableName = "YourTable"; Cursor c = db.query(tableName, null, selection, selectionArgs, null, null, null); int result = c.getCount(); c.close(); return result; 

Answer 6

how to get the column counter

 final String DATABASE_COMPARE = "select count(*) from users where uname="+loginname+ "and pwd="+loginpass; int sometotal = (int) DatabaseUtils.longForQuery(db, DATABASE_COMPARE, null); 

This is the shortest and most accurate alternative. No need to handle cursors and their closure.

Answer 7

If you are using ContentProvider, you can use:

 Cursor cursor = getContentResolver().query(CONTENT_URI, new String[] {"count(*)"}, uname=" + loginname + " and pwd=" + loginpass, null, null); cursor.moveToFirst(); int count = cursor.getInt(0); 

Answer 8

If you want to get the number of records, you need to apply the group in any field or apply the following query.

how

 db.rawQuery("select count(field) as count_record from tablename where field =" + condition, null); 

Answer 9

Another way:

 myCursor.getCount(); 

on the cursor as:

 Cursor myCursor = db.query(table_Name, new String[] { row_Username }, row_Username + " =? AND " + row_Password + " =?", new String[] { entered_Password, entered_Password }, null, null, null); 

If you can think of getting away from a raw request.

Answer 10

 int nombr = 0; Cursor cursor = sqlDatabase.rawQuery("SELECT column FROM table WHERE column = Value", null); nombr = cursor.getCount(); 

Answer 11

 SQLiteDatabase sqLiteDatabase = this.getReadableDatabase(); int counts = (int) DatabaseUtils.longForQuery(sqLiteDatabase, "SELECT COUNT(*) FROM "+ TABLE_NAME, null); sqLiteDatabase.close();