SQLite Database

Android applicaons can have applicaon databases powered by SQLite : 

–     Lightweight and file-based, ideal for mobile devices

–     Databases are private for the applicaon that creates them

–     Databases should not be used to store files

SQLite is a light weight database :

–     Atomic

–     Stable

–     Independent

–     Enduring

–     Only several kilobytes

–     Only partly support some SQL commands such as ALTER, TABLE.

SQLite is included as part of Android’s software stack :

SQLite Databases

Steps for using SQLite databases:

1.        Create a database

2.        Open the database

3.        Create a table

4.        Create and insert interface for datasets

5.        Create a query interface for datasets

6.        Close the database

SQLite Example:

public class NotesDbAdapter {

public stac final String  KEY_TITLE = "tle";

public stac final String  KEY_BODY = "body";

public stac final String  KEY_ROWID = "_id";

private stac final String TAG = "NotesDbAdapter"; private DatabaseHelper mDbHelper;

private SQLiteDatabase mDb;

/**

* Database creaon sql statement */

private stac final String  DATABASE_CREATE =

"create table notes (_id integer primary key auto increment, " + "title text not null, body text not null);";

private static final String DATABASE_NAME = "data"; private static final String DATABASE_TABLE = "notes"; private static final int DATABASE_VERSION = 2;

private final Context mCtx;

 

SQLiteOpenHelper Class : 

Abstract class for implementing a best practice pattern for creating, opening and upgrading databases

•  To create a SQLite database, the recommended approach is to create a subclass of SQLiteOpenHelper class

•       Then override its onCreate() method

–      Then execute a SQLite command to create tables in the database

•       Use the onUpgrade() method to handle upgrade of the database

–      A simple way would be to drop an existing table and replace with a new  definition

–      Beer to migrate existing data into a new table

•  Then use an instance of the helper class to manage opening or upgrading the database

–  If the database doesn’t exist, the helper will create one by calling its onCreate() handler

–  If the database version has changed, it will upgrade by calling the onUpgrade() handler

SQLite Example:

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);

}

@Override

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

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

+ newVersion + ", which will destroy all old data"); db.execSQL("DROP TABLE IF EXISTS notes"); onCreate(db);

}

}

public NotesDbAdapter(Context ctx) { this.mCtx = ctx;

}

public NotesDbAdapter open() throws SQLExcepon { mDbHelper = new DatabaseHelper(mCtx);

mDb = mDbHelper.getWritableDatabase(); return this;

}

public void close() { mDbHelper.close();

}

ContentValues() objects used to hold rows to be inserted into the database

•           Example:

public long createNote(String tle, String body) {

ContentValues initialValues = new ContentValues(); initialValues.put (KEY_TITLE, tle);

initialValues.put (KEY_BODY, body);

return mDb.insert(DATABASE_TABLE, null, initialValues );

}

public boolean deleteNote(long rowId) {

return mDb.delete(DATABASE_TABLE, KEY_ROWID + "=" + rowId, null) > 0;

}

public boolean updateNote(long rowId, String tle, String body) { ContentValues args = new ContentValues(); args.put(KEY_TITLE, tle);

args.put(KEY_BODY, body);

return mDb.update(DATABASE_TABLE, args, KEY_ROWID + "=" + rowId, null) > 0;

}

Database queries are returned as Cursor objects :

–    Pointers to the resulting sets within the underlying data

•      Cursor class provides several methods:

–    moveToFirst, moveToNext, moveToPrevious, moveToPosition  used to move to a row

–    getCount to get the number of rows in the cursor

–    getPosition  to get the current row position

–    getColumnName, getColumnNames, getColumnIndexorThrow to get info on columns

–    startManagingCursor and stopManagingCursor methods used to integrate cursor lifetime into the activity’s lifetime

public Cursor fetchAllNotes() {

return mDb.query(DATABASE_TABLE, new String[] {KEY_ROWID, KEY_TITLE, KEY_BODY}, null, null, null, null, null);

}

public Cursor fetchNote(long rowId) throws SQLExcepon  {

Cursor mCursor =

mDb.query(true, DATABASE_TABLE, new String[] {KEY_ROWID,

KEY_TITLE, KEY _BODY}, KEY_ROWID + "=" + rowId, null, null, null, null, null);

if (mCursor != null) { mCursor.moveToFirst();

}

return mCursor;

}

Within the main activity, cursors returned by the Dbadapter are used as follows:

private void fillData() {

Cursor notesCursor = mDbHelper.fetchAllNotes(); startManagingCursor(notesCursor);

Create an array to specify the fields we want to display in the list (only TITLE) String[] from = new String[]{NotesDbAdapter.KEY_TITLE};

and an array of the fields we want to bind those fields to (in this case just text1) int[] to = new int[]{R.id.text1};

Now create a simple cursor adapter and set it to display

SimpleCursorAdapter notes =

new SimpleCursorAdapter(this, R.layout.notes_row, notesCursor, from, to); setListAdapter(notes);