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:

01	package com.gregjacobs.randomquotes;

02

03	import java.util.Random;

04

05	import android.content.ContentValues;

06	import android.content.Context;

07	import android.database.Cursor;

08	import android.database.SQLException;

09	import android.database.sqlite.SQLiteDatabase;

10	import android.database.sqlite.SQLiteOpenHelper;

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

01	public class DBAdapter

02

{

03	int id = 0;

04	public static final String KEY_ROWID = "_id";

05	public static final String KEY_QUOTE = "Quote";

06	private static final String TAG = "DBAdapter";

07

08	private static final String DATABASE_NAME = "Random";

09	private static final String DATABASE_TABLE = "tblRandomQuotes";

10	private static final int DATABASE_VERSION = 1;

11

12	private static final String DATABASE_CREATE =

13	"create table tblRandomQuotes (_id integer primary key autoincrement, "

14	+ "Quote text not null );";

15

16	private final Context context;

17

18	private DatabaseHelper DBHelper;

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

01	public DBAdapter(Context ctx)

02

{

03	this.context = ctx;

04	DBHelper = new DatabaseHelper(context);

05

}

06

07	private static class DatabaseHelper extends SQLiteOpenHelper

08

{

09	DatabaseHelper(Context context)

10

{

11	super(context, DATABASE_NAME, null, DATABASE_VERSION);

12

}

13

14

@Override

15	public void onCreate(SQLiteDatabase db)

16

{

17	db.execSQL(DATABASE_CREATE);

18

}

19

20

@Override

21	public void onUpgrade(SQLiteDatabase db, int oldVersion,

22	int newVersion)

23

{

24	Log.w(TAG, "Upgrading database from version " + oldVersion

25

+ " to "

26	+ newVersion + ", which will destroy all old data");

27	db.execSQL("DROP TABLE IF EXISTS tblRandomQuotes");

28	onCreate(db);

29

}

30

}

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.

01	//---opens the database---

02	public DBAdapter open() throws SQLException

03

{

04	db = DBHelper.getWritableDatabase();

05	return this;

06

}

07

08	//---closes the database---

09	public void close()

10

{

11	DBHelper.close();

12

}

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.

1	//---insert a title into the database---

2	public long insertQuote(String Quote)

3

{

4	ContentValues initialValues = new ContentValues();

5	initialValues.put(KEY_QUOTE, Quote);

6	return db.insert(DATABASE_TABLE, null, initialValues);

7

}

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.

01	public int getAllEntries()

02

{

03	Cursor cursor = db.rawQuery(

04	"SELECT COUNT(Quote) FROM tblRandomQuotes", null);

05	if(cursor.moveToFirst()) {

06	return cursor.getInt(0);

07

}

08	return cursor.getInt(0);

09

10

}

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.

01	public String getRandomEntry()

02

{

03

04	id = getAllEntries();

05	Random random = new Random();

06	int rand = random.nextInt(getAllEntries());

07	if(rand == 0)

08

++rand;

09	Cursor cursor = db.rawQuery(

10	"SELECT Quote FROM tblRandomQuotes WHERE _id = " + rand, null);

11	if(cursor.moveToFirst()) {

12	return cursor.getString(0);

13

}

14	return cursor.getString(0);

15

16

}

17

18

}

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:

01	<?xml version="1.0" encoding="utf-8"?>

02	<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"

03	android:orientation="vertical"

04	android:layout_width="fill_parent"

05	android:layout_height="fill_parent"

06

>

07

<TextView

08	android:layout_width="fill_parent"

09	android:layout_height="wrap_content"

10	android:text="@string/Quote"

11

/>

12

<EditText

13	android:id="@+id/Quote"

14	android:layout_width="fill_parent"

15	android:layout_height="wrap_content"

16

/>

17

<Button

18	android:id="@+id/go"

19	android:layout_width="fill_parent"

20	android:layout_height="wrap_content"

21	android:text="@string/press"

22

/>

23

<Button

24	android:id="@+id/genRan"

25	android:layout_width="fill_parent"

26	android:layout_height="wrap_content"

27	android:text="@string/genRan"

28

/>

29	</LinearLayout>

Here is the strings.xml file:

1	<?xml version="1.0" encoding="utf-8"?>

2	<resources>

3	<string name="Quote">Please Enter A Quote:</string>

4	<string name="app_name">Random Quotes</string>

5	<string name="press">Press Me!</string>

6	<string name="genRan">Generate Random Quote!</string>

7	</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:

01	package com.gregjacobs.randomquotes;

02

03	import android.app.Activity;

04	import android.content.Context;

05	import android.os.Bundle;

06	import android.view.View;

07	import android.view.View.OnClickListener;

08	import android.widget.Button;

09	import android.widget.EditText;

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

01	public class QuotesMain extends Activity {

02	DBAdapter db = new DBAdapter(this);

03	EditText Quote;

04	/** Called when the activity is first created. */

05

@Override

06	public void onCreate(Bundle savedInstanceState) {

07	super.onCreate(savedInstanceState);

08	setContentView(R.layout.main);

09	// Capture our button from layout

10	Button setButton = (Button)findViewById(R.id.go);

11	Button getButton = (Button)findViewById(R.id.genRan);

12	// Register the onClick listener with the implementation above

13	setButton.setOnClickListener(mAddListener);

14	getButton.setOnClickListener(mAddListener);

15

}

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.

01	// Create an anonymous implementation of OnClickListener

02	private OnClickListener mAddListener = new OnClickListener()

03

{

04	public void onClick(View v)

05

{

06	switch(v.getId())

07

{

08	case R.id.go:

09	db.open();

10	long id = 0;

11	// do something when the button is clicked

12

try

13

{

14	Quote = (EditText)findViewById(R.id.Quote);

15	db.insertQuote(Quote.getText().toString());

16

17	id = db.getAllEntries();

18

19	Context context = getApplicationContext();

20	CharSequence text = "The quote '" + Quote.getText() + "' was added successfully!\nQuotes Total = " + id;

21	int duration = Toast.LENGTH_LONG;

22

23	Toast toast = Toast.makeText(context, text, duration);

24	toast.show();

25	Quote.setText("");

26

}

27	catch (Exception ex)

28

{

29	Context context = getApplicationContext();

30	CharSequence text = ex.toString() + "ID = " + id;

31	int duration = Toast.LENGTH_LONG;

32

33	Toast toast = Toast.makeText(context, text, duration);

34	toast.show();

35

}

36	db.close();

37

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.

01	case R.id.genRan:

02	db.open();

03	//long id1 = 0;

04	// do something when the button is clicked

05

try

06

{

07	String quote = "";

08	quote = db.getRandomEntry();

09	Context context = getApplicationContext();

10	CharSequence text = quote;

11	int duration = Toast.LENGTH_LONG;

12

13	Toast toast = Toast.makeText(context, text, duration);

14	toast.show();

15

}

16	catch (Exception ex)

17

{

18	Context context = getApplicationContext();

19	CharSequence text = ex.toString();

20	int duration = Toast.LENGTH_LONG;

21

22	Toast toast = Toast.makeText(context, text, duration);

23	toast.show();

24

}

25	db.close();

26

}

27

}

28

};

29

}

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!