How to bind values to SQLiteStatement for insert query?

Question

Insertion code using SQLiteStatement usually looks like this,

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

int intValue = 57;
String stringValue1 = "hello";
String stringValue2 = "world";   
// corresponding to each question mark in the query
statement.bindLong(1, intValue); 
statement.bindString(2, stringValue1); 
statement.bindString(3, stringValue2);

long rowId = statement.executeInsert();

Now this works perfectly fine but the issue I find here is that I have to be very careful about binding correct data to corresponding indexes. A simple swap of index will give me an error.

Also let's say in future my column_2 gets dropped from the table, then I would have to change all the indexes after the column_2 index otherwise the statement won't work. This seems trivial if I just have 3 columns. Imagine if a table has 10-12 (or even more) columns and column 2 gets dropped. I'll have to update the index of all the subsequent columns. This whole process seems inefficient and error prone.

Is there an elegant way to handle all this?

Edit : Why would I want to use SQLiteStatement ? Check this :Improve INSERT-per-second performance of SQLite?

This question looks similar: http://stackoverflow.com/questions/27233530/android-sqlite-named-parameters — Steve Chambers, Feb 02 '17 at 07:05

CL. · Answer 1 · 2017-01-29T14:46:09.293

Insertions can be done with ContentValues:

ContentValues cv = new ContentValues();
cv.put("column_1", 57);
cv.put("column_2", "hello");
cv.put("column_3", "world");
long rowId = db.insertOrThrow("table_name", null, cv);

But in the general case, the most correct way would be to use named parameters. However, these are not supported by the Android database API.

If you really want to use SQLiteStatement, write your own helper function that constructs it from a list of columns and takes care of matching it with the actual data. You also could write your own bindXxx() wrapper that maps previously-saved column names to parameter indexes.

Chetan Joshi · Answer 2 · 2017-02-02T11:49:54.630

2

You can use ContentValues with beginTransaction into SQLite that is quite easy as well as faster then prepared statements

For this you have to create ContentValues Array previously or create Content values object into your loop. and pass into insert method .this solution solve your both of problem in one.

mDatabase.beginTransaction();
    try {
        for (ContentValues cv : values) {
            long rowID = mDatabase.insert(table, " ", cv);
            if (rowID <= 0) {
                throw new SQLException("Failed to insert row into ");
            }
        }
        mDatabase.setTransactionSuccessful();
        count = values.length;
    } finally {
        mDatabase.endTransaction();
    }

edited Feb 02 '17 at 11:49

answered Feb 02 '17 at 11:42

Chetan Joshi

5,582
4
30
43

We are using Transaction no matter how we can put our data into data base object using SQL statements or ContentValues , ContentValues is standard way to put our data into Sqlite because predefined methods of Sqlite accepts ContentValues object . – Chetan Joshi Feb 02 '17 at 12:05
Please find more details in this link: http://www.outofwhatbox.com/blog/2010/12/android-using-databaseutils-inserthelper-for-faster-insertions-into-sqlite-database/ – Chetan Joshi Feb 02 '17 at 12:14

How to bind values to SQLiteStatement for insert query?

2 Answers2