Android SQLite Database Tutorial

Insert

View

Search

Update

Delete

Contact.java

package com.androidhive.androidsqlite; 
 
public class Contact {
     
    //private variables
    int _id;
    String _name;
    String _phone_number;
     
    // Empty constructor
    public Contact(){
         
    }
    // constructor
    public Contact(int id, String name, String _phone_number){
        this._id = id;
        this._name = name;
        this._phone_number = _phone_number;
    }
     
    // constructor
    public Contact(String name, String _phone_number){
        this._name = name;
        this._phone_number = _phone_number;
    }
    // getting ID
    public int getID(){
        return this._id;
    }
     
    // setting id
    public void setID(int id){
        this._id = id;
    }
     
    // getting name
    public String getName(){
        return this._name;
    }
     
    // setting name
    public void setName(String name){
        this._name = name;
    }
     
    // getting phone number
    public String getPhoneNumber(){
        return this._phone_number;
    }
     
    // setting phone number
    public void setPhoneNumber(String phone_number){
        this._phone_number = phone_number;
    }
}

DatabaseHandler.java

public class DatabaseHandler extends SQLiteOpenHelper {

    public static class Schema_DB {
        private static int DATABASE_VERSION = 5;
        private static String DATABASE_NAME = "062";
    }

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

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(Schema_Contacts.QUERY_CREATE_TABLE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        if (newVersion > oldVersion) {
            db.execSQL(Schema_Contacts.QUERY_DROP_TABLE);
            db.execSQL(Schema_Contacts.QUERY_CREATE_TABLE);
        }
    }


    public static class Schema_Contacts {
        public static final String TABLE_NAME = "contacts";
        public static final String COL1_ID = "_id";
        public static final String COL2_NAME = "name";
        public static final String COL3_PHONE_NUMBER = "phone_number";
        public static final String COL4_EMAIL_ADDRESS = "email_address";
        public static final String QUERY_CREATE_TABLE =String.format("CREATE TABLE \"%s\" (\n" +
                "\"%s\"  INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,\n" +
                "\"%s\"  TEXT NOT NULL,\n" +
                "\"%s\"  TEXT,\n" +
                "\"%s\"  TEXT\n" +
                ");",TABLE_NAME,COL1_ID,COL2_NAME,COL3_PHONE_NUMBER,COL4_EMAIL_ADDRESS);

        public static final String QUERY_DROP_TABLE =String.format("DROP TABLE IF EXISTS \"%s\";",TABLE_NAME);
    }
}

Contacts.java

public class Contacts {
    private DatabaseHandler dbHandler;

    public Contacts(DatabaseHandler dbHandler) {
        this.dbHandler = dbHandler;
    }


    public long addContact(Contact contact) {
        SQLiteDatabase db = this.dbHandler.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(DatabaseHandler.Schema_Contacts.COL2_NAME, contact.getName());
        values.put(DatabaseHandler.Schema_Contacts.COL3_PHONE_NUMBER, contact.getPhoneNumber());
        values.put(DatabaseHandler.Schema_Contacts.COL4_EMAIL_ADDRESS, contact.getEmailAddress());

        long inserted_id = db.insert(DatabaseHandler.Schema_Contacts.TABLE_NAME, null, values);
        db.close();

        return inserted_id;
    }

    public Cursor getContactCursor(int id) {
        SQLiteDatabase db = this.dbHandler.getReadableDatabase();


        String[] columns = {
                DatabaseHandler.Schema_Contacts.COL1_ID,
                DatabaseHandler.Schema_Contacts.COL2_NAME,
                DatabaseHandler.Schema_Contacts.COL3_PHONE_NUMBER,
                DatabaseHandler.Schema_Contacts.COL4_EMAIL_ADDRESS
        };

        String selection = DatabaseHandler.Schema_Contacts.COL1_ID + " = ?";
        String[] selectionArgs = {String.valueOf(id)};

        Cursor cursor = db.query(DatabaseHandler.Schema_Contacts.TABLE_NAME, columns,
                selection,
                selectionArgs, null, null, null, null);

        return cursor;
    }

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

