SQLite

In the MyRent series of labs we have used file storage as a means of data persistence. In an earlier lab we introduced an SQLite database to an app based on a subset of MyRent.Here we replace the file system in MyRent with an SQLite database. Later we shall open up this database to other applications using Android's ContentProvider.

Introduction

In the previous SQLite lab we worked with a subset of MyRent. Here we integrate an SQLite database into the full MyRent app, replacing the existing file storage system. Resume development from the end of the cameral module.

We shall remove all references to the PortfolioSerializer, replacing it with appropriate SQLite related code. The Portfolio class will still be retained and the local in-memory storage of residence objects preserved also.

App

The project structure, showing the packages you should create, is shown here in Figure 1.

In MyRentApp delete the PortfolioSerializer field and associated code. Also, delete the PortfolioSerializer file.

Observe that we have changed the signature of the Portfolio constructor, removing the serializer object and replacing it with the application context. This generates an error that will be resolved when we later refactor Portfolio.

Here is the refactored class.

package org.wit.myrent.app;

import android.app.Application;
import android.util.Log;
import org.wit.myrent.models.Portfolio;


public class MyRentApp extends Application
{
  static final String TAG = "MyRentApp";
  public Portfolio portfolio;

  protected static MyRentApp app;
  @Override
  public void onCreate()
  {
    super.onCreate();
    portfolio = new Portfolio(getApplicationContext());
    Log.d(TAG, "MyRent app launched");
    app = this;
  }

  public static MyRentApp getApp(){
    return app;
  }
}

Portfolio

Here we summarise the changes and follow with the refactored class:

