Google describes Room as providing an abstraction layer over SQLite to allow fluent database access while harnessing the full power of SQLite.

• In other words, Room aims to make your use of SQLite easier.
  • Through a lightweight annotation-based implementation of object-relational mapping (ORM) engine.
• All material is based on version 1.0.0 of Room.

If you have worked with a relational database - like SQLite - from an object-oriented language like Java or Kotlin - undoubtedly you have encountered the object-relational impedence mismatch - which is a very fancy way of saying "it's a pain getting stuff into and out of the database".

• Traditional Android development uses SQLiteDatabase for interacting with SQLite.
• That, in turn, uses:
  • Cursor objects to represent the results of queries and
  • ContentValues objects to represent data to be inserted or updated.
• While Cursor and ContentValues are objects, the are fairly generic, much in the way that a HashMap or ArrayList is generic.
• In particular, neither Cursor and ContentValues has any of our business logic.
• We have to somehow either wrap that around those objects or convert between these objects and some of ours.

• A typical ORM works off of Java or Kotlin code and either:
  • Generates a suitable database structure.
  • Works with you to identify how the Java classes should map to some existing table structure.
     
• The quintessential Java ORM is Hibernate.
• However, Hibernate was developed with server-side Java in mind and is not well-suited for slim platforms like Android devices.
   
• However, a vast roaster of Android ORMs have been created over the years to try to fill that gap:
  • SQLDelight.
  • DBFlow.
  • greenDAO.
  • OrmLite.
  • Sugar ORM.

To use the Room, you need two dependencies in your module's build.gradle.

1. The runtime library version, using the standard implementation directive.
2. An annotation processor, using the annotationProcessor derivative.
    

1. room-ktx, to pull in the core Runtime libraries plus some Kotlin-specific extensions.
2. room-compiler, used with kapt.
    

Note that Room has a minSdkVersion requirement of API level 15 or higher.

• If you attempt to build with a lower minSdkVersion, you will get a build error.
• If you try to override Room's minSkdVersion using manifest merger elements, while the project will build, expect Room to crash horribly.

Roughly speaking, your use of Room is divided into three sets of classes:

1. Entites: which are POJOs (simple classes) that model the data you are transferring into and out of the database.
2. DAO (The data access object): that provides the description of the API that you want for working with certain entities.
3. The database, which ties together all of the entities and DAOs for a single SQLite database.
    

• DAO is roughly analogous to your Retrofit interface on which you declare your Web service API.
• Your entities are the POJOs that you are expecting Gson (or whatever) to create based on the Web service response.

