使用查询字符串在 Android SQLiteDatabase 中执行插入/更新/删除的正确方法是什么? [英] What is the correct way to do inserts/updates/deletes in Android SQLiteDatabase using a query string?

问题描述

我一直在看官方文档(http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html) 和与 StackOverflow 帖子和实际观察到的行为的交叉引用，并且文档似乎具有误导性，并且可能在几个方面是错误的.

I've been looking at the official documentation (http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html) and cross-referencing with StackOverflow posts and actual observed behavior, and the documentation seems to be misleading and possibly just wrong in several respects.

我正在尝试执行参数化查询以插入/更新/删除，就像使用 rawQuery(String sql, String[] arguments) 进行选择一样. 我需要通过查询而不是使用 SQLiteDatabase 类上的插入/更新/删除方法，因为在许多情况下，查询跨越多个表，并且无论如何我们已经编写了大量用于跨所有平台的应用程序的查询(也编写用于 SQLite)，并且最好按原样使用它.

I am trying to perform parameterized queries to insert/update/delete, just as one might do for selecting with rawQuery(String sql, String[] arguments). I need to do it via a query rather than by using the insert/update/delete methods on the SQLiteDatabase class because in many cases the queries span multiple tables and in any case we have written a large set of queries that we use for this app across all our platforms (also written for SQLite), and it would be highly preferable to use it as-is.

以下是我觉得令人困惑/误导的内容:

Here's what I find confusing/misleading:

• 文档说对于 rawQuery 语句必须不以分号终止，但实际上似乎没有任何区别.这对我来说很重要，因为我们有一个巨大的 XML 文档，其中包含我在多个平台的应用中使用的查询，如果可能的话，我更愿意保持它相同.

• The doc says for rawQuery that statements must not be terminated by a semicolon, but it doesn't actually seem to make any difference. This matters to me because we have a huge XML document filled with queries I use in my app across several platforms and I'd prefer to keep it identical if possible.

rawQuery 似乎不适用于插入(是的，我试过使用和不使用分号).医生对此没有说什么.我确实看到它返回一个 Cursor，我想这可能是一个间接的暗示，它只适用于一个选择，但不一定——当它们不是结果集时，它可以简单地返回一个零长度或空 Cursor.

rawQuery doesn't seem to work for inserts (yes, I've tried with and without the semicolon). The doc don't say anything about this. I do see that it returns a Cursor, which I suppose could be an oblique hint that it only works with a select, but not necessarily -- it could simply return a zero-length or null Cursor when they was no result set.

execSQL(String sql, Object[] bindArgs) 明确表示它不与插入一起工作，但实际上它确实如此！

execSQL(String sql, Object[] bindArgs) explicitly says that it does not work with inserts, but in fact it does!

此外，尽管 execSQL(String, Object[]) 明确告诉您不要尝试 CRUD 操作，但其无参数版本不包含此类警告，并且也适用于该目的(使用不允许SQL参数的缺点).

Furthermore, although execSQL(String, Object[]) specifically tells you not to try CRUD operations, its parameterless version contains no such warning, and also works fine for that purpose (with the disadvantage of not allowing SQL parameters).

所有这一切的结果是，我能找到的成功执行带有参数化参数的插入的唯一方法是使用文档明确指示您不要使用的一种工作方法为此目的.

The result of all of this is that the only way I can find to successfully execute inserts with parametrized arguments is to use the one working method that the docs explicitly instruct you not to use for that purpose.

另外，rawQuery 不能用于插入/更新/删除真的很令人失望，因为我们有一个通用的数据层，如果我们可以使用一个统一的 API 调用来运行我们所有的 CRUD 会更优雅查询数据库.

Also, it's really a bummer that rawQuery won't work for inserts/updates/deletes, because we have a generalized data layer and it would be more elegant if we could use a single, unified API call to run all our CRUD queries on the database.

那么这里发生了什么?该文档是否已经过时了?可以用execSql做插入、更新等吗?有没有人成功地使用 rawQuery 进行插入?

So what is going on here? Is this documentation hopelessly out of date? Is it okay to use execSql to do inserts, updates, and so on? Has anyone succeeded in doing inserts with rawQuery?

附录:

针对操作系统运行:Android 4.3.

Running against OS: Android 4.3.

示例查询(什么有效，什么无效)

Sample queries (what's working, what's not)

