SQLite Database and it’s use in Android App – Tutorial

This tutorial describes SQLite database and it’s use in Android application.

SQLite Overview

SQLite is an open source RDBMS developed by D. Richard Hipp in ‘ANSI-C’ programming language. In August 2000, SQLite 1.0 was released with GNU database manager. Latest release of SQLite is 3.8.3.1 which can be downloaded from http://www.sqlite.org/download.html .

SQLite is a software library that implements:

• Self-contained: Single library contained the entire database system, which integrated directly into a host application. It requires very minimal support from external libraries or from the operating system.
• Serverless: SQLite does not require a separate server process or system to operate. The SQLite library accesses its storage files directly.
• Zero-configuration: SQLite does not need to be “installed” as there is no “setup” procedure. There is no need for an administrator to create a new database instance or assign access permissions to users. SQLite uses no configuration files. Nothing needs to be done to tell the system that SQLite is running. No actions are required to recover after a system crash or power failure. There is nothing to troubleshoot.
• Transactional: SQLite transactions are fully ACID (Atomicity, Consistency, Isolation, Durability)-compliant, allowing safe access from multiple processes or threads.

SQLite is the most widely deployed SQL database engine in the world, as it is used by several widespread browsers, operating systems, and embedded systems. The source code for SQLite is in the public domain.

SQLite Data types

Most databases use strong, static column typing. This means that the elements of a column can only hold values compatible with the column’s defined types. SQLite on the other hand, uses a more general dynamic type system.

SQLite supports only five concrete data types known as storage classes, and represent the different ways SQLite might choose to store data on disk. Every value has one of these five native storage classes:

SQLite does not have a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true). SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date and Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values.

In order to maximize compatibility between SQLite and other database engines, SQLite supports the concept of “type affinity” on columns. The type affinity of a column is the recommended type for data stored in that column. The important idea here is that the type is recommended, not required. Any column can still store any type of data. It is just that some columns, given the choice, will prefer to use one storage class over another. The preferred storage class for a column is called its “affinity”. Following type affinities are available in SQLite3 database:

• TEXT
• NUMERIC
• INTEGER
• REAL
• NONE

SQLite in Android

SQLite database API is an integral part of Android. The database that you create for an application is only accessible to itself; other application will not be able to access it. When your app creates a database, the database will be kept in a directory that will always utilize the following Android OS database path address:

DATA/data/APPLICATION_NAME/databases/DATABASE_FILENAME

Before jumping into developing SQLite database app, you need to understand and basic concepts of using SQLite in Android.

Creating and Updating database

SQLiteOpenHelper is an abstract class that takes care of opening the database if exists, creating it if it does not, and upgrading it as necessary.

Constructor of this class accept Context, DATABASE_NAME, its version etc. to create or upgrade the database. Then you need to override below mentioned methods of this class:

• onCreate() – This method is used to create tables and initial population of those tables.
• onUpgrade() – This method is called when database needs to be upgraded. You should use this method to drop tables, add tables, or do anything else it needs to upgrade to the new schema version. This method executes within a transaction. If an exception is thrown, all changes will be rolled back.

The database is not actually created or opened until one of getWritableDatabase() or getReadableDatabase() methods are called. These methods give access to the database either in writable or readable mode.

Adding, Updating, and Removing Rows

SQLiteDatabase class exposes insert(), delete(), and update() methods that encapsulate the SQL statements required to perform these actions. Additionally, the execSQL() method lets you execute any valid SQL statement.

To create a new row or to update the existing one, construct an object of ContentValues class and use its put() method to add name/value pairs representing each column name and its associate value.

To delete a row from the table, simple call the delete() method, specifying the table name and a where clause that returns the rows you want to delete.

Querying a Database

Each database query is returned as a Cursor. This lets Android manage resources more efficiently by retrieving and releasing row and column values on demand. When you try to retrieve some data using SQL SELECT statement, then the database first create a Cursor object and return its reference to you.

The pointer of this returned reference is pointing to the location before the first record of the Cursor. So, when you want to retrieve data from the Cursor, you have to first move the pointer to the first record.

Cursor class provides various methods by which you can read data. getCount() method returns the number of rows returned by the Cursor. There are moveToFirst(), moveToLast(), moveToNext(), moveToPrevious() methods are used to move the current location of the pointer.

