Android SQLite Database

For my number blocker application, I needed a way to save numbers that are to be blocked and would remain if the phone was rebooted or the application closed. To do this, I chose to store the information in a SQLite database. The first thing I needed to do was define a class that held information for each number in the databse, which can also be reused through out the application.

Phone Number Class

public class PhoneNumber {

	/**
	 * SQLite index key.
	 */
	private int id;

	/**
	 * Name of the person for the number.
	 */
	private String contactName;

	/**
	 * The phone number.
	 */
	private String contactNumber;

	/**
	 * The list the phone number is in.
	 */
	private int list;

	/**
	 * Call list fragment number.
	 */
	public final static int CALL_LIST = 0;

	/**
	 * SMS list fragment number.
	 */
	public final static int SMS_LIST = 1;

	/**
	 * Black list fragment number.
	 */
	public final static int BLACK_LIST = 2;

	/**
	 * White list fragment number.
	 */
	public final static int WHITE_LIST = 3;

	public PhoneNumber() {
		// TODO Auto-generated constructor stub
	}

	public PhoneNumber(String name, String number) {
		this.setName(name);
		this.setNumber(number);
	}

	public PhoneNumber(int id, String name, String number, int list) {
		this.id = id;
		this.contactName = name;
		this.contactNumber = number;
		this.list = list;
	}

	/**
	 * @return the id
	 */
	public int getId() {
		return id;
	}

	/**
	 * @param id the id to set
	 */
	public void setId(int id) {
		this.id = id;
	}

	/**
	 * @return the contact's name
	 */
	public String getName() {
		return contactName;
	}

	/**
	 * @param name the name to set
	 */
	public void setName(String name) {
		this.contactName = name;
	}

	/**
	 * @return the phone number
	 */
	public String getNumber() {
		return contactNumber;
	}

	/**
	 * @param number the number to set
	 */
	public void setNumber(String number) {
		this.contactNumber = number;
	}

	/**
	 * @return the list
	 */
	public int getList() {
		return list;
	}

	/**
	 * @param list the list to set
	 */
	public void setList(int list) {
		this.list = list;
	}
}

The Database

The database structure will look like:

Field Type Key
id int PRI
number text  
list int  

DatabaseHelper Class

Create a new class and extend the class SQLiteOpenHelper. After the class has been created, there are two functions that must be overriden:

  1. onCreate() - Called when the database is created for the first time. This is where the creation of tables and the initial population of the tables should happen.
  2. onUpgrade() - Called when the database needs to be upgraded. The implementation should use this method to drop tables, add tables, or do anything else it needs to upgrade to the new schema version.

After the two functions mentioned above have been overridden, you must implement your CRUD methods (Create, Read, Update, Delete). If the application is uninstalled, the database is also removed so there is no need to implement an onDelete method.

public class NumberBlockerDatabaseHandler extends SQLiteOpenHelper {
	/**
	 * Database version.
	 */
	private static final int DATABASE_VERSION = 1;

	/**
	 * Database name.
	 */
	private static final String DATABASE_NAME = "number_blocker";

	/**
	 * Name of the main database table.
	 */
	private static final String TABLE_MAIN = "main";

	/**
	 * Blacklist type.
	 */
	private static final int BLACKLIST = 1;

	/**
	 * Whitelist type.
	 */
	private static final int WHITELIST = 2;

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

	public NumberBlockerDatabaseHandler(Context context, String name,
			CursorFactory factory, int version) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	public NumberBlockerDatabaseHandler(Context context, String name,
			CursorFactory factory, int version,
			DatabaseErrorHandler errorHandler) {
		super(context, name, factory, version, errorHandler);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		String CREATE_MAIN_TABLE = "CREATE TABLE " + TABLE_MAIN + "(" + COLUMN_MAIN_ID + " INTEGER PRIMARY KEY,"
				+ COLUMN_MAIN_NUMBER + " TEXT, " + COLUMN_MAIN_LIST + " INTEGER)";
		db.execSQL(CREATE_MAIN_TABLE);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// Code to run when updates need to be performed on the database.
	}

