Saturday, 13 August 2016

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 : 

 
1
2
3
4
5
6
7
8
+------------+------------+------------------------------+---+--------+--+
| 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 |
+------------+------------+------------------------------+---+--------+--+
 
 

+------------+------------+------------------------------+---+--------+--+
| 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.

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

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.

 
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
@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);
}
 
 

@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 ? 




New
Add New user


Update
Update Record


Delete
Delete Record


View
View All





Note: The Contact details are fake or random

Do some Task : 

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, Read, Update and Delete Insert Records :

 
01
02
03
04
05
06
07
08
09
10
11
// 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
}
 
 

// 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 :

 
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
// 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;
}
 
 

// 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 :

 
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
// 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())
        });
 
}
 
 

// 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 :

 
1
2
3
4
5
6
7
8
9
// 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();
}
 
 

// 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 record: 

 
01
02
03
04
05
06
07
08
09
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
// 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;
}
 
 

// 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
GitHub-download
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)