Inquiry is a simple library for Android that makes construction and use of SQLite databases super easy.
Read and write class objects from tables in a database. Let Inquiry handle the heavy lifting.
The Gradle dependency is available via jCenter. jCenter is the default Maven repository used by Android Studio.
Add this to your module's build.gradle
file (make sure the version matches the last release):
dependencies {
// ... other dependencies
compile 'com.afollestad:inquiry:5.0.0'
}
- Quick Setup
- Instances
- Row Objects
- Getter and Setter Methods
- Builder Classes in Rows
- Querying Rows
- Inserting Rows
- Updating Rows
- Deleting Rows
- Dropping Tables
- ForeignKey Annotation
- Lazy Loading Children
- Extra: Accessing Content Providers
When your app starts, you need to initialize Inquiry. init(Context, String, int)
and destroy(Context)
can be used from anywhere that has a Context
, but a reliable place to do so is in an Activity:
public class MainActivity extends AppCompatActivity {
@Override
public void onResume() {
super.onResume();
// Creates an instance specifically for MainActivity
Inquiry.newInstance(this, "my_new_database").build();
}
@Override
public void onPause() {
super.onPause();
// Checking for isFinishing() makes sure the Activity is actually closing.
// onPause() can also be called when a Dialog opens, such as a permissions dialog.
if (isFinishing()) {
// Destroys only MainActivity's instance
Inquiry.destroy(this);
}
}
}
The static Inquiry instance that you access from this library is not always the same instance. For thread safety and other reasons, Inquiry supports uses multiple instances.
In the small example in the section above, the use of the newInstance
Builder creates a new instance for
MainActivity
. It will access a local database called "my_new_database".
The first parameter passed into newInstance()
references MainActivity
, which is an instance of android.content.Context
.
This is used to access resources, but it is also used to keep track of the newly created instance which is later destroyed in onPause()
.
If you wanted to keep track of instances with a custom string, you can:
Inquiry.newInstance(this, "my_new_database")
.instanceName("my_custom_instance")
.build();
It's very important that you destroy()
instances when you are done with them. This closes the
database lock and makes sure any memory references are cleaned up.
If for testing purposes, you want to use databases entirely in-memory as opposed to saving them in a file,
you can pass :memory
for the database name in Inquiry.newInstance(Context, String)
.
In Inquiry, a row is just an object which contains a set of values that can be read from and written to a table in your database. In a spreadsheet, a row goes from left to right; each cell is a column in the row.
@Table
public class Person {
public Person() {
// Default constructor is needed so Inquiry can auto construct instances
}
public Person(String name, int age, float rank, boolean admin, Person spouse) {
this.name = name;
this.age = age;
this.rank = rank;
this.admin = admin;
this.spouse = spouse;
}
@Column(name = "_id", primaryKey = true, notNull = true, autoIncrement = true)
public long id;
@Column
public String name;
@Column
public int age;
@Column
public float rank;
@Column
public boolean admin;
}
Notice that the class is annotated with the @Table
annotation. You must include that annotation on
any class which represents the row of a table. You can optionally specify a name in the annotation
arguments, just like you can with the @Column
annotation. By default, the name of a @Table
is
the lowercase name of the class, plus an 's'.
All the fields in the class are annotated with the @Column
annotation. If you have fields without that
annotation, they will be ignored by Inquiry.
Notice that the _id
field contains optional parameters in its annotation:
name
indicates a column name, if the column name is different than what you name the class field.primaryKey
indicates its column is the main column used to identify the row. No other row in the table can have the same value for that specific column. This is commonly used with IDs.notNull
indicates that you can never insert null as a value for that column.autoIncrement
indicates that you don't manually set the value of this column. Every time you insert a row into the table, this column will be incremented by one automatically. This can only be used with INTEGER columns (short, int, or long fields), however.
You saw that Inquiry can read and write fields to and from databases above. Inquiry also supports reading and writing properties via methods.
@Table public class Person {
private long id;
private String name;
private int age;
private float rank;
private boolean admin;
public Person() {
// Default constructor is needed so Inquiry can auto construct instances
}
public Person(String name, int age, float rank, boolean admin, Person spouse) {
this.name = name;
this.age = age;
this.rank = rank;
this.admin = admin;
this.spouse = spouse;
}
@Column(name = "_id", primaryKey = true, notNull = true, autoIncrement = true)
public long getId() {
return id;
}
public void setId(long id) {
this.id = id;
}
@Column public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@Column public int getAge() {
return age;
}
public void setAge(int age) {
return age;
}
@Column public float getRank() {
return rank;
}
public void setRank(float rank) {
this.rank = rank;
}
@Column public boolean getAdmin() {
return admin;
}
public void setAdmin(boolean admin) {
this.admin = admin;
}
}
We used get
/set
prefixes here. If you prefix a getter with get
, you must prefix the setter
with set
too. You can however completely omit get
/set
altogether, as long as you do it for
both the getter and setter.
@Table public class Person {
private long id;
private String name;
public Person() {
// Default constructor is needed so Inquiry can auto construct instances
}
public Person(String name) {
this.name = name;
}
@Column(name = "_id", primaryKey = true, notNull = true, autoIncrement = true)
public long id() {
return id;
}
public void id(long id) {
this.id = id;
}
@Column public String name() {
return name;
}
public void name(String name) {
this.name = name;
}
}
Note: if you don't include a setter method for a getter method, Inquiry will go straight to using the field to set values, while continuing to use the getter method to retrieve them.
Inquiry supports using a nested Builder
class for row construction. If your row includes an _id
field, you must also include a withId(long)
method that returns a new instance of the class
that has an ID updated to the value of the method parameter.
@Table public class Person {
private long id;
private String name;
private Person() {
// Default constructor is needed so Inquiry can auto construct instances
}
private Person(long id, String name) {
this.id = id;
this.name = name;
}
@Column(name = "_id", primaryKey = true, notNull = true, autoIncrement = true)
public long id() {
return id;
}
public Person withId(long id) {
return Person(id, this.name);
}
@Column public String name() {
return name;
}
public static class Builder {
private long id;
private String name;
public Builder() {
}
public Builder id(long id) {
this.id = id;
return this;
}
public Builder name(String name) {
this.name = name;
return this;
}
public Person build() {
return new Person(id, name);
}
}
}
If your Builder class is not named Builder
, you can add a RowBuilder
annotation to the class!
This comes in especially useful when using @AutoValue!
@Table
@AutoValue
abstract class Person {
public static Builder create() {
return new AutoValue_Person.Builder();
}
@Column(autoIncrement = true, name = "_id", primaryKey = true)
public abstract long id();
public Person withId(long id) {
return new AutoValue_Person.Builder(this).id(id).build();
}
@Column public abstract String username();
@AutoValue.Builder static abstract class Builder {
public abstract Builder id(long id);
public abstract Builder username(String username);
public abstract Person build();
}
}
This AutoValue class is 100% supported by Inquiry! When Inquiry reads this type of row from a table,
the Builder is automatically used for construction. AutoValue classes that do not have a Builder
are not currently supported due to complexity.
Querying a table retrieves rows, whether its every row in a table or rows that match a specific criteria. Here's how you would retrieve all rows from a table called "people":
// NOTE: if you pass a custom instance name rather than just a Context, pass the instance name into get() instead of a Context
Person[] result = Inquiry.get(this)
.select(Person.class)
.all();
If you only needed one row, using first()
instead of all()
is more efficient:
// NOTE: if you pass a custom instance name rather than just a Context, pass the instance name into get() instead of a Context
Person result = Inquiry.get(this)
.select(Person.class)
.first();
You can also perform the query on a separate thread using a callback:
// NOTE: if you pass a custom instance name rather than just a Context, pass the instance name into get() instead of a Context
Inquiry.get(this)
.select(Person.class)
.all(new GetCallback<Person>() {
@Override
public void result(Person[] result) {
// Do something with result
}
});
Inquiry will automatically fill in your @Column
fields with matching columns in each row of the table.
As mentioned in a previous section, @ForeignKey
fields are also automatically pulled from their foreign table.
If you wanted to find rows with specific values in their columns, you can use where
selection:
// NOTE: if you pass a custom instance name rather than just a Context, pass the instance name into get() instead of a Context
Person[] result = Inquiry.get(this)
.select(Person.class)
.where("name = ? AND age > ?", "Aidan", 21)
.all();
The first parameter is a string, specifying two conditions that must be true (AND
is used instead of OR
).
The question marks are placeholders, which are replaced by the values you specify in the second comma-separated
vararg (or array) parameter.
If you wanted, you could skip using the question marks and only use one parameter:
.where("name = 'Aidan' AND age > 21");
However, using the question marks and filler parameters can be easier to read if you're filling them in with variables. Plus, this will automatically escape any strings that contain reserved SQL characters.
Inquiry includes a convenience method called atPosition()
which lets you perform operations on a specific row
in your tables:
// NOTE: if you pass a custom instance name rather than just a Context, pass the instance name into get() instead of a Context
Person result = Inquiry.get(this)
.select(Person.class)
.atPosition(24)
.first();
Behind the scenes, it's using where(String)
to select the row. atPosition()
moves to a row position
and retrieves the row's _id
column. So, tables need to have an _id
column (which is unique for every row)
for this method to work. atPosition(int)
can even be used when updating or deleting, not just for selection.
Here's basic usage of where-in:
// NOTE: if you pass a custom instance name rather than just a Context, pass the instance name into get() instead of a Context
Person[] result = Inquiry.get(this)
.select(Person.class)
.whereIn("age", 19, 21)
.whereNotIn("age", 31, 34)
.all();
The query above will retrieve any rows where the age is equal to 19
or 21
, and not equal to 31
or 34
.
You can pass an array in place of 19, 21
too. Note that whereIn
can be used with updating and deletion too.
You can combine multiple where and where-in statements together:
// NOTE: if you pass a custom instance name rather than just a Context, pass the instance name into get() instead of a Context
Person[] result = Inquiry.get(this)
.select(Person.class)
.where("age > 8")
.where("age < 21")
.orWhereIn("name", "Aidan", "Waverly")
.whereNotIn("name", "Natalie", "Lao")
.all();
The above query translates to this where statement:
SELECT * FROM people WHERE age > 8 AND age < 21 OR name IN ('Aidan', 'Waverly')
where()
, whereIn()
, and whereNotIn()
all have variations that begin with or
.
SQL allows you to use the word LIKE
to search for things which aren't an exact match. Take this example:
// A percent sign in SQL is a wildcard. This will match anything which contains "Android".
String searchTerm = "%Android%";
Product[] results = Inquiry.get(MainActivity.this)
.select(Product.class)
.where("name LIKE ?", searchTerm)
.orWhere("description LIKE ?", searchTerm)
.orWhere("brand LIKE ?", searchTerm)
.all();
The code above will find any Product
's which have a name that contains "Android", a description
that contains "Android", or a brand that contains "Android". The percent symbol used as a prefix and
suffix in the search term means it will match anything, as long as "Android" is in the middle. You could
also use Android%
for a startsWith-like query, or %Android
for an endsWith-like query.
Note: you must put the wildcards in the arguments, putting it in the first string parameter where you see the word "LIKE" will not work on Android.
Projection allows you to only retrieve specific columns. Take this example, using the Person
class
made in a section above:
// NOTE: if you pass a custom instance name rather than just a Context, pass the instance name into get() instead of a Context
Person[] result = Inquiry.get(this)
.select(Person.class)
.projection("age", "rank")
.all();
This would retrieve all rows in the people
table. Each row would only have the age
and rank
fields
populated; all other fields would have their default values (e.g. null
or 0
).
One specific situation where projection is useful is if you were migrating a table to a new table that has more columns. If you try to select columns that don't exist in a table, you'd get an error; for migration, you'd have to select only the columns that exist in the source table. Projection also makes queries fast and use less memory.
This code would limit the maximum number of rows returned to 100. It would sort the results by values in the "name" column, in descending (Z-A, or greater to smaller) order:
// NOTE: if you pass a custom instance name rather than just a Context, pass the instance name into get() instead of a Context
Person[] result = Inquiry.get(this)
.select(Person.class)
.limit(100)
.sortByDesc("name", "rank")
.sortByAsc("age")
.all();
The above would sort every row by name descending (large to small, Z-A) first, then by rank descending, and then by age ascending (small to large). 100 rows would be returned, at the maximum.
If you prefer using a full SQL string for sorting, you can:
.sort("name DESC, rank DESC, age ASC")
These simple methods allow you to check various conditions on returned objects. It's recommended that you enable the Jack compiler and Java 8 in your projects to take advantage of the clean syntax shown here.
The any()
predicate can be used with no parameter, which will return true if there were any rows returned at all.
boolean anythingReturned = Inquiry.get(this)
.select(Row.class)
.any();
You can add a predicate parameter too. In the block below, any()
returns true if any rows have a
name matching "Aidan".
boolean aidanReturned = Inquiry.get(this)
.select(Row.class)
.any(it -> it.name.equals("Aidan"));
none()
does the opposite, obviously. Here, it will return true if no rows were returned.
boolean nothingReturned = Inquiry.get(this)
.select(Row.class)
.none();
You can again add a predicate parameter. Here it returns true if no rows returned have a name matching "Aidan".
boolean aidanNotReturned = Inquiry.get(this)
.select(Row.class)
.none(it -> it.name.equals("Aidan"));
Insertion is pretty straight forward. This inserts three People
into the table "people":
Person one = new Person("Waverly", 19, 8.9f, false);
Person two = new Person("Natalie", 42, 10f, false);
Person three = new Person("Aidan", 21, 5.7f, true);
Person[] people = new Person[] { one, two, three };
// NOTE: if you pass a custom instance name rather than just a Context, pass the instance name into get() instead of a Context
Long[] insertedIds = Inquiry.get(this)
.insert(Person.class)
.values(people)
.run();
Inquiry will automatically pull your @Column
fields out and insert them into the table people
.
Like all()
, run()
has a callback variation that will run the operation in a separate thread:
// NOTE: if you pass a custom instance name rather than just a Context, pass the instance name into get() instead of a Context
Inquiry.get(this)
.insert(Person.class)
.values(people)
.run(new RunCallback() {
@Override
public void result(Long[] insertedIds) {
// Do something
}
});
If your row class contains a field called _id
with autoIncrement
set to true, this field will
automatically be updated to a newly inserted row ID.
Updating is similar to insertion, however it results in changed rows rather than new rows. You can also use
WHERE
statements like you can with querying.
Person two = new Person("Natalie", 42, 10f, false);
// NOTE: if you pass a custom instance name rather than just a Context, pass the instance name into get() instead of a Context
Integer updatedCount = Inquiry.get(this)
.update(Person.class)
.values(new Person[] { two })
.where("name = ?", "Aidan")
.run();
The above will update all rows whose name is equal to "Aidan", setting all columns to the values in the Person
object called two
. If you didn't specify where()
args, every row in the table would be updated.
Like querying, atPosition(int)
can be used in place of where(String)
to update a specific row.
Sometimes, you don't want to change every column in a row when you update them. You can choose specifically
what columns you want to be changed using projection
:
Person two = new Person("Natalie", 42, 10f, false);
// NOTE: if you pass a custom instance name rather than just a Context, pass the instance name into get() instead of a Context
Integer updatedCount = Inquiry.get(this)
.update(Person.class)
.values(new Person[] { two })
.where("name = ?", "Aidan")
.projection("age", "rank")
.run();
The above code will update any rows with their name equal to "Aidan", however it will only modify
the age
and rank
columns of the updated rows. The other columns will be left alone.
The examples above set values to multiple matching rows at the same time. Inquiry also makes it possible to updating multiple individual rows at the same time, each having their own separate values.
Person one = // ... retrieve from a query
Person two = // ... retrieve from a query
Person[] people = new Person[] { one, two };
Integer updatedCount = Inquiry.get(this)
.update(Person.class)
.values(people)
.run();
The objects passed to values()
must be already existing rows which have populated _ids
. Each
row is updating in the database individually.
Deletion is simple:
// NOTE: if you pass a custom instance name rather than just a Context, pass the instance name into get() instead of a Context
Integer deletedCount = Inquiry.get(this)
.delete(Person.class)
.where("age = ?", 21)
.run();
The above code results in any rows with their age column set to 21 removed. If you didn't
specify where()
args, every row in the table would be deleted.
Like querying, atPosition(int)
can be used in place of where(String)
to delete a specific row.
You can also delete multiple individual rows from their objects:
Person one = // ... retrieve from a query
Person two = // ... retrieve from a query
Person[] people = new Person[] { one, two };
Integer deletedCount = Inquiry.get(this)
.delete(Person.class)
.values(people)
.run();
The objects passed to values()
must be already existing rows which have populated _ids
. Each
row is deleted from the database individually.
Dropping a table means deleting it. It's pretty straight forward:
// NOTE: if you pass a custom instance name rather than just a Context, pass the instance name into get() instead of a Context
Inquiry.get(this)
.dropTable(Person.class);
Just pass table name, and it's gone.
Inquiry provides a special annotation called @ForeignKey
. It allows you to specify relationships between
a table and another. Take the example below. Person
is a parent class, it has a List<>
of Child
objects.
public class Person {
public Person() {
// Default constructor is needed so Inquiry can auto construct instances
}
public Person(String name) {
this.name = name;
}
@Column(name = "_id", primaryKey = true, notNull = true, autoIncrement = true)
public long id;
@Column
public String name;
// inverseFieldName is optional, here it refers to the "parent" field in the Child class which gets set to a reference to this Person
@ForeignKey(tableName = "children", foreignColumnName = "parentId", inverseFieldName = "parent")
public List<Child> children;
}
public class Child {
public Child() {
// Default constructor is needed so Inquiry can auto construct instances
}
@Column(name = "_id", primaryKey = true, notNull = true, autoIncrement = true)
public long id;
@Column
public String name;
@Column
public long parentId;
public Person parent;
}
Since inverseFieldName
is set to point to the "parent" field in Child
, parent
will be set to a reference
of the Person
object during queries.
During insertion, Inquiry will first insert the parent Person
object. Its _id
field will be
populated to the new row ID. It will then loop through Child
objects inside of the children
ArrayList,
and insert each object. The parentId
of each child will be set to the _id
of the parent object.
During querying, the parent object will be retrieved first. It will then retrieve all children which
have a parentId
matching the parent object and populate the children
ArrayList.
During updating, the parent object will be updated first. It will then update each child which is
present in the children
ArrayList. Any rows in the foreign table that are no longer in the children
List will be deleted (which have a parentId
matching the _id
of the parent object). Any new rows added
to the children
List will be inserted into the foreign table.
During deletion, all children with a parentId
matching the _id
of the parent object will be deleted, followed
by the parent object.
Note that you are not limited to one level of foreign children. Foreign children can also have their own foreign children, which are all managed by the library.
In addition to using a List
with the @ForeignKey
annotation, you can also use arrays, which behave the same
way as Lists. The only difference being that you can't dynamically add or remove objects from an array without
reconstructing them.
public class Person {
public Person() {
// Default constructor is needed so Inquiry can auto construct instances
}
@Column(name = "_id", primaryKey = true, notNull = true, autoIncrement = true)
public long id;
// inverseFieldName is optional, here it refers to the "parent" field in the Child class which gets set to a reference to this Person
@ForeignKey(tableName = "children", foreignColumnName = "parentId", inverseFieldName = "parent")
public Child[] children;
}
You can even use single object instances with @ForeignKey
, which will reference and keep track of
one single row in a foreign table rather than a collection.
public class Person {
public Person() {
// Default constructor is needed so Inquiry can auto construct instances
}
@Column(name = "_id", primaryKey = true, notNull = true, autoIncrement = true)
public long id;
// inverseFieldName is optional, here it refers to the "parent" field in the Child class which gets set to a reference to this Person
@ForeignKey(tableName = "children", foreignColumnName = "parentId", inverseFieldName = "parent")
public Child child;
}
Lazy loading is pretty common on mobile platforms. Think of how you would load profile pictures in the timeline of a social media client; they load as you scroll and the image views come into view, not all right up front. This is called lazy loading.
Inquiry allows you to lazy load @ForeignKey
children of parent objects. This can help performance in
cases where your app may not need to use the children in some areas, but will in others. Take this example
which uses Inquiry's specxial LazyLoaderList
class.
public class Parent {
public Parent() {
children = new LazyLoaderList<>();
}
@Column(name = "_id", primaryKey = true, autoIncrement = true, notNull = true)
public long id;
@Column
public String name;
@ForeignKey(tableName = "children", foreignColumnName = "parentId", inverseFieldName = "parent")
public LazyLoaderList<Child> children;
}
public class Child {
public Child() {
}
@Column(name = "_id", primaryKey = true, autoIncrement = true, notNull = true)
public long id;
@Column
public long parentId;
public Parent parent;
}
The field which uses LazyLoaderList<Child>
above is a lazily loaded list. LazyLoaderList
is actually
implementing Java's List<>
interface in the background, so it can be used like a regular ArrayList<>
.
Note that it does include some utility methods such as any(AnyPredicate)
, none(AnyPredicate)
,
first(AnyPredicate)
, last(AnyPredicate)
, etc.
The difference when you use this instead of ArrayList
is that the children
won't actually be populated
from the foreign table until you access the LazyLoaderList
initially. That means if you don't use it
at all, the query will never be made. This highly optimizes initial query performance, since the first query
retrieves all Parent
's but doesn't have to retrieve each instances children right away, too.
NOTE: the LazyLoaderList
will only work if the Parent
object is retrieved through a query before accessing it.
Inquiry allows you to access content providers, which are basically external databases used in other apps. A common usage of content providers is Android's MediaStore. Most local media players use content providers to get a list of audio and video files scanned by the system; the system logs all of their meta data so the title, duration, album art, etc. can be quickly accessed.
Inquiry setup is still the same, but passing a database name is not required for content providers.
public class MainActivity extends AppCompatActivity {
@Override
public void onResume() {
super.onResume();
Inquiry.newInstance(this, null).build();
}
@Override
public void onPause() {
super.onPause();
if (isFinishing()) {
Inquiry.destroy(this);
}
}
}
This small example will read artists (for songs) on your phone. Here's the row class:
public class Photo {
public Photo() {
}
@Column(name = MediaStore.Images.Media._ID)
public long id;
@Column(name = MediaStore.Images.Media.TITLE)
public String title;
@Column(name = MediaStore.Images.Media.DATA)
public String path;
@Column(name = MediaStore.Images.Media.DATE_MODIFIED)
public long dateModified;
}
You can perform all the same operations, but you pass a content://
URI instead of a table name:
Photo[] photos = Inquiry.get(this)
.selectFrom(MediaStore.Images.Media.EXTERNAL_CONTENT_URI, Photo.class)
.all();
Insert, update, and delete work the same way. Just pass that URI instead of a table name.