The query() method from RoomDatabase doesn't work as expected. How does the query() work under the hood?

Question

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

Answer 1

I can’t understand where the SELECT/INSERT query to the database is executed

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

Where is the data that the cursor then works with?

and

then it turned out that the insertion of a new row into the table didn't occur.

There is no data. An INSERT does not return any data.

You may then say but the @Insert does 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).

the official documentation does not contain any restrictions on its use Well it implies restrictions when it says Convenience method to query the database with arguments.. The implication is that it is not intended for operations that change the database. However, see the demo below.

To insert without resorting to and using your own SQL should be done using the SupportSQLiteDatabase's execSQL method.

However, you may well wish to use the convenience @Insert in an @Dao annotated interface or abstract class. The artist_id will then be returned.

• The are some nuances though.
  • if the @PrimaryKey field 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.
  • With Room, if the autoGenerate parameter of the @PrimaryKey annotation is 0 then the it does not pass the 0 value and thus the value for the column is generated.
    • If, with Room and autogenerate is 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 the OnConflictStrategy (demo uses IGNORE)).
    • in Java a primitive, such as int, long etc has a default value of 0 (Integer, Long etc being objects are null by default).
    • coding autoGenerate=true is, 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 @Entity annotated classes basically the same but with nuances:-

@Entity
public class ArtistOriginal {
   @PrimaryKey
   public int Artist_id;
   public String Name;
}

• as implied, the class as you have defined it (int without autogenerate means that artist_id will be 0 if left to default and after 1 insert will have a UNIQUE conflict)

and,

@Entity
public class ArtistOther1 {
   @PrimaryKey(autoGenerate = true)
   public int Artist_id;
   public String Name;
}

• autoGenerate=true added

and

@Entity
public class ArtistOther2 {
   @PrimaryKey
   public Integer Artist_id;
   public String Name;
}

• artist_id is an Integer object, autoGenerate defaults to false

Some methods in an @Dao annotated abstract class (could be an interface instead of abstract class but the latter allows methods with bodies which can be useful):-

@Dao
abstract class AllDAOs {

    @Insert(onConflict = OnConflictStrategy.IGNORE)
    abstract long insert(ArtistOriginal artistOriginal);
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    abstract long insert(ArtistOther1 artistOther1);
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    abstract long insert(ArtistOther2 artistOther2);

}

Finally some activity code:-

public class MainActivity extends AppCompatActivity {

    TheDatabase db;
    AllDAOs dao;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        db = TheDatabase.getInstance(this);
        dao = db.getAllDAOs();

        SupportSQLiteDatabase sdb = db.getOpenHelper().getWritableDatabase();
        /* Ignored as no result will be returned */
        Cursor cursor = sdb.query("INSERT INTO ArtistOriginal (name) VALUES ('Candido_via_query');");
        DatabaseUtils.dumpCursor(cursor);
        cursor.close();
        /* artist_id will be generated (1??? for the first run) */
        sdb.execSQL("INSERT INTO ArtistOriginal (name) VALUES('Candido_via_execsql');");
        /* In this case the artist_id is 0 as int cannot be null */
        ArtistOriginal ao = new ArtistOriginal();
        ao.Name = "Candidi_via_@Insert";
        dao.insert(ao);
        /* In this case the artist_id is 0 as int cannot be null */
        ArtistOther1 ao1 = new ArtistOther1();
        ao1.Name = "Candido_via_@Insert";
        dao.insert(ao1);
        /* In this case the artist_id member is null as it is an Integer Object */
        ArtistOther2 ao2 = new ArtistOther2();
        ao2.Name = "Candido_via_@Insert";
        dao.insert(ao2);


        Cursor csr = db.query("" +
                "SELECT 'ArtistOriginal',* FROM ArtistOriginal " +
                "UNION ALL SELECT 'ArtistOther1',* FROM ArtistOther1 " +
                "UNION ALL SELECT 'ArtistOther2',* FROM ArtistOther2",
                null
        );
        DatabaseUtils.dumpCursor(csr);
        csr.close();
    }
}

Demo Results

The log (dumped cursors); first Cursor:-

2024-02-01 10:18:13.013I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@8c94956
2024-02-01 10:18:13.013I/System.out: <<<<<

• As per your question not inserted (BUT see below)