In many ORM systems, the entity (or that system's equivalent) is a POJO (simple class) that you happen to want to store in the database.

• It usually represents some part of your overall domain model, so a payroll system might have entities representing departments, employees, and paychecks.
   
• With Room, a better description of entities is that they are POJOs (simple classes) representing:
  • The data that you want to store in the database.
  • A typical unit of result set that you are trying to retrieve from the database.
     
• Tactically, from a coding standpoint, an entity is a Java/Kotlin class marked with the @Entity annotation.
• For example, here is a Java Trip class the serves as a Room entity:

package com.mariamaged.android.room.room1;  
  
import androidx.annotation.NonNull;  
import androidx.room.Entity;  
import androidx.room.Ignore;  
import androidx.room.PrimaryKey;  
  
import java.util.UUID;  
  
@Entity(tableName = "trips")  
class Trip {  
  
@PrimaryKey  
@NonNull  
	public final String id;  
    public final String title;  
    final int duration;  
  
@Ignore  
  Trip(String title, int duration) {  
        this(UUID.randomUUID().toString(), title, duration);  
    }  
  
  Trip(@NonNull String id, String title, int duration) {  
        this.id = id;  
        this.title = title;  
        this.duration = duration;  
    }  
  
@Override  
public String toString() {  
        return(title);  
    }  
}

• For example, here is a Kotlin NoteEntity class that serves as a Room entity:

package com.mariamaged.android.roomKotlin

import androidx.room.Entity
import androidx.room.PrimaryKey

@Entity(tableName = "notes")
data class NoteEntity(
	@PrimaryKey val id: String,
	val title: String,
	val text: String,
	val version: Int
)

• There is no superclass required for entities, and the expectation is that often they will be simple POJOs (simple classes or data classes).
   
• The @Entity annotation can have properties customizing the behaviour of your entity and how Room works with it.
  • The default name of the SQLite table is the same as the entity class name.
  • tableName allows you to override that and supply your own table name.
     
• Sometimes, your fields will be marked with annotations describing their roles.
• In this example, the id field has the @PrimaryKey annotation, telling Room that this is the unique identifier for this entity.
• Room will use that to know how to update and delete Trip/Note objects by their primary key values.
• Room also requires that in Java, any @PrimaryKey field of an object type - like String-be annotated with @NonNull, as primary keys in SQLite cannot be null.
  • In Kotlin, you can just use a non-nullable type, such as String.
     
• Similarly, sometimes your methods will be marked with annotations.
• In this case, Trip has two constructors:
  • One that generates the id from UUID.
  • One that takes the id as a constructor parameter.
• Room needs to know which constructors are eligible for its use; you mark the other constructors with the @Ignore annotation.
   
• For Room to work with a field, it needs to be public or have JavaBean-style getter and setter methods, so Room can access them.
• If the fields are final, as they are on Trip, Room will try to find a constructor to use to populate the fields, as final fields will lack the setters.

The API into the data.

Dao that provides methods for the database operations that you need: queries, inserts, updates, deletes, or whatever.

• In Room, the DAO is identified by the @Dao annotation, applied to either an:
  • Abstract class.
  • Interface.
• The actual concrete implementation will be code-generated for you by the Room annotation processor.
   

The primary role of the @Dao-annotated abstract class or interface is to have one or more methods, with their own Room annotations, identifying what you want to do with the database and your entities.

• Java Trip example:

import androidx.room.Delete;  
import androidx.room.Insert;  
import androidx.room.Query;  
import androidx.room.Update;  
  
import java.util.List;  
  
@Dao  
public interface TripStore {  
  @Query("Select * FROM trips ORDER BY title")  
  List<Trip> selectAll();  
  
  @Query("SELECT * FROM trips WHERE id =: id")  
  Trip findById(String id);  
  
  @Insert  
  void insert(Trip... trips);  
  
  @Update  
  void update(Trip... trips);  
  
  @Delete  
  void delete(Trip... trips);  
  
}

• Instead of using ? as placeholders for arguments, as we would in traditional SQLite, you use : prefixed method parameter names.
• So, in findById(), we have a String parameter named id, so we can use :id in the SQL statement wherever we might have used ? to indicate the value to bind in.
   
• The remaining three methods use the @Insert, @Update, @Delete annotations, mapped to methods of the same name.
• Here, the methods take a varags of Trip, meaning that we can insert, update, or delete as many Trip objects as we want
  • (Passing in zero Trip objects works, though that would be rather odd).
     
     
     
• Kotlin Notes example:

package com.mariamaged.android.roomKotlin  
  
import androidx.room.*  
  
@Dao  
interface NoteStore {  
    @Query("SELECT * FROM notes")  
    fun loadAll(): List<NoteEntity>  
  
    @Insert  
    fun insert(note: NoteEntity)  
  
    @Update  
    fun update(note: NoteEntity)  
  
    @Delete  
    fun delete(vararg notes: NoteEntity)  
}

• In this sample, insert() and update() each take a single NodeEntity, while delete() takes a vararg of NoteEntity.
• Room supports either pattern, as well as others, such as List of NoteEntity-choose what fits your needs.

In addition to entities and DAOs, you will have at least one @Database-annotated abstract class, extending a RoomDatabase.

• This class knits together the database file, the entities, the DAOs.
• Java Trip example:

package com.mariamaged.android.room.room1;  
  
import android.content.Context;  
  
import androidx.room.Database;  
import androidx.room.Room;  
import androidx.room.RoomDatabase;  
  
@Database(entities={Trip.class}, version = 1)  
abstract class TripDatabase extends RoomDatabase {  
        abstract TripStore tripStore();  
  
        private static final String DB_NAME ="trips.db";  
        private static volatile TripDatabase INSTANCE = null;  
  
        synchronized static TripDatabase get(Context ctxt) {  
            if(INSTANCE == null) INSTANCE = create(ctxt, false);  
            return INSTANCE;  
        }  
  
        static TripDatabase create(Context ctxt, boolean memoryOnly) {  
            RoomDatabase.Builder<TripDatabase> b;  
  
            if(memoryOnly) b = Room.inMemoryDatabaseBuilder(ctxt.getApplicationContext(), TripDatabase.class);  
            else b =  Room.databaseBuilder(ctxt.getApplicationContext(), TripDatabase.class, DB_NAME);  
  
            return b.build();  
        }  
}

• Kotlin Notes Example:

package com.mariamaged.android.roomKotlin  
  
import androidx.room.Database  
import androidx.room.RoomDatabase  
  
@Database(entities = [NoteEntity::class], version = 1)  
abstract class NoteDatabase : RoomDatabase(){  
    abstract fun notes(): NoteStore  
}

1. The @Database annotation configures the code generation process, including:
   • Identifying all of the entity classes that you care about in the entities collection.
   • Identifying the schema version of the database.

• Java:

@Database(entities = {Trip.class}, version = 1)
abstract class TripDatabase extends RoomDatabase {
}

• Kotlin:

@Database(entities = [NoteEntity::class], version = 1)  
abstract class NoteDatabase: RoomDatabase(){  
}

2. You also need abstract methods for each DAO class that return an instance of that class.
    

• Java Example:
  • In this app, we have but one DAO (TripStore), so we have an abstract method to return an instance of TripStore.

abstract TripStore tripStore();

• Kotlin Example:
  • In this app, we have but one DAO (NoteStore), so we have an abstract method, to return an instance of NoteStore.

abstract fun notes() : NoteStore

In this example, the database is a singleton.

• TripDatabase has a static getter method, cunningly named get(), that creates our singleton.
• get(), in turn, calls a create method that is responsible for creating our TripDatabase.
   
• To create a TripDatabase, we use a RoomDatabase.Builder, which we get by calling one of the two methods on the Room class:
  • databaseBuilder() is what you will normally use.
  • inMemoryDatabaseBuilder() does what the method name suggests:
    • It creates an in-memory SQLite database, useful for instrumentation tests where you do not necessarily need to persist the data for a user.
    • As soon as the database is closed, the memory holding the database contents gets freed.
• Java Example:

static TripDatabase create(Context ctxt, boolean memoryOnly) {
	RoomDatabase.Builder<TripDatabase> b;

	if(memoryOnly)
		b = Room.inMemoryDatabaseBuilder(ctxt.getApplicationContext(), TripDatabase.class);
	else
		b = Room.databaseBuilder(ctxt.getApplicationContext(), 
		TripDatabase.class, DB_NAME);
	return b.build();	
}

• Kotlin Example:

private val db = Room.databaseBuilder(context, NoteDatabase::class, "notes.db")

• The result is that when we call get(), we get a singleton -lazy initialized TripDatabase.
• From there, we can:
  • Call tripStore() on the TripDatabase top retrieve the TripStore DAO.
  • Call methods on the TripStore to query, insert, update, or delete Trip objects.

package com.mariamaged.android.room.room1;  
  
import androidx.test.ext.junit.runners.AndroidJUnit4;  
import androidx.test.platform.app.InstrumentationRegistry;  
  
import org.junit.After;  
import org.junit.Before;  
import org.junit.Test;  
import org.junit.runner.RunWith;  
  
import java.util.List;  
  
import static junit.framework.TestCase.assertTrue;  
import static org.junit.Assert.assertEquals;  
import static org.junit.Assert.assertNotEquals;  
import static org.junit.Assert.assertNotNull;  
  
@RunWith(AndroidJUnit4.class)  
public class TripTests {  
    TripDatabase db;  
    TripStore store;  
  
    @Before  
  public void setUp() {  
        db = TripDatabase.create(InstrumentationRegistry.getInstrumentation().getTargetContext(), true);  
        store = db.tripStore();  
    }  
  
    @After  
  public void tearDown() {  
        db.close();  
    }  
  
    @Test  
  public void basics() {  
        assertEquals(0, store.selectAll().size());  
  
        final Trip first = new Trip("Foo", 2880);  
  
        assertNotNull(first.id);  
        assertNotEquals(0, first.id.length());  
        store.insert(first);  
  
        assertTrip(store, first);  
  
        final Trip updated = new Trip(first.id, "Foo!!", 1440);  
        store.update(updated);  
        assertTrip(store, updated);  
  
        store.delete(updated);  
        assertEquals(0, store.selectAll().size());  
  
    }  
  
    private void assertTrip(TripStore store, Trip trip) {  
        List<Trip> results = store.selectAll();  
  
        assertNotNull(results);  
        assertEquals(1, results.size());  
        assertTrue(areIdentical(trip, results.get(0)));  
  
        Trip result = store.findById(trip.id);  
  
        assertNotNull(result);  
        assertTrue(areIdentical(trip, result));  
    }  
  
    private boolean areIdentical(Trip one, Trip two) {  
        return (one.id.equals(two.id)) &&  
                (one.title.equals(two.id)) &&  
                (one.duration == two.duration);  
    }  
}

• Here, we:
  • Create an empty database.
  • Get the DAO (TripStore).
  • Confirm that there are no trips in the database.
  • Create a Trip object and insert() it into the database, then confirm that the database was properly inserted.
  • Create a new Trip object with the same ID as the first, update() the database using it, then confirm that the database was properly modified.
  • Delete the Trip object, then confirm that the database has no trips once again.

When testing a database, though, one of the challenges is in making these tests hermetic, or self-contained.

• One test method should not depend upon another test method.
• And one test method should not affect the results of another test method accidentally.

This means that we want to start with a known starting point before each test, and we have to consider how to do that.

• One approach- is to use an in-memory database.
• The static create() method on TripDatabase, if you pass true for the second parameter, creates a TripDatabase backed by memory and not disk.
   
• There are two key advantages of using an in-memory database for instrumentation testing:
  1. It is intrinsically self-contained.
     • Once the TripDatabase is closed, its memory is released, and if separate tests use separate TripDatabase instances, one will not affect the other.
  2. Reading and writing to and from memory is much faster than is reading and writing to and from disk, so the tests run much faster.

• The one downside to having an empty starter database, such as fresh-in memory, is that you have no data.
• Eventually, you need some data to test.
   
• Alternatives include:
  1. Loading the data from some neutral format (e.g., JSON) via some utility method.
  2. Packaging one or more starter databases as assests in the instrumentation tests (e.g., src/androidTest/assest/), then using ATTACH DATABASE ... and INSERT INTO ... SELECT FROM ... SQLite to copy from the starter database to the database to be used in testing.

package com.mariamaged.android.room.room1;  
  
import androidx.room.Dao;  
import androidx.room.Delete;  
import androidx.room.Insert;  
import androidx.room.Query;  
import androidx.room.Update;  
  
import java.util.List;  
  
@Dao  
public interface TripStore {  
    @Query("Select * FROM trips ORDER BY title")  
    List<Trip> selectAll();  
  
    @Query("SELECT * from trips WHERE id = :id")  
    Trip findById(String id);  
  
    @Insert  
    void insert(Trip... trips);  
  
    @Update  
    void update(Trip... trips);  
  
    @Delete  
    void delete(Trip... trips);  
  
}

• This is a pure interface.
• More importantly, other than annotations, its API is purely domain-specific.
• Everything revolves around our Trip entity and other business logic (e.g., String values as identifiers).
   
• Room DAOs are designed to be mocked, using a mocking library like Mockito.
  • So that you can write units tests (tests that run on your development machine) in addition to - or perhaps instead of - instrumentation tests.

The only absolute requirement for a Room entity class is that it be

• (1) annotated with the @Entity annotation and
• (2) have a field identified as the primary key, typically by way of a @PrimaryKey annotation.

Anything above and beyond that is optimal.

• If you have a single field that is the primary key for your entity, using the @PrimaryKey annotation is simple and helps you clearly identify that primary key at later point.

• In SQLite, if you have an INTEGER column identified by the PRIMARY KEY , you can have SQLite assign unique values for that column, by way of the AUTOINCREMENT keyword.
• In Room, if you have an int column or Integer field that is your @PrimaryKey, you can optionally apply AUTOINCREMENT to the corresponding column by adding autoGenerate=true to the annotation:

package com.mariamaged.android.room.room1;  
  
import androidx.annotation.NonNull;  
import androidx.room.Entity;  
import androidx.room.PrimaryKey;  
  
@Entity  
public class Constant {  
    @PrimaryKey(autoGenerate=true)  
    @NonNull  
  public int id;  
    String title;  
    double value;  
  
    @Override  
  public String toString() {  
        return title;  
    }  
}

By default, autoGenerate is false.

• Setting that property to true gives you AUTOINCREMENT in the generated CREATE TABLE statement.

CREATE TABLE IF NOT EXISTS Constants (
id INTEGER PRIMARY KEY AUTOINCREMENT,
title TEXT,
value REAL NOT NULL
);

• However, this starts to get complicated in the app.
• You do not know your primary key until you insert the entity into the database.
• That presents "trickle-down complications".
• For example, you cannot make the primary key final, as then you cannot create an instance of an entity that is not yet in the database.
• While you can try to work around this (e..g, default the id to -1), then you have to keep checking to see whether you have a valid identifier.
   
• Also, notice that the value column has NOT NUL applied to it.

Room's rule is that:

• Primitive fields (ints, doubles, etc.) will be NOT NULL.
• Their object equivalents (Integer, Double, etc.) will allow null values.

In some cases, you will have a composite primary key, made up of two or more columns in the database.

• This is particularly true if you are trying to design your entities around an existing database structure, one that used a composite primary key for one of its tables.

1. If, logically, those are all parts of a single object, you could combine them into a single field.
2. However, it may be that they should be individual fields in your entity, but they happen to combine to create the primary key.
   • In that case, you can skip the @PrimaryKey annotation.
   • And use the primaryKeys property of the @Entity.
      

• One scenario for this is data versioning, where we are tracking changes to data over time, the way a version control system tracks changes to source code and other files over time.
• One approach has all versions of the same entity in the same table, with a version code attached to the "natural" primary key to identify a specific version of that content.

@Entity(primaryKeys ={"id", "versionCode"})  
class VersionedThingy {  
    @NonNull  
  public final String id;  
    public final int versionCode;  
  
    VersionedThingy(String id, int versionCode) {  
        this.id = id;  
        this.versionCode = versionCode;  
    }  
}

CREATE TABLE IF NOT EXISTS VersionedThings
(
id TEXT NOT NULL,
versionCode INTEGER NOT NULL,
PRIMARY KEY(id, versionCode)
);

Your primary key is indexed automatically by SQLite.

• However, you may wish to set up other indexes for other columns or collection of columns, to speed up queries.
• To do that, use the indices property on @Entity.
• This property takes a list of @Index annotations, each of which declares an index.

Example

• A Customer entity might have an address, which might contain a postalCode.
• You might be querying directly on a postalCode as part of a search form, and so having an index on that would be useful.

@Entity(indices = {@Index("postalCode")})  
class Customer {  
    @PrimaryKey  
  public final String id;  
  
    public final String postalCode;  
    public final String displayName;  
  
    Customer(String id, String postalCode, String displayName) {  
        this.id = id;  
        this.postalCode = postalCode;  
        this.displayName = displayName;  
    }  
}

CREATE INDEX index_Customer_postalCode ON Customer(postalCode)

• If you have a composite index, consisting of one or more fields, @Index takes a comma-delimited list of column names and will generate the composite index.
• If the index should enforce uniqueness - only one entity can have the indexed value- add the unique=true to the @Index.

@Entity(indices = {@Index(value = "postalCode", unique = true)})  
class Customer {  
    @PrimaryKey  
  public final String id;  
  
    public final String postalCode;  
    public final String displayName;  
  
    Customer(String id, String postalCode, String displayName) {  
        this.id = id;  
        this.postalCode = postalCode;  
        this.displayName = displayName;  
    }  
}

CREATE UNIQUE INDEX index_Customer_postalCode ON Customer(postalCode)

If there are fields in the entity class that should not be persisted, annotate them with @Ignore.

@Entity(primaryKeys ={"id", "versionCode"})  
class VersionedThingy {  
  @NonNull  
  public final String id;  
  public final int versionCode;  
  
  @Ignore  
  private String something;  
  
    VersionedThingy(String id, int versionCode) {  
        this.id = id;  
        this.versionCode = versionCode;  
    }  
}

• The annotation is required.
• For example, this does not work:

@Entity(primaryKeys ={"id", "versionCode"})  
class VersionedThingy {  
   @NonNull  
  public final String id;  
  public final int versionCode;  
  

  private String something;  
  
    VersionedThingy(String id, int versionCode) {  
        this.id = id;  
        this.versionCode = versionCode;  
    }  
}

You might think that since the field is private has no setter, that Room would ignore it completely.

• Room, instead, generates a build error, as it cannot tell if you want to ignore that field, or if you simply forgot to add it properly.
• With Room, transient fields are ignored automatically by default, so in the following code snippet, something will be ignored.

@Entity(primaryKeys ={"id", "versionCode"})  
class VersionedThingy {  
  @NonNull  
  public final String id;  
  public final int versionCode;  
    
  public transient String something;  
  
  VersionedThingy(String id, int versionCode) {  
        this.id = id;  
        this.versionCode = versionCode;  
    }  
}

• Note that you can also @Ignore constructors.
  • This may be required to clear up Room build errors, if the code generator cannot determine what constructor to use.

@Entity(primaryKeys ={"id", "versionCode"})  
class VersionedThingy {  
    @NonNull  
  public final String id;  
    public final int versionCode;  
  
    @Ignore  
  private String something;  
  
    @Ignore  
  VersionedThingy() {  
        this(UUID.randomUUID().toString(), 1);  
    }  
    VersionedThingy(String id, int versionCode) {  
        this.id = id;  
        this.versionCode = versionCode;  
    }  
}

• Primitive field get converted into NOT NULL.
• Object fields allow null values.
   
• If you want an object field to be NOT NULL, apply the @NoNull annotation.

@Entity(indices = {@Index(value = "postalCode", unique = true)})  
class Customer {  
    @PrimaryKey  
 @NonNull  public final String id;  
  
    @NonNull  
  public final String postalCode;  
    public final String displayName;  
  
    Customer(String id, String postalCode, String displayName) {  
        this.id = id;  
        this.postalCode = postalCode;  
        this.displayName = displayName;  
    }  
}

By default, Room will generate names for your tables and columns based off of the entity class names and field names.

• You may find that you need to control these names.
  • Particularly if you are trying to match an existing database schema
  • (e.g. you are migrating an existing Android app to use Room instead of SQLite directly).
• And for table names in particular, setting your name can simplify some of the SQL that you have to write for @Query-annotated methods.

1. To control the table name, use the tableName property on the @Entity attribute,
 and give it a valid SQLite table name.

@Entity(tableName = "thingy", primaryKeys ={"id", "versionCode"})  
class VersionedThingy {  
    @NonNull  
  public final String id;  
    public final int versionCode;  
  
    @Ignore  
  private String something;  
  
    @Ignore  
  VersionedThingy() {  
        this(UUID.randomUUID().toString(), 1);  
    }  
    VersionedThingy(String id, int versionCode) {  
        this.id = id;  
        this.versionCode = versionCode;  
    }  
}

2. To rename a column, add the @ColumnInfo

Entity(tableName = "thingy", primaryKeys ={"id", "versionCode"})  
class VersionedThingy {  
    @NonNull  
  public final String id;  
  
    @ColumnInfo(name = "version_code")  
    public final int versionCode;  
  
    @Ignore  
  private String something;  
  
    @Ignore  
  VersionedThingy() {  
        this(UUID.randomUUID().toString(), 1);  
    }  
    VersionedThingy(String id, int versionCode) {  
        this.id = id;  
        this.versionCode = versionCode;  
    }  
}

• This fails.
• The values in the primaryKeys property are the column names, not the field names.
• Since we renamed the column, we need to update primaryKeys to match.

@Entity(tableName = "thingy", primaryKeys ={"id", "version_code"})  
class VersionedThingy {  
    @NonNull  
  public final String id;  
  
    @ColumnInfo(name = "version_code")  
    public final int versionCode;  
  
    @Ignore  
  private String something;  
  
    @Ignore  
  VersionedThingy() {  
        this(UUID.randomUUID().toString(), 1);  
    }  
    VersionedThingy(String id, int versionCode) {  
        this.id = id;  
        this.versionCode = versionCode;  
    }  
}

• Also note that adding @ColumnInfo to a transient field means that this field will be included when creating the table structure.
• By default, transient fields are ignored, but adding @ColumnInfo indicates that you want that default behavior to be overriden.

• You can add an index property to indicate that you want to index the column, as an alternative to listing the column in the indices property of the @Entity annotation.

@Entity(indices = {@Index("postalCode")})  
class Customer {  
    @PrimaryKey  
 @NonNull  public final String id;  
      
    public final String postalCode;  
    public final String displayName;  
  
    Customer(String id, String postalCode, String displayName) {  
        this.id = id;  
        this.postalCode = postalCode;  
        this.displayName = displayName;  
    }  
}

@Entity  
class Customer {  
    @PrimaryKey  
 @NonNull  public final String id;  
  
    @ColumnInfo(index = true)  
    public final String postalCode;  
    public final String displayName;  
  
    Customer(String id, String postalCode, String displayName) {  
        this.id = id;  
        this.postalCode = postalCode;  
        this.displayName = displayName;  
    }  
}

• You can specify a collate property to indicate the collation sequence to apply to this column.
• Collection sequence: is a fancy way of saying "comparison function for comparing two string".

There are four options.

1. BINARY and UNDEFINED, which are equivalent, the default value, and indicate that case is sensitive.
2. NOCASE, which indicates that case is not sensitive (more accurately, that the 26 English letters are converted to uppercase).
3. RTRIM, which indicates that trailing spaces should be ignored on a case-sensitive collation.

• There is no full UTF equivalent of NOCASE in SQLite.

• Normally, Room will determine the type on the column in SQLite based on the type of the field.
  • (e.g., int or Integer turn into INTEGER columns).
• If, for some reason, you wish to override this behavior, you can use the typeAffinity property on ColumnInfo to specify other type to use.

• As we saw with findById() on TripStore, you can map method arguments to query parameters by using : syntax.

@Query("SELECT * FROM thingy WHERE id = :id AND version_code = :versionCode")
VersionedThingy findById(String id, int versionCode);

Note that Room has special support for IN in a WHERE clause.

@Query("SELECT * FROM Customer WHERE postalCode IN (:postalCodes)"
List<Customer> findByPostalCodes(String postalCodes);

@Query("SELECT * FROM Customer WHERE postalCode IN (:postalCodes)")
List<Customer> findByPostalCodes(List<String> postalCodes);

@Query("SELECT * FROM Customer WHERE postalCode IN (:postalCodes)")
List<Customer> findByPostCodes(String... postalCodes);

• Room will convert the collation argument into a comma-delimited list for use with the SQL query.

If SQLite allows ? placeholders, Room should allow method arguments to be used instead.

• Because Java needs the varargs to be the last parameter, we need to have max first.

@Query("SELECT * FROM Customer WHERE postalCode IN (:postalCodes) LIMIT :max")
List<Customer> findByPostalCodes(int max, String... postalCodes);

• In addition to returning single objects or collections of objects, a Room @Query can return a good, old-fashioned Cursor.
• This is particularly useful if you are migrating legacy code that uses Cursor Adapter or other Cursor-specific classes.
   
• Similarly, if you are looking for to expose part of a Room-defined database via a ContentProvider, it may be more convenient for you to get your results in the form of a Cursor, so that you can just return that from the provider's query() method.

For small entities, like Trip, usually we will retrieve all columns in the query.

• However, the real rule is:
  • The core return object of the @Query method must be something that Room knows how to fill in from the columns that you request.
• You have two ways to do that:
  1. Have your @Entity support only a subset of columns, allowing the rest to be null or otherwise tracking the fact that we only retrieved a subset of columns from the table.
  2. Return something other than entity that you have associated with this table.

Suppose that Customer not only tracks an id and a postalCode, but also has many other fields, including a displayName.

@Entity(indices = {@Index(value = "postalCode", unique = true)})  
class Customer {  
    @PrimaryKey  
 @NonNull  public final String id;  
  
    public final String postalCode;  
    public final String displayName;  
  
    @Ignore  
  Customer(String postalCode, String displayName) {  
        this(UUID.randomUUID().toString(), postalCode, displayName);  
    }  
  
    Customer(String id, String postalCode, String displayName) {  
        this.id = id;  
        this.postalCode = postalCode;  
        this.displayName = displayName;  
    }  
      
}

• Perhaps to show a list of customers, we need:
  • The displayName (to show in the list) .
  • The id (to know which specific customer this is).
• but we do not need the postalCode or the rest of the fields in the Customer class.
• We can still return a Customer.
• The code that Room generates will simply fill in null for the postalCode, since that was not one of the returned columns.
• However, then it is not obvious whether a given instance of Customer is:
  • Completely filled in from data in the data.
  • Or whether this is a partially-populated Customer object.

@Query("SELECT id, displayName FROM Customer WHERE postalCode IN (:postalCodes) LIMIT :max")
List<Customer> findByPostalCodes(List<String> postalCodes, int max);

• However, we could also define a dedicated CustomerDisplayTuple class:

package com.mariamaged.android.room.room1;  
  
public class CustomerDisplayTuple {  
    public final String id;  
    public final String displayName;  
  
    public CustomerDisplayTuple(String id, String displayName) {  
        this.id = id;  
        this.displayName = displayName;  
    }  
}

• Then, we can return a List of CustomerDisplayTuple from our DAO.
• This way, we get our subset of data, and we know by class whether we have the full Customer or just the subset for our display purposes.

Note that @ColumnInfo annotations can be used on any class, not just entities.

In particular, if you use @ColumnInfo on a field in an entity, you will need the same @ColumnInfo on any "tuple"-style classes that represent subsets of data that include the same field.

@Query("SELECT id, displayName FROM Customer WHERE postalCode IN (:postalCodes) LIMIT :max")
List<CustomerDisplayTuple> loadDisplayTuplesByPostalCodes(int max, String... postalCodes);