The Developing Android Apps in Kotlin course assumes that you are familiar with the following subjects:
- Databases in general
- SQL databases in particular
- The SQL language used to interact with databases
This page is a refresher and quick reference.
SQL databases store data in tables of rows and columns:
- The intersection of a row and column is called a field.
- Fields contain data, references to other fields, or references to other tables.
- Each row contains one entity. The entity is identified by a unique ID which is usually used as its primary key.
- Each column is identified by a name that is unique per table.
SQLite implements an SQL database engine that has the following characteristics:
- Self-contained (requires no other components)
- Serverless (requires no server backend)
- Zero-configuration (does not need to be configured for your app)
- Transactional (the changes within a single transaction in SQLite either occur completely or not at all)
SQLite is the most widely deployed database engine in the world. The source code for SQLite is in the public domain. For details of the SQLite database, see the SQLite website.
- A database named
- A table named
- Columns for
After inserting the words
alpha has two definitions, the table might look like this:
To find what's in a specific row, use the
_id, or retrieve rows
by formulating queries that select rows from the table by specifying
A transaction is a sequence of operations performed as a single logical unit of work. To qualify as a transaction, a logical unit of work must exhibit four properties: atomicity, consistency, isolation, and durability (ACID):
- Atomicity. Either all of a transaction's data modifications are performed, or none of the modifications are performed. Atomicity is true even if a program crash, operating-system crash, or power failure interrupts the act of writing the change to the disk.
- Consistency. When a transaction is completed, the transaction must leave all data in a consistent state.
- Isolation. Modifications made by concurrent transactions must be isolated from modifications made by concurrent transactions. A transaction either recognizes data in the state the data was in before another concurrent transaction modified it, or the transaction recognizes the data after the second transaction has completed. The transaction does not recognize an intermediate state.
- Durability. After a transaction has completed, its effects are permanently in place in the system. The modifications persist even in the event of a system failure.
Examples of transactions:
- Transferring money from a savings account to a checking account.
- Entering a term and definition into dictionary.
- Committing a changelist to the master branch.
For more on transactions, see Atomic Commit In SQLite.
You use the SQL query language to interact with the database. Queries can be very complex, but there are four basic operations:
- Inserting rows
- Deleting rows
- Updating values in rows
- Retrieving rows that meet given criteria
On Android, the data access object (DAO) provides convenience methods for inserting, deleting, and updating the database. For a full description of the query language, see SQL As Understood By SQLite.
An SQL query is highly structured. Sample query:
SELECT word, definition FROM WORD_LIST_TABLE WHERE word="alpha"
Generic version of the sample query:
Parts of the sample query:
SELECTcolumns: Select the columns to return. Use
*to return all columns.
FROMtable: Specify the table from which to get results.
WHERE: Optional keyword that precedes conditions that have to be met, for example column="value". Common operators are
>. To connect multiple conditions, use
Other parts of queries:
ORDER BY: Optional key phrase for ordering results by a column. Specify
ASCfor ascending and
DESCfor descending. If you don't specify an order, you get the default order, which might be unordered.
LIMIT: Keyword to specify a limited number of results.
Sample queries and results
The following queries use the previously defined table:
SELECT * FROM WORD_LIST_TABLE
|Gets all the rows in
|SELECT word, definition FROM WORD_LIST_TABLE WHERE _id > 2||Selects the
SELECT _id FROM WORD_LIST_TABLE WHERE word="alpha" AND definition LIKE "%art%"
SELECT definition FROM WORD_LIST_TABLE ORDER BY word DESC LIMIT 1
|Selects all definitions. Sorts in reverse and gets the first row after the
list is sorted. Sorting is by the column specified which is
SELECT * FROM WORD_LIST_TABLE LIMIT 2,1
|Returns 1 item starting at position 2. Position counting starts at 1 (not
You can practice creating and querying databases at this SQL Fiddle website.
Queries for Android SQLite
You can send queries to the SQLite database of the Android system as raw queries or as parameters.
sql, String selectionArgs) method runs the provided SQL. The
method returns a
of the result set. The following table shows how the first two sample queries
from above would look as raw queries:
String query = "SELECT * FROM WORD_LIST_TABLE";
query = "SELECT word, definition FROM WORD_LIST_TABLE WHERE _id> ? ";
table, String columns, String selection, String selectionArgs, String
groupBy, String having, String orderBy, String limit) method
queries the given table. The method returns a
Cursor over the
result set. Here's a query showing how to fill in the arguments:
SELECT * FROM WORD_LIST_TABLE
The query returns the following:
Example of arguments that you can use:
String table = "WORD_LIST_TABLE"
Note: In real code, you wouldn't create
null values. See the Android
documentation for versions of this method with different parameters.
A cursor is a pointer into a row of structured data. You can think of a cursor as a pointer to a table row.
A query returns a
object that points to the first element in the query result. The
Cursor class provides methods for moving the cursor through the
query result, and methods to get the data from the columns of each row in the
When a method returns a
Cursor object, you iterate over the
result, extract the data, do something with the data, and close the cursor to
release the memory.