In this blog I wanted to cover the fundamentals of using SQLite database within an Android app for a newbie. I am assuming that you already have an activity from which you'd like to invoke the features to get SQLite support.
SQLite
SQLite is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. Unlike other SQL databases, SQLite does not have a separate server process. It reads/writes to ordinary disk files.
There are other ways to store data in Android viz. Shared Preferences, Device Storage and Network using RESTful web service but we aren't going to go over them in the blog post. Android SQLite is easy to use because the designers of Android have been helper classes that make it very easy to get started. Let us look at this in detail.
Terminology
-
SQLiteOpenHelper
This helper class will let you create and upgrade your SQLite database.
-
SQLiteDatabase
The helper setups this database but holds off until this is ready for use. getReadableDatabase() or getWriteableDatabase() methods can be used for read or write operations respectively.
-
Cursor
If the result of a database is too big it can boke the Android device, Cusor helps alleviate this problem by wrapping the query in a Cursor and the Cursor hands them to the device in batches of any number.
Lets get started with a sample app which retrieves the contacts from the device and stores it in the SQLite database. Before we start let us create a plain old java class to store the various elements of our contacts viz name, phone number and avatar. We'll also create another class that will help us compare 2 contacts and sort it by the name.
public class PhoneBook {
private String name;
private String phone;
private Bitmap avatar;
public PhoneBook(Bitmap a, String n, String p){
avatar = a;
name = n;
phone = p;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public Bitmap getAvatar() { return avatar; }
public void setAvatar(Bitmap avatar) { this.avatar = avatar; }
}
import java.util.Comparator;
public class PhoneBookComparator implements Comparator{
public int compare(PhoneBook obj1, PhoneBook obj2) {
return obj1.getName().compareTo(obj2.getName());
}
}
import java.util.Collections;
import java.util.LinkedList;
import java.util.List;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.graphics.Bitmap;
import java.io.ByteArrayOutputStream;
import android.graphics.BitmapFactory;
public class MyDbHelper extends SQLiteOpenHelper{
private static final int DATABASE_VERSION = 1;
private static final String DATABASE_NAME = "TESTDB";
private static final String TABLE_NAME = "contacts";
private static final String KEY_ID = "id";
private static final String KEY_NAME = "name";
private static final String KEY_PHONE = "phone";
private static final String KEY_AVATAR = "avatar";
private static final String[] COLUMNS = {KEY_ID,KEY_NAME,KEY_PHONE,KEY_AVATAR};
public MyDbHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
String CREATE_TABLE = "CREATE TABLE " + TABLE_NAME + " ( " +
"id INTEGER PRIMARY KEY AUTOINCREMENT, " +
"name TEXT, "+
"phone TEXT, " +
"avatar BLOB )";
db.execSQL(CREATE_TABLE);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
this.onCreate(db);
}
}
The onCreate() method needs to be implemented because it is called the first time to create the database. OnUpgrade will also need to be implemnted and run when you need to upgrade your database.
Moving on, let us implement methods to get all contacts from the android device and store it in the database.
public List getAllContacts() {
List contacts = new LinkedList();
String query = "SELECT * FROM " + TABLE_NAME;
SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery(query, null);
if (cursor.moveToFirst()) {
do {
String name = cursor.getString(1);
String phone = cursor.getString(2);
byte[] bytes = cursor.getBlob(3);
Bitmap avatar = null;
try {
avatar = BitmapFactory.decodeByteArray(bytes, 0, bytes.length);
}
catch(Exception e){
e.printStackTrace();
}
PhoneBook phonebook = new PhoneBook(avatar, name, phone);
// Add book to books
contacts.add(phonebook);
} while (cursor.moveToNext());
}
if (cursor != null && !cursor.isClosed()) {
cursor.close();
}
// Now sort it by name
PhoneBookComparator comparator = new PhoneBookComparator();
Collections.sort(contacts, comparator);
return contacts;
}
We start off with creating a list of PhoneBook entries which are linked lists - this helps us traverse them easily. We iterate through all the contacts in our device and get the name, phone and avatar and push it to our PhoneBook list. At the end we use a neat trick to sort by contact name using the PhoneBookComparator class we created previously.
Next, let us create methods that will let us add a contact to the database, check if a contact already exists in the database - we'll use a crude way of comparing by the name to determine this. And lastly, we'll add one last method to delete all contacts from our database.
public void addContact (PhoneBook phonebook){
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(KEY_NAME, phonebook.getName());
values.put(KEY_PHONE, phonebook.getPhone());
Bitmap avatar = phonebook.getAvatar();
if (avatar != null) {
ByteArrayOutputStream stream = new ByteArrayOutputStream();
avatar.compress(Bitmap.CompressFormat.PNG, 0, stream);
byte[] avatarBitmapBytes = stream.toByteArray();
values.put(KEY_AVATAR, avatarBitmapBytes);
}
db.insert(TABLE_NAME, // table
null, //nullColumnHack
values); // key/value -> keys = column names/ values = column values
db.close();
}
public boolean doesContactExist(String name, String phone){
SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor =
db.query(TABLE_NAME, // a. table
COLUMNS, // b. column names
" name = ? and phone = ?", // c. selections
new String[]{name, phone}, // d. selections args
null, // e. group by
null, // f. having
null, // g. order by
null); // h. limit
boolean contactExists = false;
if (cursor.moveToFirst())
contactExists = true;
return contactExists;
}
public void deleteAllContacts() {
SQLiteDatabase db = this.getWritableDatabase();
db.delete(TABLE_NAME, null, null);
db.close();
}
And lastly, we'll invoke the various methods in our activity.
MyDBHelper db = new MyDBHelper(this);
List allContacts = db.getAllContacts(); // get all the contacts in the database
// Given a contact
PhoneBook contact = PhoneBook listview.getItemAtPosition(position)
if (db.doesContactExist(contact.getName())){
showToast("Contact already exists in database.");
}
else{
db.addContact(contact);
}
db.deleteAllContacts(); // delete all contacts from the database
db.close();
Hope this blog was useful to you to get up-and-running with SQLite within your Android app.