Android Database Helper Classes
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();
}
}
}
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!");
}
}