Android Development 101 – Part 3:Introduction to Databases

In this tutorial we will be pivoting from our last tutorial on Graphical Elements to start focusing on databases in Android development. The android platform uses SQLite databases in its applications and is one of five data storage options in android development. We will only be focusing on SQLite development in android because it is key to the construction of a workable/functional program. After this tutorial you should be able to implement a SQLite database that you are then able to insert and select items from tables in the database.

For this project we will be creating a Random Quote generator that has you enter quotes or sayings in a textbox and press a button to insert them into the database. We will issue a confirmation toast that allows us to see if the data was entered into the database successfully and the textbox will be blank. If a second button is pressed, the database will be accessed and told to select a random quote from the database to show in a toast on the screen.

To start off we will make a new project called RandomQuotes. In part one of the series we stepped through making a new project so we wont walk through all of the steps again but instead I will just give you the information you need. The information to get this project up and running bare bones is as follows:

Project Name: RandomQuotes

Build Target: Android 1.5

Application Name: RandomQuotes

Package Name: com.gregjacobs.randomquotes

Create Activity: QuotesMain

Min SDK Version: 3

After inserting these values and you have pressed Finish we will start by making a class file in our com.gregjacobs.randomquotes package. To do this we will right click on the package and navigate to New then to Class. When the new window pops up the only data we will enter is the Name section filling it with DBAdapter. After this is done we press Finish and are presented with a bare bones class file that we will quickly start to modify. This tutorial will be like the last in the sense that code will be posted and I will explain the important parts and what functions are doing. The only difference from the previous tutorial code will be that I include text files as well as documenting the code here so you are able to download and compare. We will start off with the DBAdapter.java file:

We will start off by importing all of the tools required to get this SQLite Database up and running. All of these might be straightforward for database programmers but we will discuss them anyways. ContentValues allow us the ability to store a set of values for insert statements, Context as explained in the last post allows us access to the application environment. Cursor is probably the most vital import we will need next to the SQLite imports. Cursor allows us access to the data returned to the cursor from a database query. SQLException allows us to throw SQL exceptions if there is ever an error, these messages provide more insight as to what the problem may be. SQLiteDatabase gives us the ability to manage a SQLite database using methods. SQLiteOpenHelper is basically a helper class that allows for creation and version management of a database. Log will basically log output in case there is an error.

Here we define all of our variables to be used in the database from the database name right down to the database create statement. We are using final variables because they will never change values and making a variable for table names and the like will later on make our lives easier than hard-coding all of our values and commiting too much (remember the re-usability).

Above we define a constructor to grab the context of the application and extend that to our DatabaseHelper just under the constructor. The DatabaseHelper class extends our SQLiteOpenHelper which will add greater functionality to management of our SQLite database. The key function that we will see used later on will be onCreate which will allow us to execute a SQL statement to create our database.

Above we have two key functions that allow us to open and close the database that can be referenced when calling them in our main .java file.

//---insert a title into the database---
public long insertQuote(String Quote)
{
ContentValues initialValues = new ContentValues();
initialValues.put(KEY_QUOTE, Quote);
return db.insert(DATABASE_TABLE, null, initialValues);
}

The function above will be processing our quotes when we call them in the main .java file. It will also be getting them ready for entry into the database by putting the string Quote into a ContentValues called initialValues which is then inserted into the database table.

This function will be querying the database table for the number of quotes entered so it can assist the random number generator in how high a number to choose so that we don’t throw an exception. We are using a rawQuery for the most part because I am personally not a huge fan of the way Android handles their queries ( having you enter in different parts of the statement in segments and separate them with commas) but I am impressed that they allow you to have full functionality with a native SQL query. The if statement will move the cursor to the first result (if there are many results) and grab the first integer it sees there. If the if statement is not true it will grab the result from the starting position anyways.

This function will be called by the main .java program to return a random result based on the number of entries into our database. We use the function getAllEntries to get the number of quotes and we then tell our random variable that it can go no higher than id. In our select statement we then tell it to look for quote WHERE _id = rand which is our random number.