        String[] columns = {
                DatabaseHandler.Schema_Contacts.COL1_ID,
                DatabaseHandler.Schema_Contacts.COL2_NAME,
                DatabaseHandler.Schema_Contacts.COL3_PHONE_NUMBER,
                DatabaseHandler.Schema_Contacts.COL4_EMAIL_ADDRESS
        };

        String selection = DatabaseHandler.Schema_Contacts.COL1_ID + " = ?";
        String[] selectionArgs = {String.valueOf(id)};

        Cursor cursor = db.query(DatabaseHandler.Schema_Contacts.TABLE_NAME, columns,
                selection,
                selectionArgs, null, null, null, null);

        if (cursor != null)
            cursor.moveToFirst();

        Contact contact = new Contact(cursor.getInt(cursor.getColumnIndex(DatabaseHandler.Schema_Contacts.COL1_ID)),
                cursor.getString(cursor.getColumnIndex(DatabaseHandler.Schema_Contacts.COL2_NAME)),
                cursor.getString(cursor.getColumnIndex(DatabaseHandler.Schema_Contacts.COL3_PHONE_NUMBER)),
                cursor.getString(cursor.getColumnIndex(DatabaseHandler.Schema_Contacts.COL4_EMAIL_ADDRESS)));


        cursor.close();
        db.close();

