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();

Answer 1

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.

Answer 2

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();

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();