While exploring the capabilities of the Room library and the RoomDatabase class, I decided to test the query() method. Since the description in the official documentation does not contain any restrictions on its use, I decided to test the two simplest methods: SELECT and INSERT.
I'm testing queries with a simple database. My SELECT looked like this:
Cursor cursor = db.query("select * from artist”, null);
The artist table is described as follows:
@Entity
public class Artist {
@PrimaryKey
public int Artist_id;
public String Name;
}
This method worked well and returned me a Cursor with which I can view the table data.
But when I decided to try using the INSERT method as follows:
Cursor cursor = db.query("insert into artist (name) values('Candido');”, null);
then it turned out that the insertion of a new row into the table didn't occur. I decided to find out by debugging why the query() method executes select operation and performs insert, but could not find the place in the library code where the check occurs. Then I decided to perform SELECT and INSERT, but with the rawQuery() method from SQLiteDatabase class and result was the same.
During step-by-step debugging in the library class SQLiteDirectCursorDriver, when debugging, the program executes the following method:
public Cursor query(CursorFactory factory, String[] selectionArgs) {
final SQLiteQuery query = new SQLiteQuery(mDatabase, mSql, mCancellationSignal);
final Cursor cursor;
try {
query.bindAllArgsAsStrings(selectionArgs);
if (factory == null) {
cursor = new SQLiteCursor(this, mEditTable, query);
} else {
cursor = factory.newCursor(mDatabase, this, mEditTable, query);
}
} catch (RuntimeException ex) {
query.close();
throw ex;
}
mQuery = query;
return cursor;
}
This is the method that is called during the creation of the Cursor object, which is returned by the query() method that I am testing. When creating a SQLiteQuery, its constructor calls the constructor of the parent class SQLiteProgram, which contains the following lines:
db.getThreadSession().prepare(mSql,
db.getThreadDefaultConnectionFlags(assumeReadOnly),
cancellationSignalForPrepare, info);
I assumed that a query to the database occurs in this place, but this is probably not the case, since the description of the prepare() method of the SQLiteSession class says that method "Prepares a statement for execution but does not bind its parameters or execute it". If we return to the query() method, the code of which I described above, then you should pay attention to the following line
cursor = new SQLiteCursor(this, mEditTable, query);
The constructor description says: "Execute a query and provide access to its result set through a Cursor interface."
But if I open the code of the class constructor and the code of the parent class constructor, then I did not find the code there that executes my sql in the database.
I can’t understand where the SELECT/INSERT query to the database is executed, in what place, in what class and method does this happen? Where is the data that the cursor then works with? Please help me understand the internal structure of this process.
The code was tested on the Android 13 API 33 Emulator arm64-v8a
I don't believe that you need to (in short do what needs to be done in the most convenient way and perhaps heeding there is no way to make changes and return a value in a single execution of an SQL statement).
and
There is no data. An INSERT does not return any data.
You may then say but the
@Insertdoes return the artist_id, this is because the underlying code of the insert (which is the SQLite API which Room invokes) follows the insert with another API call that returns the rowid (which artist_id is an alias of).To insert without resorting to and using your own SQL should be done using the
SupportSQLiteDatabase'sexecSQLmethod.However, you may well wish to use the convenience
@Insertin an@Daoannotated interface or abstract class. The artist_id will then be returned.@PrimaryKeyfield is and integer type (e.g. int, long. Integer, Long) then it will be a special column in that it will be an alias of the rowid and if, when inserting, and no value is provided then the value will be generated.autoGenerateparameter of the@PrimaryKeyannotation is 0 then the it does not pass the 0 value and thus the value for the column is generated.autogenerateis false (the default if not coded), then if the value is null, it does not pass the value. However, the value of 0 is passed and either 0 will be used or a UNIQUE conflict will be issued (i.e. the row will not be inserted and the result is dependent upon theOnConflictStrategy(demo uses IGNORE)).autoGenerate=trueis, in most cases, a waste/inefficient (see https://www.sqlite.org/autoinc.html as to why).As such perhaps consider the following demo, which tries to explain:-
First some
@Entityannotated classes basically the same but with nuances:-autogeneratemeans that artist_id will be 0 if left to default and after 1 insert will have a UNIQUE conflict)and,
autoGenerate=trueaddedand
autoGeneratedefaults to falseSome methods in an
@Daoannotated abstract class (could be an interface instead of abstract class but the latter allows methods with bodies which can be useful):-Finally some activity code:-
Demo Results
The log (dumped cursors); first Cursor:-
Then, the second Cursor (split into the 3 tables UNION'ed):-
then, as expected, the single row in the Other1 and Other2 table with appropriately generated artist_id values
Using App Inspection, then:-
and
More
If the dao.insert(...) are changed to retrieve the returned long as per:-
And the App is rerun (not uninstalled and rerun) in debug mode with a breakpoint at where the 2nd Cursor/Query is executed then (actually 2nd rerun):-
autoGeneratebeing false.@Insertsreturn a positive value (3).The convenience methods not only execute the core insert/delete or update but additional use the API to gather the respective data as per:-
Additional re comments
The inserts are done when when the SQL is executed but there is nothing reports this. If the following method is added:-
And this is called after each insert:-
Then a first run's log will include:-
Only if
autoGenerateis false (or omitted and thus defaults to false) in which case the underlying generated insert SQL used is:-If
autoGenerateis true then Room generates the insert SQL as:-i.e.an artist_id that is 0 will be taken as being null and thus the artist_id will be generated
@Databaseannotated class(es) and the@Daoannotated interfaces/abstract class(es). The INSERT SQL above was copied from those classes.The artist_id, being the primary key must be unique, so if it is a primitive and left to default to 0 only one such insert will work, subsequent inserts will result in a UNIQUE conflict.
autoGenerate = true, which results in theAUTOINCREMENTkeyword/clause being used, which is rarely needed and is inefficient.Unless there is good reason not to and then if there is use an appropriate way. Typically the appropriate Room way, would be to use an
@Querywhere the SQL would be the "special" INSERT (more likely for UPDATE or DELETE as@Deleteand@Updaterely upon the primary key to ascertain the row to be actioned upon).Perhaps the "rule" should be only use a SupportSQliteDatabase if necessary (which is pretty rare).