Android Application Development: Playing with SQLite Database

In this article, I will focus on building an Android app using an SQLite database; I assume readers are familiar with the basics of database operations and concepts. Here, we will also be using some threaded programming in Java, so I’m assuming readers are familiar with this concept.

The SQLite database is used in Android for storing and managing application data. The SQLite database is an open source lightweight transaction SQL database engine. There are other means of storing application data in Android rather than a database (they are known as Preferences), but those are more often used to store a very small amount of information, like usernames and passwords, so that users are not prompted for their credentials every time the application is started.

In this article, I will develop a simple log-in application, which stores user details in the database, and performs basic DDL (Data Definition Language, e.g., creation of tables, etc); DML (Data Manipulation Language, e.g., insert, delete, etc) transactions on the database. In this application, I have dealt with creating a user interface too: I have added a splash screen for the application, and navigation within the application is altered to enhance usability. Hence, this article also takes you to the next level of designing a user interface for your application.

Let’s get started with the design of the application (see Figures 1 and 2).

We will create a database that holds the credentials of all the users. There are two types of user log-ins:

1. User login: Whenever someone tries to log in to the application, we query the credentials entered by that person against our database. If the entered credentials are correct, then we allow the person to log in, otherwise we display a suitable message.
2. Admin login: We also allow the admin to see the credentials of all the registered users. The admin user account is created during the installation of the application.

Now, let’s get started with the implementation of the application. The completed Android application project will look like what’s shown in Figure 3.

We start with the creation of a splash screen for our application. Here, we will be using some threaded programming, as stated earlier.

setTheme(android.R.style.Theme_Translucent_NoTitleBar);
setContentView(R.layout.splashscreen);

try {
    int waited = 0;
    while(_active && (waited < _splashTime)){
        sleep(100);
        if(_active) {
            waited += 100;
        }
    }
} catch(InterruptedException e) {
    // do nothing
} finally {
    finish();
    startActivity(new Intent(SplashScreen.this, DatabaseActivity.class));
    stop();
}

The above snippet, from the “run” method, is the heart of a threaded program in Java, where all the work associated with a thread is done. The motive of this thread is to delay the functioning of the main thread for 5 seconds, and then return back to the main thread, which takes you to the log-in page of the application.

Thus, we create a splash screen. We set the theme of this activity as one of the built-in themes — android.R.style.Theme_Translucent_NoTitleBar, and set the layout of the activity to our custom-made layout, which is R.layout.splashscreen.

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout
  xmlns:android="http://schemas.android.com/apk/res/android"
  android:layout_width="fill_parent"
  android:layout_height="fill_parent"
  android:gravity="center"
  android:orientation="vertical"
  android:padding="20dp"
  >  
  <ImageView
      android:src="@drawable/databaseicon"
      android:layout_height="wrap_content"
      android:layout_width="wrap_content"
      android:layout_gravity="center"
      android:padding="20dp"
      android:scaleType="fitCenter"
  ></ImageView>

  <TextView
      android:text="A Simple Login Example Using Database"
      android:layout_width="fill_parent"
      android:layout_height="wrap_content"
      android:textColor="#ABCDEF"
      android:scaleType="fitCenter"
  ></TextView>
</LinearLayout>

In this layout, we have an image and text, which we display at the launch of the application for 5 seconds. The only new Android property you see from the previous article is android:scaleType; which is used while scaling the image. If the screen size changes, then the image will be scaled, always keeping it in the centre, so that the splash screen layout is always at the centre — even for different sizes of screen.

When the splash screen activity is completed, we fire an Intent to launch another activity called DatabaseActivity, which takes you to the log-in page. See examples of the splash screen and log-in page in Figures 4 and 5.

DatabaseActivity involves taking in the values, validating the input, and querying the database. The full code of this application can be found in my Github repository. I have already explained in my first article about Basic Android Application Development– how to take inputs and create layouts. Therefore, I will concentrate more on the core concept rather than on the UI and basic validations.

