Basic sqlite CRUD examples in Android

1. DatabaseHandler.java, declaring the database version, database name, table name and table columns.

public class DatabaseHandler extends SQLiteOpenHelper {
    // All Static variables
    // Database Version
    private static final int DATABASE_VERSION = 1;

    // Database Name
    private static final String DATABASE_NAME = "contacts_db";

    // Contact table name
    private static final String TABLE_CONTACTS = "contacts";

    // Contact Table Columns names
    private static final String KEY_ID      = "id";
    private static final String KEY_FNANE   = "first_name";
    private static final String KEY_LNANE   = "last_name";
    private static final String KEY_EMAIL   = "email";
    private static final String KEY_PH_NO   = "phone";
    private static final String KEY_DOB     = "dob";
    private static final String KEY_GENDER  = "gender";

    private final ArrayList<Contact> contactList = new ArrayList<Contact>();
}

2. The constructor for the DatabaseHandler, it needs the context object from the activity. The super call in the constructor needs the context, database naame and database version.

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

3. The onCreate() method for creating the database and the onUpgrade() method for upgrading the database. These two methods will be executed if the database is already created and the version number from the constructor is greater than the current database. In this demo, it will drop the table alone with all the data in it. You probably wounldn’t want to do this, or at least save the data before drop it.

// Creating contacts table
@Override
public void onCreate(SQLiteDatabase db) {
    Log.d("DatabaseHandler", "creating database");
    String CREATE_CONTACTS_TABLE = "CREATE TABLE " + TABLE_CONTACTS + "("
            + KEY_ID + " INTEGER PRIMARY KEY,"
            + KEY_FNANE + " TEXT,"
            + KEY_LNANE + " TEXT,"
            + KEY_EMAIL + " TEXT,"
            + KEY_PH_NO + " TEXT,"
            + KEY_DOB + " TEXT,"
            + KEY_GENDER + " TEXT" + ")";
    db.execSQL(CREATE_CONTACTS_TABLE);
}

// Upgrading database
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    Log.d("DatabaseHandler", "upgrading database");

    // Drop older table if existed
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_CONTACTS);
    // Create tables again
    onCreate(db);
}

4. Adding a row in sqlite table.

public long addContact(Contact contact) {
    long insertId = -1;
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues values = new ContentValues();
    values.put(KEY_FNANE, contact.getFirstName());
    values.put(KEY_LNANE, contact.getLastName());
    values.put(KEY_EMAIL, contact.getEmail());
    values.put(KEY_PH_NO, contact.getPhoneNumber());
    values.put(KEY_DOB, contact.getDob());
    values.put(KEY_GENDER, contact.getGender());

    // Inserting Row
    insertId = db.insert(TABLE_CONTACTS, null, values);
    db.close(); // Closing database connection
    return insertId;
}

5. Reading from sqlite table.

public Contact getContact(int id) {
    SQLiteDatabase db = this.getReadableDatabase();

    Cursor cursor = db.query(TABLE_CONTACTS,
            new String[] {KEY_ID, KEY_FNANE, KEY_LNANE, KEY_EMAIL, KEY_PH_NO, KEY_DOB, KEY_GENDER },
            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),
            cursor.getString(4),
            cursor.getString(5),
            cursor.getString(6));

    // return contact
    cursor.close();
    db.close();

    return contact;
}

6. Reading multiple rows from a sqlite table.

public ArrayList<Contact> getContactList() {
    try {
        contactList.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.setFirstName(cursor.getString(1));
                contact.setLastName(cursor.getString(2));
                contact.setEmail(cursor.getString(3));
                contact.setPhoneNumber(cursor.getString(4));
                contact.setDob(cursor.getString(5));
                contact.setGender(cursor.getString(6));
                // Adding contact to list
                contactList.add(contact);
            } while (cursor.moveToNext());
        }
        // return contact list
        cursor.close();
        db.close();
        return contactList;
    } catch (Exception e) {
        // TODO: handle exception
        Log.e("getContactList", "" + e);
    }
    return contactList;
}

7. Updating data in sqlite.

