Android and SQLite

This tutorial demonstrates how you can use SQLite in your Android application to build a list of high scores.

Why Use SQLite in your Android App?

SQLite can be a powerful tool that makes it easy to store, access, and manipulate data.

Say that you have designed a game for Android, and you would like to keep track of the user's high scores for the game. You could store the high scores in an SQLite database, and retrieve the scores sorted from highest to lowest to display to the user.

I should also note that some basic understanding of SQL would be helpful when reading this tutorial.

Building a Simple App

We are going to build a simple app that will allow us to test using SQLite to store and retrieve our list of high scores. Our app must allow us to easily add new scores and easily retrieve the list of high scores.


The Layout

Our app will include an text input and a corresponding button to add a new high score, and a webview to display the highscores. The webview will refresh whenever a new highscore has been added.

Screenshot of the layout

The first text input will contain the name of the player for the high score. The second text input will contain the score.


The complete XML code for the layout is pasted below.

<?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">
 <EditText android:layout_height="wrap_content"
  android:layout_width="fill_parent" android:id="@+id/editTextName"
  android:layout_marginTop="10dip" android:layout_marginLeft="10dip"
  android:layout_marginRight="10dip"></EditText>
 <EditText android:layout_height="wrap_content"
  android:layout_width="fill_parent" android:id="@+id/editTextHighScore"
  android:layout_marginTop="10dip" android:layout_marginLeft="10dip"
  android:layout_marginRight="10dip"></EditText>
 <Button android:layout_width="wrap_content"
  android:layout_height="wrap_content" android:layout_marginLeft="10dip"
  android:id="@+id/buttonSaveHighScore" android:text="Save High Score"></Button>

 <WebView android:id="@+id/webview" android:layout_width="fill_parent"
  android:layout_height="fill_parent" android:layout_margin="10dip" />
</LinearLayout>

Setting Up

First, we must make sure that we have an SQLite database set up. Then we need to make sure that we have a table set up to store the high score entries.

Before we go about doing that, we want to create a set of constants to use when referencing the database. These constants are listed below.

// Constants
public static final String DATABASE_NAME = "highscores.db";
public static final String HIGH_SCORE_TABLE = "highscore";
public static final String COLUMN_ID = "ID";
public static final String COLUMN_SCORE = "SCORE";
public static final String COLUMN_NAME = "NAME";

Creating the Table

We want the table to be able to hold two pieces of information, a name and a score. In our onResume method, we are going to open the database, and create the tables if necessary. This code is listed below.

@Override
protected void onResume() {
 super.onResume();
 scoreDB = openOrCreateDatabase(DATABASE_NAME,
   SQLiteDatabase.CREATE_IF_NECESSARY
     | SQLiteDatabase.OPEN_READWRITE, null);
 scoreDB.execSQL("CREATE TABLE IF NOT EXISTS " + HIGH_SCORE_TABLE + " ("
   + COLUMN_ID + " INTEGER PRIMARY KEY, " + COLUMN_NAME
   + " VARCHAR, " + COLUMN_SCORE + " INT)");
}

Save High Score Button

We want to add data when the "Save High Score" button is clicked. To do this, we will add an onclick listener to this button.

final EditText editTextName = (EditText) findViewById(R.id.editTextName);
final EditText editTextScore = (EditText) findViewById(R.id.editTextHighScore);
final WebView webview = (WebView) findViewById(R.id.webview);

Button saveHighScore = (Button) findViewById(R.id.buttonSaveHighScore);
saveHighScore.setOnClickListener(new OnClickListener() {

 @Override
 public void onClick(View v) {
 
        }
});

Inside the onClick method, we will want to validate the data before we add it to the database. We want to make sure that the inputted score is a valid integer. The code below accomplishes this.

// First get the values from the EditText
String name = editTextName.getText().toString();
int score = 0;

try {
 score = Integer
   .parseInt(editTextScore.getText().toString());
} catch (NumberFormatException e) {
 return;
}

Adding the Data

Now that we have validated the data, we are ready to add it to the database. This is accomplished by making a map with the keys being the column names and the values being what you want to store.

// Add the values
ContentValues values = new ContentValues();
values.put(COLUMN_NAME, name);
values.put(COLUMN_SCORE, score);
scoreDB.insert(HIGH_SCORE_TABLE, null, values);

Retrieving the Data

After storing the specific entry, we want to retrieve all the high scores. We do this by getting a cursor object. Using the cursor we can iterate over the results.

// Retrieve the new list of scores
Cursor c = scoreDB.query(HIGH_SCORE_TABLE, new String[] {
  COLUMN_NAME, COLUMN_SCORE }, null, null, null, null,
  COLUMN_SCORE);

This code will retrieve the columns COLUMN_NAME and COLUMN_SCORE from the HIGH_SCORE_TABLE and sort based on COLUMN_SCORE.