In this activity, we set the layout to main.xml, as shown in Figure 5; we write the basic code to get inputs from the EditBoxes for usernames and passwords. We also write code for the basic validation for null strings, as we had discussed in my first article. The main validation that is required here is that of the username and password against the database. I have written a method called validateLogin that does the task.

Button mLogin;
Button mNewUser;
Button mShowAll;
EditText mUsername;
EditText mPassword;
DbHelper mydb = null;

These are the instances that we will be using in this Activity class to accomplish our objective. I created the DBHelper class to create and upgrade the database. We will discuss DBHelper in detail, as we move ahead.

public boolean validateLogin(String uname, String pass, Context context) {
mydb = new DbHelper(context);
SQLiteDatabase db = mydb.getReadableDatabase();
//SELECT
String[] columns = {"_id"};
//WHERE clause
String selection = "username=? AND password=?";
//WHERE clause arguments
String[] selectionArgs = {uname,pass};
Cursor cursor = null;
try{
    //SELECT _id FROM login WHERE username=uname AND password=pass
    cursor = db.query(DbHelper.SAKET_TABLE_NAME, columns, selection, selectionArgs, null, null, null);
    startManagingCursor(cursor);
} catch(Exception e){
    e.printStackTrace();
}
int numberOfRows = cursor.getCount();
if(numberOfRows <= 0){
    Toast.makeText(getApplicationContext(), "Login Failed..\nTry Again", Toast.LENGTH_SHORT).show();
    return false;
    }    
    return true;
}

In this method, we will be using the built-in database APIs offered by Android to perform database operations. First, we need to create an instance of a class called DBHelper, to connect to our database, so that we can use it. To operate on a database, we need to follow the sequence given below:

• Open a database
• Read/write operations on a database
• Commit
• Close the database.

Following the same order, we open the database using the getReadableDatabase() or getWritableDatabase() methods, based on the required mode of operation. We can use a generic SQLiteDatabase instance, which is returned by the getReadableDatabase() method. Now that we have the database object, we need to perform a query on it to validate the user credentials in the database. Thus, we need to code the query as follows:

public Cursor query (String table,
String[] columns,
String selection,
String[] selectionArgs,
String groupBy,
String having,
String orderBy
)

These variables are for the following purposes:

• columns — the columns that are requested in the SELECT statement.
• selection — the WHERE clause of the statement.
• selectionArgs — the values of the place-holders in the selection string.
• groupBy — the SQL GROUP BY clause.
• orderBy — the SQL ORDER BY clause.

Our query hence becomes what’s shown below:

db.query(DbHelper.SAKET_TABLE_NAME,
columns,
selection,
selectionArgs, null, null, null);

    columns = _id
    selection = username=? AND password=?
    selectionArgs = uname,pass

In our query, we selected the table column _ID from the table SAKET_TABLE_NAME, which is the table I have created to store the user credentials. The selection parameter has the WHERE clause (the condition of the query that needs to be verified with the database); selectionArgs has the values of the place-holders in the selection parameter.

This query returns a CURSOR — a collection of data returned by the query. CURSOR is a database construct used to hold values when a query returns more than one row of data. It needs to be opened first, before you can issue a FETCH command to get the contents of the cursor, and last of all, you need to close the cursor. The task of managing a cursor is done by the API startManagingCursor(cursorname). This opens and closes the cursor.

After getting the cursor, we can get the data from the cursor using its built-in methods, such as:

• getCount() — the count of rows returned by the query
• getXXX(int columnIndex) — based on the data type of the column, you can retrieve the data. For example, getInt(int columnIndex), getDouble, getString, etc.
• getColumnIndex(String columnName) — Returns you the column index for the named column.

