What Is the Correct Way to Do Inserts/Updates/Deletes in Android SQLitedatabase Using a Query String

What is the correct way to do inserts/updates/deletes in Android SQLiteDatabase using a query string?

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.

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.

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

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.

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

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) explicitly says that it does not work with selects, but in fact it does!

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

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

How to update ,insert and delete the rows in Sqlite?

If you want to insert rows to the table, use ContentValues (just like your update code) and the insert() method:

SQLiteDatabase db = dbHandler.getWritableDatabase();
for (int i = 0; i < 3; i++) {
    ContentValues cValues = new ContentValues();
    cValues.put(DbHandler.COLUMN_ID, i + 1);
    cValues.put(DbHandler.COLUMN_NAME, "Test");
    cValues.put(DbHandler.COLUMN_SERIALNUMBER, String.valueOf(i + 1));
    cValues.put(DbHandler.COLUMN_COUNTALL, 10);
    cValues.put(DbHandler.COLUMN_COUNTHIGH, i + 1);
    cValues.put(DbHandler.COLUMN_COUNTLOW, i + 1);
    cValues.put(DbHandler.COLUMN_RAWDATA, i + 1);
    db.insert(DbHandler.TABLE_USERS, null, cValues);
}
db.close();

The above code will insert 3 rows in the table:

1 Test 1 10 1 1 1
2 Test 2 10 2 2 2
3 Test 1 10 3 3 3

Now, say that you want to update the column COLUMN_COUNTHIGH to 100 and the column COLUMN_COUNTLOW to 0 of the row with id = 2, then you use the update() method:

SQLiteDatabase db = dbHandler.getWritableDatabase();
ContentValues cValues = new ContentValues();
cValues.put(DbHandler.COLUMN_COUNTHIGH, 100);
cValues.put(DbHandler.COLUMN_COUNTLOW, 0);
db.update(DbHandler.TABLE_USERS, cValues, DbHandler.COLUMN_ID + " = ?", new String[] {String.valueOf(2)});
db.close();

So the update() method's arguments are:

the table's name

the ContentValues

the WHERE clause which contains the column names and the placeholders ? that will decide which row(s) will be updated

and the value(s) of the column(s) which will be set at the place of ?

If you want to delete say the row with id = 1, use the delete() method:

db.delete(DbHandler.TABLE_USERS, DbHandler.COLUMN_ID + " = ?", new String[] {String.valueOf(1)}); 

The arguments are similar as of those of the update() method (without the ContentValues).

Android insert/update/delete SQLite queries

You need whereArgs for

String where = "id=?";

something like :

sqliteDb.update(tableName, values, where, new String[] {"42"});

where 42 would be the _id of the row to update. Also prefer BaseColumns._ID to "id".

Why is such DELETE query not working?

rawQuery() just compiles the SQL but does not run it. To actually run it, use either execSQL() or call one of the moveTo...() methods on the cursor returned by rawQuery().

For further info, see What is the correct way to do inserts/updates/deletes in Android SQLiteDatabase using a query string?

Android/SQLite: Insert-Update table columns to keep the identifier

I can understand the perceived notion that it is best for performance to do all this logic in SQL, but perhaps the simplest (least code) solution is the best one in this case? Why not attempt the update first, and then use insertWithOnConflict() with CONFLICT_IGNORE to do the insert (if necessary) and get the row id you need:

public Uri insert(Uri uri, ContentValues values) {
    final SQLiteDatabase db = mOpenHelper.getWritableDatabase();
    String selection = "latitude=? AND longitude=?"; 
    String[] selectionArgs = new String[] {values.getAsString("latitude"),
                values.getAsString("longitude")};

    //Do an update if the constraints match
    db.update(DatabaseProperties.TABLE_NAME, values, selection, null);

    //This will return the id of the newly inserted row if no conflict
    //It will also return the offending row without modifying it if in conflict
    long id = db.insertWithOnConflict(DatabaseProperties.TABLE_NAME, null, values, CONFLICT_IGNORE);        

    return ContentUris.withAppendedId(uri, id);
}

A simpler solution would be to check the return value of update() and only do the insert if the affected count was zero, but then there would be a case where you could not obtain the id of the existing row without an additional select. This form of insert will always return to you the correct id to pass back in the Uri, and won't modify the database more than necessary.

If you want to do a large number of these at once, you might look at the bulkInsert() method on your provider, where you can run multiple inserts inside a single transaction. In this case, since you don't need to return the id of the updated record, the "simpler" solution should work just fine:

public int bulkInsert(Uri uri, ContentValues[] values) {
    final SQLiteDatabase db = mOpenHelper.getWritableDatabase();
    String selection = "latitude=? AND longitude=?";
    String[] selectionArgs = null;

    int rowsAdded = 0;
    long rowId;
    db.beginTransaction();
    try {
        for (ContentValues cv : values) {
            selectionArgs = new String[] {cv.getAsString("latitude"),
                cv.getAsString("longitude")};

            int affected = db.update(DatabaseProperties.TABLE_NAME, 
                cv, selection, selectionArgs);
            if (affected == 0) {
                rowId = db.insert(DatabaseProperties.TABLE_NAME, null, cv);
                if (rowId > 0) rowsAdded++;
            }
        }
        db.setTransactionSuccessful();
    } catch (SQLException ex) {
        Log.w(TAG, ex);
    } finally {
        db.endTransaction();
    }

    return rowsAdded;
}

In truth, the transaction code is what makes things faster by minimizing the number of times the database memory is written to the file, bulkInsert() just allows multiple ContentValues to be passed in with a single call to the provider.

Android sqlite database no such column when update query

During update your column name is not match with table actual column name. Try below:

String sql = "UPDATE PRINCIPAL SET selectname=?, eventname=?, eventorg=?, eventdays=?, eventstart=?, image=? WHERE id=? ";

There are two mismatch in column between create and update sql

name should be selectname

eventorganization should be eventorg

Also your table name PRINCIPALS not match with PRINCIPAL.

---

Related Topics