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:

package com.gregjacobs.randomquotes;

import java.util.Random;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

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.

public class DBAdapter
{
    int id = 0;
    public static final String KEY_ROWID = "_id";
    public static final String KEY_QUOTE = "Quote";
    private static final String TAG = "DBAdapter";

    private static final String DATABASE_NAME = "Random";
    private static final String DATABASE_TABLE = "tblRandomQuotes";
    private static final int DATABASE_VERSION = 1;

    private static final String DATABASE_CREATE =
        "create table tblRandomQuotes (_id integer primary key autoincrement, "
        + "Quote text not null );";

    private final Context context;

    private DatabaseHelper DBHelper;
    private SQLiteDatabase db;

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).

    public DBAdapter(Context ctx)
    {
        this.context = ctx;
        DBHelper = new DatabaseHelper(context);
    }

	private static class DatabaseHelper extends SQLiteOpenHelper
    {
        DatabaseHelper(Context context)
        {
            super(context, 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)
        {
            Log.w(TAG, "Upgrading database from version " + oldVersion
                  + " to "
                  + newVersion + ", which will destroy all old data");
            db.execSQL("DROP TABLE IF EXISTS tblRandomQuotes");
            onCreate(db);
        }
    }
    

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.

    //---opens the database---
    public DBAdapter open() throws SQLException
    {
        db = DBHelper.getWritableDatabase();
        return this;
    }

    //---closes the database---
    public void close()
    {
        DBHelper.close();
    }
    

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.

    public int getAllEntries()
    {
        Cursor cursor = db.rawQuery(
                    "SELECT COUNT(Quote) FROM tblRandomQuotes", null);
                if(cursor.moveToFirst()) {
                    return cursor.getInt(0);
                }
                return cursor.getInt(0);

    }
    

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.

    public String getRandomEntry()
    {

    	id = getAllEntries();
    	Random random = new Random();
    	int rand = random.nextInt(getAllEntries());
    	if(rand == 0)
    		++rand;
        Cursor cursor = db.rawQuery(
                    "SELECT Quote FROM tblRandomQuotes WHERE _id = " + rand, null);
                if(cursor.moveToFirst()) {
                    return cursor.getString(0);
                }
                return cursor.getString(0);

    }

}

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:

<?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"
    >
<TextView
    android:layout_width="fill_parent"
    android:layout_height="wrap_content"
    android:text="@string/Quote"
/>
<EditText
android:id="@+id/Quote"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
/>
<Button
android:id="@+id/go"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:text="@string/press"
/>
<Button
android:id="@+id/genRan"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:text="@string/genRan"
/>
</LinearLayout>

Here is the strings.xml file:

<?xml version="1.0" encoding="utf-8"?>
<resources>
    <string name="Quote">Please Enter A Quote:</string>
    <string name="app_name">Random Quotes</string>
    <string name="press">Press Me!</string>
    <string name="genRan">Generate Random Quote!</string>
</resources>

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:

package com.gregjacobs.randomquotes;

import android.app.Activity;
import android.content.Context;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;

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.

