SQLite Database
Android stores your database in private disk space for the application so no other applications by default can access your database.
Data Types
- Integer
- Real
- Text
- Blob
Basic Steps
- Design the database, i.e., define tables and their columns.
- Extend SQLiteOpenHelper and override onCreate(), onUpgrade(), and optionally onOpen() callback methods to create a new database or connect to the existing database.
- Get a SQLiteDatabase object to represent the database by calling either SQLiteOpenHelper#getWritableDatabase() for both read and write or SQLiteOpenHelper#getReadableDatabase() for queries only.
- Use SQLDatabase to perform database CRUD operations on the database.
- Close the database by calling SQLiteDatabase#close().
Designing the Database
For the whole database, you should define a contract class, which defines table names and their column names as String constants. This contract class becomes a central repository for table names and column names so they can be changed at this one place and propagated to the rest of you application.
For example, if we have a database named MailOrder and two tables, Customer and Product, we can define a contract class:
public final class MailOrderContract {
publicMailOrderContract() {}
public static abstract class Customer implements BaseColumns {
public static final String TABLE_NAME = “customer”;
public static final String COLUMN_NAME_FNAME = “fname”;
public static final String COLUMN_NAME_LNAME = “lname”;
public static final String COLUMN_NAME_ADDRESS = “address”;
}
public static abstract class Product implements BaseColumns {
public static final String TABLE_NAME = “product”;
public static final String COLUMN_NAME_ITEM_ID = “itemid”;
public static final String COLUMN_NAME_LNAME = “name”;
public static final String COLUMN_NAME_DESCRIPTION = “description”;
public static final String COLUMN_NAME_UNIT_PRICE = “price”;
}
SQLiteOpenHelper
SQLiteOpenHelper helps you create your database and get references to it. You should subclass SQLiteOpenHelper and override onCreate(), onUpgrade() and optionally onOpen() callback method.
For example, for our MailOrder database,
public class MailOrderDbOpenHelper extends SQLiteOpenHelper {
public static final int DATABASE_VERSION = 1;
public static final String DATABASE_NAME = “mailorde.db”;
private static final String TEXT_TYPE = “ TEXT”;
private static final String INTEGER_TYPE = “ INTEGER”;
private static final String REAL_TYPE = “ REAL”;
private static final String COMMA = “, “;
// compose the CREATE statement for customer table
private static final SQL_CREATE_CUSTOMER =
“CREATE TABLE “ +customer.TABLE_NAME + “(“ +
Customer._ID + “ INTEGERAUTO_INCREMENT PRIMARY KEY, “ +
Customer.COLUMN_NAME_FNAME + TEXT_TYPE + COMMA +
Customer.COLUMN_NAME_LNAME + TEXT_TYPE + COMMA +
Customer.COLUM_NAME_ADDRESS + TEXT_TYPE +
“)”;
// compose the CREATE statement for product table
private static final SQL_CREATE_PRODUCT =
“CREATE TABLE “ +product.TABLE_NAME + “(“ +
Product._ID + “ INTEGER PRIMARY KEY AUTO_INCREMENT, “ +
Product.COLUMN_NAME_ITEM_ID + INTEGER + COMMA +
Product.COLUMN_NAME_NAME + TEXT_TYPE + COMMA +
Product.COLUMN_NAME_DESCRIPTION + TEXT_TYPE + COMMA +
Product.COLUMN_NAME_ADDRESS + REAL_TYPE +
“)”;
// compose the drop statements for tables
private static final SQL_DROP_CUSTOMER =
“DROP TABLE IF EXISTS “ +Customer.TABLE_NAME;
private static final SQL_DROP_PRODUCT =
“DROP TABLE IF EXISTS “ +Product.TABLE_NAME;
publicMailOrderDbOpenHelper(Context context) {
// call the super constructor which will call the callbacks
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
publicvoid onCreate(SQLiteDatbasedb) {
// create all tables
db.execSQL(SQL_CREATE_CUSTOMER);
db.execSQL(SQL_CREATE_PRODUCT);
// initialize the database
initialize(db);
}
public void onUpgrade(SQLiteDatbasedb, intoldVerion, intnewVersion) {
// we simply drop all the tables and create them
db.execSQL(SQL_DROP_CUSTOMER);
db.execSQL(SQL_DROP_PRODUCT);
onCreate(db);
}
Private void initialize(SQLiteDatabasedb) {
// you can initialize the database
// e.g., insert rows into tables
}
}
SQLiteDatabase
An instance of SQLiteDatabase is a representation of or a reference to the databases. It can be used to perform all CRUD operations on the database.
- Get a reference to the database.
You can get two different types of SQLiteDatabase, one is read only, which means you can only query the database and you cannot insert, delete, or update any records through it, the other is a writable database, which can be used to perform all CRUD operations. The read-only version may be used to safe-guard the database access so no insert, delete, and update operations are carried out by mistake.
To get a read-only and writable database,
public class MainActivity extends Activity {
privateSQLiteDatabasemReadOnlyDb;
privateSQLiteDatabasemWritableDb;
...
public void onCreate(Bundle savedInstanceStated) {
...
MailOrderDbOpenHelperdbHelper = new MailOrderDbOpenHelper(this);
mReadOnlyDb = dbHelper.getReadableDatabase();
mWritableDb = dbHelper.getWritableDatabase();
...
}
...
If you get database references in the onCreate() method of an Activity, you should close them in the onDestroy() method of the Activity to release all of its resources ,
public class MainActivity extends Activity {
privateSQLiteDatabasemReadOnlyDb;
privateSQLiteDatabasemWritableDb;
...
public void onDestroy() {
...
if (mReadOnlyDb != null) mReadOnlyDb.close();
if (mWritableDb != null) mWritableDb.close();
...
super.onDestroy();
}
...
- Insert Data into a Database
Use SQLiteDatabase#insert() to insert data into a table in the database. Remember, the database reference must be a writable one. You can insert only one record into a table per call. The record is specified as an object of ContentValues, which basically is a hash map. For example, to specify a new customer record,
ContentValues customer = new ContentValues();
customer.put(Customer.COLUMN_NAME_FNAME, “John”);
customer.put(Customer.COLUMN_NAME_LNAME, “Smith”);
customer.put(Customer.COLUMN_NAME_ADDRESS, “LSC 193”);
The insert() method has three parameters, the first is the name of the table, the third is the values specified as an instance of ContentValues. The method returns the value of the primary key:
introwId = mWritableDb.insert(Customer.TABLE_NAME, null, customer);
The columns that are not specified will take either a NULL value or their default. In the above example, the _ID field will be assigned a new value by the SQLite DBMS.
- Delete Data from a Database
Use SQLiteDatabase#delete() to delete data from a table in the database. The selection criteria for deletion consist of a selection clause and selection arguments. The clause defines a boolean expression with question marks as place holders. The arguments supply values for those place holders.
The delete() method takes three parameters. The first is the table name, the second the selection clause, and the third selection arguments. If the selection clause is “1” and the selection arguments is set null, the method would delete all records in the table. The method return the number of rows deleted.
For example, to delete all customers with last name “smith”,
String selectClause = Customer.COLUMN_NAME_LNAME + “? COLLATE NOCASE”;
String[] selectArgs = { “smith” };
int count = mWritableDb.delete(Customer.TABLE_NAME, selectClause, selectArgs);
- Update a Database
Use SQLiteDatabase#update() to update the data in a table in the database. An update action can be divided into selection criteria and column assignments with new values. For the selection criteria, the same syntax is used as in delete(); the syntax for column assignments is the same as in insert().
The update() method takes four parameters. The first is the table name, the second the column assignments with new values in the form of ContentValues, and the third selection clause, and the fourth selection arguments. If the selection clause is “1” and the selection arguments is set null, the method would update all records in the table. The method return the number of rows deleted.
For example, to set the first name to “George” for customers with last name “smith”,
ContentValuesnewColumnsAndValues = new ContentValues();
newColumnsAndValues.put(Customer.COLUMN_NAME_FNAME, “George”);
String selectClause = Customer.COLUMN_NAME_LNAME + “? COLLATE NOCASE”;
String[] selectArgs = { “smith” };
int count = mWritableDb.update(Customer.TABLE_NAME,
newColumnsAndValues, selectClause, selectArgs);
- Query a Database
A SQL query may be comprised of the following six clauses:
- SELECT: the columns to return
- FROM: the table to query
- WHERE: selection criteria
- GROUP BY: columns used to group rows for aggregate functions
- HAVING: criteria for filtering row groups
- ORDER BY: columns to sort the result rows
SQLiteDatrabase offers an insert() method that allows you to specify those clauses as individual parameters. The syntax of this insert() is:
Cursor cursor =
query(String from_table_name, // FROM - table name
String[] select_columns, // SELECT – columns to return
String where_clause, // WHERE – selection clause
String[] where_args, // WHERE – selection arguments
String group_by_columns, // GROUP BY – “,”-separated column names
String having_expression, // HAVING – having expression
String order_by_columns) // ORDER BY – sort order
The result of a query is returns as a Cursor object.
String[] projection = {
Customer._ID,
Customer.COLUMN_NAME_FNAME,
Customer.COLUMN_NAME_LNAME
};
String selection = Cusotmer.COLUMN_NAME_ADDRESS + " LIKE ‘%?%’";
String[] selectionArgs = { “Scranton, PA” };
String sortOrder =
Customer.COLUMN_NAME_LNAME + " DESC, " +
Customer.COLUMN_NAME_FNAME + " ASC"
Cursor c = db.query(
Customer.TABLE_NAME, // table name
projection, // The columns to return
selection, // The columns for the WHERE clause
selectionArgs, // The values for the WHERE clause
null, // don't group the rows
null, // don't filter by row groups
sortOrder // The sort order
);
Another useful form of query is rawQuery(), which allows you to pass a SQL SELECT statement with question marks in its WHERE clause as place holders and supply values for those place holders as another parameter. This form becomes necessary if you need to use multiple tables, aggregate functions, nested queries. It returns the result as a Cursor object.
Cursor cursor = rawQuery(String sql_statment, String[] select_args);
For example, to retrieve the names of customer who lives in Scranton, PA;.
Cursor cursor = rawQuery(“SELECT * FROM “ + Customer.TABLE_NAME +
“ WHERE “ + Customer.COLUMN_NAME_ADDRESS + “
LIKE ‘%?%’, String[] {“Scranton, PA”});
A Cursor should be closed once it is no longer needed. Generally, you should use the following standard idiom for handling cursors.
Cursor cursor = null;
// String firstName = null; // the info we need from Cursor
try {
cursor = mReadOnlyDb.query(... );
// get data out of cursor, i.e.,
// firstName = cursor.getString(1);
} catch (Exception e) {
// handle all exceptions as needed
} finally { // this guarantees the cursor is closed.
if(cursor != null) {
cursor.close();
}
}
If your Cursor is used in a Loader, such as a SimpleCursorLoader, the LoaderManager or the Loader will close the cursor for you and you should not try to close it manually by you.
- How to Process Cursors
A Cursor can hold the result of a SQL query. You may consider logically a Cursor an array of records with a cursor or a pointer pointing to a cell in the array. You can access only the record at the cell pointed by the cursor and you can move the cursor to a different cell by calling moveToFirst(), moveToLast(), moveToNext(), movetToPrevious(), and move(index). You can check if the cursor has moved out of the bounds by calling isBeforeFirst() and isAfterLast().
Here are a few useful other methods of Cursor
- void close() – closes the Cursor, releasing all of its resources
- intgetColumnCount() – returns the total number of columns
- intgetCount() – returns the number of rows
- String getColumnName(intColIndex) – returns the column name at the 0-based index
- getDouble(columnIndex), getInt(columnIndex), getShort(columnIndex),getLong(columIndex), getString(columnIndex) – returns the value at the type indicated at the column index.
To iterate through all rows in a Cursor:
Cursor cursor = mReadableDb.query(Product.TABLE_NAME, ...);
for(cursor.moveTofirst(); !cursor.isAfterLast(); cursor.moveToNext()) {
intitemId = cursor.getInt(0);
String name = cursor.getString(1);
String description = cursor.getString(2)
double price = cursor.getDouble(3);
textViewItemId.setText(“” + itemId);
textViewName.setText(name);
textViewDescription.setText(description);
textViewPrice.setText(“” + price);
}
For a complete list of Cursor methods, visit:
- Other Useful Methods of SQLiteDatabase
- execSQL(String sqlStatment): void
Normally used for defining database schema. Not for SQL select, insert, delete, update.
- getPath():String
Returns the path to the database file. Normally at /data/data/package_name/databases/
- getVersion():int
Returns the version of the database.
- isDatabaseIntegrityOk(): boolean
Returns false if any database constraints are violated. For example, duplicates exist on a UNIQUE column.
- isReadOnly(): boolean
Returns true if the SQLiteDatabase reference is a readable database.
- Batch Inserts and Transactions
The insert method introduced above can only insert one row into a table at a time. If you need to load a large number of rows into a table, for example, loading a set of JSON records from a remote database, this form of insert may not be efficient enough.
You can use what is called batch inserts to insert a large set of rows in a transaction.
ArrayList<Customer> customers = loadCustomers();
String sql = “INSERT INTO “ +Customer.TABLE_NAME +
“(“ +Customer.COLUMN_NAME_FNAME + COMMA +
Customer.COLUMN_NAME_LNAME + COMMA +
Customer.COLUMN_NAME_ADDRESS +
“) VALUES (?, ?, ?)”;
SQLiteStatementstmt = mWritableDb.compileStatemet(sql);
mWritableDb.beginTransaction();
try {
for (i = 0; icustomers.size(); i++) {
// bind data to the question marks.
// question marks are 1-based.
stmt.bindString(1, customers.get(i).getFname());
stmt.bindString(2, customers.get(i).getLname());
stmt.bindString(3, customers.get(i).getAddress());
stmt.executeInsert();
stmt.clearBindings();
}
mWritableDb.setTransactionSuccessful();
// try not to include any work after setting it successful
}
Catch (Exception e) {
// process the exception
} finally {
// try not to include any work between setting successful and
// ending transaction.
mWritableDb.endTransaction();
}
SQLite transactions can be used to guarantee a set of statements be executed atomically, meaning either everything is executed successfully or nothing is executed.
The standard idiom for using transactions is:
mWriteDb.beginTransaction();
try {
...
// perform CRUD operations
mWritableDb.setTransactionSuccessful();
}
Catch (Exception e) {
// process exception
}
Finally {
mWritableDb.endTransaction();
}
The call to setTransactionSuccessfully() marks the current transaction as successful and the call to endTransaction() commits it. Do not do any more database work, any work, after this call and before the call to endTransaction().
If any statement in the try block fails, all actions performed will be rolled back. However if exceptions after the call to setTransactionSuccessful() would not abort the transaction.
Here are some other useful methods of SQLiteStatement:
bindAllArgsAsStrings(String[] args);
bindBlob(int index, byte[] value);
bindDouble(int index, double value);
bindLong(int index, Long value);
bindNULL(int index)
bindString(int index, String value);
clearBindings()
execute(): void; // executes DDL statements, not DML statements
executeInsert(): int // returns the id of the inserted row
executeUpdateDelete(): int // returns the number of affected rows
simpleQueryForLong(): long // returns a single value of type long
simpleQueryForString: String // returns a single value of type String
Transactions may begin in the following different modes:
- beginTransaction(): void
Begins a transaction in EXCLUSIVE mode
- beginTransactionNonExclusive(): void
Begins a transaction in IMMEDIATE mode
- beginTransactionWithListener(SQLiteTransactionListener, listener): void
Begins a transaction in EXCLUSIVE mode
- beginTransactionWithListenerNonExclusive(SQLiteTransactionListener, listener): void
- Begins a transaction in IMMEDIATE mode
Regarding the Exclusive and Immediate transaction modes, here is a description from SQLite website at :
“Transactions can be deferred, immediate, or exclusive. The default transaction behavior is deferred. Deferred means that no locks are acquired on the database until the database is first accessed. Thus with a deferred transaction, the BEGIN statement itself does nothing to the filesystem. Locks are not acquired until the first read or write operation. The first read operation against a database creates a SHARED lock and the first write operation creates a RESERVED lock. Because the acquisition of locks is deferred until they are needed, it is possible that another thread or process could create a separate transaction and write to the database after the BEGIN on the current thread has executed. If the transaction is immediate, then RESERVED locks are acquired on all databases as soon as the BEGIN command is executed, without waiting for the database to be used. After a BEGIN IMMEDIATE, no other database connection will be able to write to the database or do a BEGIN IMMEDIATE or BEGIN EXCLUSIVE. Other processes can continue to read from the database, however. An exclusive transaction causes EXCLUSIVE locks to be acquired on all databases. After a BEGIN EXCLUSIVE, no other database connection except for read_uncommitted connections will be able to read the database and no other connection without exception will be able to write the database until the transaction is complete.”
- Some Random Notes
- In an app, there should be only one call to the constructor of the SQLiteOpenHelper subclass. You may retain a reference so you can get readable database references and writable database references for the rest of the app.
- Both readable database references and writable database references are expensive resources and you should not open and close them often. Close the database only when you are sure it is no longer needed.
- Normally, if you obtain a database reference in onCreate() or onStart(), then close it in onDestroy() or onStop(), respectively.