Creating a Data Access Object with SQLite

For this fifth Critical Thinking assignment you will modify an existing app that tracks business expenses. Download the existing app from and explore the application. There are two inner classes: ExpenseItem, which defines constants used in mapping the Expense data to the database, and Helper. You should see that the Expense class is a simple POJO with properties of amount, description, and expenseDate.

A DAO is an excellent and widely-used design pattern where the code to access a data source is encapsulated within a single class.

Open edu.csuglobal.expenses.data.DAO.java.

Inside the DAO constructor near line 35, add the code to initialize an instance of the database helper class at the location marked TODO.

Locate the queryExpenses() method and complete the steps marked TODO.

Expand app | java |edu.csug.expenses(androidTest) | data.

Right-click DAOTest and select Run 'DAOTest'. If you are offered a choice of two DAOTest options, take the first one, which has the Android symbol next to it. When prompted to choose a device, select the Galaxy-Nexus AVD and click OK.

Click the 4:Run button at the bottom of the Android Studio window to show the test results.

Open edu.csug.expenses.ExpensesListActivity.java. In the following steps, you may see class names marked as errors due to the required imports not being in the code. The simple solution is to click the class name. When prompted, press . Android Studio will add the required import.

Locate the onCreate() method and complete the steps marked TODO.

If it is not already running, start the Galaxy-Nexus AVD. Make sure the app is selected next to the Run button, then run the application by clicking the green run arrow as normal. You should see the list of sample expenses.

At the end of the onCreate() method in ExpensesListActivity, add a call to registerForContextMenu(). Use mList as the reference for the View; this should display the context menu.

Override onCreateContextMenu() and inflate the menu with an id of R.menu.context_menu. Right-clicking on the file and selecting Generate | Override Methods... is the easy way to create the new method.

Save and execute the application.

Click and hold on one of the expenses in the list. Your context menu should appear. Of course, there is no associated functionality yet.

Add a new method public int deleteExpensesById(int id) to the DAO.

Create a where clause, get an instance of a writable database, then call the delete method.

Uncomment the onContextItemSelected() code at the bottom of ExpensesListActivity. Organize imports as needed.

Test your work.

Assignment deliverables (all in a ZIP file):

A screenshot showing the execution of your application.

A detailed overview of the steps taken to complete your application.

The project folder containing all source code for your application.

Ensure that your assignment deliverables are attached before submitting your assignment.

