How to use prepared instructions in SQlite in Android?

Question

How to use prepared instructions in SQlite in Android?

Answer 1

I use ready-made statements in Android all the time, it's pretty simple:

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

Answer 2

For prepared SQLite statements in Android, there is a SQLiteStatement . Prepared statements help speed up work (especially for operators who need to be executed several times), and also help to avoid attack attacks. See this article for a general discussion of prepared statements.

SQLiteStatement intended for use with SQL statements that do not return multiple values. (This means that you will not use them for most queries.) The following are some examples:

Create 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 should be used with CREATE and DROP, but is not intended to be used with SELECT, INSERT, DELETE and UPDATE because they return values. (But see this question .)

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 executeInsert() , not execute() . Of course, you would not want to always enter the same things on each line. You can use bindings for this .

 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); // These match to the two question marks in the sql string statement.bindString(2, stringValue); long rowId = statement.executeInsert(); 

Usually you use prepared instructions when you want to quickly repeat something (for example, INSERT) many times. The prepared statement ensures that the SQL query does not need to be parsed and compiled every time. You can speed things up using transactions . This allows you to immediately apply all the changes. Here is an example:

 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 more transaction details and to speed up database insertion.

• Atomic Commit In SQLite (detailed explanation, go to part 3)
• database transactions
• An example of adding and updating Android SQLite
• Android SQLite transaction example with INSERT prepared statement
• Reload your SQLite inserts on Android
• stack overflow

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

Note: executeUpdateDelete() can also be used for DELETE statements and was introduced in API 11. See this & A question .

Query

Usually, when you run a query, you want to return a cursor with a lot of lines. However, this is not what SQLiteStatement . You do not run a query with it, unless you need a simple result, for example, the number of rows in the database that you can do with simpleQueryForLong()

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

Usually you run the query() SQLiteDatabase method to get the 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 more about queries.

Answer 3

If you want the cursor to return, you might think 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 Genscripts snippet for a more complete version. Please note that this is a parameterized SQL query, therefore, in fact, this is a prepared statement.

Answer 4

The jasonhudgins example will not work. You cannot execute a request using stmt.execute() and return a value (or Cursor ).

You can only precompile statements that either do not return any rows at all (for example, insert, or create a table statement), or a single row and column (and use simpleQueryForLong() or simpleQueryForString() ).

Answer 5

To get the cursor, you cannot use compiledStatement. However, if you want to use a fully prepared SQL statement, I recommend that you adapt the jbaez method ... Using db.rawQuery() instead of db.query() .