Categories
Android Uncategorized

Working with a database in Android

Android uses SQLite for its database needs. SQLite is a very fast and lightweight database. It is file based which makes it highly portable and an ideal candidate for use as an embedded database. For those who prefer to just go through the code and learn, the Notebook application example on Android’s website can be very useful. Let’s begin with the tutorial shall we.

First, you will need to make a class that will interact with the database. After that class has been written, you will instantiate it and use it to perform your data operations. This will keep your code encapsulated and easy to maintain. Android tries to encourage its programmers to use the MVC concept and makes it easy to implement it.

Assume your table structure is this :

create tblData (
_id int primary key autoincrement,
alert_text text,

alert_date varchar(20),
alert_time varhcar(20)
);

Setting up Connections and getting everything ready

Our database interaction class will be called DBLayer. It is a public class, so it will need to be in a file called DBLayer.java. Let’s say we have a table with 3 fields and we want to set and get data from it. This is how you set the class up :

/**
 * Database Wrapper
 *
 * DB Schema:
 * ------------------------------------
 * Create table tblData (
 *     _id int primary key autoincrement,
 *     alert_text text,
 *     alert_date varchar(20),
 *     alert_time varchar(20)
 * );
 */
public class DBLayer {

	/*******************************************************
	* These constants refer to the fields in the database.
	* _id is Android's naming convention for ID fields.
	* Our database has 3 fields, so we have 3 constants.
	********************************************************/

	public static final String KEY_NAME  = "name";
	public static final String KEY_VAL   = "value";
	public static final String KEY_ROWID = "_id";

	/*******************************************************
	* This is not needed for the database. It is
	* here to help us tag our logging messages
	*******************************************************/
	public static final String TAG = "AlertDB";

	private DatabaseHelper dbHelper ;
	private SQLiteDatabase db;

	private Context mCtx = null;

	/*******************************************************
	* Some other constants related to our database's
	* information. They should be self explanatory
	********************************************************/
	private static final String DATABASE_NAME = "gAlert_db";
	private static final String DATABASE_TABLE = "tblData";
	private static final int DATABASE_VERSION = 2;
	private static final String DATABASE_CREATE =
	"create table gAlert_alerts ( "
	+ "_id integer primary key autoincrement, "
	+ "alert_text text not null,"
	+ "alert_date varchar(20) not null,"
	+ "alert_time varchar(20) not null"
	+");";

	/********************************************************
	* Think of this as a driver for your database
	* interaction. You can just copy and paste this part in
	* all your database interaction classes.
	*********************************************************/
	private static class DatabaseHelper extends SQLiteOpenHelper {
		DatabaseHelper(Context ctx) {
			super(ctx, DATABASE_NAME, null, DATABASE_VERSION);
		}
		@Override
		public void onCreate(SQLiteDatabase db) {
			db.execSQL(DATABASE_CREATE);
		}
		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			db.execSQL("DROP IF TABLE EXISTS "+DATABASE_TABLE);
			onCreate(db);
		}
	}

	/** Constructor */
	public DBLayer(Context ctx) {
		mCtx = ctx;
	}
	/********************************************************
	* This opens a connection to the database but if
	* something goes wrong, it will throw an exception.
	********************************************************/
	public DBLayer open () throws SQLException {
		dbHelper = new DatabaseHelper(mCtx);
		db = dbHelper.getWritableDatabase();
		return this;
	}
	/** Closes a database connection */
	public void close() {
		dbHelper.close();
	}
}

I have read articles which state a different way using databases in Android. From what I can tell these tutorials were for versions prior to 1.0. In version 1.0, you create a class which extends SQLiteOpenHelper and you encase that class in another class which you will call to insert and retrieve data. The comments in the code above should be self-explanatory. If you have any questions I haven’t answered, please leave a comment and I will post a reply.

You don’t need to have a database already set up. If the database you try to open doesn’t exist, then Android will create it. It will run the onCreate() method when it creates the database. And, if the database version changes on the phone. It will run the onUpgrade() method.

Running your Queries

Now the class DBLayer can connect to the database. However, just connecting is of no use unless you can retrieve some data from your database. There are 4 methods you can use in Android and they all perform different functions. They are : query, insert, delete and update. query() is used to retrieve data. The others should be self-explanatory.