	/**
	 * Adds a new record to the database.
	 * @param entry Entry to be added.
	 */
	public long addEntry(PhoneNumber entry) {
		// TODO add additional logic code for custom rules.
		Log.v("DatabaseHandler", "adding entry");

		values.put(COLUMN_MAIN_NUMBER, entry.getNumber());
		values.put(COLUMN_MAIN_LIST, entry.getList());

		long insert_id = db.insert(TABLE_MAIN, null, values);

		db.close();

		Log.v("DatabaseHandler", "inserted as ID #" + String.valueOf(insert_id));

		return insert_id;
	}

	/**
	 * Retrieves and entry.
	 * @param id ID of the entry to retrieve from the database.
	 * @return PhoneBlockerListEntry the retrieved entry.
	 */
	public PhoneNumber getEntry(int id) {
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.query(true, TABLE_MAIN, new String[] { COLUMN_MAIN_ID,  COLUMN_MAIN_NUMBER,  COLUMN_MAIN_LIST}, COLUMN_MAIN_ID + "=?", new String[] { String.valueOf(COLUMN_MAIN_ID) }, null, null, null, null);

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

		// TODO add code for custom rules.
		int id = Integer.parseInt(cursor.getString(0));
		String name = NumberBlocker.getContactName(this.context, cursor.getString(1));
		String number = cursor.getString(1);
		int list = Integer.parseInt(cursor.getString(2));
		PhoneNumber entry = new PhoneNumber(id, name, number, list);

		db.close();

		return entry;
	}

	/**
	 * Retrieves all the entries from the database.
	 * @return ArrayList<PhoneNumber> All entries from the database.
	 */
	public ArrayList<PhoneNumber> getAllEntries() {
		ArrayList<PhoneNumber> entries = new ArrayList<PhoneNumber>();
		PhoneNumber entry;

		String sql = "SELECT * FROM " + TABLE_MAIN;

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

		// TODO add code for custom rules.

		if(cursor.moveToFirst()) {
			do {
				entry = new PhoneNumber(NumberBlocker.getContactName(this.context, cursor.getString(1)), cursor.getString(1));

				entries.add(entry);
			} while(cursor.moveToNext());
		}

		return entries;
	}

	/**
	 * Retrieves all entries from a specific list.
	 * @param list The list to retrieve the entries from.
	 * @return All entries found for the specified list.
	 */
	public ArrayList<PhoneNumber> getAllEntriesForList(int list) {
		ArrayList<PhoneNumber> entries = new ArrayList<PhoneNumber>();
		PhoneNumber entry;

		String sql = "SELECT * FROM " + TABLE_MAIN + " WHERE " + COLUMN_MAIN_LIST + "=" + list;

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

		if(cursor.moveToFirst()) {
			do {
				entry = new PhoneNumber(Integer.parseInt(cursor.getString(0)), NumberBlocker.getContactName(this.context, cursor.getString(1)), cursor.getString(1), Integer.parseInt(cursor.getString(2)));
				entries.add(entry);
			} while(cursor.moveToNext());
		}

		return entries;
	}

	/**
	 * Updates an existing entry in the database.
	 * @param entry
	 * @return
	 */
	public int updateEntry(PhoneNumber entry) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();

		values.put(COLUMN_MAIN_NUMBER, entry.getNumber());
		values.put(COLUMN_MAIN_LIST, entry.getList());

		return db.update(TABLE_MAIN, values, COLUMN_MAIN_ID + " = ?", new String[]{String.valueOf(entry.getId())});
	}

	/**
	 * Deletes an existing entry from the database.
	 * @param entry The entry that is to be deleted.
	 */
	public void deleteEntry(PhoneNumber entry) {
		SQLiteDatabase db = this.getWritableDatabase();
		String whereClause = COLUMN_MAIN_ID + " = ?";
		String[] whereArgs = { String.valueOf(entry.getId()) };
		db.delete(TABLE_MAIN, whereClause, whereArgs);
		db.close();
	}
}