public class QuotesMain extends Activity {
	DBAdapter db = new DBAdapter(this);
	EditText Quote;
    /** Called when the activity is first created. */
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);
        // Capture our button from layout
        Button setButton = (Button)findViewById(R.id.go);
        Button getButton = (Button)findViewById(R.id.genRan);
        // Register the onClick listener with the implementation above
        setButton.setOnClickListener(mAddListener);
        getButton.setOnClickListener(mAddListener);
    }

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.

    // Create an anonymous implementation of OnClickListener
    private OnClickListener mAddListener = new OnClickListener()
    {
    	public void onClick(View v)
    	{
    		switch(v.getId())
    		{
    		case R.id.go:
				db.open();
				long id = 0;
				// do something when the button is clicked
				try
				{
					Quote = (EditText)findViewById(R.id.Quote);
					db.insertQuote(Quote.getText().toString());

					id = db.getAllEntries();

					Context context = getApplicationContext();
					CharSequence text = "The quote '" + Quote.getText() + "' was added successfully!\nQuotes Total = " + id;
					int duration = Toast.LENGTH_LONG;

					Toast toast = Toast.makeText(context, text, duration);
					toast.show();
					Quote.setText("");
				}
				catch (Exception ex)
				{
					Context context = getApplicationContext();
					CharSequence text = ex.toString() + "ID = " + id;
					int duration = Toast.LENGTH_LONG;

					Toast toast = Toast.makeText(context, text, duration);
					toast.show();
				}
				db.close();
				break;

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.

    		case R.id.genRan:
    			db.open();
    			//long id1 = 0;
    			// do something when the button is clicked
    			try
    			{
    				String quote = "";
    				quote = db.getRandomEntry();
    				Context context = getApplicationContext();
    				CharSequence text = quote;
    				int duration = Toast.LENGTH_LONG;

    				Toast toast = Toast.makeText(context, text, duration);
    				toast.show();
    			}
    			catch (Exception ex)
    			{
    				Context context = getApplicationContext();
    				CharSequence text = ex.toString();
    				int duration = Toast.LENGTH_LONG;

    				Toast toast = Toast.makeText(context, text, duration);
    				toast.show();
    			}
    			db.close();
    		}
		}
    };
}

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!

Text files of code for comparison:

DBAdapterstringsmainQuotesMain

Articles used for reference:

DevX – Creating and Using Databases in Android
Android Developers – Reference Guide

Continue on to Part 4: Advanced Database/GUI Code & DDMS