package edu.csug.expenses.data; import java.util.HashMap; import android.content.ContentValues; import android.content.Context; import android.content.res.Resources; import android.database.Cursor; import android.database.SQLException; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteQueryBuilder; import android.text.TextUtils; import android.util.Log; /** * Data Access Object supporting the Expenses Application * @author Course 2771 Development Team * */ public class DAO { private static final String TAG="DAO"; private DatabaseHelper mDbHelper; /** * A projection map used to select columns from the database */ private static HashMap sExpensesProjectionMap; /* * Static initializer block used to configure the projection map (column mappings) */ static { // Creates a new projection map instance. The map returns a column name // given a string. The two are usually equal. sExpensesProjectionMap = new HashMap(); // Maps the string "_ID" to the column name "_ID" sExpensesProjectionMap.put(Expense.ExpenseItem.COLUMN_NAME_ID, Expense.ExpenseItem.COLUMN_NAME_ID); sExpensesProjectionMap.put(Expense.ExpenseItem.COLUMN_NAME_DESCRIPTION, Expense.ExpenseItem.COLUMN_NAME_DESCRIPTION); sExpensesProjectionMap.put(Expense.ExpenseItem.COLUMN_NAME_AMOUNT, Expense.ExpenseItem.COLUMN_NAME_AMOUNT); sExpensesProjectionMap.put(Expense.ExpenseItem.COLUMN_NAME_EXPENSE_DATE, Expense.ExpenseItem.COLUMN_NAME_EXPENSE_DATE); } /** * Normal constructor for the DAO * @param context */ public DAO(Context context) { mDbHelper = new DatabaseHelper(context); } /** * Special constructor to support unit testing * @param context * @param testMode if true then an in-memory DB will be created which avoids breaking the real db * during testing */ protected DAO(Context context, boolean testMode) { if(testMode){ mDbHelper = new DatabaseHelper(context, testMode); } else { mDbHelper = new DatabaseHelper(context); } } /** * Deletes expenses by the id of the expense * @param id * @return number of rows deleted - should be 1 for success */ public int deleteExpensesById(long id) { String finalWhere = Expense.ExpenseItem.COLUMN_NAME_ID + // The ID column name " = " + // test for equality id; SQLiteDatabase db = mDbHelper.getWritableDatabase(); // Performs the delete. int count = db.delete( Expense.ExpenseItem.TABLE_NAME, // The database table name. finalWhere, // The final WHERE clause null // The incoming where clause values. ); return count; } /** * Deletes expenses as specified by the where clause composed of where and whereArgs * @param where where clause - if null, matches all rows * @param whereArgs values for ? place holders in the where clause * @return number of rows deleted */ public int deleteExpenses(String where, String[] whereArgs) { // Opens the database object in "write" mode. SQLiteDatabase db = mDbHelper.getWritableDatabase(); int count = db.delete( Expense.ExpenseItem.TABLE_NAME, // The database table name where, // The incoming where clause column names whereArgs // The incoming where clause values ); // Returns the number of rows deleted. return count; } /** * Inserts an Expense item which is expressed in expenseValues * * @param expenseValues the values to populate the Expense item with. Any that are missing will * be defaulted * @return the id of the expense added. This is in fact the SQLite ROWID value */ public long insertExpense(ContentValues expenseValues) { // If the incoming values map is not null, uses it for the new values. if (expenseValues != null) { expenseValues = new ContentValues(expenseValues); } else { // Otherwise, create a new value map expenseValues = new ContentValues(); } // Gets the current system time in milliseconds Long now = Long.valueOf(System.currentTimeMillis()); // If the values map doesn't contain the expense date, sets the value to the current // time. if (expenseValues.containsKey(Expense.ExpenseItem.COLUMN_NAME_EXPENSE_DATE) == false) { expenseValues.put(Expense.ExpenseItem.COLUMN_NAME_EXPENSE_DATE, now); } // If the values map doesn't contain a description, sets the value to the default description. if (expenseValues.containsKey(Expense.ExpenseItem.COLUMN_NAME_DESCRIPTION) == false) { Resources r = Resources.getSystem(); expenseValues.put(Expense.ExpenseItem.COLUMN_NAME_DESCRIPTION, r.getString(android.R.string.untitled)); } // If the values map doesn't contain note text, sets the amount to 0.0f. if (expenseValues.containsKey(Expense.ExpenseItem.COLUMN_NAME_AMOUNT) == false) { expenseValues.put(Expense.ExpenseItem.COLUMN_NAME_AMOUNT, 0.0f); } // Opens the database object in "write" mode. SQLiteDatabase db = mDbHelper.getWritableDatabase(); // Performs the insert and returns the ID of the new expense item. long rowId = db.insert( Expense.ExpenseItem.TABLE_NAME, // The table to insert into. Expense.ExpenseItem.COLUMN_NAME_DESCRIPTION, // A hack, SQLite sets this column value to null // if values is empty. expenseValues // A map of column names, and the values to insert // into the columns. ); //Log.d(TAG, "Insert ROWID= " + rowId); if(rowId < 0){ throw new SQLException("Failed to insert expense."); } return rowId; } /** * Queries the database for a single expense item based on it's ID * @param expenseId the ID of the expense item to return * @param projection the columns to populate in the cursor * @return a Cursor containing the data for the Expense item */ public Cursor queryExpenseById(long expenseId, String[] projection) { // Constructs a new query builder and sets its table name SQLiteQueryBuilder qb = new SQLiteQueryBuilder(); qb.setTables(Expense.ExpenseItem.TABLE_NAME); qb.appendWhere(Expense.ExpenseItem.COLUMN_NAME_ID + // the name of the ID column "=" + expenseId); return performQuery(qb, projection, null, null, null); } /** * General query method for expenses. Allows where clause and sort order to be * specified @see {@link android.database.sqlite.SQLiteQueryBuilder#query(SQLiteDatabase, String[], String, String[], String, String, String)} * @param projection * @param selection A filter declaring which rows to return * @param selectionArgs You may include ?s in selection, which will be replaced by the values from selectionArgs * @param sortOrder How to order the rows, formatted as an SQL ORDER BY * @return */ public Cursor queryExpenses(String[] projection, String selection, String[] selectionArgs, String sortOrder) { // Constructs a new query builder and sets its table name SQLiteQueryBuilder qb = new SQLiteQueryBuilder(); qb.setTables(Expense.ExpenseItem.TABLE_NAME); return performQuery(qb, projection, selection, selectionArgs, sortOrder); } /** * Helper method to map public query methods onto the SQLQueryBuilder class methods * @param qb * @param projection * @param selection * @param selectionArgs * @param sortOrder * @return */ private Cursor performQuery(SQLiteQueryBuilder qb, String[] projection, String selection, String[] selectionArgs, String sortOrder) { // Opens the database object in "read" mode, since no writes need to be done. SQLiteDatabase db = mDbHelper.getReadableDatabase(); qb.setProjectionMap(sExpensesProjectionMap); String orderBy; // If no sort order is specified, uses the default if (TextUtils.isEmpty(sortOrder)) { orderBy = Expense.ExpenseItem.DEFAULT_SORT_ORDER; } else { // otherwise, uses the incoming sort order orderBy = sortOrder; } Log.i(TAG, "performQuery. Projection: " + projection); /* * Performs the query. If no problems occur trying to read the database, then a Cursor * object is returned; otherwise, the cursor variable contains null. If no records were * selected, then the Cursor object is empty, and Cursor.getCount() returns 0. */ Cursor c = qb.query( db, // The database to query projection, // The columns to return from the query selection, // The columns for the where clause selectionArgs, // The values for the where clause null, // don't group the rows null, // don't filter by row groups orderBy // The sort order ); return c; } /** * Updates the expense item specified by the expenseId parameter. If an ID parameter is also specified in the values * parameter it will be ignored. * @param expenseId * @param values * @return */ public int updateExpenseById( long expenseId, ContentValues values) { // Opens the database object in "write" mode. SQLiteDatabase db = mDbHelper.getWritableDatabase(); int count; String where = Expense.ExpenseItem.COLUMN_NAME_ID + // The ID column name " = " + expenseId; // test for equality // Does the update and returns the number of rows updated. count = db.update( Expense.ExpenseItem.TABLE_NAME, // The database table name. values, // A map of column names and new values to use. where, // The final WHERE clause to use // placeholders for whereArgs null // The where clause column values to select on, or // null if the values are in the where argument. ); // Returns the number of rows updated. return count; } /** * Updates all of the expenses matching where and whereArgs with the values supplied. * @param values * @param where where clause - if null, matches all rows * @param whereArgs values for ? place holders in the where clause * @return */ public int updateExpenses( ContentValues values, String where, String[] whereArgs) { // Opens the database object in "write" mode. SQLiteDatabase db = mDbHelper.getWritableDatabase(); int count; // Does the update and returns the number of rows updated. count = db.update( Expense.ExpenseItem.TABLE_NAME, // The database table name. values, // A map of column names and new values to use. where, // The where clause column names. whereArgs // The where clause column values to select on. ); return count; } /** * Used by test classes to directly access the database helper * @return a handle to the database helper object for the provider's data. */ DatabaseHelper getDbHelperForTest() { return mDbHelper; } } 

*************************************************

package edu.csug.expenses.data; import android.content.ContentValues; import android.content.Context; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; import java.util.Calendar; public class DatabaseHelper extends SQLiteOpenHelper { private boolean mTestMode = false; private static final String DATABASE_NAME = "expenses.db"; // Should this be qualified?   private static final int DATABASE_VERSION = 1; private static final String EXPENSES_TABLE_CREATE = "CREATE TABLE " + Expense.ExpenseItem.TABLE_NAME + " (" + Expense.ExpenseItem.COLUMN_NAME_ID + " INTEGER PRIMARY KEY," + Expense.ExpenseItem.COLUMN_NAME_DESCRIPTION + " TEXT, " + Expense.ExpenseItem.COLUMN_NAME_AMOUNT + " REAL, " + Expense.ExpenseItem.COLUMN_NAME_EXPENSE_DATE + " TEXT " + ");"; public DatabaseHelper(Context context) { // If the 2nd parameter is null then the database is held in memory -- this form creates a file backed database  super(context, DATABASE_NAME, null, DATABASE_VERSION); // TODO Auto-generated constructor stub  } /**  * Alternative constructor for test mode  * @param context  * @param testMode state of flag is irrelevant. The presence of the 2nd argument causes the in-memory db to be created  */  public DatabaseHelper(Context context, boolean testMode) { // If the 2nd parameter is null then the database is held in memory -- this form creates an in memory database  super(context, null, null, DATABASE_VERSION); mTestMode = testMode; // TODO Auto-generated constructor stub  } @Override public void onCreate(SQLiteDatabase db) { // TODO Auto-generated method stub  // NOTE: the db passed in here seems to be linked to the file system  // The exec does effect the database.  db.execSQL(EXPENSES_TABLE_CREATE); // For the purposes of the course, add some dummy data items into the database  if(!mTestMode){ // Don't populate the DB in test mode  Calendar cal = Calendar.getInstance(); cal.add(Calendar.DATE, -4); insertExpense(db, 1, "Samples Restaurant", 28.50, cal.getTimeInMillis() + ""); cal.add(Calendar.DATE, -1); insertExpense(db, 2, "Night in 6 star hotel", 22222.99, cal.getTimeInMillis() + ""); cal.add(Calendar.DATE, -1); insertExpense(db, 3, "Lunch at the Restaurant at the End of the Universe", 9991.99, cal.getTimeInMillis() + ""); cal.add(Calendar.DATE, -10); insertExpense(db, 4, "Travel to Restaurant at the End of the Universe", 132221.99, cal.getTimeInMillis() + ""); } } private void insertExpense(SQLiteDatabase db, long id, String description, double amount, String date) { ContentValues values = new ContentValues(); values.put(Expense.ExpenseItem.COLUMN_NAME_ID, id); values.put(Expense.ExpenseItem.COLUMN_NAME_DESCRIPTION, description); values.put(Expense.ExpenseItem.COLUMN_NAME_AMOUNT, amount); values.put(Expense.ExpenseItem.COLUMN_NAME_EXPENSE_DATE, date); db.insert(Expense.ExpenseItem.TABLE_NAME, null, values); } /**  * Not sure what to do with this. Could ignore for the course...  */  @Override public void onUpgrade(SQLiteDatabase db, int arg1, int arg2) { // TODO Auto-generated method stub   } }