SQLiteDatabase.query 方法 [英] SQLiteDatabase.query method

问题描述

我使用的是 SQLiteDatabase 的查询方法.如何使用查询方法?

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

我试过了:

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

tableColumns - 列参数构造如下.

tableColumns - columns parameter is constructed as follows.

String[] columns = new String[]{KEY_ID, KEY_CONTENT};

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

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?

推荐答案

tableColumns

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

• 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

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

• 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

• 指定whereClause中每个?的内容，按照它们出现的顺序填充

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

其他人

• 就像whereClause关键字后的语句，或者null，如果你不使用它.

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

例子

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

相当于下面的原始查询

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

<小时>

通过使用 Where/Bind -Args 版本，您会自动获得转义值，并且您不必担心输入数据是否包含 '.

不安全:String whereClause = "column1='" + value + "'";
安全:String whereClause = "column1=?";

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

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

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

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 whereClause though)

这篇关于SQLiteDatabase.query 方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！