72 thoughts on “Android Development 101 – Part 3:Introduction To Databases

  1. Never mind, for some reason my DBAdapter.java was in “default package” and my QuotesMain.java was in “com.martinunderwood.randomquotes”. I copied over the file, and no errors, worked great! Thanks again for the tutorials!

  2. I’m new to databases, and I was wondering, where exactly is this information being stored? I thought to have a server, you needed to pay for the space?

    Again, I’m at new at this, so I apologize if this is a dumb question. Also, if you know of any good resources I could use to learn more, please let me know!

    Thanks!

  3. New to Android, Java development.
    Environment: Win7 professional 64-bit
    Eclipse (Helios), Android SDK, JDK jdk1.6.0_25.

    The first two tutorials (HelloWorld, and Graphical Elements) went like a breeze. On this third tutorial I’m getting my first errors, and so am attempting to learn debugging.:>)

    When running the Databases example in Debug (rt-click project, Debug As, Android Application…) After hitting F6 a few times, Debug hits this line of code
    setButton.setOnClickListener(mAddListener);

    the debugger opens a Class.class tab which says: “Source not found”: The source attachment does not contain the Source for the file Class.class. You can change the source attachment by clicking Change Attached Source below.

    Clicking brings up a dialog “Source Attachment configuration”. The existing string is: C:/Android-SDK/platforms/android-3. Is this what it means by “Source”?

    In the dialog, there are three buttons Workspace, External File, External Folder.
    What “Source” is it looking for? Note, there is an Android.jar file at that location.

    I’m not sure what this is asking me to do. I gather that there is some setting I need to (re-)set that tells Eclipse(?) where the Java JDK or Android SDK is?

    I checked “Ben’s” advice above and all these files AndroidManifest.xml, DBAdapter.java and QuotesMain.java have the same package name.

    Thanks in advance for any assistance.

  4. … and of course… as soon as I post the problems…

    One of the posts above (or in the next example) mentioned running a “Project >> Clean..” I did this and re-ran the RandonQuotes app and Bingo!. The app runs.

    I’m seeing the issues others have raised with generating a random quote:
    Changing this line:
    int rand = random.nextInt(getAllEntries());
    to this:
    int rand = random.nextInt(id);
    appears to have eliminate the JavaLangException.

    However, the Generate Random quote is not succeeding. It is either displaying a empty “toast” or the toast is displaying the last entry I created manually. Will try the post above to see if they resolve the problem for me as well.

    If there is an answer to what “Source” the app was looking for earlier, I’d appreciate hearing it. As I’m new to the Java, Android environment, every little bit helps.

    Thanks again

  5. .. sigh.. again. Reading IS fundamental.
    I’d read the description of what the Generate Random was doing, and evidently re-wrote it in my head.
    So teh Generate Random just gets a random entry, from those already entered, in the db and displays it. For some reason I thought it was generating some random text and displaying that.

    Any way… its running great now. I’ll also incorporate Toby’s observation for using the constant DATABASE_TABLE instead of typing in the tblRandomQuotes (nice one).

    Also, backed off the change noted on my post above:
    Changing this line:
    int rand = random.nextInt(getAllEntries());
    to this:
    int rand = random.nextInt(id);
    left it at:
    int rand = random.nextInt(getAllEntries());

    Great post. looking forward to the next – Advanced Database/GUI code and DDMS

  6. I have a quiz apps which I have 20 pictures to be used and from those pictures I want to use 5 pictures at a time for the quiz which would be randomly chosen frmo the 20 pictures (this makes a different quiz everytime).

    the pictures are tagged with category and score so when the 5 questions are done it will be evaluated per total score and scores per category. please help me code this. thanks. Eva

  7. Hi! Thnx for the tutorial. It’s great.

    I copied the DBAdapter.java file in my project, but when i use it, it displays the toast

    “The quote ‘” + Quote.getText() + “‘ was added successfully!\nQuotes Total = ” + id;

    but the id always remains 0;

    can u help me on solving this…

    and also when i try to use this code in another class

    DBAdapter db = new DBAdapter(this);
    db.open();
    String quote = “”;
    quote = db.getRandomEntry();
    aname.setText(quote);
    db.close();

    it doesn’t work (Stops the program)

    Can anybody help me please??

  8. Hi I been trying to follow this tutorial ,but every time I this app i get an error saying “the process android.media has stopped unexpectedly. Please try again.
    Why is this error coming ? And how to solve it

  9. Hi Greg! Thanks so much for the tutorials. I am three semesters into computer programming, and a few months into android programming and you have helped me so much to get my feet wet!

    Little edits I made to your code for fun/whateva:
    -Added an android:hint to the EditText box in the main.xml file.
    -Put the “press me!” and “generate random quote!” buttons in a horizontal nested layout so that they are next to, rather than on top of, each other
    -Changed any android:layout_width=”fill_parent” to android:layout_width=”match_parent” (fill_parent has been depreciated as of API 8!)

    Thanks so much and I look forward to trying more of your tutorials!

  10. i have got a strange issue, in my QuotesMain.java, when I do
    “Button setButton = (Button)findViewById(R.id.go);
    Button getButton = (Button)findViewById(R.id.genRan);”

    it gives me error on .go and .genRan saying cannot be resolved and where i refer these variables. When I press ctl+space, i get upto R.Id. Even if I have copy pasted whole code, it doesn’t works for me.

    Any help/.

    1. I’m getting the same error as vinoo… Eclipse wants me to add menu items into ~\res\menu\quotes_main.xml for R.id.go, R.id.genRan AND R.id.Quotes.

      Very confused. Any help would be greatly appreciated!

  11. Hi,
    I’m filling in the code in eclipse IDE but I have an error that is when defining:
    private DatabaseHelper DBHelper;
    it is marked red and says DatabaseHelper can not resolve to a type.
    I have imported every thing you showd.
    Could you help me please.

  12. Thanks for a great tutorial. In your tutorial you have show how to fetch records for random quote. How to fetch records for a button (NextToRandom/Next) ? How do you iterate through all the records? I would really appreciate your help.
    Thanks again.

Leave a Reply to BenCancel reply

Please be kind and respectful to help make the comments section excellent. (Comment Policy)

This site uses Akismet to reduce spam. Learn how your comment data is processed.