Developing Notepad Application - Part 1 (Creating SQLite database)

Application minimum required SDK version - 14 (ICS)

Contents

1. Intro
2. Creating SQLite database
3. Accessing SQLite database from ADB shell.
4. Links

1. Intro

In this tutorial we will develop a simple notepad Application for Android that allows you to create, read and edit text notes. To understand this tutorial you've got to have basic knowledge about relational databases and SQL.

The app and the idea are not mine, almost all of the code is taken from Android developers web site. The tutorial there is just awesome but there are some topics that I would like to highlight in more detail. There are also some things that are deprecated and I will replace them with new implementations and point out where it happens. 

We will start from a scratch and add some functionality step by step. Before reading this tutorial I highly recommend to look through the original article from Android web site.

In this tutorial we are going to create a very simple database that will serve us as data storage for our app.

At the end of this tutorial you will know how to:

• create a simple SQLite database in Android.
• get access to SQLite database via the command prompt (terminal) and execute some simple queries.

2. Short facts about SQLite database

First, some facts about SQLite in general:

• SQLite is free and open-source embedded SQL database engine. 
• SQLite reads and writes directly to ordinary disk files. 
• A complete SQL database is contained in a single disk file. 
• The database file format is cross-platform.
• It supports standard relational database features including syntax, transactions and prepared statements. Transactions are ACID.

Database Structure

Our database will contain only one table and the following columns to persist notes:

• title -- text (note title)
• body -- text (note body text)
• _id -- integer, autoincrement (the id of the note)

Creating the Database

Let's create a new project with default settings which contains a single activity called NotesListActivity. Now in the same package with our activity create a class called NotesDbAdapter which will contain a subclass of SQLiteOpenHelper as a subclass which is a class to manage database creation and it's versioning. NotesDbAdapter will grant us an access to our database by defining CRUD methods in it. So let's create the wrapper class for our database and define all the constants we need and static inner class to get a database connection.

NotesDbAdapter.java

package com.blogspot.android_by_example.notepad;

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

public class NotesDbAdapter {

 public static final String COLUMN_TITLE = "title";
 public static final String COLUMN_BODY = "body";
 public static final String COLUMN_ID = "_id";

 public static final String TAG = NotesDbAdapter.class.getSimpleName();
 private DatabaseHelper mDbHelper;
 private SQLiteDatabase mDb;

 private static final String DATABASE_NAME = "notes.db";
 private static final String TABLE_NAME = "notes";
 private static final int DATABASE_VERSION = 1;

 private static final String DATABASE_CREATE = 
   "create table notes (_id integer primary key autoincrement, "
   + "title text not null, body text not null);";
 
 private final Context mContext;

 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);
   Log.d(TAG, "onCreate() database");
  }

  @Override
  public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
   db.execSQL("DROP TABLE IF EXISTS notes");
   onCreate(db);
   Log.d(TAG, "onUpdate() database");
  }
 }

 public NotesDbAdapter(Context context) {
  this.mContext = context;
 }
 
 public NotesDbAdapter open() throws SQLException {
  mDbHelper = new DatabaseHelper(mContext);
  mDb = mDbHelper.getWritableDatabase();
  return this;
 }
}

The code looks quite big but actually there is nothing special here. We create static subclass of SQLiteOpenHelper and implement abstract methods onCreate() and onUpgrade(). The onCreate() method is invoked when there is no database available for our app, so in this method we have to execute all the statements to create our database. onUpgrade() is invoked when the version of the current database version is smaller than new version (database version is provided when SQLiteOpenHelper subclass instance is created and this version is passed as a parameter to a superclass constructor).

NotesDbAdapter is a wrapper class that holds the instance of SQLiteOpenHelper and provides API for database access (We will define basic CRUD operations in the next tutorials).

Now we need to create an instance of our NotesDbAdapter in our activity an call open() method to create the database. That's all the code we need to have a working database.

NotesListActivity.java

package com.blogspot.android_by_example.notepad;

import android.os.Bundle;
import android.app.Activity;

public class NotesListActivity extends Activity {
 
 private NotesDbAdapter mDbHelper;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_notes_list);
        
        mDbHelper = new NotesDbAdapter(this);
        mDbHelper.open();
    }
}

3. Accessing SQLite Database from ADB Shell

Now we need to make sure that our database is created and in a working state. Let's open DDMS perspective in Eclipse (or launch monitor from tools folder in your SDK location) and locate the database file on the device file system.

Emulator/device instances store SQLite3 databases in the folder:

/data/data/<package_name>/databases/<database_name>

So in our case the database will be located at:

/data/data/com.blogspot.android_by_example.notepad/databases/notes.db

Database file location

To issue SQL queries to your database enter a remote shell on the emulator instance and enter sqlite3 command following with the full path to our database. Here is an example:

C:\Users\Taras Osiris>adb shell
root@android:/ # sqlite3 /data/data/com.blogspot.android_by_example.notepad/databases/notes.db
.android_by_example.notepad/databases/notes.db                                <
SQLite version 3.7.11 2012-03-20 11:35:50
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

On Windows to acces ADB directly from command prompt add path of your SDK to your environment variables. On Mac OS follow this instructions.

If you have only one USB device or emulator connected you don't need to specify what device is the target for your commands. Otherwise you need to direct your commands to the targeted device by specifying arguments after adb command. For more details read about issuing ADB commands.

adb [-d|-e|-s <serialNumber>] shell

Now you can execute your SQL queries directly to your database. Let's insert a few rows into our database and than query all of them.

sqlite> INSERT INTO notes (title, body) VALUES ("Title1", "Body1");
INSERT INTO notes (title, body) VALUES ("Title1", "Body1");
sqlite> INSERT INTO notes (title, body) VALUES ("Title2", "Body2");
INSERT INTO notes (title, body) VALUES ("Title2", "Body2");
sqlite> INSERT INTO notes (title, body) VALUES ("Title3", "Body3");
INSERT INTO notes (title, body) VALUES ("Title3", "Body3");
sqlite> SELECT * FROM notes;
SELECT * FROM notes;
1|Title1|Body1
2|Title2|Body2
3|Title3|Body3

As you can see, everything is great, our database is fine and ready to use. In the next part of tutorial we are going to work on ListActivity that will display a list of our notes from database.

Links and Materials Used

• Android SQLite Database and ContentProvider Tutorial at vogella.com
• Issuing ADB command in android and using SQLite from shell
• SQLite in Android (rus)