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