Then, the second Cursor (split into the 3 tables UNION'ed):-

2024-02-01 10:18:13.042I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@3574073
2024-02-01 10:18:13.042I/System.out: 0 {
2024-02-01 10:18:13.042I/System.out:    'ArtistOriginal'=ArtistOriginal
2024-02-01 10:18:13.043I/System.out:    Artist_id=0
2024-02-01 10:18:13.043I/System.out:    Name=Candidi_via_@Insert
2024-02-01 10:18:13.043I/System.out: }
2024-02-01 10:18:13.043I/System.out: 1 {
2024-02-01 10:18:13.043I/System.out:    'ArtistOriginal'=ArtistOriginal
2024-02-01 10:18:13.043I/System.out:    Artist_id=1
2024-02-01 10:18:13.043I/System.out:    Name=Candido_via_query
2024-02-01 10:18:13.043I/System.out: }
2024-02-01 10:18:13.043I/System.out: 2 {
2024-02-01 10:18:13.043I/System.out:    'ArtistOriginal'=ArtistOriginal
2024-02-01 10:18:13.043I/System.out:    Artist_id=2
2024-02-01 10:18:13.043I/System.out:    Name=Candido_via_execsql
2024-02-01 10:18:13.043I/System.out: }

• ??? Where did 3 rows come from???(rhetorical). As can be seen the query did, in this case, result in a row being added, but it was not available when the first Cursor was generated.

then, as expected, the single row in the Other1 and Other2 table with appropriately generated artist_id values

2024-02-01 10:18:13.043I/System.out: 3 {
2024-02-01 10:18:13.043I/System.out:    'ArtistOriginal'=ArtistOther1
2024-02-01 10:18:13.043I/System.out:    Artist_id=1
2024-02-01 10:18:13.043I/System.out:    Name=Candido_via_@Insert
2024-02-01 10:18:13.043I/System.out: }


2024-02-01 10:18:13.044I/System.out: 4 {
2024-02-01 10:18:13.044I/System.out:    'ArtistOriginal'=ArtistOther2
2024-02-01 10:18:13.044I/System.out:    Artist_id=1
2024-02-01 10:18:13.044I/System.out:    Name=Candido_via_@Insert
2024-02-01 10:18:13.044I/System.out: }
2024-02-01 10:18:13.044I/System.out: <<<<<

Using App Inspection, then:-

and

More

If the dao.insert(...) are changed to retrieve the returned long as per:-

    long aiid = dao.insert(ao);
    ....
    long ao1id = dao.insert(ao1);
    ....
    long ao2id = dao.insert(ao2);

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

• the -1 returned indicates not inserted, thus due to the int defaulting to 0 and autoGenerate being false.
• the other convenience @Inserts return 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:-

PRAGMA count_changes; PRAGMA count_changes = boolean;

Query or change the count-changes flag. Normally, when the count-changes flag is not set, INSERT, UPDATE and DELETE statements return no data. When count-changes is set, each of these commands returns a single row of data consisting of one integer value - the number of rows inserted, modified or deleted by the command. The returned change count does not include any insertions, modifications or deletions performed by triggers, any changes made automatically by foreign key actions, or updates caused by an upsert.

Another way to get the row change counts is to use the sqlite3_changes() or sqlite3_total_changes() interfaces. There is a subtle different, though. When an INSERT, UPDATE, or DELETE is run against a view using an INSTEAD OF trigger, the count_changes pragma reports the number of rows in the view that fired the trigger, whereas sqlite3_changes() and sqlite3_total_changes() do not.

• https://www.sqlite.org/pragma.html#pragma_count_changes (note even though pragma_count_changes is deprecated it provides a good description). Of course if a row is or rows are inserted it is not the count of inserted but the rowid(s) returned.

Additional re comments

I read what you write and was very surprised that the insertion of data with sdb.query(...) and sdb.execSQL(...) only happened after the dao.insert(ao) method was called;

The inserts are done when when the SQL is executed but there is nothing reports this. If the following method is added:-

void logDataInDatabase(SupportSQLiteDatabase sdb,String tag_suffix) {
    Log.d("CSRDUMP_" + tag_suffix,"Dumping Cursor");
    Cursor csr = db.query("" +
                    "SELECT 'ArtistOriginal' AS t,* FROM ArtistOriginal " +
                    "UNION ALL SELECT 'ArtistOther1',* FROM ArtistOther1 " +
                    "UNION ALL SELECT 'ArtistOther2',* FROM ArtistOther2",
            null);
    DatabaseUtils.dumpCursor(csr);
    csr.close();
}

• i.e. the query that gathers all rows from all three tables

And this is called after each insert:-

    Cursor cursor = sdb.query("INSERT INTO ArtistOriginal (name) VALUES ('Candido_via_query');");
    DatabaseUtils.dumpCursor(cursor);
    cursor.close();
    logDataInDatabase(sdb,"QRYINS");
    /* artist_id will be generated (1??? for the first run) */
    sdb.execSQL("INSERT INTO ArtistOriginal (name) VALUES('Candido_via_execsql');");
    logDataInDatabase(sdb,"EXEINS");
    /* In this case the artist_id is 0 as int cannot be null */
    ArtistOriginal ao = new ArtistOriginal();
    ao.Name = "Candidi_via_@Insert";
    long aiid = dao.insert(ao);
    logDataInDatabase(sdb,"DAOINS");

Then a first run's log will include:-

2024-02-02 05:28:44.284 D/CSRDUMP_QRYINS: Dumping Cursor
2024-02-02 05:28:44.287 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@8f4e6c4
2024-02-02 05:28:44.287 I/System.out: 0 {
2024-02-02 05:28:44.287 I/System.out:    t=ArtistOriginal
2024-02-02 05:28:44.287 I/System.out:    Artist_id=1
2024-02-02 05:28:44.287 I/System.out:    Name=Candido_via_query
2024-02-02 05:28:44.287 I/System.out: }
2024-02-02 05:28:44.287 I/System.out: <<<<<


2024-02-02 05:28:44.288 D/CSRDUMP_EXEINS: Dumping Cursor
2024-02-02 05:28:44.289 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@e2e29ad
2024-02-02 05:28:44.290 I/System.out: 0 {
2024-02-02 05:28:44.290 I/System.out:    t=ArtistOriginal
2024-02-02 05:28:44.290 I/System.out:    Artist_id=1
2024-02-02 05:28:44.290 I/System.out:    Name=Candido_via_query
2024-02-02 05:28:44.290 I/System.out: }
2024-02-02 05:28:44.290 I/System.out: 1 {
2024-02-02 05:28:44.290 I/System.out:    t=ArtistOriginal
2024-02-02 05:28:44.290 I/System.out:    Artist_id=2
2024-02-02 05:28:44.291 I/System.out:    Name=Candido_via_execsql
2024-02-02 05:28:44.291 I/System.out: }
2024-02-02 05:28:44.291 I/System.out: <<<<<


2024-02-02 05:28:44.294 D/CSRDUMP_DAOINS: Dumping Cursor
2024-02-02 05:28:44.294 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@3574073
2024-02-02 05:28:44.295 I/System.out: 0 {
2024-02-02 05:28:44.296 I/System.out:    t=ArtistOriginal
2024-02-02 05:28:44.296 I/System.out:    Artist_id=0
2024-02-02 05:28:44.296 I/System.out:    Name=Candidi_via_@Insert
2024-02-02 05:28:44.296 I/System.out: }
2024-02-02 05:28:44.296 I/System.out: 1 {
2024-02-02 05:28:44.296 I/System.out:    t=ArtistOriginal
2024-02-02 05:28:44.296 I/System.out:    Artist_id=1
2024-02-02 05:28:44.296 I/System.out:    Name=Candido_via_query
2024-02-02 05:28:44.296 I/System.out: }
2024-02-02 05:28:44.296 I/System.out: 2 {
2024-02-02 05:28:44.296 I/System.out:    t=ArtistOriginal
2024-02-02 05:28:44.296 I/System.out:    Artist_id=2
2024-02-02 05:28:44.296 I/System.out:    Name=Candido_via_execsql
2024-02-02 05:28:44.296 I/System.out: }
2024-02-02 05:28:44.296 I/System.out: <<<<<

• i.e. the inserts are done when the SQL is executed (not the order i.e. artist_id 0 was the last added)

What surprised me even more is that, as you write, int is a primitive type and conflict will occur when inserting another more into the database...

Only if autoGenerate is false (or omitted and thus defaults to false) in which case the underlying generated insert SQL used is:-

"INSERT OR IGNORE INTO `ArtistOriginal` (`Artist_id`,`Name`) VALUES (?,?)"

• i.e. the artist_id is inserted asis

If autoGenerate is true then Room generates the insert SQL as:-

"INSERT OR IGNORE INTO `ArtistOther1` (`Artist_id`,`Name`) VALUES (nullif(?, 0),?)"

• i.e.an artist_id that is 0 will be taken as being null and thus the artist_id will be generated

  • note the generated java can be found in the java(generated) folder (visible in Android View). Within there will be classes that are the same as the @Database annotated class(es) and the @Dao annotated 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.

• this, IMO, is incorrect as it has a tendency to result in the use of autoGenerate = true, which results in the AUTOINCREMENT keyword/clause being used, which is rarely needed and is inefficient.

simply do not use methods for insertion that are not intended for this

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 @Query where the SQL would be the "special" INSERT (more likely for UPDATE or DELETE as @Delete and @Update rely 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).