Map new POJO class with lesser fields to an existing Room table

Question

Currently, we have the following Dao and model class.

NoteDao

@Query("SELECT * FROM note")
public abstract LiveData<List<Note>> getAllNotes();

Note class

@Entity(
    tableName = "note"
)
public class Note {
    @ColumnInfo(name = "title")
    private String title;

    // Can contain a huge String.
    @ColumnInfo(name = "body")
    private String body;
}

---

However, in certain situation, we are only interested to load title only.

Loading all Notes with large body string at once, may cause OutOfMemoryException

Is there any way, we can create another POJO as following?

public class SimpleNote {
    private String title;
}

Then, we are able to return list of SimpleNote from NoteDao?

@Query("???")
public abstract LiveData<List<SimpleNote>> getAllSimpleNotes();

Zoan

Yan Cheng did you checked those answers? – Elboa

Answer 1

As can be seen in "Returning subsets of columns" docs:

Most of the time, you need to get only a few fields of an entity. For example, your UI might display just a user's first name and last name, rather than every detail about the user. By fetching only the columns that appear in your app's UI, you save valuable resources, and your query completes more quickly.

Room allows you to return any Java-based object from your queries as long as the set of result columns can be mapped into the returned object. For example, you can create the following plain old Java-based object (POJO) to fetch the user's first name and last name:

data class NameTuple(
    @ColumnInfo(name = "first_name") val firstName: String?,
    @ColumnInfo(name = "last_name") val lastName: String?
)

Now, you can use this POJO in your query method:

@Dao
interface MyDao {
    @Query("SELECT first_name, last_name FROM user")
    fun loadFullName(): List<NameTuple>
}

Room understands that the query returns values for the first_name and last_name columns and that these values can be mapped into the fields of the NameTuple class. Therefore, Room can generate the proper code. If the query returns too many columns, or a column that doesn't exist in the NameTuple class, Room displays a warning.

---

Back to your case: having defined SimpleNote as such:

public class SimpleNote {
    @ColumnInfo(name = "title")
    private String title;
}

Then you can query the table:

@Query("SELECT title FROM note")
public abstract LiveData<List<SimpleNote>> getAllSimpleNotes();

Sharpfreeze

Answer 2

This will work for you:

@Query("SELECT title FROM note") 
LiveData<List<String>> getAllNoteTitles();

When you call SELECT * , it will select all the fields inside the table.

Erb

Answer 3

You can return any POJO from a room select query function, you just need to stick to your POJO's field names on your query string.

class SumAveragePojo
{ 
    public float total;
    public float average;
}

If your query returns all the necessary fields for your POJo, then you're good to go. Here's an example of the flexibility of this:

@Query("SELECT SUM(stepCount) as total, AVG(stepCount) as average FROM userFitnessDailyRecords where forDay BETWEEN :startDay AND :endDay ORDER BY forDay ASC")
SumAveragePojo getUserFitnessSumAndAverageForLastThirtyDays(Date startDay, Date endDay);

So you can safely do:

@Query("SELECT title FROM note") 
LiveData<List<SimpleNote>> getAllNoteTitles();

Iridissa

Answer 4

Instead of using type String, one can map it to Note, even when not selecting the body column:

@Query("SELECT title FROM note")
LiveData<List<Note>> getAllNotesPreview();

In Room, @Entity is usually being mapped 1:1 to a table, as it is common for ORM. Therefore auto-mapping two objects to the same table is not possible. Selecting by column-name(s) indeed seems to be the least effort, which is no problem when only reading from there. For read/write access, one would still need the full detail-view of the record, which has all the columns.

---

Despite the code provided looks alike Java, in Kotlin this could also be:

@Query("SELECT title FROM note")
Flow<List<Note>> getAllNotesPreview();

This would rule out mutation, which makes sense when not all columns have been queried and mapped. Otherwise it might only blank the absent body column, when eg. editing the title column. See Using Flow with Room (CodeLab) or Unidirectional data flow on Android using Kotlin (see the associated GitHub repository for a roughly similar sample application, which is demonstrating it).

Pathological

In Kotlin the proper syntax would be, fun getAllNotesPreview() : Flow<List<Note>> – Swor

Answer 5

If you only added title in the class the Room won't load body I used the following dependancies

implementation 'androidx.lifecycle:lifecycle-extensions:2.2.0'
implementation 'androidx.lifecycle:lifecycle-common-java8:2.2.0'
implementation 'androidx.room:room-runtime:2.2.4'
annotationProcessor 'androidx.room:room-compiler:2.2.4'

And the SimpleNote class i created is as follows

public class SimpleNote {
    private String title;

    public SimpleNote(String title) {
        this.title = title;
    }

    public String getTitle() {
        return title;
    }
}

I am able to query only title as follows

@Dao
public interface NotesDao {
    @Query("SELECT * FROM notes_table")
    LiveData<List<Notes>> loadAllNotes();

    @Insert
    void insertNotes(List<Notes> notes);

    //loads only the title
    @Query("SELECT * FROM notes_table")
    LiveData<List<SimpleNote>> loadSimpleNotes();
}

Meagre

Answer 6

you can directly use

@Query("SELECT title FROM note")
public abstract LiveData<List<SimpleNote>> getAllSimpleNotes();


public class SimpleNote {
    private String title;
}

as long as your column name is same i.e. title

Wheels

Answer 7

You could use a view into the database which would be extensible as well.

@DatabaseView(value = "SELECT title FROM note", viewName = "simple-note")
public class SimpleNote {
    @ColumnInfo(name = "title")
    private String title;
}

You only need to register it with the database entities.

@Database(entities = {Note.class}, views = {SimpleNote.class}, version = 3)

Note: Like entities, you can run SELECT statements against views. However, you cannot run INSERT, UPDATE, or DELETE statements against views.

@Query("SELECT * from simple-note")
public abstract LiveData<List<SimpleNote>> getAllSimpleNotes();

Kruger