Android Database Helper Classes

DatabaseFunctions.java

import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

public class DatabaseFunctions {

	private static final String tag = "DatabaseFunctions";
	static SQLiteDatabase db;
	static DBHelper dbHelper;
	
	public static final int BLOCKED = 1;
	public static final int NOT_BLOCKED = 0;
	public static final int UNKNOWN = 2;
	public static final int UNKNOWN_BLOCKED = 3;

	
	public static void addFriend(String friendUserId, String userId,
			String friendName, int status, String profile_pic, double distance) {

		ContentValues values;
		try {
			values = new ContentValues();
			values.put(DBHelper.FRIEND_USER_ID, friendUserId);
			values.put(DBHelper.FRIEND_NAME, friendName);
			values.put(DBHelper.FRIEND_BLOCKED, status);
			values.put(DBHelper.FRIEND_PROFILE_PIC, profile_pic);
			values.put(DBHelper.FRIEND_DISTANCE, distance);
			values.put(DBHelper.USER_ID, userId);
			db.insert(DBHelper.TABLE_FRIENDS, null, values);
		} catch (Exception e) {
			Logcat.e(tag, "Insert Friend error : " + e.toString());
		}

	}
	public static boolean isFriend(String friendUserId, String userId) {
		Cursor cursor = null;
		try {
			cursor = db.rawQuery("SELECT * FROM " + DBHelper.TABLE_FRIENDS
					+ " where " + DBHelper.FRIEND_USER_ID + "='" + friendUserId
					+ "' and " + DBHelper.USER_ID + "='" + userId + "' and "
					+ DBHelper.FRIEND_BLOCKED + "=" + NOT_BLOCKED, null);

			if (cursor.getCount() > 0) {
				return true;
			} else {
				return false;
			}

		} catch (Exception e) {
			Logcat.e(tag, "isFriendByDeviceId Error : " + e.toString());
			return false;
		} finally {
			cursor.close();
		}
	}

	public static boolean updateFriendDistance(String friendUserId,
			String userId, Double distance) {
		ContentValues values;
		try {
			values = new ContentValues();
			values.put(DBHelper.FRIEND_DISTANCE, distance);
			db.update(DBHelper.TABLE_FRIENDS, values, DBHelper.FRIEND_USER_ID
					+ "=? and " + DBHelper.USER_ID + "=?", new String[] {
					friendUserId, userId });

			Log.e(tag, "updateDistance Updated!");
			return true;
		} catch (Exception e) {
			Log.e(tag, "updateFriendDistance error : " + e.toString());
			return false;
		}
	}
	public static void deleteAllFriends(String userId) {

		try {
			int countRow = db.delete(DBHelper.TABLE_FRIENDS, DBHelper.USER_ID
					+ "=? and (" + DBHelper.FRIEND_BLOCKED + "=? OR "
					+ DBHelper.FRIEND_BLOCKED + "=?)", new String[] { userId,
					String.valueOf(NOT_BLOCKED), String.valueOf(BLOCKED) });
			Logcat.e(tag, "All friends deleted : " + countRow);
		} catch (Exception e) {
			Logcat.e(tag, "Delete friends error : " + e.toString());
		}

	}
	public static ArrayList<Chat> getChatHistoryForDeviceId(
			String friendUserId, String userId) {
		Cursor cursor = null;
		ArrayList<Chat> chatHistory = null;
		try {
			cursor = db.rawQuery("SELECT * FROM " + DBHelper.TABLE_CHATHISTORY
					+ " where " + DBHelper.FRIEND_USER_ID + "='" + friendUserId
					+ "'" + " and " + DBHelper.USER_ID + "='" + userId + "'",
					null);

			if (cursor != null) {
				chatHistory = new ArrayList<Chat>();
				long time;
				while (cursor.moveToNext()) {
					time = Long.parseLong(cursor.getString(2));
					Chat chat = new Chat(cursor.getString(0), time,
							cursor.getString(1), cursor.getInt(3),
							cursor.getInt(4));
					chatHistory.add(chat);

				}
				return chatHistory;
			} else {
				return chatHistory;
			}

		} catch (Exception e) {
			Logcat.e(tag, "getChatHistory Error : " + e.toString());
			return chatHistory;
		} finally {
			if (cursor != null) {
				cursor.close();
			}
		}
	}


	public static String getProfilePic(String friendUserId, String userId) {
		Cursor cursor = null;
		String profilePic = "";
		try {
			cursor = db.rawQuery("SELECT " + DBHelper.FRIEND_PROFILE_PIC
					+ " FROM " + DBHelper.TABLE_FRIENDS + " where "
					+ DBHelper.FRIEND_USER_ID + "='" + friendUserId + "'"
					+ " and " + DBHelper.USER_ID + "='" + userId + "'", null);

			if (cursor != null) {
				while (cursor.moveToNext()) {
					profilePic = cursor.getString(0);
				}
				return profilePic;
			} else {
				return profilePic;
			}

		} catch (Exception e) {
			Logcat.e(tag, "getProfilePic Error : " + e.toString());
			return profilePic;
		} finally {
			if (cursor != null) {
				cursor.close();
			}
		}
	}

	public static void openDB(Context context) {
		dbHelper = new DBHelper(context);
		if (db != null) {
			if (!db.isOpen()) {
				db = dbHelper.getWritableDatabase();
			}
		} else {
			db = dbHelper.getWritableDatabase();
		}
	}

	public static void closeDB() {
		if (db.isOpen()) {
			db.close();
			dbHelper.close();
		}
	}
}

DBHelper.java

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DBHelper extends SQLiteOpenHelper {

	public static final int DB_VERSION = 1;
	public static final String DB_NAME = "TestDB";

	public static final String USER_ID = "user_id";
	public static final String TABLE_FRIENDS = "friends";
	public static final String FRIEND_USER_ID = "friend_user_id";
	public static final String FRIEND_NAME = "friend_name";
	public static final String FRIEND_BLOCKED = "friend_blocked";
	public static final String FRIEND_PROFILE_PIC = "friend_profile_pic";
	public static final String FRIEND_DISTANCE = "friend_distance";
	
	public static final String TAG = DBHelper.class.getSimpleName();

	@SuppressWarnings("unused")
	private Context mContext;

	public DBHelper(Context context) {
		super(context, DB_NAME, null, DB_VERSION);
		this.mContext = context;
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		version1Create(db);
		Log.e(TAG, "Database created successfully!");
	}

	private void version1Create(SQLiteDatabase db) {
		String createFriends = "CREATE TABLE IF NOT EXISTS "
				+ TABLE_FRIENDS + "("
				+ FRIEND_USER_ID + " VARCHAR, "
				+ FRIEND_NAME + " VARCHAR, "
				+ FRIEND_BLOCKED + " INTEGER, "
				+ FRIEND_PROFILE_PIC + " VARCHAR, "
				+ FRIEND_DISTANCE + " REAL,"
				+ USER_ID + " VARCHAR);";
		
		db.execSQL(createFriends);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub

		switch (newVersion) {
		case 1:
			version1Create(db);
			Log.e(DB_NAME + " UPGRADE", "Database updated to version " + 1);
		}

		Log.e(TAG, "Database updated successfully!");
	}

}