-
Notifications
You must be signed in to change notification settings - Fork 132
Using SquidDatabase
SquidDatabase is your one-stop shop for all the read/write operations you'll do on your database. You define a subclass of SquidDatabase for each kind of database in your app, with one instance per distinct database. It supports all the basic CRUD operations as well as several other useful utilities.
Many of the insert operations on the database operate on instances of a TableModel subclass. Whatever properties are set on the instance of the table model will be used to insert a new row into the database. Typically, you will want to use the persist() method to insert a new row:
Person newPerson = new Person();
newPerson.setFirstName("Sam");
newPerson.setLastName("Bosley");
newPerson.setAge(26);
// This will insert a new row into the database
database.persist(newPerson);
// After the call to persist, the model will have an id,
// i.e. the _id of the new row that was inserted
long rowId = newPerson.getId();You can also use persistWithOnConflict() if you want to manually specify a conflict algorithm to use.
Note that persist() will only insert a new row if the model you are persisting doesn't have its id property set. If it does, SquidDatabase will treat it as an update instead (see the Update section below). If you want to create a new row from the model object regardless of whether or not an id is set, you can call createNew(model), which will clear the id property and insert a new row automatically. However, we recommend you use persist() or persistWithOnConflict() in most cases.
To read data from the database, you will either use the query() method or one of various fetch methods.
SquidDatabase.query() is probably the most common method of reading data from the database. It takes as arguments a model class and a SquiDB query object, and returns a SquidCursor.
The model class argument is optional (you can pass null), but recommended. Its purpose is twofold:
- SquidDatabase can automatically infer the FROM clause based on the model class, if one is not already specified on the query already. This can make your code a bit cleaner, especially for simple queries. Note that this only works for the "outer" query; if the Query contains any nested Query objects (e.g. "select from (select ...)", "select ... where column in (select ...)", any of the compound selects), then those must all have a FROM clause.
- The returned SquidCursor will have a type parameter of the given model class. This will serve as a hint to consumers of the cursor that they should strongly consider using that model class to interpret the results of the cursor.
// from clause is left out; SquidDatabase will infer it from Person.class
Query votersQuery = Query.select(Person.PROPERTIES).where(Person.AGE.gte(18));
SquidCursor<User> voters = database.query(Person.class, votersQuery);
try {
Person p = new Person();
for (voters.moveToFirst(); !voters.isAfterLast(); voters.moveToNext()) {
p.readPropertiesFromCursor(voters);
Log.i(p.getName() + " can vote, he/she is " + p.getAge() + " years old");
}
} finally {
voters.close();
}Note the pattern of creating a single Person object and repopulating it from the cursor during each loop iteration. See Maximizing SquiDB performance for more information.
There are three methods on SquidDatabase that begin with "fetch": fetch(), fetchByCriterion(), and fetchByQuery(). Like with query(), these methods can infer the table to select from. The fetch methods are distinct from query in that instead of returning a SquidCursor of many results, they return only a single model object of the type specified by the model class argument.
fetch() gets a model object by row id:
long rowId = 1;
Person person = database.fetch(Person.class, rowId);fetchByCriterion() returns the first row matching a given criterion object:
String guid = "xyz123";
User user = database.fetchByCriterion(User.class, User.GUID.eq(guid));fetchByQuery() returns the first row of the query result:
// Gets the user with the alphabetically lowest last name
Query query = Query.select().orderBy(Person.LAST_NAME.asc());
Person Person = database.fetchByQuery(Person.class, query);If you don't need to fetch all the columns for a model object but are only interested in a couple of them, all the fetch methods let you specify an optional varargs Property list:
long id = 1;
Person person = database.fetch(Person.class, id, Person.FIRST_NAME, Person.LAST_NAME);
// This will work
String fullName = person.getFirstName() + " " + person.getLastName();
// This may throw an exception or return a default value since "age" wasn't read:
int age = person.getAge();Updating a single model row works very similarly to insert as they both use the persist() method. When a model object has an id property set, persist() will update the row with that id using only the changed values on the model.
// The person passed to this method should have an id,
// i.e. it exists in the database
public void incrementPersonAge(Person person) {
person.setAge(person.getAge() + 1);
database.persist(person);
// The corresponding row in the database is updated
// with a new value for the age column
}If you want to update several rows at once based on a predicate rather than updating a single row, you can use update() or updateWithOnConflict(). These methods take a Criterion object and a "template" model object:
// This will update the firstName column to be "Samuel"
// for all rows in the users table with firstName = "Sam"
Person template = new Person();
template.setFirstName("Samuel");
database.update(Person.FIRST_NAME.eq("Sam"), template);There are two variants of delete: deleting a single row from a table by its id, or deleting all rows matching a predicate. Deleting a single row looks a lot like persist:
// delete the row with _id = 1
long rowId = 1;
database.delete(Person.class, rowId);Deleting multiple rows uses a Criterion object and looks a lot like update:
// Delete all people named Sam
database.deleteWhere(Person.class, Person.FIRST_NAME.eq("Sam"));SquidDatabase also includes insert/update/delete methods that take as arguments their corresponding SquiDB grammar objects instead of operating on model classes. We tend to prefer the model-based variants whenever possible, but some situations are better suited to using these statements, so feel free to use them if you prefer writing the SQL directly yourself.
SquidDatabase can count the number of rows in a table matching a predicate:
int howManySams = database.count(Person.class, Person.FIRST_NAME.eq("Sam"));If you want to count how many rows are in a table with no filter, use countAll:
int howManyUsers = database.countAll(Person.class);SquidDatabase has wrappers around Android's SQLiteDatabase transaction methods. You can begin a transaction, set a transaction successful, and commit a transaction.
database.beginTransaction();
try {
/* Do a bunch of inserts/updates/deletes/whatever */
database.setTransactionSuccessful();
} finally {
database.endTransaction();
}SquidDatabase has a convenience method for notifying a Uri of changes. This can be helpful particularly when you want to signal that interested SquidCursorLoaders should refresh themselves.
Person newPerson = new Person().setFirstName("Sam");
database.persist(newPerson);
database.notifyChange(Person.CONTENT_URI); // Anyone listening to the Uri will get notifiedAlternatively, you can set up data change notifications to happen automatically using UriNotifier or any other DataChangedNotifier.
See also: