如何在 Android 的 SQlite 中使用准备好的语句？

How do I use prepared statements in SQlite in Android?

提问by pupeno

How do I use prepared statements in SQlite in Android?

如何在 Android 的 SQlite 中使用准备好的语句？

采纳答案by jasonhudgins

I use prepared statements in Android all the time, it's quite simple :

我一直在 Android 中使用准备好的语句，它很简单：

SQLiteDatabase db = dbHelper.getWritableDatabase();
SQLiteStatement stmt = db.compileStatement("INSERT INTO Country (code) VALUES (?)");
stmt.bindString(1, "US");
stmt.executeInsert();

回答by Suragch

For prepared SQLite statements in Android there is SQLiteStatement. Prepared statements help you speed up performance (especially for statements that need to be executed multiple times) and also help avoid against injection attacks. See this articlefor a general discussion on prepared statements.

对于 Android 中准备好的 SQLite 语句，有SQLiteStatement。准备好的语句可以帮助您提高性能（特别是对于需要多次执行的语句），还有助于避免注入攻击。有关准备好的语句的一般性讨论，请参阅本文。

SQLiteStatementis meant to be used with SQL statements that do not return multiple values. (That means you wouldn't use them for most queries.) Below are some examples:

SQLiteStatement旨在与不返回多个值的 SQL 语句一起使用。（这意味着您不会在大多数查询中使用它们。）以下是一些示例：

Create a table

创建一个表

String sql = "CREATE TABLE table_name (column_1 INTEGER PRIMARY KEY, column_2 TEXT)";
SQLiteStatement stmt = db.compileStatement(sql);
stmt.execute();

The execute()method does not return a value so it is appropriate to use with CREATE and DROP but not intended to be used with SELECT, INSERT, DELETE, and UPDATE because these return values. (But see this question.)

该execute()方法不返回值，因此适合与 CREATE 和 DROP 一起使用，但不打算与 SELECT、INSERT、DELETE 和 UPDATE 一起使用，因为这些返回值。（但看到这个问题。）

Insert values

插入值

String sql = "INSERT INTO table_name (column_1, column_2) VALUES (57, 'hello')";
SQLiteStatement statement = db.compileStatement(sql);
long rowId = statement.executeInsert();

Note that the executeInsert()method is used rather than execute(). Of course, you wouldn't want to always enter the same things in every row. For that you can use bindings.

请注意，executeInsert()使用的是该方法而不是execute()。当然，您不会希望总是在每一行中输入相同的内容。为此，您可以使用bindings。

String sql = "INSERT INTO table_name (column_1, column_2) VALUES (?, ?)";
SQLiteStatement statement = db.compileStatement(sql);

int intValue = 57;
String stringValue = "hello";

statement.bindLong(1, intValue); // 1-based: matches first '?' in sql string
statement.bindString(2, stringValue);  // matches second '?' in sql string

long rowId = statement.executeInsert();

Usually you use prepared statements when you want to quickly repeat something (like an INSERT) many times. The prepared statement makes it so that the SQL statement doesn't have to be parsed and compiled every time. You can speed things up even more by using transactions. This allows all the changes to be applied at once. Here is an example:

通常，当您想多次快速重复某事（如 INSERT）时，您会使用准备好的语句。准备好的语句使得 SQL 语句不必每次都被解析和编译。通过使用事务，您可以进一步加快处理速度。这允许一次应用所有更改。下面是一个例子：

String stringValue = "hello";
try {

    db.beginTransaction();
    String sql = "INSERT INTO table_name (column_1, column_2) VALUES (?, ?)";
    SQLiteStatement statement = db.compileStatement(sql);

    for (int i = 0; i < 1000; i++) {
        statement.clearBindings();
        statement.bindLong(1, i);
        statement.bindString(2, stringValue + i);
        statement.executeInsert();
    }

    db.setTransactionSuccessful(); // This commits the transaction if there were no exceptions

} catch (Exception e) {
    Log.w("Exception:", e);
} finally {
    db.endTransaction();
}

Check out these links for some more good info on transactions and speeding up database inserts.

查看这些链接以获取有关事务和加速数据库插入的更多好信息。

• Atomic Commit In SQLite(Great in depth explanation, go to Part 3)
• Database transactions
• Android SQLite bulk insert and update example
• Android SQLite Transaction Example with INSERT Prepared Statement
• Turbocharge your SQLite inserts on Android
• https://stackoverflow.com/a/8163179/3681880

• SQLite 中的原子提交（非常深入的解释，请转到第 3 部分）
• 数据库事务
• Android SQLite 批量插入和更新示例
• 带有 INSERT 准备好的语句的 Android SQLite 事务示例
• 在 Android 上增强您的 SQLite 插入
• https://stackoverflow.com/a/8163179/3681880

Update rows

更新行

This is a basic example. You can also apply the concepts from the section above.

这是一个基本的例子。您还可以应用上述部分中的概念。

String sql = "UPDATE table_name SET column_2=? WHERE column_1=?";
SQLiteStatement statement = db.compileStatement(sql);

int id = 7;
String stringValue = "hi there";

statement.bindString(1, stringValue);
statement.bindLong(2, id);

int numberOfRowsAffected = statement.executeUpdateDelete();

Delete rows

删除行

The executeUpdateDelete()method can also be used for DELETE statements and was introduced in API 11. See this Q&A.

该executeUpdateDelete()方法也可用于 DELETE 语句并在 API 11 中引入。请参阅此问答。

Here is an example.

这是一个例子。

try {

    db.beginTransaction();
    String sql = "DELETE FROM " + table_name +
            " WHERE " + column_1 + " = ?";
    SQLiteStatement statement = db.compileStatement(sql);

    for (Long id : words) {
        statement.clearBindings();
        statement.bindLong(1, id);
        statement.executeUpdateDelete();
    }

    db.setTransactionSuccessful();

} catch (SQLException e) {
    Log.w("Exception:", e);
} finally {
    db.endTransaction();
}

Query

询问

Normally when you run a query, you want to get a cursor back with lots of rows. That's not what SQLiteStatementis for, though. You don't run a query with it unless you only need a simple result, like the number of rows in the database, which you can do with simpleQueryForLong()

通常，当您运行查询时，您希望返回包含大量行的游标。不过，这不是SQLiteStatement为了什么。您不要使用它运行查询，除非您只需要一个简单的结果，例如数据库中的行数，您可以使用它simpleQueryForLong()

String sql = "SELECT COUNT(*) FROM table_name";
SQLiteStatement statement = db.compileStatement(sql);
long result = statement.simpleQueryForLong();

Usually you will run the query()method of SQLiteDatabaseto get a cursor.

通常你会运行SQLiteDatabase的query()方法来获取游标。

SQLiteDatabase db = dbHelper.getReadableDatabase();
String table = "table_name";
String[] columnsToReturn = { "column_1", "column_2" };
String selection = "column_1 =?";
String[] selectionArgs = { someValue }; // matched to "?" in selection
Cursor dbCursor = db.query(table, columnsToReturn, selection, selectionArgs, null, null, null);

See this answerfor better details about queries.

有关查询的更多详细信息，请参阅此答案。

回答by jbaez

If you want a cursor on return, then you might consider something like this:

如果你想要一个返回的游标，那么你可以考虑这样的事情：

SQLiteDatabase db = dbHelper.getWritableDatabase();

public Cursor fetchByCountryCode(String strCountryCode)
{
    /**
     * SELECT * FROM Country
     *      WHERE code = US
     */
    return cursor = db.query(true, 
        "Country",                        /**< Table name. */
        null,                             /**< All the fields that you want the 
                                                cursor to contain; null means all.*/
        "code=?",                         /**< WHERE statement without the WHERE clause. */
        new String[] { strCountryCode },    /**< Selection arguments. */
        null, null, null, null);
}

/** Fill a cursor with the results. */
Cursor c = fetchByCountryCode("US");

/** Retrieve data from the fields. */
String strCountryCode = c.getString(cursor.getColumnIndex("code"));

/** Assuming that you have a field/column with the name "country_name" */
String strCountryName = c.getString(cursor.getColumnIndex("country_name"));

See this snippet Genscriptsin case you want a more complete one. Note that this is a parameterized SQL query, so in essence, it's a prepared statement.

如果您想要更完整的代码，请参阅此片段Genscripts。请注意，这是一个参数化的 SQL 查询，因此本质上，它是一个准备好的语句。

回答by redfish64

jasonhudgins example won't work. You can't execute a query with stmt.execute()and get a value (or a Cursor) back.

jasonhudgins 的例子是行不通的。您无法执行查询stmt.execute()并Cursor返回值（或）。

You can only precompile statements that either returns no rows at all (such as an insert, or create table statement) or a single row and column, (and use simpleQueryForLong()or simpleQueryForString()).

您只能预编译根本不返回任何行（例如插入或创建表语句）或单行和单列（并使用simpleQueryForLong()或simpleQueryForString()）的语句。

回答by Aaron

To get a cursor, you can't use a compiledStatement. However, if you want to use a full prepared SQL statement, I recommend an adaptation of jbaez's method... Using db.rawQuery()instead of db.query().

要获得游标，您不能使用compiledStatement。但是，如果您想使用完整的准备好的 SQL 语句，我建议对 jbaez 的方法进行改编...使用db.rawQuery()代替db.query().