Using these API methods, we can retrieve the data. In this scenario, usernames are assumed to be unique; if the username exists in the table, then the getCount() method should return only one row. If the row count is 0, that means that there is no such user record; then, we Toastthe user with the appropriate message. Now, when the users’ credentials are verified, they are taken to a ‘logged-in’ page, as shown below.

NewUserActivity is the activity that registers a new user. In this activity, we demonstrate how to insert a row of data in the database. When users click on the New User button, they are taken to the New User page. In this page, the users need to enter the new username, password, and email address. When that is done, they need to click the Registerbutton, as shown in Figure 7.

The Register button invokes a method addEntry() in the NewUserActivity class, which adds the entries of the users in the database, thus registering them.

public void addEntry(String uname, String pass, String email){
    SQLiteDatabase db = myDb.getWritableDatabase();
    ContentValues values = new ContentValues();
    values.put("username", uname);
    values.put("password", pass);
    values.put("email", email);

    try{
        db.insert(DbHelper.SAKET_TABLE_NAME, null, values);
        Toast.makeText(getApplicationContext(), "User Registered", Toast.LENGTH_SHORT).show();
    } catch(Exception e){
        e.printStackTrace();
    }
}

Here, we open the database for writing, by invoking the getWritableDatabase() method. In Android, the values that need to be inserted are entered using ContentValues. An instance of this class is passed in the INSERT statement, whose syntax is as follows:

public long insert (String table, String nullColumnHack, ContentValues values)

Here, the parameters are as follows:

• table — table name
• nullColumnHack — SQL doesn’t allow inserting a completely empty row, so if initialValues is empty, this column will explicitly be assigned a NULL value.
• ContentValues — Holds the values that the INSERT statement seeks to insert in the database.

DBHelper is the class that creates the database, and it can also be configured to upgrade the database (if, in future, the developer changes the database design in later versions of the application).

public class DbHelper extends SQLiteOpenHelper {
    private static final String DATABASE_NAME = "saket.db";
    private static final int DATABASE_VERSION = 1;
    public static final String SAKET_TABLE_NAME = "login";
    private static final String SAKET_TABLE_CREATE =
                    "CREATE TABLE " + SAKET_TABLE_NAME + "(" +
                    "_id INTEGER PRIMARY KEY AUTOINCREMENT,"+
                    "username TEXT NOT NULL, password TEXT NOT NULL, email             TEXT NOT NULL);";
    private static final String SAKET_DB_ADMIN = "INSERT INTO         "+SAKET_TABLE_NAME+"values(1, admin, password, admin@gmail.com);";

    public DbHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        System.out.println("In constructor");
    }

    // (non-Javadoc)
    // @see android.database.sqlite.SQLiteOpenHelper#onCreate(android.database.sqlite.SQLiteDatabase)
    
    @Override
    public void onCreate(SQLiteDatabase db) {        
        try{
            //Create Database
            db.execSQL(SAKET_TABLE_CREATE);

            //create admin account
            db.execSQL(SAKET_DB_ADMIN);
            System.out.println("In onCreate");
        } catch(Exception e) {
            e.printStackTrace();
        }
    }

    // (non-Javadoc)
    // @see android.database.sqlite.SQLiteOpenHelper#onUpgrade(android.database.sqlite.SQLiteDatabase, int, int)
     
    @Override
    public void onUpgrade(SQLiteDatabase arg0, int oldVersion, int newVersion) {

    }
}

In this class, we create the database in the onCreate() method. This method is invoked when the application is installed on the device. The onUpgrade() method is invoked when there is an existing database, and if there are some changes that need to be done to it.

In Android, to use SQLiteDatabase, we need to extend the SQLiteOpenHelper class. It is mandatory to have a constructor, and it is also mandatory to have DATABASE NAME and a DATABASE VERSION that need to be passed to the SQLiteOpenHelper class constructor.

In the onCreate() method, we execute the CREATE statement, using the API method db.execSQL(String query). In this method, we also create a default admin account, with the default password set to “admin”.

