This app will add some persistence to the xkcd app to allow users to set certain comics as favorites and see their view history.
First thing we need to do is design a schema for our database. What data do we want to store? We could store the entire comic. This has it's own advantages and disadvantages, it would improve load times, but also add a lot of local storage. For this project, we'll store a history of viewed comics, when they were viewed and allow the user to mark them as a favorite.
- Our DB will be a single table which will hold 3 columns, an
integer
ID, aninteger
for the timestamp when the item was last read, and aninteger
which will serve as a bool replacement (1 for true and 0 for false)
The contract is where we define our schema in Android.
- Create a new Java class called
XkcdDbContract
. Inside of that class, create a static class calledComicEntry
which willimplements
BaseColumns
.
BaseColumns gives us a default _ID field which we can use and is used when wrapping your database with other classes
- Create a constant data member for your table's name and one for each of the column names.
We don't need a data member for ID as it is provided with the BaseColumns
- In db-fiddle, write a CREATE TABLE query to build your comics table as defined in Part 1
- In your contract class, create a constant
String
calledSQL_CREATE_TABLE
. Copy the query from db-fiddle to this member - Replace all column names in your create member with references to the data members.
- Create a constant data member called
SQL_DELETE_TABLE
this will delete the entire table
Use the
DROP TABLE IF EXISTS
query to delete your table, just put your table name at the end
- Be sure that your SQL queries all end in a semi colon.
- Create a new
XkcdDbHelper
class whichextends
SQLiteOpenHelper
- Add data members for DATABASE_VERSION and DATABASE_NAME
- Add a constructor to the class which accepts a
Context
and calls the super constructor passing in the context, db name,null
, and db version. - Override the
onCreate
method. CallexecSQL
on theSQLiteDatabase
object which is passed into the method. Pass theSQL_CREATE_TABLE
data member from your contract class toexecSQL
- Override the
onUpgrade
method. CallexecSQL
on the passed database and pass in yourSQL_DELETE_TABLE
data member.
We will need to implement the CRUD(L) functions in a DAO class to facilitate database interactions and make them clearer to understand. After the CRUD functions are implemented using ids, we can add additional functions as necessary, like filtered read, broader updates, or joins as necessary.
-
First, we need a method to get a singleton instance of the database.
A singleton is an object which can only be instantiated once. We can do this by only having private constructors and creating a
getInstance
method which would normally return a reference to the object instance, however, in this case, it won't return anything and will just store the value in a static member so the name will be different -
Create a data member of type
SQLiteDatabase
-
Write a method called
initializeInstance
which will create an instance of yourXkcdDbHelper
class, callgetWritableDatabase
on the object and then store that all in theSQLiteDatabase
object. This is all done if theSQLiteDatabase
object isnull
In all methods which need to access the database member, call the
initializeInstance
method first. -
We now need an object to store our db results in as it will store things that aren't in the web api objects. Create a class called
XkcdDbInfo
-
In this class, add data members to match the ones in your db schema (the id already exists in the comic object so that isn't necessary)
-
Add getters and setters
-
In your
XkcdComic
object, add a data member of typeXkcdDbInfo
, as well as a getter and setter for it. -
Write a method called
createComic
which accepts aXkcdComic
object. -
Create a
ContentValues
variable in this method and assign it using the empty constructor for that class. -
call the
put
method on that variable for each column in your databasebe sure to put the column name constant as the first parameter and the desired value from the comic object as the second parameter
-
Once the values object is built, call
insert
on the database object and pass the name of the table,null
, and the values object -
Write a method called
readComic
, this will accept an int id. It will return aXkcdDbInfo
object. -
In db-fiddle, write a query to read a single entry from the table when provided with the id
-
Take that query and copy it into a string value in your method.
-
Replace the hard coded ID with the one provided in the method signature
-
Call the
rawQuery
method on your database, pass it in your query string and a null value. Store the resulting cursor. -
If a call to
moveToNext
on that cursor returns true retrieve all the values from that cursor and stores them in aXkcdDbInfo
objectremember, there are two parts to retrieving each piece of data from a cursor, first you call
getInt
on it (or whatever type you want), then you must pass in the column index which is done by callinggetColumnIndexOrThrow
and passing in the column name -
Return the constructed
XkcdDbInfo
objectFor added peace of mind call
getCount
on your cursor and make sure it returns 1 before getting the data -
Write a method called
updateComic
which accepts aXkcdDbInfo
object. -
First we want to write a where clause and check it. Create two String variables, one for the where clause and one for the whole query.
Write the where clause then write the rest of the query and tack the where clause onto the end of it
-
Call
rawQuery
with the complete clause and store the result -
Check that the result affects the correct number of entries (in this case, 1) by checking the
getCount
method -
Build a
ContentValues
object like in the create method. -
Pass it into the database's
update
method, then pass in the table name, your values object, the where clause, then null -
Create a method called
deleteComic
which accepts an id -
Test your where clause like in the update method
-
Write a DELETE query using your where clause.
-
Call
execSQL
with your query.execSQL will execute an SQL query like rawSQL will, but will not return a result.
-
Test your methods by entering test data, reading it, updating it and then deleting it. There is no need to attach this to the GUI yet, just use the debugger or log for now.
If you get to this point, you can continue or wait until tomorrow when I have these instructions complete.
Improve the get random comic feature, by only retrieving unseen comics until they have all been viewed.