SQLIteDatabase.query method

最新推荐文章于 2022-12-19 19:18:05 发布

转载最新推荐文章于 2022-12-19 19:18:05 发布 · 1.8k 阅读

sql 专栏收录该内容

9 篇文章

订阅专栏

本文详细解释了SQLiteDatabase中的query方法参数使用，包括tableColumns、whereClause、whereArgs的构造方式，通过示例代码帮助理解如何正确应用这些参数。

SQLIteDatabase.query method

up vote 7 down vote favorite

I am using the query method of SQLitDatabase. I am little bit confused about the parameters using in "query" method.

Cursor cursor = sqLiteDatabase.query(tableName, tableColumns, whereClause, whereArgs, groupBy, having, orderBy);

tableColumns - columns parameter is constructed as follows.
String[] columns = new String[]{KEY_ID, KEY_CONTENT};

If we need to get all the fields, how should the column parameter to be constructed. Do we need to include all the Field Names in String array?

whereClause - Confused about the construction of this parameter.

whereArgs - Confused about the construction of this parameter.

Can anybody help me

thanks in advance.

asked May 15 '12 at 12:29

sree_iphonedev
198 4 17

	Try a simple approach like this – Imran Rana May 15 '12 at 12:34
	I know that method. But I am trying to learn how to implement query method instead of rawQuery. – sree_iphonedev May 15 '12 at 12:40

4 Answers

active oldest votes

up vote 15 down vote accepted

tableColumns

null for all columns as in SELECT * FROM ...
new String[] { "column1", "column2", ... } for specific columns as in SELECT column1, column2 FROM ... - you can also put complex expressions here:
new String[] { "(SELECT max(column1) FROM table1) AS max" } would give you a column named max holding the max value of column1

whereClause

the part you put after WHERE without that keyword, e.g. "column1 > 5"
should include ? for things that are dynamic, e.g. "column1=?" -> see whereArgs

whereArgs

specify the content that fills each ? in whereClause in the order they appear

the others

just like whereClause the statement after the keyword or null if you don't use it.

Example

String[] tableColumns = new String[] {
    "column1",
    "(SELECT max(column1) FROM table2) AS max"
};
String whereClause = "column1 = ? OR column1 = ?";
String[] whereArgs = new String[] {
    "value1",
    "value2"
};
String orderBy = "column1";
Cursor c = sqLiteDatabase.query("table1", tableColumns, whereClause, whereArgs,
        null, null, orderBy);

// since we have a named column we can do
int idx = c.getColumnIndex("max");

is equivalent to the following raw query

String queryString =
    "SELECT column1, (SELECT max(column1) FROM table1) AS max FROM table1 " +
    "WHERE column1 = ? OR column1 = ? ORDER BY column1";
sqLiteDatabase.rawQuery(queryString, whereArgs);

By using the Where/Bind -Args version you get automatically escaped values and you don't have to worry if input-data contains '.

Unsafe: String whereClause = "column1='" + value + "'";
Safe: String whereClause = "column1=?";

because if value contains a ' your statement either breaks and you get exceptions or does unintended things, for example value = "XYZ'; DROP TABLE table1;--" might even drop your table since the statement would become two statements and a comment:

SELECT * FROM table1 where column1='XYZ'; DROP TABLE table1;--'

using the args version XYZ'; DROP TABLE table1;-- would be escaped to 'XYZ''; DROP TABLE table1;--' and would only be treated as a value. Even if the ' is not intended to do bad things it is still quite common that people have it in their names or use it in texts, filenames, passwords etc. So always use the args version. (It is okay to build int and other primitives directly into whereClausethough)