To retrieve the data from the Cursor, Cursor class provides get methods, e.g., getLong(columnIndex), getString(columnIndex). Each of these methods will help you to retrieve a specific type of data by accepting the column number.

To execute a query on a database object, you can use the query() or rawQuery() methods. Queries can be created by using the SQLiteQueryBuilder class.

SQLite in Action

Here in this example, we are going to create Contacts application. This is going to be very basic example where we are creating a single table using three columns (id, name and email).

1. Using Eclipse, create an Android project and name it SQLiteExample.

2. Add a new Java Class file to the package and name it Contact. This class will be used to create a getter and setter methods for contact. Code for this class is:

[java]
package com.soigne.sqliteexample;

public class Contact {
private int id;
private String name;
private String email;

public Contact() {}

public Contact(String name, String email) {
this.name = name;
this.email = email;
}

public Contact(int id, String name, String email) {
this.id = id;
this.name = name;
this.email = email;
}

public int getId() {
return this.id;
}

public void setId(int id) {
this.id = id;
}

public String getName() {
return this.name;
}

public void setName(String name) {
this.name = name;
}

public String getEmail() {
return this.email;
}

public void setEmail(String email) {
this.email = email;
}
}
[/java]

3.  Add one more class to the package and name it DBHelper which extends SQLiteOpenHelper class and helps in maintaining database and its table. This class will be like this:

[java]
package com.soigne.sqliteexample;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

//This class is used for creating and maintaining the database
public class DBHelper extends SQLiteOpenHelper {
//Static variables used to create the database
private static final int DATABASE_VERSION = 1;
private static final String DATABASE_NAME = "contactsDB";

//Static variables used to create a table
public static final String TABLE_NAME = "contacts";
public static final String KEY_ID = "id";
public static final String KEY_NAME = "name";
public static final String KEY_EMAIL = "email";

public DBHelper(Context context) {
//Passing argument to parent class which requires following parameters
//Context – use to open or create the database
//Database Name – name of the database file or null for in-memory database
//Factory – Used for creating cursor objects, or null for default
//Version – Version of the database file
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}

@Override
public void onCreate(SQLiteDatabase db) {
//Here in onCreate method database will be created
db.execSQL("create table " + TABLE_NAME
+ "(" + KEY_ID + " integer primary key autoincrement,"
+ KEY_NAME + " text not null,"
+ KEY_EMAIL + " text not null)");
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
//onUpgrade method will be used for upgrading the database. It will first drop the old database
//onCreate method will be called to create the new one.

Log.i("Contact App", "Upgrading database from version " + oldVersion + " to "
+ newVersion + ", which will destroy all old data");
db.execSQL("drop table if exists " + TABLE_NAME);
onCreate(db);
}
}
[/java]

4. Next, you need to create one more class which is used for performing all data retrieval and manipulation tasks. Name this class as DBAdapter. Code for this class will be:

[java]
package com.soigne.sqliteexample;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

