在 Android 中使用 SQLite 查询来计算行数

我正在尝试创建一个简单的 Login 表单，将在登录屏幕上输入的登录 ID 和密码与存储在数据库中的登录 ID 和密码进行比较。

我使用以下查询:

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

问题是，我不知道如何执行上面的查询并存储返回的计数。

下面是数据库表的外观(我已经成功地使用 ExecSQL 方法创建了数据库)

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);";

有没有人能好心地指导我如何做到这一点？如果可能的话，请提供一个示例片段来完成上述任务。

137544 次浏览
小开

If you want to get the count of records then you have to apply the group by on some field or apply the below query.

Like

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

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

Your DADABASE_COMPARE SQL statement is currently invalid, loginname and loginpass won't be escaped, there is no space between loginname and the and, and you end the statement with ); instead of ; -- If you were logging in as bob with the password of password, that statement would end up as

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

Also, you should probably use the selectionArgs feature, instead of concatenating loginname and loginpass.

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 });
...
}
小开

Use an SQLiteStatement.

e.g.

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


long count = s.simpleQueryForLong();
小开

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

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

@scottyab the parametrized DatabaseUtils.queryNumEntries(db, table, whereparams) exists at API 11 +, the one without the whereparams exists since API 1. The answer would have to be creating a Cursor with a 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 the parameterized query.

小开

how to get count column

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 most concise and precise alternative. No need to handle cursors and their closing.

小开

Assuming you already have a Database (db) connection established, I think the most elegant way is to stick to 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;
小开

Another way would be using:

myCursor.getCount();

on a Cursor like:

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 the raw query.

小开

If you are using ContentProvider then 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);
小开 
int nombr = 0;
Cursor cursor = sqlDatabase.rawQuery("SELECT column FROM table WHERE column = Value", null);
nombr = cursor.getCount();

提交答案