• We replace the PortfolioSerializer field with a DbHelper.
• In the addResidence method we save to both the local list of residences and to the database.
• In the deleteResidence method we delete from both the local and the database storage.
• We add a new method, updateResidence updates an existing element in the local list and the matching record in the database (it's id remains unchanged).
• Also added is a method refreshResidences that clears the content from the local list of residences and replaces it with the content of the incoming parameter list.

Before proceeding, replace ResidenceFragment.onPause with the following (portfolio now invokes the newly introduced method updateResidence):

  @Override
  public void onPause() {
    super.onPause();
    portfolio.updateResidence(residence);
  }

Here is the refactored Portfolio class:

package org.wit.myrent.models;

import static org.wit.android.helpers.LogHelpers.info;

import java.util.ArrayList;
import java.util.List;

import android.content.Context;
import android.util.Log;

import org.wit.myrent.sqlite.DbHelper;

public class Portfolio
{
  public ArrayList<Residence> residences;
  public DbHelper dbHelper;

  public Portfolio(Context context) {
    try {
      dbHelper = new DbHelper(context);
      residences = (ArrayList<Residence>) dbHelper.selectResidences();
    }
    catch (Exception e) {
      info(this, "Error loading residences: " + e.getMessage());
      residences = new ArrayList<Residence>();
    }
  }

  /**
   * Obtain the entire database of residences
   *
   * @return All the residences in the database as an ArrayList
   */
  public ArrayList<Residence> selectResidences() {
    return (ArrayList<Residence>) dbHelper.selectResidences();
  }

  /**
   * Add incoming residence to both local and database storage
   *
   * @param residence The residence object to be added to local and database storage.
   */
  public void addResidence(Residence residence) {
    residences.add(residence);
    dbHelper.addResidence(residence);
  }

  /**
   * Obtain specified residence from local list and return.
   *
   * @param id The Long id identifier of the residence sought.
   * @return The specified residence if it exists.
   */
  public Residence getResidence(Long id) {
    Log.i(this.getClass().getSimpleName(), "Long id id: " + id);

    for (Residence res : residences) {
      if (id.equals(res.id)) {
        return res;
      }
    }
    info(this, "failed to find residence. returning first element array to avoid crash");
    return null;
  }

  /**
   * Delete Residence object from local and remote storage
   *
   * @param residence Residence object for deletion.
   */
  public void deleteResidence(Residence residence) {
    dbHelper.deleteResidence(residence);
    residences.remove(residence);
  }

  public void updateResidence(Residence residence) {
    dbHelper.updateResidence(residence);
    updateLocalResidences(residence);

  }

  /**
   * Clear local and sqlite residences and refresh with incoming list.
   * @param residences List residence objects
   */
  public void refreshResidences(List<Residence> residences)
  {
    dbHelper.deleteResidences();
    this.residences.clear();

    dbHelper.addResidences(residences);

    for (int i = 0; i < residences.size(); i += 1) {
      this.residences.add(residences.get(i));
    }
  }

  /**
   * Search the list of residences for argument residence
   * If found replace it with argument residence.
   * If not found just add the argument residence.
   *
   * @param residence The Residence object with which the list of Residences to be updated.
   */
  private void updateLocalResidences(Residence residence) {
    for (int i = 0; i < residences.size(); i += 1) {
      Residence r = residences.get(i);
      if (r.id.equals(residence.id)) {
        residences.remove(i);
        residences.add(residence);
        return;
      }
    }
  }
}

DbHelper

Finally, we introduce a copy of the DbHelper class that we developed in the earlier MyRentSQLite app but modified to reflect different Residence id and date types:

package org.wit.myrent.sqlite;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.DatabaseUtils;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import org.wit.myrent.models.Residence;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class DbHelper extends SQLiteOpenHelper
{
  static final String TAG = "DbHelper";
  static final String DATABASE_NAME = "residences.db";
  static final int DATABASE_VERSION = 1;
  static final String TABLE_RESIDENCES = "tableResidences";

  static final String PRIMARY_KEY = "id";
  static final String GEOLOCATION = "geolocation";
  static final String DATE = "date";
  static final String RENTED = "rented";
  static final String TENANT = "tenant";
  static final String ZOOM = "zoom";
  static final String PHOTO = "photo";

  Context context;

  public DbHelper(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
    this.context = context;
  }

  @Override
  public void onCreate(SQLiteDatabase db) {
    String createTable =
        "CREATE TABLE tableResidences " +
            "(id text primary key, " +
            "geolocation text," +
            "date text," +
            "rented text," +
            "tenant text," +
            "zoom text," +
            "photo text)";

    db.execSQL(createTable);
    Log.d(TAG, "DbHelper.onCreated: " + createTable);
  }

  /**
   * @param residence Reference to Residence object to be added to database
   */
  public void addResidence(Residence residence) {
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues values = new ContentValues();
    values.put(PRIMARY_KEY, residence.id);
    values.put(GEOLOCATION, residence.geolocation);
    values.put(DATE, residence.date);
    values.put(RENTED, residence.rented == true ? "yes" : "no");
    values.put(TENANT, residence.tenant);
    values.put(ZOOM, Double.toString(residence.zoom));
    values.put(PHOTO, residence.photo);

    // Insert record
    db.insert(TABLE_RESIDENCES, null, values);
    db.close();
  }

  /**
   * Persist a list of residences
   *
   * @param residences The list of Residence object to be saved to database.
   */
  public void addResidences(List<Residence> residences) {
    for (Residence residence : residences) {
      addResidence(residence);
    }
  }

  public Residence selectResidence(UUID resId) {
    Residence residence;
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = null;

    try {
      residence = new Residence();

      cursor = db.rawQuery("SELECT * FROM tableResidences WHERE uuid = ?", new String[]{resId.toString() + ""});

      if (cursor.getCount() > 0) {
        int columnIndex = 0;
        cursor.moveToFirst();
        residence.id = cursor.getLong(columnIndex++);
        residence.geolocation = cursor.getString(columnIndex++);
        residence.date = Long.parseLong(cursor.getString(columnIndex++));
        residence.rented = cursor.getString(columnIndex++).equalsIgnoreCase("yes") ? true : false;
        residence.tenant = cursor.getString(columnIndex++);
        residence.zoom = Double.parseDouble(cursor.getString(columnIndex++));
        residence.photo = cursor.getString(columnIndex++);
      }
    }
    finally {
      cursor.close();
    }
    return residence;
  }

  public void deleteResidence(Residence residence) {
    SQLiteDatabase db = this.getWritableDatabase();
    try {
      db.delete("tableResidences", "id" + "=?", new String[]{residence.id + ""});
    }
    catch (Exception e) {
      Log.d(TAG, "delete residence failure: " + e.getMessage());
    }
  }

  /**
   * Query database and select entire tableResidences.
   *
   * @return A list of Residence object records
   */
  public List<Residence> selectResidences() {
    List<Residence> residences = new ArrayList<Residence>();
    String query = "SELECT * FROM " + "tableResidences";
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor cursor = db.rawQuery(query, null);
    if (cursor.moveToFirst()) {
      int columnIndex = 0;
      do {
        Residence residence = new Residence();
        residence.id = cursor.getLong(columnIndex++);
        residence.geolocation = cursor.getString(columnIndex++);
        residence.date = Long.parseLong(cursor.getString(columnIndex++));
        residence.rented = cursor.getString(columnIndex++).equalsIgnoreCase("yes") ? true : false;
        residence.tenant = cursor.getString(columnIndex++);
        residence.zoom = Double.parseDouble(cursor.getString(columnIndex++));
        residence.photo = cursor.getString(columnIndex++);
        columnIndex = 0;

        residences.add(residence);
      } while (cursor.moveToNext());
    }
    cursor.close();
    return residences;
  }

  /**
   * Delete all records
   */
  public void deleteResidences() {
    SQLiteDatabase db = this.getWritableDatabase();
    try {
      db.execSQL("delete from tableResidences");
    }
    catch (Exception e) {
      Log.d(TAG, "delete residences failure: " + e.getMessage());
    }
  }

  /**
   * Queries the database for the number of records.
   *
   * @return The number of records in the dataabase.
   */
  public long getCount() {
    SQLiteDatabase db = this.getReadableDatabase();
    long numberRecords = DatabaseUtils.queryNumEntries(db, TABLE_RESIDENCES);
    db.close();
    return numberRecords;
  }

  /**
   * Update an existing Residence record.
   * All fields except record id updated.
   *
   * @param residence The Residence record being updated.
   */
  public void updateResidence(Residence residence) {
    SQLiteDatabase db = this.getWritableDatabase();
    try {
      ContentValues values = new ContentValues();
      values.put(GEOLOCATION, residence.geolocation);
      values.put(DATE, residence.date);
      values.put(RENTED, residence.rented == true ? "yes" : "no");
      values.put(TENANT, residence.tenant);
      values.put(ZOOM, Double.toString(residence.zoom));
      values.put(PHOTO, residence.photo);
      db.update("tableResidences", values, "id" + "=?", new String[]{residence.id + ""});
    }
    catch (Exception e) {
      Log.d(TAG, "update residences failure: " + e.getMessage());
    }
  }

  @Override
  public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL("drop table if exists " + TABLE_RESIDENCES);
    Log.d(TAG, "onUpdated");
    onCreate(db);
  }
}

Test the new feature by:

• populating the list view with a variety of residences, closing the app and reopening. Is the list correct?
• Delete a selection from the list, close and reopen. Again, verify the list is correct.

The application at the end of this lab is available for reference here: myrent-12