// This is the class where all the data related tasks will be performed
public class DBAdapter {
private SQLiteDatabase database;
private DBHelper helper;
private String[] allColumns = {DBHelper.KEY_ID, DBHelper.KEY_NAME, DBHelper.KEY_EMAIL};

public DBAdapter(Context context) {
// Through this constructor we are creating/upgrading the database
helper = new DBHelper(context);
}

public void open() throws SQLException {
// Here database is opened with write permissions and will be assigned to the variable of this class
database = helper.getWritableDatabase();
}

public void close() {
// Closing the connection to the database
helper.close();
}

public long addContact(Contact contact) {
// All the values used to create a record in table are collected in an ContectValues object
ContentValues values = new ContentValues();
values.put(DBHelper.KEY_NAME, contact.getName());
values.put(DBHelper.KEY_EMAIL, contact.getEmail());

// Then ContentValues object will be passed to the insert() method of database which in turn return
// the ID value of the record inserted to the table
return database.insert(DBHelper.TABLE_NAME, null, values);
}

public Contact getContact(int id) throws SQLException {
// Queries performed on database table return cursors
// query() method of database will be used to build and execute the query.
Cursor cursor = database.query(DBHelper.TABLE_NAME, allColumns, DBHelper.KEY_ID + "=?", new String[] {String.valueOf(id)}, null, null, null);
if(cursor != null)
cursor.moveToFirst();

// Values of the Cursor object are fed into Contacts object which will be returned by this method
Contact contact = new Contact(Integer.parseInt(cursor.getString(0)), cursor.getString(1), cursor.getString(2));
cursor.close();
return contact;
}

public List<Contact> getAllContacts() {
// List is a collection which maintains an ordering for its elements. ArrayList is an implementation
// of List only, backed by an array.
List<Contact> contactList = new ArrayList<Contact>();
String query = "SELECT * FROM " + DBHelper.TABLE_NAME;

// rawQuery() method is used here. Unlike query() method, this method used SQL statement for executing
// a query on your database.
Cursor cursor = database.rawQuery(query, null);
if(cursor.moveToFirst()) {
do {
// Once Cursor object is instantiated, we traverse through it and assign each element’s value
// to the Contact object which in turn added to the List object created earlier
Contact contact = new Contact();
contact.setId(Integer.parseInt(cursor.getString(0)));
contact.setName(cursor.getString(1));
contact.setEmail(cursor.getString(2));
contactList.add(contact);
} while(cursor.moveToNext());
}
cursor.close();
// Returning the entire list populated with all records of Contacts table
return contactList;
}

public boolean updateContact(Contact contact) {
// Here again ContentValues object is created and instantiated with new values of Contact object
ContentValues values = new ContentValues();
values.put(DBHelper.KEY_NAME, contact.getName());
values.put(DBHelper.KEY_EMAIL, contact.getEmail());

// Method will return true or false depending upon the result of the update() method of the SQLite database
return database.update(DBHelper.TABLE_NAME, values, DBHelper.KEY_ID + "=?", new String[]{String.valueOf(contact.getId())}) > 0;
}

public boolean deleteContact(long id) {
return database.delete(DBHelper.TABLE_NAME, DBHelper.KEY_ID + "=" + String.valueOf(id), null) > 0;
}

public int getContactCount() {
String query = "SELECT * FROM" + DBHelper.TABLE_NAME;
Cursor cursor = database.rawQuery(query, null);
return cursor.getCount();
}
}
[/java]

5. Finally, add the following code to the MainActivity class of your project:

[java]
package com.soigne.sqliteexample;

import java.util.List;

import android.app.Activity;
import android.os.Bundle;
import android.util.Log;
import android.widget.Toast;

public class MainActivity extends Activity {

@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.main);

DBAdapter db = new DBAdapter(this);
db.open();

// Code for adding contacts to the table. Once contacts are added, their entries are made to the log
// of your application.
Contact contact = new Contact("User1", "user1@tech-soigne.com");
long id = db.addContact(contact);
Log.d("Contacts App", "Contact added with ID: " + String.valueOf(id));
contact = new Contact("User2", "user2@tech-soigne.com");
id = db.addContact(contact);
Log.d("Contacts App", "Contact added with ID: " + String.valueOf(id));

// This section will display all contacts in a table using Toast
List<Contact> contacts = db.getAllContacts();
for(Contact c : contacts) {
String display = "ID: " + c.getId() + ", Name: " + c.getName() + ", Email: " + c.getEmail();
Toast.makeText(this, display, Toast.LENGTH_SHORT).show();
}

// A particular contact is displayed here.
Contact c = db.getContact(1);
String display = "ID: " + c.getId() + ", Name: " + c.getName() + ", Email: " + c.getEmail();
Toast.makeText(this, display, Toast.LENGTH_SHORT).show();

// Updating the contact values by passing new values through Contact object
c = new Contact(1, "User2", "user2email@tech-soigne.com");
if(db.updateContact(c))
Toast.makeText(this, "Update successfully", Toast.LENGTH_SHORT).show();
else
Toast.makeText(this, "Update failed", Toast.LENGTH_SHORT).show();

// Deleting the contact from the database
if(db.deleteContact(1))
Toast.makeText(this, "Contact deleted successfully", Toast.LENGTH_SHORT).show();
else
Toast.makeText(this, "Deletion failed", Toast.LENGTH_SHORT).show();

db.close();
}
}
[/java]

* Note: Keep on commenting different sections for better understanding of all concepts of data manipulation.

References

1. http://www.sqlite.org/
2. http://en.wikipedia.org/wiki/SQLite
3. http://developer.android.com/index.html