Android SQLite Database Tutorial
Our Schema
One of the main principles of SQL databases is the schema: a formal declaration of how the database is organized. The schema is reflected in the SQL statements that you use to create your database. Below is the sample schema for the sqlite database that we will be using in this tutorial.
|
1 2 3 4 5 6 7 |
CREATE TABLE 'contact' ( '_id' INTEGER PRIMARY KEY AUTOINCREMENT, 'name' TEXT, 'organization' TEXT NULL, 'phone' TEXT, 'address' TEXT NULL ); |
Now we shall create a companion model class, known as a Contact class, which explicitly specifies the layout of your schema in a systematic and self-documenting way.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
package demos.sqlite.db; public class Contact { private int _id; private String name; private String organization; private String phone; private String address; public Contact() {} /* Setters and Getters for all variables */ } |
Create a Database Using a SQL Helper
The ContactHelper class allows you to use the same constants across all the other classes in the same package. This lets you change a column name in one place and have it propagate throughout your code.
A good way to organize a ContractHelper class is to put definitions that are global to your whole database in the root level of the class. Then create an inner class for each table that enumerates its columns.
Note: By implementing the
BaseColumnsinterface, your inner class can inherit a primary key field called_IDthat some Android classes such as cursor adaptors will expect it to have. It’s not required, but this can help your database work harmoniously with the Android framework.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 |
package demos.phonebook.db; import android.content.ContentValues; import android.content.Context; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; import android.provider.BaseColumns; import android.util.Log; public class ContactHelper extends SQLiteOpenHelper implements BaseColumns { public static final String DATABASE = "phonebook"; public static final SQLiteDatabase.CursorFactory FACTORY = null; public static final int DATABASE_VERSION = 1; public static final String TABLE = "contact"; public static final String COL_NAME = "name"; public static final String COL_ORGANIZATION = "organization"; public static final String COL_PHONE = "phone"; public static final String COL_ADDRESS = "address"; public static final String SQL_CREATE_ENTRIES = "" + "CREATE TABLE IF NOT EXISTS " + ContactHelper.TABLE +" ("+ ContactHelper._ID +" INTEGER PRIMARY KEY AUTOINCREMENT, "+ ContactHelper.COL_NAME +" TEXT, "+ ContactHelper.COL_ORGANIZATION +" TEXT NULL, "+ ContactHelper.COL_PHONE +" INTEGER, "+ ContactHelper.COL_ADDRESS +" TEXT NULL"+ ")"; public static final String SQL_DELETE_ENTRIES = "DROP TABLE IF EXISTS " + ContactHelper.TABLE; public ContactHelper(Context context) { super(context, DATABASE, FACTORY, DATABASE_VERSION); } @Override public void onCreate(SQLiteDatabase db) { db.execSQL(SQL_CREATE_ENTRIES); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { db.execSQL(SQL_DELETE_ENTRIES); onCreate(db); } @Override public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) { onUpgrade(db, oldVersion, newVersion); } } |
Once you have defined how your database looks, you should implement methods that create and maintain the database and tables. Here are some typical statements that create and delete a table:
Add new contact to the database
Insert data into the database by passing a ContentValues object to the insert() method:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
public long addContact(Contact contact) { ContentValues values = new ContentValues(); values.put(ContactHelper.COL_NAME, contact.getName()); values.put(ContactHelper.COL_ORGANIZATION, contact.getOrganization()); values.put(ContactHelper.COL_PHONE, contact.getPhone()); values.put(ContactHelper.COL_ADDRESS, contact.getAddress()); try { SQLiteDatabase db = this.getWritableDatabase(); return db.insert(ContactHelper.TABLE, null, values); } catch (Exception e){ Log.e("SQLite Exception", e.getMessage()); return -1; } } |
Retrieve contact(s) from the database
To read from a database, use the query() method, passing it your selection criteria and desired columns. The method combines elements of insert() and update(), except the column list defines the data you want to fetch, rather than the data to insert. The results of the query are returned to you in a Cursor object.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
public Contact getContact (int id) { String[] projection = { ContactHelper._ID, ContactHelper.COL_NAME, ContactHelper.COL_ORGANIZATION, ContactHelper.COL_PHONE, ContactHelper.COL_ADDRESS, }; String selection = ContactHelper._ID + " = ?"; String[] selectionArgs = { String.valueOf(id) }; Contact contact = new Contact(); try { SQLiteDatabase db = this.getReadableDatabase(); Cursor cursor = db.query( ContactHelper.TABLE, projection, selection, selectionArgs, null, null, null ); if (cursor.moveToFirst()) { cursor.moveToFirst(); contact.setId(cursor.getInt(cursor.getColumnIndex(ContactHelper._ID))); contact.setName(cursor.getString(cursor.getColumnIndex(ContactHelper.COL_NAME))); contact.setOrganization(cursor.getString(cursor.getColumnIndex(ContactHelper.COL_ORGANIZATION))); contact.setPhone(cursor.getString(cursor.getColumnIndex(ContactHelper.COL_PHONE))); contact.setAddress(cursor.getString(cursor.getColumnIndex(ContactHelper.COL_ADDRESS))); cursor.close(); } else { contact = null; } return contact; }catch (Exception e){ Log.e("SQLite Exception", e.getMessage()); return null; } } |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
public Cursor getContacts () { String[] projection = { ContactHelper._ID, ContactHelper.COL_NAME, ContactHelper.COL_ORGANIZATION, ContactHelper.COL_PHONE, ContactHelper.COL_ADDRESS, }; try { SQLiteDatabase db = this.getReadableDatabase(); return db.query( ContactHelper.TABLE, projection, null, null, null, null, null ); }catch (Exception e){ Log.e("SQLite Exception", e.getMessage()); return null; } } |
Update a contact in the database
When you need to modify a subset of your database values, use the update() method.
Updating the table combines the content values syntax of insert() with the where syntax of delete().
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
public int updateContact(Contact contact) { ContentValues values = new ContentValues(); values.put(ContactHelper.COL_NAME, contact.getName()); values.put(ContactHelper.COL_ORGANIZATION, contact.getOrganization()); values.put(ContactHelper.COL_PHONE, contact.getPhone()); values.put(ContactHelper.COL_ADDRESS, contact.getAddress()); String selection = ContactHelper._ID + " LIKE ?"; String[] selectionArgs = { String.valueOf(contact.get_id()) }; try { SQLiteDatabase db = this.getReadableDatabase(); return db.update(ContactHelper.TABLE, values, selection, selectionArgs); }catch (Exception e){ Log.e("SQLite Exception", e.getMessage()); return -1; } } |
Delete Information from the database
To delete rows from a table, you need to provide selection criteria that identify the rows. The database API provides a mechanism for creating selection criteria that protects against SQL injection. The mechanism divides the selection specification into a selection clause and selection arguments. The clause defines the columns to look at, and also allows you to combine column tests. The arguments are values to test against that are bound into the clause. Because the result isn’t handled the same as a regular SQL statement, it is immune to SQL injection.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
public int deleteContact(int id) { String selection = ContactHelper._ID + " LIKE ?"; String[] selectionArgs = { String.valueOf(id) }; try { SQLiteDatabase db = this.getWritableDatabase(); return db.delete(ContactHelper.TABLE, selection, selectionArgs); }catch (Exception e){ Log.e("SQLite Exception", e.getMessage()); return -1; } } |
Away from using only the SQLiteDatabase class methods we have looked at, i.e., insert(), query(), update(), and delete(), you might be interested in using the rawQuery() and execSQL() methods, and the SQLiteQueryBuilder class.
rawQuery()directly accepts an SQL select statement as input.execSQL()allows to execute an SQL statement directly.SQLiteQueryBuilderis a convenience class that helps to build SQL queries.
Now that we have all the database related code ready, let’s get down to using this SQLite database in our Android application in part II of this tutorial.