After this class file is completed, we have a fully reusable database adapter that is ready to start inserting quotes into the database. We now need to focus on both of the XML files which will be a quick trip down memory lane so code and pictures will be posted and we shouldn’t have to review as everything is basically from the last post. Here is the main.xml:

Both are pretty straight forward and the only difference from these files and the previous posts is the additional string node in strings.xml and the extra button in main.xml. Now we have the layout in place with everything where we want it to be it is now our task to code the QuotesMain.java file. This file will register our two buttons and attach them to one event handler using a switch statement. Here is the code for our QuotesMain.java file:

Here we are importing all of the required items to be able to pull this project together. All of these should be familiar to you from Graphical Elements and if they aren’t it is a good post to start on and work your way here.

We now have to buttons being referenced by id and they are getButton (which gets the information from the text box and inserts it into the database) and setButton (which retrieves a random quote from the database depending on the number of items in the database). These both have the same event handler and decisions on what code to run are made below.

In the above case statement we can see that we grab the text from the textbox and insert the data into the database using db.insertQuote from the DBAdapter java class. After a successful insertion we will display a toast that allows us to see what quote was entered in successfully and what the number of quotes in the database are.

This case uses a string variable to reference the random entry we are pulling out of the database using db.getRandomEntry. We then display that data in a toast to show that the information was actually grabbed. All of this code when pulled together and displayed on an android screen should look like this:

Entering Text:

Displaying Random Entries:

With an introduction to databases for android covered you can start writing applications that require data storage such as the final product mentioned in the first post. There are a plethora of other features to cover in SQLite databasing for android. More of those will be covered in the next tutorial. Things such as updating your database, deleting entries and getting to know your way around the DDMS (Dalvik Debug Monitor Service) are all an essential part of android programming. If you can’t wait till the next article to check these articles on DDMS and Updating and Deleting. As always if anyone has problems, questions or issues don’t hesitate to ask and I will try my hardest to get back to you before the next post! Until the next time, Happy Hacking!

@Jeff – The emulator on my computer runs completely fine. Plus, it gives you a chance to try out Google’s Android OS! If you follow the tutorials and instead of making the emulator 1.5 change it to 2.1 and you should be able to see most of the new features. As far as devices I am in Canada and was using the HTC Magic but a HTC Dream might do if you just want to develop apps.

damn i need to start paying attention to these. this is about where i started getting lost in java class.
and def. try an emulator. you can reset the emulator if you’ve reached the ‘point of no return’ but doing the same with a physical device can be much more frustrating. i haven’t tried any android emulators, but once i start actually reading these guides and trying them out, i’d rather test them on an emulator til they’re perfect, then real world test them in the phone.

You can’t make the device misbehave very easily. If your code is bad Android will kill the app.

@fotoflojoe

Speaking from experience here; I haven’t found uploading an app to my dev device vs uploading an app to an AVD all that different. Neither is instant however neither take all that long. It’s not like the AVD has any magic features to make it faster i.e. the ability to replace parts of the application on the fly.
The real advantage of the emulator IMHO is that you can test different device configs without actually having those devices.

@All

You can get < £100 android phones now. These don't have great screens or any of the fancy coprocessor stuff but they are very good just to test that your app works on the real hardware. The emulator is very poor when it comes to rendering animation etc and is in general "slower" so you're less likely to hit race conditions etc.

Fck java and android just get an N900. This phone and maemo was made for the real linux fans and you can write super fast apps in C/C++ just like you used to. The crosscompiler env for it is also no time to setup since theres a full vmware image on maemos site.

I doubt that ANY android based phone would beat the N900 in multitasking and oh yeah why would you run partially closed source android on your n900… Not to mention lame macos based iphones, nokia will pwn them all.

Which bits are closed? The market place etc require agreements with google and source for new releases is withheld until it’s ready but you can download and build yourself a complete Android environment.. and you can get the source for all of it it. Oh, and you don’t have to link against GPL libraries…