// Works
db.execSql("INSERT into MyTable (Col1, Col2) VALUES (?, ?)", new String[] { "Foo", "Bar" });
db.execSql("INSERT into MyTable (Col1, Col2) VALUES (?, ?);", new String[] { "Foo", "Bar" });

// No exception thrown, but no changes made to the database
db.rawQuery("INSERT into MyTable (Col1, Col2) VALUES (?, ?)", new String[] { "Foo", "Bar" });
db.rawQuery("INSERT into MyTable (Col1, Col2) VALUES (?, ?);", new String[] { "Foo", "Bar" });

推荐答案

你说得对.文档令人困惑.总的来说，该设计试图为 sqlite3 C API 提供一个方便的 Java 包装器.在大多数情况下，如果您按照设计者的意图使用它，它就可以正常工作，例如使用方便的方法进行 CRUD 操作.但是他们还需要提供原始查询方法 execSQL() 和 rawQuery() 用于方便 CRUD 方法不够强大或根本不适用的情况(创建表等).这会导致抽象泄漏.

You are right. The documentation is confusing. Overall, the design attempts to provide a convenient Java wrapper around the sqlite3 C API. For most of the time it works fine if you use it the way the designers intended, e.g. using the convenience methods for CRUD operations. But they also needed to provide raw query methods execSQL() and rawQuery() for cases where the convenience CRUD methods are not powerful enough or not applicable at all (CREATE TABLE and so on). This causes leaky abstractions.

文档说对于 rawQuery 语句不能以分号结尾，但实际上似乎没有任何区别.这对我来说很重要，因为我们有一个巨大的 XML 文档，其中包含我在多个平台的应用中使用的查询，如果可能的话，我更愿意保持它相同.

The doc says for rawQuery that statements must not be terminated by a semicolon, but it doesn't actually seem to make any difference. This matters to me because we have a huge XML document filled with queries I use in my app across several platforms and I'd prefer to keep it identical if possible.

文档很糟糕.实际上，Android SQLiteDatabase 本身使用分号结尾的查询字符串调用rawQuery.

The docs are bad. In fact, Android SQLiteDatabase itself calls rawQuery with a semicolon-terminated query string.

rawQuery 似乎不适用于插入(是的，我试过使用和不使用分号).医生对此没有说什么.我确实看到它返回一个 Cursor，我想这可能是一个间接的暗示，它只适用于一个选择，但不一定——当它们不是结果集时，它可以简单地返回一个零长度或空 Cursor.

rawQuery doesn't seem to work for inserts (yes, I've tried with and without the semicolon). The doc don't say anything about this. I do see that it returns a Cursor, which I suppose could be an oblique hint that it only works with a select, but not necessarily -- it could simply return a zero-length or null Cursor when they was no result set.

它确实有效，但您需要了解它在本机级别是如何工作的.

It does work but you'll need to understand how it works on native level.

将 execSQL() 视为 sqlite3_exec() 运行查询并返回成功或错误代码.

Think of execSQL() as sqlite3_exec() that runs the query and returns either success or an error code.

将 rawQuery() 视为 sqlite3_prepare() 编译查询但尚未运行它.要实际运行它，请使用 Cursor 上的 moveTo...() 方法之一.将此视为 sqlite3_step().将任何 rawQuery() 与 moveTo...() 结合实际上会改变数据库.

Think of rawQuery() as sqlite3_prepare() that compiles the query but does not run it yet. To actually run it, use one of the moveTo...() methods on the Cursor. Think of this as sqlite3_step(). Combining any rawQuery() with moveTo...() will actually alter the database.

execSQL(String sql, Object[] bindArgs) 明确表示它不适用于选择，但实际上它适用！

execSQL(String sql, Object[] bindArgs) explicitly says that it does not work with selects, but in fact it does!

此外，尽管 execSQL(String, Object[]) 明确告诉您不要尝试 CRUD 操作，但它的无参数版本不包含此类警告，并且也适用于该目的(缺点是不允许使用 SQL 参数).

Furthermore, although execSQL(String, Object[]) specifically tells you not to try CRUD operations, its parameterless version contains no such warning, and also works fine for that purpose (with the disadvantage of not allowing SQL parameters).

它适用于所有 CRUD 操作.对于 CRUD 的 R 读取部分，根本无法获取所选数据.

It works fine for all CRUD operations. For the R read part of CRUD, there's just no way to get the selected data.

这篇关于使用查询字符串在 Android SQLiteDatabase 中执行插入/更新/删除的正确方法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！