This app will add some persistence to the xkcd app to allow users to set certain comics as favorites and see a list of their favorites..
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, check to see if it null before accessing it.
-
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
-
Write a constructor which accepts no parameters and sets the favorite to false and the timestamp to
System.currentTimeMillis
-
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 aXkcdDbComic
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.
You can also use the delete method if so inclined.
-
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 we have covered this.
Now that we have the initial chunk of our SQL interface done, we'll want to start connecting it to the existing XkcdDao
. Thankfully, all of the comic retrieval methods in this class use the same method in the end getImage
. We'll start working there.
- In
getComic
, after the comic and image are downloaded, we'll update our database. First callreadComic
and store the returned object. - If the returned value is null, we'll need to add a new entry in our database. Construct a new
XkcdDbInfo
object , add it to the retrievedXkcdComic
object and pass it to yourcreateComic
method. - If the value isn't null, update it with an updated timestamp, store it in your
XkcdComic
object, and then pass it to theupdateComic
method.
-
In the main activity, add some sort of way the user can mark a comic as favorite and show the result.
I just put a checkbox in the top corner, but you could allow the user to long press the comic and change it's border or do anything else you feel clearly communicates this. Remember, we'll also have to allow them to get to the favorites/history page
-
Whatever, you choose, in your listener for that, you'll need to update the favorite value in your comic object (be sure to allow for removing favorite as well as adding favorite) and pass it to a new method from your
XkcdDao
class calledsetFavorite
. -
This class will accept a
XkcdComic
object and use it to callupdateComic
Remember, since we check each comic when we download it, we don't need to check again here.
Finally, we'll add methods to each of our layers to support.
- In your
XkcdDbDao
write a new method calledreadFavorites
. Select all entries which have the favorite value set to true (or 1 since there isn't a boolean value for SQLite). Return this list. - In your
XkcdDao
object, write a wrapper for this method which just calls it and returns the result. - Create a new activity. This will need to have a button and the ability to display a list of items in your preferred way.
- In this activity, call
readFavorites
and use the result to update your list.
Improve the get random comic feature, by only retrieving unseen comics until they have all been viewed.
Add a list of history to the favorites page