Android SQLiteDatabase.query 方法

SQLiteDatabase.query method

提问by sree_iphonedev

I am using the query method of SQLiteDatabase. How do I use the query method?

我正在使用 SQLiteDatabase 的查询方法。如何使用查询方法？

I tried this:

我试过这个：

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

tableColumns- columns parameter is constructed as follows.

tableColumns- columns 参数的构造如下。

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?

如果我们需要获取所有的字段，列参数应该如何构造。我们是否需要在字符串数组中包含所有字段名称？

How do I properly use the query method?

如何正确使用查询方法？

回答by zapl

tableColumns

表格列

• nullfor 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 maxholding the max value of column1

• null对于所有列，如 SELECT * FROM ...
• new String[] { "column1", "column2", ... }对于特定的列SELECT column1, column2 FROM ...- 你也可以在这里放置复杂的表达式：
  new String[] { "(SELECT max(column1) FROM table1) AS max" }会给你一个名为的列，其中max包含最大值column1

whereClause

where子句

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

• 您在WHERE没有该关键字的情况下放置的部分，例如"column1 > 5"
• 应该包括?动态的东西，例如"column1=?"-> 见whereArgs

whereArgs

其中参数

• specify the content that fills each ?in whereClausein the order they appear

• 指定填充每个内容?中whereClause的出现顺序

the others

其他

• just like whereClausethe statement after the keyword or nullif you don't use it.

• 就像whereClause关键字后面的语句一样，或者null如果您不使用它。

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 '.

通过使用 Where/Bind -Args 版本，您可以自动获得转义值，并且您不必担心 input-data 是否包含'.

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

不安全：String whereClause = "column1='" + value + "'";
安全：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:

因为如果 value 包含一个'你的语句，要么中断，要么出现异常，或者做一些意想不到的事情，例如，value = "XYZ'; DROP TABLE table1;--"甚至可能会删除你的表，因为该语句将变成两个语句和一个注释：

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 intand other primitives directly into whereClausethough)

使用 args 版本XYZ'; DROP TABLE table1;--将被转义，'XYZ''; DROP TABLE table1;--'并且只会被视为一个值。即使'它不是为了做坏事，人们在他们的名字中使用它或在文本、文件名、密码等中使用它仍然很常见。所以总是使用 args 版本。（虽然可以int直接构建和其他原语whereClause）

回答by Suragch

This is a more general answer meant to be a quick reference for future viewers.

这是一个更一般的答案，旨在为未来的观众提供快速参考。

Example

例子

SQLiteDatabase db = helper.getReadableDatabase();

String table = "table2";
String[] columns = {"column1", "column3"};
String selection = "column3 =?";
String[] selectionArgs = {"apple"};
String groupBy = null;
String having = null;
String orderBy = "column3 DESC";
String limit = "10";

Cursor cursor = db.query(table, columns, selection, selectionArgs, groupBy, having, orderBy, limit);

Explanation from the documentation

文档中的解释

• tableString: The table name to compile the query against.
• columnsString: A list of which columns to return. Passing null will return all columns, which is discouraged to prevent reading data from storage that isn't going to be used.
• selectionString: A filter declaring which rows to return, formatted as an SQL WHERE clause (excluding the WHERE itself). Passing null will return all rows for the given table.
• selectionArgsString: You may include ?s in selection, which will be replaced by the values from selectionArgs, in order that they appear in the selection. The values will be bound as Strings.
• groupByString: A filter declaring how to group rows, formatted as an SQL GROUP BY clause (excluding the GROUP BY itself). Passing null will cause the rows to not be grouped.
• havingString: A filter declare which row groups to include in the cursor, if row grouping is being used, formatted as an SQL HAVING clause (excluding the HAVING itself). Passing null will cause all row groups to be included, and is required when row grouping is not being used.
• orderByString: How to order the rows, formatted as an SQL ORDER BY clause (excluding the ORDER BY itself). Passing null will use the default sort order, which may be unordered.
• limitString: Limits the number of rows returned by the query, formatted as LIMIT clause. Passing null denotes no LIMIT clause.

• table字符串：要针对其编译查询的表名。
• columns字符串：要返回的列的列表。传递 null 将返回所有列，不鼓励这样做以防止从不会使用的存储中读取数据。
• selection字符串：声明要返回哪些行的过滤器，格式为 SQL WHERE 子句（不包括 WHERE 本身）。传递 null 将返回给定表的所有行。
• selectionArgs字符串：您可以在选择中包含 ?s，它将被来自 selectionArgs 的值替换，以便它们出现在选择中。这些值将绑定为字符串。
• groupBy字符串：声明如何对行进行分组的过滤器，格式为 SQL GROUP BY 子句（不包括 GROUP BY 本身）。传递 null 将导致行不被分组。
• having字符串：如果使用行分组，则过滤器声明要包含在游标中的行组，格式为 SQL HAVING 子句（不包括 HAVING 本身）。传递 null 将导致包含所有行组，并且在不使用行分组时需要。
• orderBy字符串：如何对行进行排序，格式为 SQL ORDER BY 子句（不包括 ORDER BY 本身）。传递 null 将使用默认排序顺序，该顺序可能是无序的。
• limit字符串：限制查询返回的行数，格式为 LIMIT 子句。传递 null 表示没有 LIMIT 子句。

回答by Ancantus

Where clause and args work together to form the WHERE statement of the SQL query. So say you looking to express

Where 子句和 args 共同构成 SQL 查询的 WHERE 语句。所以说你想表达

WHERE Column1 = 'value1' AND Column2 = 'value2'

Then your whereClause and whereArgs will be as follows

那么你的 whereClause 和 whereArgs 将如下

String whereClause = "Column1 =? AND Column2 =?";
String[] whereArgs = new String[]{"value1", "value2"};

If you want to select all table columns, i believe a null string passed to tableColumns will suffice.

如果要选择所有表列，我相信传递给 tableColumns 的空字符串就足够了。

回答by Puneet Verma

if your SQL query is like this

如果你的 SQL 查询是这样的

SELECT col-1, col-2 FROM tableName WHERE col-1=apple,col-2=mango
GROUPBY col-3 HAVING Count(col-4) > 5  ORDERBY col-2 DESC LIMIT 15;

Then for query() method, we can do as:-

然后对于 query() 方法，我们可以这样做：-

String table = "tableName";
String[] columns = {"col-1", "col-2"};
String selection = "col-1 =? AND col-2=?";       
String[] selectionArgs = {"apple","mango"};
String groupBy =col-3;
String having =" COUNT(col-4) > 5";
String orderBy = "col-2 DESC";
String limit = "15";

query(tableName, columns, selection, selectionArgs, groupBy, having, orderBy, limit);

回答by Avi Kumar Manku

db.query(
        TABLE_NAME,
        new String[] { TABLE_ROW_ID, TABLE_ROW_ONE, TABLE_ROW_TWO },
        TABLE_ROW_ID + "=" + rowID,
        null, null, null, null, null
);

TABLE_ROW_ID + "=" + rowID, here =is the whereclause. To select all values you will have to give all column names:

TABLE_ROW_ID + "=" + rowID，这里=是where条款。要选择所有值，您必须提供所有列名称：

or you can use a raw query like this 
db.rawQuery("SELECT * FROM permissions_table WHERE name = 'Comics' ", null);

and here is a good tutorialfor database.

这是一个很好的数据库教程。