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.

By Moazzam

Moazzam is a software developer working in the big apple. He has over a decade of experience in development. While most of his work involves developing web applications, he does development for Android and Windows Mobile in his spare time.

11 replies on “Working with a database in Android”

This is exactly what I was looking for.. a proper dataaccess implementaion against the 1.0 sdk. Thanks a lot, the code is very clear. The only question I have is in the upgrade method, it says “if table exists” and passes the db name, will that drop the db itself?

Thanks again!

Hello buddy,

thanks very much. really a superb class…!
i used it as component…and it is working perfect…

Hello,
I’ve tried implementing this code but I’m getting errors whenever I try to insert. Could you give me some details of how the DBLayer class is implemented into other source files.
I have tried using your code with no changes and in my source file, declaring a private DBLayer object ( named db) and initialising in the onCreate method. It looks like this,

private DBAdapter db;

public void onCreate(Bundle savedInstanceState){
super.onCreate(savedInstanceState);
setContentView(R.layout.disp_profile);
db = new DBAdapter(this);
db.open();

I then use the db methods in a onClick listener to insert data when this button is clicked.

db.createAlert(profi.getName(), “”+profi.getAge() , “”+profi.getWeight());

where profi is a object i created to hold a users profile information, getAge and getWeight return int.

I hope you can help.

Thanks
John

meh,
knew that would happen, as soon as i posted my comment i discovered the problem. It was a incorrect Toast statement.
Cheers anyway. This is the best walkthrough I’ve seen. Very easy to understand and doesn’t get you bogged down in the GUIs that other examples do.
Cheers
John

Hello,
I’ve tried implementing this code but I’m getting errors whenever I try to insert. Could you give me some details of how the DBLayer class is implemented into other source files.

what is Toast statement?

I’m a newbie in androids

Hi,

How do I debug an Sqlite Database. I need to check whether the value is being added. Is there a way to debug it?

Thanks,
Prerna

Hi,

I am not clear about on onUpgrade() method functionality. Can anyone reply to below questions?

1. Is it possible to take the data backup when you delete the table in onUpgrade()?

Ex: I have browser application and I am planning to upgrade the browser without losing my previous bookmarks and browser preferences. Can you please provide the detailed explanation and code?

Thanks in advance

Leave a Reply

Your email address will not be published. Required fields are marked *