Today we’re going to wrap up our sub-series on persistence as we continue on with the 31 Days of Android. We’ve already covered Shared Preferences and writing and reading files. This leaves today to talk about SQLite. SQLite is a small format embedded relational database system which is fancy talk for saying it’s a tiny database that you can use in your applications. For any database needs, SQLite is the recommended approach you should take. Today we’ll look at how to create a database for your app, how to do the standard CRUD (Create, Read, Update, and Delete) with your db, and lastly, how to handle changes to your database. You can grab the sample code we’ll start with today here.
Creating a Database
In order to create a database you need to override the SQLiteOpenHelper class and override the onCreate method to handle the actual creation. Let’s start by adding a new class to your project. Right click on src/com.daytwentyfour and go to New -> Class. Name your class “DatabaseOpenHelper” and enter android.database.sqlite.SQLiteOpenHelper as the superclass. When you create the class it will already have stubs for onCreate and onUpgrade for you to implement. You will still need to add a constructor for your class though:
private final static String databaseName = "DayTwentyFourDb";
private final static int databaseVersion = 1;
public DatabaseOpenHelper(Context context) {
super(context, databaseName, null, databaseVersion);
}
Here in your constructor, you’re passing in a static database name and a static database version. The variables need to be static to be accessible in the constructor. The database version is important for upgrading purposes which we’ll get into later. Next, in the onCreate method, you will do the actual database creation:
@Override
public void onCreate(SQLiteDatabase sqLiteDB) {
String createSql = "CREATE TABLE DayTwentyFour " +
"(_id integer primary key autoincrement, " +
"name text not null);";
sqLiteDB.execSQL(createSql);
}
Here you’re creating a SQL command that will create a new database table named “DayTwentyFour” with two columns. _id which is an integer, a primary key, and will auto increment. Name is a text field and is not allowed to be null. Once you’ve created that command in String form, you pass it into the SQLiteDatabase passed into the onCreate method. Lastly we’ll only take a brief look at the onUpgrade method:
@Override
public void onUpgrade(SQLiteDatabase sqLiteDB, int oldVersion, int newVersion) {
sqLiteDB.execSQL("DROP TABLE IF EXISTS DayTwentyFour");
onCreate(sqLiteDB);
}
Here, you’re dropping the “DayTwentyFour” table if it exists and then recreating it. We will talk more about upgrading later but for now, this will suffice. Next you need to create an Adapter. This isn’t a defined concept but seems to be an accepted pattern for database access in Android. Right click on src/com.daytwentyfour and go to New -> Class. Name your new class “DatabaseAdapter” and leave it with the java.lang.Object as it’s superclass. The first step is to add a few member variables and a constructor:
private Context context;
private SQLiteDatabase database;
private DatabaseOpenHelper dbHelper;
public DatabaseAdapter(Context context) {
this.context = context;
}
Context should be familiar to you by now. The SQLiteDatabase variable is the Android SDK’s way of exposing methods to perform CRUD on your database. Lastly you have a reference to the DatabaseOpenHelper you just created. Next you need methods to open and close your database helper:
public DatabaseAdapter open() throws SQLException {
dbHelper = new DatabaseOpenHelper(context);
database = dbHelper.getWritableDatabase();
return this;
}
public void close() {
dbHelper.close();
}
In the open method you’re creating a new instance of the DatabaseOpenHelper and then using it to get a writable database. In the close, you’re simply calling close on the database helper. Now you’ve done enough work to actually get the database created. Open the src/com.daytwentyfour/DayTwentyFourActivity.java class and go to the bottom of the onCreate method. You haven’t done anything that will interact with the database and save any data, but just calling open will create the database. Add the following code to the end of the onCreate method:
//Create our database by opening it and closing it
DatabaseAdapter databaseAdapter = new DatabaseAdapter(getApplicationContext());
databaseAdapter.open();
databaseAdapter.close();
Checking that your Database was Created
Now after you run your application, you have two ways to check to make sure it worked and created the database. The first option is to load the DDMS Perspective and switch to File Explorer. If you select your emulator in the Devices view in the top left, you should then be able to navigate down to /data/data/com.daytwentyfour/databases and you should see a DayTwentyFourDb file:
The second option is slightly more complicated but important for something we’re going to do later. Open up a command line window and navigate to your android SDK directory. For example, mine is located at c:\Program files (x86)\Android\android-sdk. From there, you need to go into the platform-tools folder so you can access adb.exe. ADB can be used to connect to emulators and devices from the command line. If you’re not sure what the device or emulator you want to connect to is named, you can run adb devices. This should list out all currently connected devices and running emulators. My emulator is showing up as emulator-5554. Next you need to run adb and connect to the emulator with a shell like so:
$ adb -s emulator-5554 shell
After that, you’ll be running the command line ON the device. To check the same directory you did above in File Explorer, execute this command:
# ls /data/data/com.daytwentyfour/databases
Doing so should yield a single file, DayTwentyFourDb. The reason I showed you the arguably more complicated command line method is that later, after you’ve inserted data, you can open and check the database via this command line where as with File Explorer, you can’t.
Dealing with CRUD
Now that you’ve checked and made sure your database is being created successfully, it’s time to look at some CRUD (Create, Read, Update, Delete). Open the DatabaseAdapter class you created and add the following:
public long createRecord(String text) {
ContentValues contentValue = new ContentValues();
contentValue.put("name", text);
return database.insert("DayTwentyFour", null, contentValue);
}
ContentValues implements Parcelable which you may have dealt with for sending data between activities. Here you’re just putting a text value into the ContentValues that matches the name of the only column you put in the database, outside the auto incremented ID column. After that, you use the SQLiteDatabase class to do the insert. Go back to the DayTwentyFourActivity class and change your button1 onClickListener to do the following:
button1.setOnClickListener(new OnClickListener() {
public void onClick(View v) {
lblTextViewOne.setText(editText1.getText());
DatabaseAdapter databaseAdapter = new DatabaseAdapter(getApplicationContext());
databaseAdapter.open();
databaseAdapter.createRecord(editText1.getText().toString());
databaseAdapter.close();
}
});
The only thing different here from what you were doing before to create the database, is the call to the adapter to create a record. Now go ahead and run your app. When you tap on the button, you won’t see anything, but the data should insert behind the scenes. To check and see that data was inserted, you need to return to the command line. Provided you are still “ADBed” into your emulator, you can enter the following to get into the SQLite database:
sqlite3 /data/data/com.daytwentyfour/databases/DayTwentyFourDb
Now there are many different commands you can use here (run .help to have them listed). Running .tables will give you a list of tables that are in your database. .dump <tablename> will output the contents of the table. Go ahead and run:
.dump DayTwentyFour
You should get the following out (you might have fewer statements at the bottom depending on how many times you tapped the button):
sqlite> .tables
.tables
DayTwentyFour android_metadata
sqlite> .dump DayTwentyFour
.dump DayTwentyFour
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE DayTwentyFour (_id integer primary key autoincrement, name text not
null);
INSERT INTO "DayTwentyFour" VALUES(1,'This was our DayTwentyFour test.');
INSERT INTO "DayTwentyFour" VALUES(2,'This was our DatyFour test.');
INSERT INTO "DayTwentyFour" VALUES(3,'This was our DatyFour test.');
INSERT INTO "DayTwentyFour" VALUES(4,'This was our DatyFour test.');
INSERT INTO "DayTwentyFour" VALUES(5,'This was our DatyFour test.');
INSERT INTO "DayTwentyFour" VALUES(6,'This was our DatyFour test.');
INSERT INTO "DayTwentyFour" VALUES(7,'This was our DatyFour test.');
INSERT INTO "DayTwentyFour" VALUES(8,'This was our DatyFour test.');
INSERT INTO "DayTwentyFour" VALUES(9,'This was our DatyFour test.');
INSERT INTO "DayTwentyFour" VALUES(10,'This was our DatyFour test.');
Now you know that you’re inserting successfully. Let’s take a look at how to pull data out.
public ArrayList<String> fetchAllRecords() {
Cursor cursor = database.query("DayTwentyFour", new String[] { "_id", "name"},
null, null, null, null, null);
ArrayList<String> records = new ArrayList<String>();
cursor.moveToFirst();
for (int i = 0; i < cursor.getCount(); i++) {
records.add(cursor.getString(1));
cursor.moveToNext();
}
cursor.close();
return records;
}
Here you’re doing a database query and returning it’s results to a Cursor. The query takes in the table name and the column names. There are additional parameters that you can pass in to specify order, selection arguments, as well as a record number limit. Here you’re fetching all of the record and putting them into an array which is returned. Go ahead and call this method from the second button onClickListener in DayTwentyFourActivity and set the EditText and TextView to the first row returned. You should end up with something like this:
button2.setOnClickListener(new OnClickListener() {
public void onClick(View v) {
DatabaseAdapter databaseAdapter = new DatabaseAdapter(getApplicationContext());
databaseAdapter.open();
ArrayList<String> records = databaseAdapter.fetchAllRecords();
if (records.size() > 0) {
editText1.setText(records.get(0));
lblTextViewOne.setText(records.get(0));
}
databaseAdapter.close();
}
});
You’re checking to make sure your database at least had a record before you use it, but that should be it. Now if you run your app and tap the second button, the text of your two Views should be set.
Very quickly we’ll go over the other CRUD methods you might use.
Fetching a Specific Record
Fetching a specific record is very similar to the method above where you’re fetching all records. The difference is that one of the nulls passed into the query method is replaced with selection criteria:
public String fetchRecord(long rowId) throws SQLException {
Cursor mCursor = database.query(true, "DayTwentyFour", new String[] { "_id",
"name" }, "_id ="+ rowId, null, null, null, null, null);
if (mCursor != null) {
mCursor.moveToFirst();
return (mCursor.getString(1));
}
return null;
}
Updating a Record
Updating a record is very similar to the insertion but, again, you’re specifying criteria:
public boolean updateRecord(long rowId, String text) {
ContentValues contentValue = new ContentValues();
contentValue.put("name", text);
return database.update("DayTwentyFour", contentValue, "_id =" + rowId, null) > 0;
}
Deleting Records
Deleting records is very simple as well, just more criteria:
public boolean deleteRecord(long rowId) {
return database.delete("DayTwentyFour", "_id =" + rowId, null) > 0;
}
This should get you pretty far with using databases. An important reminder is that if the user goes into Settings -> Manage Applications and pulls up your application and hits the Clear Data button, you’re database WILL be deleted.
What about Upgrades
Earlier on I covered the onUpgrade method of your DatabaseOpenHelper class very quickly. This method is actually very important if you’re working on an application that you anticipate making database changes on. The way that Android handles database changes is that it expects you to do a lot of the work. As you’ll recall, you created a databaseVersion variable in your DatabaseOpenHelper. If you increase this number and release an updated version of your app, the onUpgrade method will be called. Where previously you were just dropping the table and calling on create, here you’d need to handle retrieving all of the data for the table, recreating the table, and then reinserting the data. Something a lot like this:
@Override
public void onUpgrade(SQLiteDatabase sqLiteDB, int oldVersion, int newVersion) {
//Pull all of your data and keep it in a local variable
sqLiteDB.execSQL("DROP TABLE IF EXISTS DayTwentyFour");
onCreate(sqLiteDB);
//Insert that data you pulled out earlier into the new table
}
You can view an example of this implemented by a real app here.
Lastly, you can download the final source code from today here.