It appears that the _id field in the database starts with 1 and not zero, so the code in DBAdapter.getRandomEntry() as is will never return the last element in the table. It looks like the developer kind of saw that with this code:

if (rand == 0) ++rand;

Take that a step further and remove the if condition – always add 1 to rand before doing the query and the code will return each of the rows in the database if you press the GenerateRandomQuote button enough times.

Another issue in the same method – the getAllEntries() method is called twice for no apparent reason – once to set the value of id, and once to pass into the random.nextInt() method. Modify the random.nextInt() call to:

int rand = random.nextInt(id);

The code will work as written, but it will perform better with this change (two database hits per call instead of three). Another option would be to just get rid of the id variable completely.

Another observation – the getAllEntries() method doesn’t really get all entries, it gets a count of the entries. It should be named appropriately for what it’s doing – getEntriesCount() for example.

My current understanding leads me to believe there is an error with your program. As I understand it Random.nextInt(int param) is inclusive in respect to the 0, but exclusive in respect to the integer parameter. And because you want to generate a number between 1 and param, you would want have rand++ regardless of whether or not the value is equal to 0, otherwise the quote with the highest _id will never be displayed.

I am in the process of rewriting the program according to your specifications as we speak, so I don’t know if maybe I’m misunderstanding something or not, please let me know.

I appear to be missing something. You set DBAdapter db = new DBAdapter(this); yet your only constructor in DBAdapter is DBAdapter(Context). As a result I had to move the instantiation into the onCreate function where I could provide it with the ApplicationContext.

Otherwise, great tutorials, some of the best I’ve seen. Keep up the good work, I’ll be looking for future ones.

I hate to be a nit pick, but there is a slit miner problem with the way this code works. It will never show the last given quote sense the random is exclusive but this can be fixed by just removing the following the if statement so it always increments the rand integer moving the rand from [0,1-id] to [1,id] wish is the desired range.

Hi Greg,
I am getting an error. It says no such table tblRandomQuotes. It is not able to create a table. What can be the error? I double checked and compared the code with what you have provided and it seems to be the same. I have uploaded the error screenshot here: http://jump.fm/TUHKH

@Sunil Kamat
I can’t be shore but i think your query is wrong it should be (“SELECT Quote FROM tblRandomQuotes WHERE _id = ” + rand) but what it looks to be on yours is (“SELECT Quote FROM tblRandomQuotes = ” + rand). you may also want to check that your rand is withing range. The _id shouldn’t be 0. This is my best gees from what you showed. I hope this will be of help.

But the problem is while inserting the data. It says “no such table: tblRandomQuotes” (screenshot link abv).

It goes to this line :
Cursor cursor = db.rawQuery(“SELECT COUNT(Quote) FROM tblRandomQuotes”, null);
and then throws the exception saying no such table. But it did not give any error while at the table creation.

I feel that it is not creating the table itself. The SQL for table creation is same as what is given in the article.

Is it working at your end? Have you made any changes apart from the code given in the article?

OK. I found the problem. Initially while creating the table, I had made a typo. I changed it later after one run. But it is not taking table with the new table name and is giving the previously mentioned errors.

Old Name: tblRandomuotes
New Name: tblRandomQuotes

Should I run a delete table query to delete the table with typo? Is there any other way to clear the temporarily created tables?

Java is fine. The language has always been solid and the standard library is broad and (usually) well thought-out. And you get the add bonus that it’s a mature environment without all the Ruby-esque fangirls tweeting non-sense..

Anyhow, you don’t really need a book. Look at android examples and you will start to pick it up. You don’t really need to learn Java through and through, any java book you could would discuss the standard libraries etc which you will only be using a fraction of. O’Reilly’s Java Pocket Guide is good to have around for those “I’ve totally forgotten how this works” moments when you need to refresh yourself on some detail about syntax etc.

Getting your head around the fact that you don’t really have a main loop and you have to drive everything with “events” is the biggest challenge IMHO.