        return contact;
    }

    public List<Contact> getAllContacts() {
        List<Contact> contactList = new ArrayList<Contact>();

        String selectQuery = String.format("SELECT * FROM %s", DatabaseHandler.Schema_Contacts.TABLE_NAME);

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

        if (cursor.moveToFirst()) {
            do {
                Contact contact = new Contact(cursor.getInt(cursor.getColumnIndex(DatabaseHandler.Schema_Contacts.COL1_ID)),
                        cursor.getString(cursor.getColumnIndex(DatabaseHandler.Schema_Contacts.COL2_NAME)),
                        cursor.getString(cursor.getColumnIndex(DatabaseHandler.Schema_Contacts.COL3_PHONE_NUMBER)),
                        cursor.getString(cursor.getColumnIndex(DatabaseHandler.Schema_Contacts.COL4_EMAIL_ADDRESS)));

                contactList.add(contact);
            } while (cursor.moveToNext());
        }


        cursor.close();
        db.close();

        return contactList;
    }

    public Cursor getAllContactsCursor() {
        String selectQuery = String.format("SELECT * FROM %s", DatabaseHandler.Schema_Contacts.TABLE_NAME);

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

        return cursor;
    }

    public List<Contact> getContactsByName(String name) {
        List<Contact> contactList = new ArrayList<Contact>();

        SQLiteDatabase db = this.dbHandler.getWritableDatabase();

        String[] columns = {
                DatabaseHandler.Schema_Contacts.COL1_ID,
                DatabaseHandler.Schema_Contacts.COL2_NAME,
                DatabaseHandler.Schema_Contacts.COL3_PHONE_NUMBER,
                DatabaseHandler.Schema_Contacts.COL4_EMAIL_ADDRESS
        };

        String selection = DatabaseHandler.Schema_Contacts.COL2_NAME + " LIKE ?";
        String[] selectionArgs = {"%" + name + "%"};

        Cursor cursor = db.query(DatabaseHandler.Schema_Contacts.TABLE_NAME, columns, selection, selectionArgs, null, null, null);

        if (cursor.moveToFirst()) {
            do {
                Contact contact = new Contact(cursor.getInt(cursor.getColumnIndex(DatabaseHandler.Schema_Contacts.COL1_ID)),
                        cursor.getString(cursor.getColumnIndex(DatabaseHandler.Schema_Contacts.COL2_NAME)),
                        cursor.getString(cursor.getColumnIndex(DatabaseHandler.Schema_Contacts.COL3_PHONE_NUMBER)),
                        cursor.getString(cursor.getColumnIndex(DatabaseHandler.Schema_Contacts.COL4_EMAIL_ADDRESS)));

                contactList.add(contact);
            } while (cursor.moveToNext());
        }


        cursor.close();
        db.close();

        return contactList;
    }

    public Cursor getContactsByNameCursor(String name) {

        SQLiteDatabase db = this.dbHandler.getWritableDatabase();

        String[] columns = {
                DatabaseHandler.Schema_Contacts.COL1_ID,
                DatabaseHandler.Schema_Contacts.COL2_NAME,
                DatabaseHandler.Schema_Contacts.COL3_PHONE_NUMBER,
                DatabaseHandler.Schema_Contacts.COL4_EMAIL_ADDRESS
        };

        String selection = DatabaseHandler.Schema_Contacts.COL2_NAME + " LIKE ?";
        String[] selectionArgs = {name};

        Cursor cursor = db.query(DatabaseHandler.Schema_Contacts.TABLE_NAME, columns, selection, selectionArgs, null, null, null);


        return cursor;
    }

    public int getContactsCount() {
        String countQuery = "SELECT  * FROM " + DatabaseHandler.Schema_Contacts.TABLE_NAME;
        SQLiteDatabase db = this.dbHandler.getReadableDatabase();
        Cursor cursor = db.rawQuery(countQuery, null);

        int count = cursor.getCount();

        cursor.close();
        db.close();


        return count;
    }

    public int updateContact(Contact contact) {
        SQLiteDatabase db = this.dbHandler.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(DatabaseHandler.Schema_Contacts.COL2_NAME, contact.getName());
        values.put(DatabaseHandler.Schema_Contacts.COL3_PHONE_NUMBER, contact.getPhoneNumber());
        values.put(DatabaseHandler.Schema_Contacts.COL4_EMAIL_ADDRESS, contact.getEmailAddress());


        int rows_affected = db.update(DatabaseHandler.Schema_Contacts.TABLE_NAME, values,
                DatabaseHandler.Schema_Contacts.COL1_ID + " = ?",
                new String[]{String.valueOf(contact.getId())});
        db.close();

        return rows_affected;
    }

    public int deleteContact(Contact contact) {
        SQLiteDatabase db = this.dbHandler.getWritableDatabase();
        int rows_affected = db.delete(DatabaseHandler.Schema_Contacts.TABLE_NAME,
                DatabaseHandler.Schema_Contacts.COL1_ID + " = ?",
                new String[]{String.valueOf(contact.getId())});
        db.close();

        return rows_affected;
    }
}

References
https://github.com/mhdr/AndroidSamples/tree/master/062
http://www.androidhive.info/2011/11/android-sqlite-database-tutorial/
https://www.youtube.com/watch?v=38DOncHIazs&t=1510s&list=PLshdtb5UWjSp0879mLeCsDQN6L73XBZTk&index=34
https://www.youtube.com/watch?v=ahE8bQRD4f0&t=3s&list=PLshdtb5UWjSp0879mLeCsDQN6L73XBZTk&index=35
https://www.youtube.com/watch?v=V4FqE83K1n0&list=PLshdtb5UWjSp0879mLeCsDQN6L73XBZTk&index=36
https://www.youtube.com/watch?v=Im6oY8QSVHU&list=PLshdtb5UWjSp0879mLeCsDQN6L73XBZTk&index=37
https://www.youtube.com/watch?v=HSTt_M4bpBY&list=PLshdtb5UWjSp0879mLeCsDQN6L73XBZTk&index=38
https://www.youtube.com/watch?v=g2x1lzBKB8M&list=PLshdtb5UWjSp0879mLeCsDQN6L73XBZTk&index=39