Write and Read an SQLite Database

Tap an item to update or delete it. Or tap the three white dots to pull down the options menu.

A ListView gets its views from an Adapter. For example, this project had a ListView whose views came from an ArrayAdapter<String>, and the ArrayAdapter<String> got each String from an array of Strings. Another previous project had a ListView whose views came from a SimpleCursorAdapter, and the SimpleCursorAdapter got each String from a Cursor that read from a pre-written SQLiteDatabase of contacts.

This app has a ListView whose views come from a SimpleCursorAdapter. The SimpleCursorAdapter gets its strings from a Cursor that gets its strings from an SQLiteDatabase object that was created and populated by an SQLiteOpenHelper that is part of the app. In real life, each record of a multi-column database would be displayed in a view that is a horizontal LinearLayout containing a row of TextViews.

The database must have a column named _id (lowercase with underscore) declared as an INTEGER PRIMARY KEY; see the Class Overview for CursorAdapter. The list of columns in MainActivity must include this column.

See Saving Data in SQL Databases and Using Databases and classes SQLiteOpenHelper and SQLiteDatabase. The Note Pad app accesses a database.

Source code in Sqlite.zip

  1. MainActivity.java
  2. Helper.java: defines a subclass of SQLiteOpenHelper.
  3. activity_main.xml. The RelativeLayout displays a ListView when the table is non-empty, and a TextView when the table is empty.
  4. edit_text.xml. This EditText is displayed by the AlertDialogs. The EditText’s left and right margins (a.k.a. its start and end margins) match the padding of the ScrollView that contains the message in the dialog. These margins are honoured only if the EditText is contained in a FrameLayout.
  5. menu_main.xml. This options menu is displayed by the Activity when you tap the three white dots in the action bar.
  6. strings.xml
  7. AndroidManifest.xml
  8. build.gradle (Module: app)

Create the project

Select the folder app/java/edu.nyu.scps.sqlite in the Android Studio project view. Pull down
File → New → Java Class
Create New Class
Name: Helper
Kind: Class
OK

Edit your new file Helper.java and insert the words extends SQLiteOpenHelper. Click on the word Helper and pull down
Code → Implement Methods…
OK

Select the folder app/res/layout in the Android Studio project view. Pull down
File → New → Layout resource file
New Resource File
File name: edit_text
Root element: FrameLayout
OK

adb: the Android debug bridge

Open a Terminal window on your Mac, or a cmd.exe Command Prompt window in Microsoft Windows. 

adb devices
List of devices attached
ca1784a34445a8d0308	device
0088080744830RTG	device
192.168.57.101:5555	device

adb -s 192.168.57.101:5555 shell
root@vbox86p:/ # pwd
root@vbox86p:/ # ls		(lowercase LS for "list")
root@vbox86p:/ # ls -l		(minus lowercase L)

root@vbox86p:/ # cd data/data/edu.nyu.scps.sqlite/databases
root@vbox86p:/data/data/edu.nyu.scps.sqlite/databases # pwd
root@vbox86p:/data/data/edu.nyu.scps.sqlite/databases # ls -l stooges.db
-rw-rw---- u0_a129  u0_a129     20480 2015-09-07 09:15 stooges.db

root@vbox86p:/data/data/edu.nyu.scps.sqlite/databases # sqlite3 stooges.db
SQLite version 3.7.11 2012-03-20 11:35:50
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .help
sqlite> .tables
android_metadata people

sqlite> .dump people
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE people (	_id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT);
INSERT INTO people VALUES(1,'Moe');
INSERT INTO people VALUES(2,'Larry');
INSERT INTO people VALUES(3,'Curly');
INSERT INTO people VALUES(4,'Shemp');
COMMIT;

sqlite> .headers ON
sqlite> .mode column
sqlite> select * from people;
_id         name
----------  ----------
1           Moe
2           Larry
3           Curly
4           Shemp

sqlite> .exit
root@vbox86p:/data/data/edu.nyu.scps.sqlite/databases # exit

Pull a file from the emulator to your Mac or PC

cd		Go to your home directory (or your Destop) on your computer.
pwd		Make sure you arrived there.

adb -s 192.168.57.101:5555 pull /data/data/edu.nyu.scps.sqlite/databases/stooges.db stooges.db
1049 KB/s (20480 bytes in 0.019s)

ls -l stooges.db
sqlite3 stooges.db

sqlite> .headers ON
sqlite> .mode column
sqlite> select * from people;
sqlite> .exit

sqlite3 stooges.db .dump

