Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
My sample application so far uses a single database table to store tours data. I'm now going to add some new functionality to the application. I'm going to let the user add a tour to a list that we'll call mytours--a custom list-- and then I'll let them show a filtered view that only shows the tours that they've selected. I'll need a second database table to manage this, and I'll define it first in my database open helper class. Now this entire task will take quite a bit of code, so as I have in the past, I've provided a typing help file called typinghelp.txt that's a part of the Multiple Tables project.
I'll start with the first snippet where I'm defining the table mytours. There are two declarations here, one for the new table name, and one for an SQL create statement that will create the table in the database. I'll copy that code to the clipboard, and I'll go to the class ToursDBOpenHelper.java. In the class I'll place the cursor after all of the existing constants and then I'll paste this new code into place. So now I have constants to define two tables, one called tours and one called mytours.
This new table has only a single column, an INTEGER PRIMARY KEY. It's not auto-incrementing because I'm going to add explicit values to this table. Then I'll be joining them together at runtime to show the list that the user has selected. Now I need to use these constants. I'll scroll down toward the bottom of the class and I'll go to the on create method. I have an existing line of code that's executing the create statement for the tours table. I'll duplicate that code, and I'll change the constant that I'm calling from TABLE_CREATE to TABLE_MYTOURS_CREATE.
Then I'll also add code to drop that table when I'm upgrading the database. I'll go to the onUpgrade method, and I'll duplicate the existing drop statement and I'll change this one from TABLE_TOURS to TABLE_MYTOURS, my new table name constant. Finally, to trigger the database upgrade, I'll increment the database version. I'll scroll up to the top of the class and locate my database version constant and I'll increment it form 1 to 2.
Remember, you can only increment your database version. You can't decrement it and it must be a whole number. I'll save those changes and I'll test them. Before I test, I'm going to open my LogCat console. If you have any existing messages in the LogCat console, clear them and then make sure that you're filtering on the EXPLORECA tag. Now I'll run the application in the emulator. As the application comes to the screen, Android detects the difference between the old version and the new version of the database and the code is correctly executed to upgrade it.
So now my database has two tables, TOURS and MYTOURS, and I'm ready to add some SQL statements that can add data to the table and retrieve it. Next, I'll go to my DataSource class, ToursDataSource.java. As I described in a previous video in this course, you can either have multiple data sources, one for each table, or if your database structure is simple enough, you can manage the whole thing with a single data source, and that's what I'm going to do.
I'm going to be adding a couple of methods. One to insert a new row into the new mytours table and one to join the mytours and the tours table together to get back a filtered list of only the tours that the user has selected. Once again, I'll go over to the typinghelp file. Here is my next to code snippet. This is the code that will add a new row to mytours. I'll select and copy it, then go back to the DataSource class, I'll go down to the bottom of the DataSource class and I'll paste in the new method after the existing method, cursor to list. Let's take a look at the method.
Just as with the previous insert method, I'm creating an instance of the ContentValues class. I'm adding a single value, the COLUMN_ID of a tour object that's been passed in. Then I'm inserting that into the MYTOURS table and getting back a value. Then I'm testing whether the result is -1. Here's why. If the user tries to insert an item that's already in the mytours table, that will cause an index conflict, because remember, I set that column as a primary key.
When that happens, the insert method returns a value of -1. So I'm returning an expression that says result not equal to -1. If that expression resolves to true, that means that the insert succeeded. I've added the new the row. And if returns false, that means that the item was already in the database table. Now I'll add code to the application to call this method. I'll do this from the TourDetailActivity class. In this activity, I've added a new menu item that's labeled add to mytours and I've added some code to this class that will handle that menu item.
The ID of the menu item is menu_add and here is the code that I'll place in this position. I'll create an if/else statement. I'll type if and press Ctrl+Space and then choose if else. I'll set the condition to datasource.addToMyTours, and I'll pass in the current tour object which was created when this activity came to the screen. If the call to this method returns true, that means a new item was added to the mytours list.
And for now, all I'm going to do is call the log class' I method, and I'll pass in the LOGTAG constant and a message of Tour added. And if it fails, I'll put a message of Tour not added. I'll duplicate that line of code and move it down and I'll change the output. Notice I'm getting an error on the LOGTAG constant. That's because it was initially commented out in this version of the activity.
I'll remove the comment and scroll back down and all of the errors have been cleared. I'll save my code in the Activity class and also in the DataSource class. I'll make sure I don't have any errors, and now I'll run the application with this new code in place. When the application comes to the screen, I'll click in to an item in the list and then I'll click the menu item ADD TO MY TOURS. Nothing happens in the application yet, but I'll go back to Eclipse and once again go to my LogCat console and I see the log message Tour added.
Now I'll go back to the application and try to add the same tour again, and this time I get the message Tour not added. I'll go back to the application and click the back button to go back to the tour list. I'll click into another item. I'll click ADD TO MY TOURS and I get that Tour added and by now I should have two items in the mytours table. But I'm not quite done yet. Now I'm going to add code that retrieves that data and displays just the selected tours.
I'll go back to my ToursDataSource class. I've already added this method to add new a row to my tours. Now I'll add a method that joins this table with the tours table and returns just the selected tours. Once again, I have some help in the typinghelp file. I'll scroll down to the bottom and I'll select this method, findMyTours. I'll copy it to the clipboard, go back to the DataSource class, and paste it into place.
And let's take a look at the code. There is a query variable which is a select statement that joins the two tables together--tours and mytours--on the primary key columns, both of which are named tourId. I'm then using a method called rawQuery. When you call the rawQuery method, the first argument is a string representing the SQL statement and the second argument can be an array of parameter values. This SQL statement doesn't have any parameters, so I'm just passing in null.
Then there is a Log output telling me how many rows I got back. I'm processing the cursor using my existing method cursorToList and returning that data. I'll save the changes to the DataSource, and then I'll go to my MainActivity class and add some code to call the new method. My MainActivity has a new menu choice labeled mytours, and when the user selects that menu choice, they'll trigger a call to the onOptionsItemSelected method and I'll add the code to call the new method here. The code will tours = datasource.findMyTours.
And then I'll call the refreshDisplay method, and the screen will update with just the selected tours. I'll save and run the application. Now I'm re-launching the application, but I'm not going to go add a new item to mytours. I'm just going to do the filtering right now to show that the data is persisting in storage even between application launches. I'll click MY TOURS and there are the tours that I previously selected.
I'll click on ALL and then I go back to all tours. I'll drag the list up a bit and I'll choose another tour. I'll click on ADD TO MY TOURS. I'll then return to the main display and filter again, and I'll see that it's been correctly added. So my two tables are now a part of my database structure, and I've given the user an interface that lets them add items to that second table and use the second table to create a filtered view.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 98677 Viewers
61 Video lessons · 85992 Viewers
71 Video lessons · 69843 Viewers
56 Video lessons · 102133 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.