OK. Here is what I did to solve my problem but this is not the correct solution. Just a work around to execute the program.

Here is what happened:
I made a typo in the table name in the create table query and did the first run which created the database and the table (with typo) as required but the “select count(Quote)…” query had the correct table name as given in the tutorial. So it was giving me as error saying table not found. I corrected the table name in the create query and ran the program again, but the error was still the same at the “select count(Quote)…” query.

Solution:
To make it work, I just changed the DATABASE_NAME to “RandomQ” instead of “Random” and executed the application again with the corrected table name in the create query. Now it referred to the new Database and everything worked fine.

The only thing I am not sure about is, how to delete the table created in the “Random” database. Because after one run, it always referenced the old table name, even if the name was changed later in the create query.

Basically, it should have created another table in the same DB and the queries should have referenced that table but it always said table not found.

@Sunil Kamat
in the onUpgrade() method you have the command to remove the database have you tried running that in the constructor for one build or just the drop command. Another way that i think will work is by changing the DATABASE_VERSION to 2 and it will be run the command ones on the first start. Ether way you chose mack sore that the misspell table and the correct spelled table is there so everything will be removed.

Oh and the number is an important part of my name it is an binary ASCII value and san is lick Mr in Japanese. Just a little play on things but dont worry about it.

@Sakey @11011san
Thanks for your response guys!
Well, the DATABASE_VERSION change worked great. Resetting the virtual phones will take out my other programs too which I have. So I guess version change is the best and the most neat option, at least in my scenario.

Ok, first off THANKS for having tutorials like this available. They’re extremely valuable to scipt kitties like myself. With that said, I’ve gone over the code a million times and while it seems I’ve followed your example to the t, and while Eclipse doesn’t show a single red x or squiggle, when loaded on my emulator or droid i get the same result. Everything looks like it should, I type in a quote, press the button to store and I get a message saying the app’s stopped unexpectedly try again later, and I’m forced to close. Any ideas why this might be??

@Noob13 – Good Question! I will get back to you on that but for now what you could do is attach your device to your computer and if it is recognized bu DDMS you could push the database onto the current device. (Good example being the Part 4 of this tutorial and you would use the icon next to the one mentioned to push it onto a device)

@Toby – Yes, that is the correct way of programming the class to be completely reusable. This was done in a quick and dirty style to show the abilities of android and how easy it is to program in the language. I strongly encourage people who have suggestions to reprogram anything submitted here to make it better & faster. This tutorial is only scratching the surface of what Android can do. I will admit when programming that class I slipped back into the SQL mindset and should have used variables instead of table names etc. Sorry about the confusion and to get off my tangent, you are completely right :)

Anyone know how to rename a class and the java file after creating it? I stupidly named Adapter to Adpater when creating the class… I first thought I was doing something wrong.. LOL. Or there is no way but to delete the class and do everything from scratch again?

It wasn’t generating all quotes for me cause’ I don’t think the SELECT COUNT was counting all the quotes correctly and was always leaving out the last quote inserted. So the +1 fixed that for me and eliminated the need for the if statement. Awesome site! Keep your tutorials coming, for an android writer, you’re easy to understand. Peace.

I also get a crash when clicking the button to put the typed quote into the database. I can’t put a toast anywhere which will appear before the crash UNLESS i click the other button. This returns nullpointerexception (I assume because there are no quotes).

I would have thought that the error would have been within the case statement (if it works for one button and not the other) but i don’t think it is

playplay1978, it was crashing on mine too, because of typos I did in my package name, I double checked that AndroidManifest.xml, DBAdapter.java and QuotesMain.java all have the same package name. Because of that error I also had “R cannot be resolved”.

Thanks for these tutorials. I’m having a slight issue though. In my QuotesMain.java I get a bunch of “DBAdapter cannot be resolved to a type” errors. I’ve checked everything against the pictures (granted, I am very sleep ATM), but can’t seem to find out why this is being caused. If anyone has any help, it’d be much appreciated!