Android의 SQlite에서 준비된 문을 어떻게 사용합니까?

Android의 SQlite에서 준비된 문을 어떻게 사용합니까?

---

Android의 SQlite에서 준비된 문을 어떻게 사용합니까?

---

저는 항상 Android에서 준비된 진술을 사용합니다. 매우 간단합니다.

SQLiteDatabase db = dbHelper.getWritableDatabase();
SQLiteStatement stmt = db.compileStatement("SELECT * FROM Country WHERE code = ?");
stmt.bindString(1, "US");
stmt.execute();

---

Android에서 준비된 SQLite 문에는 SQLiteStatement가 있습니다. 준비된 문은 성능 속도를 높이고 (특히 여러 번 실행해야하는 문) 주입 공격을 방지하는 데 도움이됩니다. 준비된 진술에 대한 일반적인 논의는 이 기사 를 참조하십시오 .

SQLiteStatement여러 값을 반환하지 않는 SQL 문과 함께 사용됩니다. (즉, 대부분의 쿼리에 사용하지 않을 것입니다.) 다음은 몇 가지 예입니다.

테이블 만들기

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

이 execute()메서드는 값을 반환하지 않으므로 CREATE 및 DROP과 함께 사용하는 것이 적절하지만 이러한 반환 값이 있으므로 SELECT, INSERT, DELETE 및 UPDATE와 함께 사용할 수 없습니다. (그러나이 질문을보십시오 .)

값 삽입

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

이 executeInsert()메서드가 대신 사용됩니다 execute(). 물론 모든 행에 항상 동일한 내용을 입력하고 싶지는 않을 것입니다. 이를 위해 바인딩 을 사용할 수 있습니다 .

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

일반적으로 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

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.

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 SQLiteStatement is 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()

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

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

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 answer for better details about queries.

---

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 Genscripts in case you want a more complete one. Note that this is a parameterized SQL query, so in essence, it's a prepared statement.

---

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

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()).

---

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().

참고URL : https://stackoverflow.com/questions/433392/how-do-i-use-prepared-statements-in-sqlite-in-android