Iterating over the Data

Now that our query has been executed, we will loop from the end of the data back to the beginning. As we loop over the data we will use a StringBuilder to create a simple string of HTML to display the data.

Once the string of data has been built, we can display it in the WebView. The code to do this is pasted below.

StringBuilder builder = new StringBuilder();
builder.append("<html><body><h1>High Scores</h1><table>");

c.moveToLast();
for(int i=c.getCount()-1; i>=0; i--) {
 
 // Get the data
 builder.append("<tr><td>");
 builder.append(c.getString(0));
 builder.append("</td><td>");
 builder.append(c.getString(1));
 builder.append("</td></tr>");
 
 // Move the cursor
 c.moveToPrevious();
}

builder.append("</table></html>");
webview.loadData(builder.toString(), "text/html", "UTF-8");

Closing the Cursor and Database

After we are done using the cursor, we should close it.

// Close the cursor
c.close();

We should also close the database itself when we are not using. You could close it in the onPause method of your activity.

@Override
protected void onPause() {
 super.onPause();

 if (scoreDB.isOpen()) {
  scoreDB.close();
 }

}

The Full Code

Below is a screenshot of what the app looks like when it is working.

Screenshot of the application with some high scores added.

The full code for the Android application is pasted below.

package com.dreamdom.tutorials.sqlite;

import android.app.Activity;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.webkit.WebView;
import android.widget.Button;
import android.widget.EditText;

public class MainActivity extends Activity {

 // Constants
 public static final String DATABASE_NAME = "highscores.db";
 public static final String HIGH_SCORE_TABLE = "highscore";
 public static final String COLUMN_ID = "ID";
 public static final String COLUMN_SCORE = "SCORE";
 public static final String COLUMN_NAME = "NAME";

 private SQLiteDatabase scoreDB;

 /** Called when the activity is first created. */
 @Override
 public void onCreate(Bundle savedInstanceState) {
  super.onCreate(savedInstanceState);
  setContentView(R.layout.main);

  final EditText editTextName = (EditText) findViewById(R.id.editTextName);
  final EditText editTextScore = (EditText) findViewById(R.id.editTextHighScore);
  final WebView webview = (WebView) findViewById(R.id.webview);

  Button saveHighScore = (Button) findViewById(R.id.buttonSaveHighScore);
  saveHighScore.setOnClickListener(new OnClickListener() {

   @Override
   public void onClick(View v) {

    // First get the values from the EditText
    String name = editTextName.getText().toString();
    int score = 0;

    try {
     score = Integer
       .parseInt(editTextScore.getText().toString());
    } catch (NumberFormatException e) {
     return;
    }

    // Add the values
    ContentValues values = new ContentValues();
    values.put(COLUMN_NAME, name);
    values.put(COLUMN_SCORE, score);
    scoreDB.insert(HIGH_SCORE_TABLE, null, values);

    
    // Retrieve the new list of scores
    Cursor c = scoreDB.query(HIGH_SCORE_TABLE, new String[] {
      COLUMN_NAME, COLUMN_SCORE }, null, null, null, null,
      COLUMN_SCORE);
    
    StringBuilder builder = new StringBuilder();
    builder.append("<html><body><h1>High Scores</h1><table>");
    
    c.moveToLast();
    for(int i=c.getCount()-1; i>=0; i--) {
     
     // Get the data
     builder.append("<tr><td>");
     builder.append(c.getString(0));
     builder.append("</td><td>");
     builder.append(c.getString(1));
     builder.append("</td></tr>");
     
     // Move the cursor
     c.moveToPrevious();
    }
    
    builder.append("</table></html>");
    webview.loadData(builder.toString(), "text/html", "UTF-8");
    
    // Close the cursor
    c.close();

   }
  });
 }

 @Override
 protected void onResume() {
  super.onResume();
  scoreDB = openOrCreateDatabase(DATABASE_NAME,
    SQLiteDatabase.CREATE_IF_NECESSARY
      | SQLiteDatabase.OPEN_READWRITE, null);
  scoreDB.execSQL("CREATE TABLE IF NOT EXISTS " + HIGH_SCORE_TABLE + " ("
    + COLUMN_ID + " INTEGER PRIMARY KEY, " + COLUMN_NAME
    + " VARCHAR, " + COLUMN_SCORE + " INT)");
 }

 @Override
 protected void onPause() {
  super.onPause();

  if (scoreDB.isOpen()) {
   scoreDB.close();
  }

 }

Final Thoughts

This tutorial was designed to introduce you to SQLite on Android. If you would like to share this SQLite data among different applications, then you must create a full content provider. This is more complex and will be featured in a later tutorial.

Also, in this example we never delete any data out of the database. A user probably won't like it if they download your app and it starts using a ton of your phone's storage memory. Keep this in mind as you design your Android apps

Please leave any feedback in the comments. Thanks!