Android Architecture Room DAOs - mariamaged/Java-Android-Kotlin GitHub Wiki

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

• A @Query can also return an int, for simple aggregate functions:

@Query("SELECT COUNT(*) FROM Customer")
int getCustomerCount();

• If you wish to compute several aggregate functions, create a "tuple"-style class to hold the values:

package com.mariamaged.android.room.room1;  
  
public class CustomerStats {  
    public final int count;  
    public final String max;  
  
    public CustomerStats(int count, String max) {  
        this.count = count;  
        this.max = max;  
    }  
}

• And use AS to name the aggregate function "columns" to match the tuple:

@Query("SELECT COUNT(*) AS count, MAX(postalCode) FROM Customer AS max FROM Customer")
CustomerStats getCustomerStats();

Sometimes, you do not know the query at compile time.

• One scenario for this is when you want to expose a Room-managed database via a ContentProvider to third-party apps.
• You could document that you support a limited set of options in your provider's query() method, ones that you can map to @Query methods on you DAO.

Room 1.1.0 added a new option for this: @RawQuery.

• Like @Query, this is an annotation that you can add to your method on your @Dao.
• And, like @Query, you can have that method return instances of an @Entity or other POJO.
• However, rather than supplying a fixed SQL statement in the annotation, you can provide SupportSQLiteQuery object as a parameter to the @RawQuery method.
   
• A SupportSQLiteQuery comes from the support database API, which is how Room interacts with your Room database.
• Fortunately, for the purpose of @RawQuery, the only thing that you need from the API is SimpleSQLiteQuery.
• Its constructor takes the same two parameters as does rawQuery() on a SQLiteDatabase:
  • The SQL statement to execute.
  • An Object array of values to use to replace positional placeholders.

@RawQuery
abstract List<Foo> _findMeSomething(SupportSQLiteQuery query);

@RawQuery
abstract List<Foo> _findMeSomething(SupportSQLiteQuery query);

List<Foo> findMeSomething(String value) {
	return _findMeSomething(new SimpleSQLiteQuery("SELECT some, columns FROM your_table WHERE something = ?", new Object[] value));
}

To get data out of a database, generally it is useful to put data into it.

Generally speaking, these scenarios are simpler than @RawQuery.

• @Insert, @Update, @Delete work with entities.
• TripStore uses varargs, so we can pass zero, one, or several Trip objects, though passing zero objects would be a waste of time.
• However, in addition to varargs, you can have these methods accept:
  • A single entity.
  • Individual entities as separate parameters (void insert(Trip trip1, Trip trip2)).
  • A list of entities.

Frequently, you have these methods return void.

• However:
  • For @Update and @Delete,
    • You can have them return an int.
    • Which will be the number of rows affected by the update or delete operations.
  • For an @Insert method accepting a single entity.
    • You can have it return a long.
    • Which will be the ROWID of the entity (and, if you are using an auto-increment int as your primary key, this will also be that key).
  • For an @Insert method accepting multiple entities.
    • You can have it return an array of long objects.
    • Or a List of Long objects.
    • Being the corresponding ROWID values for these inserted entities.

• @Insert and @Update support an optional onConflict property.
• This maps to SQLite's ON CONFLICT clause and indicates which should happen if there is either:
  • A uniqueness violation (duplicate primary key).
  • A NOT NULL violation when the insert or update should occur.
     
• The default strategy for @Insert and @Update is ABORT.
• You might want to consider changing that to be ROLLBACK, particularly if you start using transactions.

@Insert(onConflict = onConflictStrategy.ROLLBACK)
void insert(Trip... trips);

The primary problem with @Insert, @Update, and @Delete is that they need entities.

• In part, that is so the DAO method knows what table to work against.
   

For anything else, use @Query.

• @Query not only works with operations that return result sets, but with any SQL that you wish to execute, even if that SQL does not return a result set.

Examples

@Query("DELETE FROM Customer")
void nukeCustomersFromOrbit();

@Query("DELETE FROM Customer WHERE id IN (:ids)")
int nukeCertainCustomersFromOrbit(String... ids);

• By default, SQLite treats each individual SQL statement as an individual transaction.
• To the extent that Room winds up generating multiple SQL statements in response to out annotations, it is Room's responsibility to wrap these statements in a suitable transaction.
   
• However, sometimes, you have business logic that requires a transaction, for operations that require multiple DAO methods.
• For example, persisting an invoice might involve inserting an Invoice might involve:
  • Inserting an Invoice.
  • And all of its InvoiceLineItem objects.
  • And that might require more than one DAO method to achieve.

Your DAO can have one or more methods that have the @Transaction annotation.

• Whatever a @Transaction- annotated method does is wrapped in a SQLite transaction.
• The transaction will be committed if the @Transaction- annotated method does not throw an exception.

• Here, the idea is that your @Transaction- annotated method would make multiple DAO calls to other methods (e.g., ones with @Insert or @Query annotations), so that the work performed in those other methods "succeed or fail as a whole".
• Note that you will need to use an abstract class, not an interface, as an interface cannot have arbitrary method implementations in them.

public abstract class InvoiceStore {  
  
    @Insert  
   public abstract void _insert(Invoice invoice);  
  
    @Insert  
   public abstract void insert(List<InvoiceLineItem> lineItems);  
   
    @Transaction  
    public void insert(Invoice invoice) {  
        _insert(invoice);  
        insert(invoice.getLineItems());  
    }  
}
}

• It may seem odd to specifically request a transaction on a @Query-annotated method.
• After all, the default behaviour of SQLite is to have each individual SQL statement be in its own transaction.

First Scenario

• One is tied to @Relation.

Second Scenario

• The other is tied to Android's SQLite support: things gets weird when the result set of a query exceeds 1 MB.
• In that case, using the regular Android SQLiteDatabase API, the Cursor that you get back does not contain the full result set.
• Instead, it contains a "window" of results.
• And, if you position the Cursor after that window, the query is re-executed to load in the next window.
• This can lead to inconsistencies, if the database is changed in between those two database requests to populate the window.
   

Room, by default, will load the entire result set into your entities, quickly moving through the windows as needed, but there is still a chance that a database modification occurs while this is going on.

• Using @Transaction would help ensure that this is not an issue, by having the entire query - including traversing the window - occur inside a transaction.