In this post, I’ll go over bare bones database management in an Android application, demonstrating how to create a database and perform simple CRUD actions against this database. I’ll also wire up a very simple UI to test our CRUD functionality. Finally, I’ll show how to utilize the built-in database upgrade functionality.
The Database
Android ships with SQLite, an open-source and very lightweight database engine that is popular in a great many products. From their web site:
SQLite is a software library that implements a self-contained,serverless, zero-configuration, transactional SQL database engine. SQLite is the most widely deployed SQL database engine in the world. The source code for SQLite is in the public domain.
We will be creating a simple one table database for a fictional game that will track player history. Each row will represent a game, and will store whether the player won or lost, the time it took to finish the game, and what difficulty level the computer opponent was set to.
First Steps
We will extend SQLiteOpenHelper to help us talk to our database. Create a new Android project and add a new class file called DbHelper. Extend SQLiteOpenHelper, and let Eclipse add the methods that need to be overridden.
package com.personal.androidfun.databasetests;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
public class DbHelper extends SQLiteOpenHelper {
public DbHelper(Context context, String name, CursorFactory factory,
int version) {
super(context, name, factory, version);
// TODO Auto-generated constructor stub
}
@Override
public void onCreate(SQLiteDatabase db) {
// TODO Auto-generated method stub
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO Auto-generated method stub
}
}
You can see that we have to implement two methods, onCreate and onUpgrade. To ensure that clients of this class don’t have to know all of the nitty gritty database details, we’ll change the constructor as below.
public DbHelper(Context context) {
super(context, "games.db", null, 1);
}
The onCreate method is called whenever the database is first created. For our simple example, we will create the below schema.
So our onCreate method will now look like the below.
@Override
public void onCreate(SQLiteDatabase db) {
String sql = "create table Games " +
"(PlayerId integer primary key, " +
"Result integer, " +
"PlayTime integer, " +
"Difficulty integer); ";
db.execSQL(sql);
}
We will ignore the onUpdate method for now and focus on getting our database created with simple CRUD. Now that our DbHelper class is created, we can move on to the CRUD operations.
The Data Layer
Add another class to our project called DataLayer. This class will be responsible for using our DbHelper to perform our desired CRUD functions. We will start with the “C” portion, record creation. Let’s add a new method called AddGame to our DataLayer, giving us a class that looks like below.
public class DataLayer {
private DbHelper _dbHelper;
public DataLayer(Context c) {
_dbHelper = new DbHelper(c);
}
public void AddGame(int playerId, int result, int playTime, int difficulty) {
SQLiteDatabase db = _dbHelper.getWritableDatabase();
try {
ContentValues values = new ContentValues();
values.put("PlayerId", playerId);
values.put("Result", result);
values.put("PlayTime", playTime);
values.put("Difficulty", difficulty);
db.insert("Games", "", values);
} finally {
if (db != null)
db.close();
}
}
}
The code is pretty straightforward. First you’ll notice our constructor, where we are instantiating our DbHelper class that we created above. The Context object will be passed in from our app. For the AddGame method, we are keeping this bare bones, so we just pass in the four values to be written out. We use our DbHelper class to bring back a writable version of our database. We then hydrate a ContentValues object which contains name-value pairs that will be written out to our database. Next, we call the insert method on the database object returned by our DbHelper. Finally, we clean up by closing the database after our insert.
Testing Harness
To test our fun, we’ll whip up a mangled UI that we’ll use to call the methods as we go. Add a button to the main layout, and add an event handler that will instantiate our DataLayer and add a new game. The simple layout is below.
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout
xmlns:android="http://schemas.android.com/apk/res/android"
android:orientation="vertical"
android:layout_width="fill_parent"
android:layout_height="fill_parent">
<Button
android:text="Add Record"
android:id="@+id/AddRecord"
android:layout_width="wrap_content"
android:layout_height="wrap_content"></Button>
</LinearLayout>
And below is the code for the main screen.
package com.personal.androidfun.databasetests;
import android.app.Activity;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
public class main extends Activity implements OnClickListener {
@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.main);
Button b = (Button) findViewById(R.id.AddRecord);
b.setOnClickListener(this);
}
@Override
public void onClick(View v) {
DataLayer d = new DataLayer(getBaseContext());
d.AddGame(1, 2, 3, 4);
}
}
Now you can run the app, press the button, and trust me that a record was written out! Yeah, I know, not very satisfactory.
Using sqlite3 With adb
There’s a better way to check out the status of our database than just trusting me. The Android SDK ships with sqlite3, a command line tool that you can use to issue queries against any database on your emulator. Additionally, the Android SDK has a tool called adb, or Android Debug Bridge, that allows you to essentially start a shell prompt on your emulator, as if it were a PC. We will be using both of these tools to first open our database and then verify that we our code is doing the right thing.
The first step is verifying that the Tools folder in the Android SDK is on your path so that we can launch the executable. The actual location may vary; mine is pictured below.
Add this directory to your path and make sure your Android emulator is running. Then start a command prompt, type adb shell, and press enter.
You now have a command shell on your emulator. You can issue standard *nix commands, such as ls and cd. To work with our newly created database, we have to find it. All databases on an Android device are created in /data/data/<your package name>/databases. In our case, we called our package com.personal.androidfun.databasetests, so we issue some cd commands to get to the right spot.
So now we’ve used adb to open up a shell onto our emulator and navigate to where our newly created database is stored. Next we have to connect to it using sqlite3. To do this, type sqlite3 <database name>, where our database is called games.db.
And now we’re connected to our database. You can issue any valid SQL against your database, from CRUD to DDL. You can find full instructions on how to use the sqlite3 tool here.
The final step in this section is to verify that our application actually inserted a row into our database. The fact that the database exists verifies that our onCreate method actually created it. To verify our insertion, we issue a standard select statement, terminated by a semicolon.
There’s our simple row, with column values separated by pipes. So far, so good.
RUD…
We’ve taken care of the creation part of our CRUD requirement, now we need to take care of the rest. First, reading information. We need to add a method to our DataLayer class. For now, we will just select all records every time. We’ll create a small class to represent a row in the database, called GameResult.
package com.personal.androidfun.databasetests;
public class GameResult {
public GameResult(int playerId, int result, int playTime, int difficulty) {
PlayerId = playerId;
Result = result;
PlayTime = playTime;
Difficulty = difficulty;
}
public int PlayerId;
public int Result;
public int PlayTime;
public int Difficulty;
}
Next, we’ll update our DataLayer class to select all records from the table and hydrate a list of our new GameResult objects.
public ArrayList<GameResult> SelectGames() {
SQLiteDatabase db = _dbHelper.getReadableDatabase();
try {
ArrayList<GameResult> results = new ArrayList<GameResult>();
Cursor c = db.rawQuery("select * from Games", null);
if (c.getCount() > 0) {
c.moveToFirst();
do {
results.add(new GameResult(
c.getInt(c.getColumnIndex("PlayerId")),
c.getInt(c.getColumnIndex("Result")),
c.getInt(c.getColumnIndex("PlayTime")),
c.getInt(c.getColumnIndex("Difficulty"))));
} while (c.moveToNext());
}
return results;
} finally {
if (db != null)
db.close();
}
}
We use the rawQuery method to do a simple select * from our table. We make sure we have records before we try to access our cursor. If we have records, we run through our cursor, hydrating a GameResult object for every row we find.
We’ll add another button to our layout, call it SelectRecords, and add some code to handle the selection. The updated layout looks like below.
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout
xmlns:android="http://schemas.android.com/apk/res/android"
android:orientation="vertical"
android:layout_width="fill_parent"
android:layout_height="fill_parent">
<Button
android:text="Add Record"
android:id="@+id/AddRecord"
android:layout_width="wrap_content"
android:layout_height="wrap_content"></Button>
<Button
android:text="Select Records"
android:id="@+id/SelectRecords"
android:layout_width="wrap_content"
android:layout_height="wrap_content"></Button>
</LinearLayout>
Finally, we augment our button handler in our main class to handle both adding and querying, and wire it up to listen to click events on our new button.
public class main extends Activity implements OnClickListener {
@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.main);
Button b = (Button) findViewById(R.id.AddRecord);
b.setOnClickListener(this);
b = (Button) findViewById(R.id.SelectRecords);
b.setOnClickListener(this);
}
@Override
public void onClick(View v) {
Button b = (Button) v;
DataLayer d = new DataLayer(getBaseContext());
switch (b.getId()) {
case R.id.AddRecord:
d.AddGame(1, 2, 3, 4);
break;
case R.id.SelectRecords:
ArrayList<GameResult> results = d.SelectGames();
Toast t = Toast.makeText(getBaseContext(), "# records: "
+ results.size(), 1000);
t.show();
break;
}
}
}
And now we have the R part of CRUD. Run the app, click the Select Records button, and we should have something like below.
For the U and the D part of our CRUD goal, I’ll skip the layout snippets and just show the code.
For our update piece, we will update all PlayTime columns in our database to be a random number. We again go back to our DataLayer class and add a new method.
public int UpdateGames() {
SQLiteDatabase db = _dbHelper.getWritableDatabase();
try {
Random r = new Random();
ContentValues values = new ContentValues();
values.put("PlayTime", r.nextInt());
int affected = db.update("Games", values, null, null);
return affected;
} finally {
if (db != null)
db.close();
}
}
Notice that to perform an update we use the now-familiar ContentValues object, populating it only with the key-value pairs that we want to update.
And below is our new onClick handler in our main class (don’t forget to wire up the new Update button to the handler).
@Override
public void onClick(View v) {
Toast t = null;
Button b = (Button) v;
DataLayer d = new DataLayer(getBaseContext());
switch (b.getId()) {
case R.id.AddRecord:
d.AddGame(1, 2, 3, 4);
break;
case R.id.SelectRecords:
ArrayList<GameResult> results = d.SelectGames();
t = Toast.makeText(getBaseContext(),
"# records: " + results.size(), 1000);
t.show();
break;
case R.id.UpdateRecords:
int affected = d.UpdateGames();
t = Toast.makeText(getBaseContext(), "records affected: "
+ affected, 1000);
t.show();
}
}
And now we have the U part of our CRUD goal. To verify that our update ran successfully, switch back to our adb session and re-run our select statement.
We’re almost there. Now for the delete part, and fixing our schema so we can add more than one row for a player. Again, we go back to our DataLayer class and add a new method to handle deletes.
public int DeleteGames() {
SQLiteDatabase db = _dbHelper.getWritableDatabase();
try {
int recordsDeleted = db.delete("Games", "1", null);
return recordsDeleted;
} finally {
if (db != null)
db.close();
}
}
Note here that we are passing a “1” as the where clause to our delete. This tells the method to both delete everything and to return the number of rows that were deleted.
Then we add a new button to fire our Delete method, augment the onClick handler to call our new method on the DataLayer class, and wire up the new button to our handler. I’ll only show the augmented onClick handler below.
@Override
public void onClick(View v) {
Toast t = null;
Button b = (Button) v;
DataLayer d = new DataLayer(getBaseContext());
switch (b.getId()) {
case R.id.AddRecord:
d.AddGame(1, 2, 3, 4);
break;
case R.id.SelectRecords:
ArrayList<GameResult> results = d.SelectGames();
t = Toast.makeText(getBaseContext(),
"# records: " + results.size(), 1000);
t.show();
break;
case R.id.UpdateRecords:
int affected = d.UpdateGames();
t = Toast.makeText(getBaseContext(), "records affected: "
+ affected, 1000);
t.show();
break;
case R.id.DeleteRecords:
int deleted = d.DeleteGames();
t = Toast.makeText(getBaseContext(), "records deleted: "
+ deleted, 1000);
t.show();
break;
}
}
And that’s it – we’ve created a database and implemented very bare bones CRUD against it. But what about that hokey primary key?
Updating the Database Version
You’ll notice that our initial database version had PlayerId as the primary key. What this tells us is that we can only ever have one row per player in our database, which obviously goes against our goal of storing a row for every game every player ever finishes. We are going to use the onUpgrade method that we left unimplemented at the beginning to auto-upgrade our database.
For this we need to go back to the DbHelper class that we started with, which we review again below.
public class DbHelper extends SQLiteOpenHelper {
public DbHelper(Context context) {
super(context, "games.db", null, 1);
}
@Override
public void onCreate(SQLiteDatabase db) {
String sql = "create table Games " + "(PlayerId integer primary key, "
+ "Result integer, " + "PlayTime integer, "
+ "Difficulty integer); ";
db.execSQL(sql);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// do nothing for now
}
}
Upon reviewing the onUpgrade method, we see that we have two parameters specifying the old version of the database and the new version of the database. Our flawed database version started as version 1, as we can see in our constructor. We’ll decree our new database version to be version 2. To update our games database from version 1 to version 2, we will drop the existing table and create a new one that adheres to the below diagram.
Our onUpgrade method will look like below.
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
if (oldVersion == 1 && newVersion == 2) {
String sql = "drop table Games;";
db.execSQL(sql);
sql = "create table Games " + "(Id integer primary key, "
+ "PlayerId integer, " + "Result integer, "
+ "PlayTime integer, " + "Difficulty integer); ";
db.execSQL(sql);
}
}
And we have to pass the new version to our base class constructor.
public DbHelper(Context context) {
super(context, "games.db", null, 2);
}
Now whenever the new version of our app that uses our version 2 database runs on a device with a version 1 database, the onUpgrade method is called to update the database schema. Our onUpgrade code is destructive in that it drops the table and recreates it; you can implement this in a safer fashion to preserve your users’ data.
Next Steps
Our bare bones CRUD app has about 0 robustness built in, but it does demonstrate the absolute basics of Android database access. Improving on said robustness is beyond the scope of this article, and is something we might be seeing in the next few weeks.
[...] Android Development – Database ManagementGood tutorial on using SQLite on Android [...]
Very nice. I have good experience with actually using a bunch of instrumentation tests as a test harness for the data access instead of hauling up an actual ui for it. That way its all automated an any refactoring will break the tests..
Cool! I’m looking to explore the testing side of things in Android soon. I’m a firm believer in as close to 100% test coverage as you can come.
Nice tutorial and explanation, thanks for putting the time in to describe it.
Thanks! This tutorial helped a lot!
Thank you so much!
Bookmarked!
nice
Nice and simple – that helps separate out what Android SDK provides and what the developer provides.
Suggestion: could you show the schema after you create and before you insert? I get a table ‘android_metadata’ that I did not create and I am trying to determine if I am getting from my team code sharing or from Android SDK.
Also, another tutorial that I think others would find useful would be show how to use an SQL Browser (e.g., in Firefox) to browse the DB after it grows. One has to copy the schema out to the server to view.
Great ideas, will definitely explore them an see what I come up with.
A great tutorial – thanks!
Hi. I have a question. Ive noticed you are using VS 2008 as development tool. Is that possible with android?. I mean, how can u create an android project using VS?. Im new in Android and i have figured out you can only use either Eclipse or Netbeans.
No, you can’t create an Android project using Visual Studio; I use Eclipse for all my samples.
I use Visual Studio at work all the time though.
Hello, I have tried implementing your example exactly as it’s written. But for some reason, the application will not run. Are these just snippets of what you did? May I have the source code please.
Thanks,
Charles Harris
Hello, I was just checking the status of my request above. Thanks and have a good night.
Charles Harris
Great tutorial, thank you very much
Nice 1 Thanx a lot….!! Keep Updating new fashions..!!
Thanks mate. Been trying to make this work for ages now. Your method worked like a charm! Would it be possible for someone to explain how to execute a query and display it?
thanks again!
HI,
How to show all the data present in table…
Please help..
Thanks,
Aditya
I am having issues with the onUpgrade method. I created a database for my app and then I wanted to make changes to it so I altered the database version passed to the create method but the onUpgrade is never called when I start the app. Is there something special you have to do to get this to work in the eclipse emulator?
Thanks.
Hey there!
How do i add a database to an already done project? What if i want to add it on a particualr screen?
thanks you!
Thanks for this very nice and detailed explanation of SQL lite. Appreciate your patience on the same. Do you know any good GUI where we could add the data in SQLLite. I don’t mind paying money for the same.
Thanks for such a good tutorial. I helped me a lot.
Thanks for such a good tutorial. It helped me a lot
Writing my first Android app. Heck, my first app really. Your post helped me out so much today. I read through several other tuts on this topic, but somehow yours was easiest to get my head around. I was easily able to adapt your classes to the specifics of my app. Thanks so much for the time you put into this. Your post is at least a couple of years old now, but it’s still helping out people! Yay!
Thanks for the good word! Looks like I’ll finally be getting back into Android development here on the next couple of weeks so I will be able to provide some fresh content. Keep hacking away!
I just wanted to say thanks for putting together this tutorial. I looked around for while looking for guides to implementing databases in the android development environment and not only was this the only helpful one, it’s one of the best tutorials on anything related to programming I’ve found.