Android Local SQLite Database Example

Android platform includes the SQLite embedded database and provides out of the box support to use it via Android APIs. In this tutorial we shall see how to get started with SQLitedatabase in Android. SQLiteis nothing but a relational database and our SQLskills will help.

How to Use SQLite with Android?

To use SQLitein Android, a java class should be created as a sub class of SQLiteOpenHelper. This class will act as a database controller which will have the methods to perform the CRUD operations. This custom java class should override the methods named onCreate()and .onUpgrade()

onCreate() method will be called for the first time when the Android application is run. First the database instance should be created using the method like getReadableDatabase() or getWritableDatabase() based on the type of access required. Android supports this method by providing in-built methods. For that, SQLiteQueryBuilder class should be imported.

Lets have three Android Activity for List, Add and Edit operations and ensure that these are declared in manifest file. And then we need to create subclass of SQLiteHelper to manage SQLite database.

Database & Table Structure :

+------------+------------+------------------------------+---+--------+--+
| Field Name |  Field Type                   | Sample                    |
+------------+------------+------------------------------+---+--------+--+
| ID         |  PRIMARY KEY [Auto Generated] |  1                        |
| Name       |  TEXT                         | Chintan Khetiya           |
| Number     |  TEXT                         | 787-806-0124              |
| Email      |  TEXT                         | khetiya.chintan@gmail.com |
+------------+------------+------------------------------+---+--------+--+

Create or Setup Database

DatabaseHandler.java is going to be our custom java class that will manage the SQLite database. We should extend SQLiteOpenHelper and override the essential methods. The constructor is the hook that will be used to setup the database. While running the Android application, the database will be created for the first time.

public DatabaseHandler(Context applicationcontext) {
super(applicationcontext, "androidsqlite.db", null, 1);
Log.d(LOGCAT,"Created");
}

Table Creation and Upgrade

SQLiteOpenHelper provides callback methods and we should override it to get our job done. Those callback methods that we can override are onCreate(), onUpgrade(), onOpen() and onDowngrade(). And onCreate() and onUpgrade() are abstract methods and must be overridden.

onCreate(SQLiteDatabase database) – is the method which is called first time when the database is created and we need to use this method to create the tables and populate it as per the need.

@Override
public void onCreate(SQLiteDatabase db) {
String CREATE_CONTACTS_TABLE = "CREATE TABLE " + TABLE_CONTACTS + "("
+ KEY_ID + " INTEGER PRIMARY KEY," + KEY_NAME + " TEXT,"
+ KEY_PH_NO + " TEXT," + KEY_EMAIL + " TEXT" + ")";
db.execSQL(CREATE_CONTACTS_TABLE);
}

onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) – is the method called when upgrade is done. We can drop the database and reset if required.

// Upgrading database
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// Drop older table if existed
db.execSQL("DROP TABLE IF EXISTS " + TABLE_CONTACTS);

// Create tables again
onCreate(db);
}

How it looks after all task ?

Add New user		Update Record
Delete Record		View All

Note: The Contact details are fake or random

Do some Task : Insert, Read, Update and Delete

We shall have other user defined methods to handle the sql aobve operations. The <code>Contact table </code> will be created when the onCreate() method is invoked while installing the application. For performing operations like insert, update, the SQLiteDatabase instance should be created using the methods like getReadableDatabase() or getWritableDatabase(). ContentValues() are used to pass values to the query.

Insert Records :

// Adding new contact
public void Add_Contact(Contact contact) {
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(KEY_NAME, contact.getName()); // Contact Name
values.put(KEY_PH_NO, contact.getPhoneNumber()); // Contact Phone
values.put(KEY_EMAIL, contact.getEmail()); // Contact Email
// Inserting Row
db.insert(TABLE_CONTACTS, null, values);
db.close(); // Closing database connection
}

Read Records  :

// Getting single contact
Contact Get_Contact(int id) {
SQLiteDatabase db = this.getReadableDatabase();

Cursor cursor = db.query(TABLE_CONTACTS, new String[] { KEY_ID,
KEY_NAME, KEY_PH_NO, KEY_EMAIL }, KEY_ID + "=?",
new String[] { String.valueOf(id) }, null, null, null, null);
if (cursor != null)
cursor.moveToFirst();

Contact contact = new Contact(Integer.parseInt(cursor.getString(0)),
cursor.getString(1), cursor.getString(2), cursor.getString(3));
// return contact
cursor.close();
db.close();

return contact;
}

Update Records :

// Updating single contact
public int Update_Contact(Contact contact) {
SQLiteDatabase db = this.getWritableDatabase();

ContentValues values = new ContentValues();

values.put(KEY_NAME, contact.getName());
values.put(KEY_PH_NO, contact.getPhoneNumber());
values.put(KEY_EMAIL, contact.getEmail());

// updating row

return db.update(TABLE_CONTACTS, values, KEY_ID + " = ?",
new String[] { String.valueOf(contact.getID()) });

}

Delete Records :

// Deleting single contact
public void Delete_Contact(int id) {
SQLiteDatabase db = this.getWritableDatabase();
db.delete(TABLE_CONTACTS, KEY_ID + " = ?",
new String[] { String.valueOf(id) });
db.close();
}

Read All records

// Getting All Contacts
public ArrayList<Contact> Get_Contacts() {
try {
contact_list.clear();

// Select All Query
String selectQuery = "SELECT * FROM " + TABLE_CONTACTS;

SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery(selectQuery, null);

// looping through all rows and adding to list
if (cursor.moveToFirst()) {
do {
Contact contact = new Contact();
contact.setID(Integer.parseInt(cursor.getString(0)));
contact.setName(cursor.getString(1));
contact.setPhoneNumber(cursor.getString(2));
contact.setEmail(cursor.getString(3));
// Adding contact to list
contact_list.add(contact);
} while (cursor.moveToNext());
}

// return contact list
cursor.close();
db.close();
return contact_list;
} catch (Exception e) {
// TODO: handle exception
Log.e("all_contact", "" + e);
}

return contact_list;
}

GitHub-download