SQLite in Android Development

SQLite is a software library that implements a self-contained, server-less, zero-configuration, transactional SQL database engine. SQLite is the most widely placed SQL database. Like other database you do not need to configure it in your system.
Features:
1. SQLite does not require a separate server process or system to operate.
2. SQLite comes with zero-configuration.
3. A complete SQLite database is stored in a single cross-platform disk file.
4. SQLite is very small and light weight, less than 400KiB fully configured or less than 250KiB with optional features omitted.
5. SQLite is self-contained, which means no external dependencies.
6. SQLite transactions are fully ACID-compliant, allowing safe access from multiple processes or threads.
7. SQLite supports most of the query language.
8. SQLite is written in C and provides simple and easy-to-use API.
9. SQLite is available on UNIX, Linux, Mac OS-X, Android, iOS and Windows.

Android has many ways of storing data, one of the way is using SQLite. SQLite database comes in bundle with Android.
SQLiteOpenHelper class helps to use SQLite database in Android. It has methods to be performed on SQLite database such as Create, Retrieve, Update and Delete (CRUD).
To make life easier, we make one class with all getter and setter.
In the below example, we are creating a database ‘contactsManager’, and then one table in the database as ‘Contact’ having column as ‘ID’, ‘Name’ and ‘Phone Number’.
And the creation and insertion is executed in onCreate() method.
The output is in Logcat.

Example:

Directory Hierarchy

sqlite01

Contact.java

package com.wordpress.pawanthepro.sqliteexample.contactdb;

/**
* Created by Pawan on 10/22/2016.
*/

public class Contact {
int id;
String name;
String phoneNumber;

public Contact(){

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

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

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

public String getPhoneNumber(){
return phoneNumber;
}
public void setPhoneNumber(String phoneNumber){
this.phoneNumber=phoneNumber;
}
}

DatabaseHandler.java

package com.wordpress.pawanthepro.sqliteexample.contactdb;

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

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

/**
* Created by Pawan on 10/22/2016.
*/

public class DatabaseHandler extends SQLiteOpenHelper {

private static final int DATABASE_VERSION = 1;
private static final String DATABASE_NAME = "contactsManager";
private static final String TABLE_CONTACTS = "contacts";

// Contacts Table Columns names
private static final String KEY_ID = "id";
private static final String KEY_NAME = "name";
private static final String KEY_PH_NO = "phone_number";

public DatabaseHandler(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}

@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 " + ");";
db.execSQL(CREATE_CONTACTS_TABLE);
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE IF EXISTS " + TABLE_CONTACTS);
onCreate(db);
}

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

public Contact getContact(int id) {
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.query(TABLE_CONTACTS, new String[] { KEY_ID, KEY_NAME, KEY_PH_NO }, 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));
return contact;
}

public List getAllContacts() {
List contactList = new ArrayList();
String selectQuery = "SELECT * FROM " + TABLE_CONTACTS;
SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery(selectQuery, null);

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));
contactList.add(contact);
} while (cursor.moveToNext());
}
return contactList;
}

public int getContactsCount() {
String countQuery = "SELECT * FROM " + TABLE_CONTACTS;
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.rawQuery(countQuery, null);
cursor.close();
return cursor.getCount();
}

public int updateContact(Contact contact) {
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(KEY_NAME, contact.getName());
values.put(KEY_PH_NO, contact.getPhoneNumber());
return db.update(TABLE_CONTACTS, values, KEY_ID + " = ?",
new String[] { String.valueOf(contact.getId()) });
}
}

MainActivity.java

package com.wordpress.pawanthepro.sqliteexample;

import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;

import com.wordpress.pawanthepro.sqliteexample.contactdb.Contact;
import com.wordpress.pawanthepro.sqliteexample.contactdb.DatabaseHandler;

import java.util.List;

public class MainActivity extends AppCompatActivity {

EditText id, name, phoneNumber;
Button save;

@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
id=(EditText)findViewById(R.id.id);
name=(EditText)findViewById(R.id.name);
phoneNumber=(EditText)findViewById(R.id.phoneNumber);
save=(Button)findViewById(R.id.save);

DatabaseHandler db=new DatabaseHandler(this);

Contact con1=new Contact(1,"Pawan", "+919833****");
Contact con2=new Contact(2,"Pawan the", "+919833****");
Contact con3=new Contact(3,"Pawan the pro", "+919833****");
db.addContact(con1);
db.addContact(con2);
db.addContact(con3);

Log.d("Reading: ", "Reading all contacts..");
List contacts = db.getAllContacts();

for (Contact cn : contacts) {
String log = "Id: "+cn.getId()+" ,Name: " + cn.getName() + " ,Phone: " + cn.getPhoneNumber();
// Writing Contacts to log
Log.d("Name: ", log);
}

}
}

Output:

sqlite02