Android Tabager #4 Databases

Data bases are everywhere, and for a good reason, there soooooo useful. Today were going to add a database to our app, were going to insert a record of a made up rom to represent an update, well then compare this to the rom build of the device, and display whether its up to date.

When the final one is done well be comparing it to an online database, but this will be a good exercise to see how its done.

Step 1: Create the DBAdapter activity

To start with create a new activity called DBAdapter. The quickest way is to right click on the package container inside the res folder (it will be just above the main activity we've been working with) from there go to new then class Once its open paste in the following

Step 2: Break down of DBAdapter

Now there's a lot there so let's dive in. At the top we have the imports, these are the same as before, and allow us to use a lot code from various packages. Below these we have

Here were creating some variables and populating them with some very important information, such as the name of the database, the columns of table etc. This is done to keep things nice and tidy, it's also nice to have a quick reference at the top of the class of what the databases are made of. I don't always use them though as you can see, I prefer to generally just right sql statements.

The above code sets up the tools needed to manipulate the database etc.

Now we get to the good stuff... Firstly we need to create our database which is done by

So when the adapter is first created its going to run db.execSQL(DATABASE_CREATE), db.execSQL() lets us pass a SQL statement to the database. Let's take a quick detour and talk about this SQL thing I keep mentioning. SQL stands for structured query language, and is a way to talk to and control databases. I will try and do a one of sql cheat sheet at some point, but simply put a sql statement generally does 3 things:

what are you doing -> to what are you doing it to -> a condition (this is optional)

So let's say that we had a database, in this database we have a table called phone numbers. In this table we have three fields, Firstname, Surname and Number. Lets say I want to see my good friends Adam Smith number, I would do the following: SELECT Number FROM numbers WHERE Firstname = "Adam" AND Surname = "Smith". We'll thats a quick concept of sql and should help you to make more sense of the code were using if you have no prior experience.

So back to db.execSQL(DATABASE_CREATE), were running the code found in the DATABASE_CREATE variable we looked at earlier which was "create table rom (_id integer primary key autoincrement, rom text not null);". Create table rom, does exactly what it says on the tin and creates a table called rom, inside the () is the fields that we want in our table which are separated by a comma. Now the first one is called _id which is an integer and also our primary key. Nearly all database tables must have a key field, this is the field that will be unique for every record and is normally done using numbers like we have. The autoincrement is saying that it will automatically give a row of data the next available number, which makes life easy for us. The next field is called rom and will be our rom version, its going to be text field and were telling it that it cannot be empty, as there wouldn't be much point in a blank record.

Next we have

This checks whether the DATABASE_VERSION has changed, and if so performs whatever action you like. Currently were saying that if the database version has changed delete the database ( db.execSQL("DROP TABLE IF EXISTS rom"); ) and then recreate it, were also righting to the log that we did this. This is really good when your building your app and your testing and changing things as you can very quickly rebuild your database. Other times you will use this method to make changes to your database after your app is live. For example in one of my apps my users where asking for a feature, adding it required me to alter a table. But I couldn't tell the app to rebuild the table as it contained a lot of data for a lot of users. With this method I was able to say if they were on version x and they go onto y add etc to the table. This way it would only happen the once for those who needed it. So it's good to keep this in the back of your mind.

The rest of the adapter contains

These are pretty much self explanatory, the first method open() opens the database for us, the second close() closes it. The method insert passes a string called Rom. It creates a variable string called exec, which we populate with "INSERT INTO rom (rom) VALUES (?)". The SQL statement is going to insert into the rom field in the rom table the value ? which stands for a currently undefined variable. Next we call db.execSQL and pass in the exec string which will run the SQL statement, after the exec string there is new String[] {Rom}, what this is doing is passing the Rom string that we originally passed into the method to the ?. For example if used the method like this:

insert("my test");

The SQL command would end up being "INSERT INTO rom (rom) VALUES ("my test")".

Finally we have the getRoms method which is a cursor. Cursors are used to store the data returned from a database. We start of again with an exec string and populate it with "SELECT rom FROM rom", this simply gets all the rom field records from the rom table. This time though we run return(db.rawQuery(exec, null)); which will return the requested information from the SQL query.

Step 3: Changes to main.xml

Right well that's the hard part over, don't worry if it's not making too much sense at this point, it should click more when we use it in a second.

We also need to make some changes to the main.xml file and your main activity. Here's what your main.xml will look like:

So we've added the following:

These text views will be used to show the display rom which is the rom version that is normally shown in the android settings, it can sometimes differ to the rom were currently displaying, so it will be useful to display. The other will be used to tell us whether we need an update or not.

Step 4: Changes to the main activity

Right let's look at our main activity, the go through the new code and what's it doing:

So the first new addition is also the most important. We need to access the DBAdapter we just made to use the methods it contains to make changes to and use the database. To do this we use DBAdapter db = new DBAdapter(this); which creates an instance of the adapter and calls it db.

Directly below this we have String rom; this is the variable well be using later to hold the text to inform us of whether we need an update or not.

A little below this I've added another variable with the other variable decelerations String display;, which will be used to place pre-mentioned display info.

In the Variables of os details section display = android.os.Build.DISPLAY; has been added to the bottom.

Below this in the Displaying of os variables section weve added: TextView osDisplay = (TextView) findViewById(; osRelease.setText(display);

We saw how this all works in the TabagerSchool:#2 Displaying info class.

Now were up to some new stuff, and were going to put the DBAdapter we have to good use.

So as we can see we are doing three different things... Firstly we have; which is calling the open() method from the DBAdapter class we made earlier. So now the database is open we can do some thing with it, in this case db.insert("MyTestRom");. So were running the insert method and giving it "MyTestRom" to put into the table. Finally we run db.close(); to close the database.

So we now have our imaginary update in a database, we can now check the database to see whether we need an update or not. This code is what is used to accomplish it:

So once again we open the database, next we create a Cursor called c and place the results of the getRoms() method, c.moveToFirst(); will move to the first record returned and is good practice. Next we get the first string in the cursor (As we only have one in there this is fairly simple, If we had more in the table we would need to loop through them, but that's for another time) and place it the rom variable we created earlier rom = c.getString(0);. Finally we close the cursor with c.close(); followed by the database.

So we've pulled out the fake latest rom version for our device from the database and we can now check it with our current rom version. Before we make the check the Text View for the update is set up, TextView Update = (TextView) findViewById(;

Here's the finale that compares the rom and displays the result:

The if statement is being used here, it's very simple to use, and allows us to say if something is something then do this, if not do this. For example:

.equals() is used to compare two strings to see if there the same, so is the contents of rom the same as the contents of display 'rom.equals(display)' (display being display = android.os.Build.DISPLAY; which should be the name of the rom as you would find in your devices settings). If it does then: Update.setText("Up to date"); Update.setTextColor(Color.GREEN);

Which will set the Update Text View to "Up to date" and make the text green. (We saw in the last post how to change the color of the text in the xml, .setTextColor will let you change colour from within your activity )

If it doesn't then do:

Which will display the message "Update available latest version " and the rom version in a red text.

Step 5: Run the App

Lets give it a run shall we, you should get the above message saying that you need the update.

After this try getting your current rom, either from your devices settings or from the app you've made. edit db.insert("MyTestRom"); to insert your rom version. Then open your DBAdapter file and change the DATABASE_VERSION variable to 2. Save the changes and re run the app. Changing the database will run the onUpgrade() method, which if you remember deleted the table and recreated it, giving us a nice new empty table. This time your current Rom will be inserted and the update text will read "up to date".

After this comment out (put // before the line of code) the insert code, as there is no point in inserting a record every time we run the app.

This was indeed a long a tutorial, and covered a lot of ground, I tried to keep as simple as possible. It proves the concept of checking for the rom version and has given a nice overview of how to use databases.