Is it better to use Update or execSQL to update data on SQLite?

Question

I saw in a tutorial a code updating data from a SQlite database using execSQL:

    String update = "UPDATE FRUIT SET COLOR=? WHERE ID=?";
    myDatabase.execSQL( update, new Object[] {"RED", 7});
    Cursor cursor = myDatabase.rawQuery("SELECT * FROM FRUIT;", null);
    if (cursor.moveToFirst()) {
        do {
            String name = cursor.getString(cursor.getColumnIndex("NAME"));
            String color = cursor.getString(cursor.getColumnIndex("COLOR"));

            Log.i(TAG, "onCreate: Name: " + name + ", color: " + color);
        } while (cursor.moveToNext());
    }

but, I read this in the oficial documentation of Android:

The code using execSQL worked but it's better to use update or I can still use execSQL since it worked? What's better for good practice? Since this tutorial is from a trustworthy source, why are they using execSQL?

Answer 1

The issue/warning regarding using execSQL may be for a few reasons, one of them being that you do no get anything returned when using execSQL, whilst the convenience methods return potentially useful values.

insert will return a long containing the id of the inserted row else -1. update and delete return the number of affected rows.

Using the convenience methods also reduces the chance of making typing errors by build the underlying SQL, adding the keywords and enclosing strings and for some importantly offering a level of protection against SQL injection (execSQL's bindArgs also offers protection against SQL Injection, likewise with rawQuery).

However, there are limitations and sometimes the use of execSQL/rawQuery becomes necessary for some more complex situations.