How to query entire table using Room Library fts4 in android

Question

I have Notes application written in Java. Here's main part of Room Database usage:

I have this Dao:

@Dao
public interface NoteDao {

    @Query("INSERT INTO notes (contents) VALUES ('new note')")
    void create();

    @Query("SELECT * from notes; ")
    List<Note> getAllNotes();

    @Query("UPDATE notes SET contents = :contents WHERE rowid = :id")
    void save(String contents, int id);

    @Query("DELETE from notes WHERE rowid = :id")
    void delete(int id);
}

Where getAllNotes() function is failing to compile and here's the error: "The columns returned by the query does not have the fields [rowid] in com.sultanraja.notes.Note even though they are annotated as non-null or primitive. Columns returned by the query: [contents]"

The Note class is as follows:

@Fts4
@Entity(tableName = "notes")
public class Note {
    @PrimaryKey
    @NonNull
    public int rowid;

    @ColumnInfo(name = "contents")
    public String contents;

}

And the database is as follows:

@Database(entities = {Note.class}, version = 2, exportSchema = false)
public abstract class NotesDatabase extends RoomDatabase {

    public abstract NoteDao NoteDao();
}

Before I have this version of the database I had a regular one not fts4 and everything was alright. after I changed the schema and used fts4 tables, the select * from table does not return all columns and compilation error happens. the question is why?

Thanks.

Martin Zeitler Martin Zeitler · Accepted Answer · 2020-09-15T12:47:56

It cannot ORM map that one field. Try to annotate it properly:

@PrimaryKey
@NonNull
@ColumnInfo(name = "id")
public int rowid;

Or call it by it's expected name:

@PrimaryKey
@NonNull
public int id;

If in question, just open the database with an editor and see how the column is being called. Android Studio meanwhile even has such editor. Hint: per default SQLite calls it _id. SQLite command .schema notes would also show the table definition.

How to query entire table using Room Library fts4 in android

2 Answers