Here’s a method that will retrieve multiple rows from the database:

	public Cursor fetchAllAlerts() {
		try {
			return db.query(DATABASE_TABLE,
				new String[] {KEY_ALERT_TEXT, KEY_ALERT_DATE,
				KEY_ALERT_TIME, KEY_ROWID}, null, null,
				null, null, null);
		} catch (Exception e) {
			Log.e("yo", e.getMessage());
			return null;
		}
	}

You will rarely (if ever) find yourself writing any SQL for Android applications. The reason being Android’s framework does all the dirty work behind the scenes. All you have to do is set  your object up and provide the data retrieval, insertion methods with the neccesary information.

Here are some more methods. Everything should be self explanatory.

	public long createAlert(String text, String date, String time) {
	ContentValues vals = new ContentValues();
	vals.put(KEY_ALERT_TEXT, text);
	vals.put(KEY_ALERT_DATE, date);
	vals.put(KEY_ALERT_TIME, time);
	return db.insert(DATABASE_TABLE, null, vals);
}

public boolean deleteAlert(long rowId) {
	return db.delete(DATABASE_TABLE, KEY_ROWID+"="+rowId, null) > 0;
}

public Cursor fetchAllAlerts() {
	try {
		return db.query(DATABASE_TABLE, new String[] {
				KEY_ALERT_TEXT, KEY_ALERT_DATE,
				KEY_ALERT_TIME, KEY_ROWID}, 
					null, null, null, null, null);
	} catch (Exception e) {
		Log.e("yo", e.getMessage());
		return null;
	}
}

public Cursor fetchAlert(long rowId) throws SQLException {
	Cursor ret = db.query(DATABASE_TABLE, new String[] {
				KEY_ALERT_TEXT, KEY_ALERT_DATE,
				KEY_ALERT_TIME, KEY_ROWID},
				KEY_ROWID+"="+rowId, null, null,
				null, null);
	if (ret != null) {
		ret.moveToFirst();
	}
	return ret;
}

public boolean updateAlert(long rowId, String text, String date, String time) {
	ContentValues vals = new ContentValues();
	vals.put(KEY_ALERT_TEXT, text);
	vals.put(KEY_ALERT_DATE, date);
	vals.put(KEY_ALERT_TIME, time);
	return db.update(DATABASE_TABLE, vals,KEY_ROWID+"="+rowId, null) > 0;
}

You can get the code for the whole class here. In this article, there wasn’t much explanation because I didn’t think I needed to explain anymore after pasting the code. I am sure there are people reading this who feel otherwise. Please let me know what needs more explanation and I will elaborate on that topic.

Categories
Android Uncategorized

Debugging errors in Android

Android has a great framework to work in. However, it is in its infancy and debugging my code has been a very big problem for me. Whenever something goes wrong in the code, I always get “source not found” error. It doesn’t tell me I have a nullPointer exception , or string is incomplete.  No matter what’s wrong with the code, I always get “source not found” error. The reason that happens is Android doesn’t pack the source along with the application it sends to the emulator (maybe they should do that for a debugging run). After searching the net for a while, I came across this solution – wrap your code in a try and catch block and then log the exception to console. Here is an example :

public class SomeClass extends ListActivity {
	private AlertDB db ;
	private static final int ALERT_ADD = 100;

	@Override
	public void onCreate(Bundle savedInstanceState) {
	super.onCreate(savedInstanceState);
	//setContentView(R.layout.alert_list);
	try {
		db = new AlertDB(this);
		Cursor c = db.fetchAllAlerts();
	} catch (Exception e) {
		Log.e("ERROR", "ERROR IN CODE:"+e.toString());
	}
}

Doing that will display a neat little line in your logcat window (look at the screenshots at the bottom). And, instead of getting the annoying “source not found” error. You will know what the actual error is. In Android, you can log three types of events – information, warning and error. Information is just verbose text being written out to the log.  I don’t need to explain warning and errors.

For logging information you can use :

Log.i(TAG, message);

For logging warnings you can use :

Log.w(TAG, message);

I have already shown how to log errors in the code above.

Click to enlarge
Click to enlarge
Click to enlarge
Click to enlarge