Suppose you have used sqlite3 to modify stooges.db on your Mac or PC. Can you push the file from your computer back to the device or emulator?

Things to try

  1. Be more selective. Change the third parameter of each query to one of the following strings. 
    "name LIKE 'L%'"   /* begins with uppercase L */
"name LIKE '%y'"   /* ends with lowercase y */
"name LIKE '%r%'"  /* contains lowercase r */
"name LIKE '%M%' OR name LIKE '%m%'"  /* contains M or m */
"name LIKE '__e%'" /* third character is e (two underscores) */
"length(name) >= 5"
"_id % 2 == 0"     /* just the even numbers */
"_id % 2 == 1"     /* just the odd numbers */
"_id % 2 == 0 AND name LIKE '%y'"

  2. Display a Log.d message in the constructor and in the onCreate method of the Helper. Verify that if the database file /data/data/edu.nyu.scps.sqlite/databases/stooges.db does not already exist (e.g., the first time you press the Android Studio run button), both methods are called. Verify that if the database file does already exist (e.g., the second time you press the Android Studio run button, or when you destroy and re-create the Activity by a change of orientation), only the constructor is called.

    There are two ways to remove the database file (and the databases directory that contains it): by uninstalling the app, or by running the following commands. The -ld option of ls lists the directory itself, not the files and subdirectories in the directory. The -fR option of rm removes the directory and all of its contents, including subdirectories, sub-subdirectories, etc. 

    adb -s 192.168.57.101:5555 shell
root@vbox86p:/ # cd /data/data/edu.nyu.scps.sqlite

root@vbox86p:/data/data # ls -ld databases
drwxrwx--x u0_a132  u0_a132           2015-09-07 11:47 databases

root@vbox86p:/data/data # rm -fR databases

root@vbox86p:/data/data # ls -ld databases
databases: no such file or directory

root@vbox86p:/data/data # exit

    adb -s 192.168.57.101:5555 shell rm -fR /data/data/edu.nyu.scps.sqlite/databases
  3. Can you hide or disable the menu entry for “Delete all” when the table is empty?

  4. Add menu entries for alphabetical vs. numeric order (and maybe also for ascending vs. descending numerical order). Add the following field to class MainActivity. 
        private String sortOrder = null;
    Change the seventh parameter of each call to query to sortOrder. Add strings to strings.xml, items to menu_main.xml, and the following if statements to the onOptionsItemSelected method of MainActivity. 
            if (id == R.id.action_alphabetical) {
            sortOrder = "name ASC";
            Cursor cursor = db.query(tableName, columns, null, null, null, null, sortOrder);
            adapter.changeCursor(cursor);
            return true;
        }

        if (id == R.id.action_numerical) {
            sortOrder = "_id ASC";
            Cursor cursor = db.query(tableName, columns, null, null, null, null, sortOrder);
            adapter.changeCursor(cursor);
            return true;
        }

  5. Create a contract class for the database. Add the following file, named StoogesContract.java, to the project. Classes StoogesContract and StoogesContract.Columns cannot be instantiated because their constructors are private. Class StoogesContract.Columns has the field StoogesContract.Columns._ID (inherited from interface BaseColumns, containing the string "_id") as well as the field StoogesContract.Columns.NAME (containing the string "name"). 
    package edu.nyu.scps.sqlite;

import android.provider.BaseColumns;

public final class StoogesContract {
    public static final String TABLE = "people";

    private StoogesContract() {
    }

    public static final class Columns implements BaseColumns {
        private Columns() {
	}

        public static final String NAME = "name";
    }
}
    In classes Helper and MainActivity, change every string "people" to StoogesContract.TABLE, change every string "_id" to StoogesContract.Columns._ID, and change every string "name" to StoogesContract.Columns.NAME. You will also have to make these changes when the three strings are embedded in longer strings. For example, change 
            String createTable = "CREATE TABLE people ("
                + "_id INTEGER PRIMARY KEY AUTOINCREMENT,"
                + "name TEXT"
                + ");";
    to 
            String createTable = "CREATE TABLE " + StoogesContract.TABLE + " ("
                + StoogesContract.Columns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                + StoogesContract.Columns.NAME + " TEXT"
                + ");";
    and in onClick, change "_id = ?" to StoogesContract.Columns._ID + " = ?".

    One advantage of having a contract class is that we will get a Java compilation error if we misspell the name of any field of the contract class in Helper and MainActivity. We would not have gotten any Java compilation error if we misspelled the strings "people", "_id", or "name", but we might have gotten a runtime error later from SQLite.