The AdminPage activity is the admin page that the application presents. In this activity, we just verify the admin password in the class itself, because we already know the password — but it is for you as a developer to decide whether you want to verify the password of the admin account via a database query as well.

It is inherently insecure to have an unchangeable default admin password, so on security principles, for a real-life application, you should certainly provide the ability to change the admin password. However, this being only a demo application, we have not added in the extra code.

mPassword = (EditText)findViewById(R.id.editPassword);

String pass = mPassword.getText().toString();
if(pass.equals("") || pass == null){    
    Toast.makeText(getApplicationContext(), "Password Missing", Toast.LENGTH_SHORT).show();
    }else{
    if(pass.equals("admin")){
        Toast.makeText(getApplicationContext(), "Retrieving Data", Toast.LENGTH_SHORT).show();
        retrieveEntries();
    }else{
        Toast.makeText(getApplicationContext(), "Admin Login Failed", Toast.LENGTH_SHORT).show();
    }
}

When the admin logs in, then he or she is shown a list of all the registered users — or we Toast the admin with a suitable error message, as shown in Figures 8 and 9.

public class DbHelper extends SQLiteOpenHelper {

	private static final String DATABASE_NAME = "saket.db";
	private static final int DATABASE_VERSION = 1;
    public static final String SAKET_TABLE_NAME = "login";
	private static final String SAKET_TABLE_CREATE =
	                "CREATE TABLE " + SAKET_TABLE_NAME + "(" +
	                "_id INTEGER PRIMARY KEY AUTOINCREMENT,"+
	                "username TEXT NOT NULL, password TEXT NOT NULL, email TEXT NOT NULL);";
	private static final String SAKET_DB_ADMIN = "INSERT INTO "+SAKET_TABLE_NAME+"values(1, admin, password, admin@gmail.com);";

	public DbHelper(Context context) {

		super(context, DATABASE_NAME, null, DATABASE_VERSION);
		System.out.println("In constructor");

	}

	// (non-Javadoc)
	// @see android.database.sqlite.SQLiteOpenHelper#onCreate(android.database.sqlite.SQLiteDatabase)

	@Override
	public void onCreate(SQLiteDatabase db) {

		try{
			//Create Database
			db.execSQL(SAKET_TABLE_CREATE);

			//create admin account
			db.execSQL(SAKET_DB_ADMIN);
			System.out.println("In onCreate");
		}catch(Exception e){
			e.printStackTrace();
		}
	}

	// (non-Javadoc)
	// @see android.database.sqlite.SQLiteOpenHelper#onUpgrade(android.database.sqlite.SQLiteDatabase, int, int)

	@Override
	public void onUpgrade(SQLiteDatabase arg0, int oldVersion, int newVersion) {

	}

}

In the admin page, the above-mentioned two methods are used to retrieve the entries from the database, and show them to the admin in a TextView. In the above code:

• The retrieveEntries() method queries the database for all the users created.
• The showDatabase() method displays all the data obtained in the CURSOR in a TextView. The data from the cursor is retrieved by processing the cursor in a while loop, querying the columns based on their index. A StringBuilder object is used to build the final string containing all the data from the cursor, which is then fed to the TextView for display.
• The onBackPressed() method is used to alter the action of the back key on the device to suit our application. Developers can then fire intents to decide the action they want to perform.

There are a lot of changes that can be applied to the code regarding the authentication mechanism; there is a provision to add many other exceptions, and prompt the user with a suitable message. Readers are free to contribute to this piece of code for their own understanding. You can pull a clone of this project from my Github repository and play around with the code.

There is another improvement that can be made to the code, by introducing transaction APIs like beginTransaction(), setTransactionSuccessfull(), etc. You can play around with these APIs, and enhance your applications’ database transactions to be more secure and atomic. These APIs also help application transactions to be compliant with the ACID definition of a database transaction.

This is all I have for you in this article. We’ll explore another interesting concept of Android application development later. Till then, happy coding!