// Updating single contact
public int updateContact(Contact contact) {
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues values = new ContentValues();
    values.put(KEY_FNANE, contact.getFirstName());
    values.put(KEY_LNANE, contact.getLastName());
    values.put(KEY_EMAIL, contact.getEmail());
    values.put(KEY_PH_NO, contact.getPhoneNumber());
    values.put(KEY_DOB, contact.getDob());
    values.put(KEY_GENDER, contact.getGender());

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

8. Deleting data in sqlite.

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

9. Get the number of rows in a table in sqlite.

// Getting contacts Count
public int getContactSize() {
    String countQuery = "SELECT count(*) FROM " + TABLE_CONTACTS;
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db.rawQuery(countQuery, null);

    int count = 0;
    if(cursor != null && cursor.getCount() > 0) {
        cursor.moveToFirst();
        count = cursor.getInt(0);
    }

    cursor.close();
    // return count
    return count;
}

10. The database hanlder class all in one.

public class DatabaseHandler extends SQLiteOpenHelper {
    // All Static variables
    // Database Version
    private static final int DATABASE_VERSION = 3;

    // Database Name
    private static final String DATABASE_NAME = "contacts_db";

    // Contact table name
    private static final String TABLE_CONTACTS = "contacts";

    // Contact Table Columns names
    private static final String KEY_ID      = "id";
    private static final String KEY_FNANE   = "first_name";
    private static final String KEY_LNANE   = "last_name";
    private static final String KEY_EMAIL   = "email";
    private static final String KEY_PH_NO   = "phone";
    private static final String KEY_DOB     = "dob";
    private static final String KEY_GENDER  = "gender";

    private final ArrayList<Contact> contactList = new ArrayList<Contact>();

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

    // Creating contacts table
    @Override
    public void onCreate(SQLiteDatabase db) {
        Log.d("DatabaseHandler", "creating database");
        String CREATE_CONTACTS_TABLE = "CREATE TABLE " + TABLE_CONTACTS + "("
                + KEY_ID + " INTEGER PRIMARY KEY,"
                + KEY_FNANE + " TEXT,"
                + KEY_LNANE + " TEXT,"
                + KEY_EMAIL + " TEXT,"
                + KEY_PH_NO + " TEXT,"
                + KEY_DOB + " TEXT,"
                + KEY_GENDER + " TEXT" + ")";
        db.execSQL(CREATE_CONTACTS_TABLE);
    }

    // Upgrading database
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.d("DatabaseHandler", "upgrading database");

        // Drop older table if existed
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_CONTACTS);
        // Create tables again
        onCreate(db);
    }

    /**
     * All CRUD(Create, Read, Update, Delete) Operations
     */

    // Adding new contact
    public long addContact(Contact contact) {
        long insertId = -1;
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(KEY_FNANE, contact.getFirstName());
        values.put(KEY_LNANE, contact.getLastName());
        values.put(KEY_EMAIL, contact.getEmail());
        values.put(KEY_PH_NO, contact.getPhoneNumber());
        values.put(KEY_DOB, contact.getDob());
        values.put(KEY_GENDER, contact.getGender());

        // Inserting Row
        insertId = db.insert(TABLE_CONTACTS, null, values);
        db.close(); // Closing database connection
        return insertId;
    }

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

        Cursor cursor = db.query(TABLE_CONTACTS,
                new String[] {KEY_ID, KEY_FNANE, KEY_LNANE, KEY_EMAIL, KEY_PH_NO, KEY_DOB, KEY_GENDER },
                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),
                cursor.getString(4),
                cursor.getString(5),
                cursor.getString(6));

        // return contact
        cursor.close();
        db.close();

        return contact;
    }

    // Getting All Contacts
    public ArrayList<Contact> getContactList() {
        try {
            contactList.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.setFirstName(cursor.getString(1));
                    contact.setLastName(cursor.getString(2));
                    contact.setEmail(cursor.getString(3));
                    contact.setPhoneNumber(cursor.getString(4));
                    contact.setDob(cursor.getString(5));
                    contact.setGender(cursor.getString(6));
                    // Adding contact to list
                    contactList.add(contact);
                } while (cursor.moveToNext());
            }
            // return contact list
            cursor.close();
            db.close();
            return contactList;
        } catch (Exception e) {
            // TODO: handle exception
            Log.e("getContactList", "" + e);
        }
        return contactList;
    }

    // Updating single contact
    public int updateContact(Contact contact) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(KEY_FNANE, contact.getFirstName());
        values.put(KEY_LNANE, contact.getLastName());
        values.put(KEY_EMAIL, contact.getEmail());
        values.put(KEY_PH_NO, contact.getPhoneNumber());
        values.put(KEY_DOB, contact.getDob());
        values.put(KEY_GENDER, contact.getGender());

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

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

    // Getting contacts Count
    public int getContactSize() {
        String countQuery = "SELECT count(*) FROM " + TABLE_CONTACTS;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(countQuery, null);

        int count = 0;
        if(cursor != null && cursor.getCount() > 0) {
            cursor.moveToFirst();
            count = cursor.getInt(0);
        }

        cursor.close();
        // return count
        return count;
    }
}

To use this database base hanlder class in an Activity class.

DatabaseHandler dbHandler = new DatabaseHandler(getApplicationContext());
dbHandler.addContact(c);

The pojo model class Contact.java

public class Contact {

    private long id;
    private String firstName;
    private String lastName;
    private String email;
    private String phoneNumber;
    private String dob;
    private String gender;

    public Contact(){}

    public Contact(String firstName, String lastName, String email, String phone, String dob, String gender) {
        this.firstName          = firstName;
        this.lastName           = lastName;
        this.email              = email;
        this.phoneNumber        = phone;
        this.dob                = dob;
        this.gender             = gender;
    }

    public Contact(long id, String firstName, String lastName, String email, String phoneNumber, String dob, String gender) {
        this.id = id;
        this.firstName = firstName;
        this.lastName = lastName;
        this.email = email;
        this.phoneNumber = phoneNumber;
        this.dob = dob;
        this.gender = gender;
    }

    public long getId() {
        return id;
    }

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

    public String getFirstName() {
        return firstName;
    }

    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getPhoneNumber() {
        return phoneNumber;
    }

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

    public String getDob() {
        return dob;
    }

    public void setDob(String dob) {
        this.dob = dob;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }
}