Working with multiple database tables
Video: Working with multiple database tablesMy 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.
- Retrieving and displaying data
- Filtering and sorting data
Viewers: in countries Watching now:
Take your Android programming skills to the next level with the Android built-in framework that enables local data management in text files and SQLite-based relational databases. This course shows you how to create datacentric apps for Android devices, using SQLite, Java, and the built-in android.database packages. Author David Gassner describes how to define shared preferences, work with JSON and XML files in internal and external data stores, and create new local SQLite databases.
- Exploring local data storage options
- Creating an Android virtual device
- Starting a new project
- Defining preferences with Java and activities
- Creating and reading JSON and XML data files
- Creating a new SQLite database
- Inserting and retrieving data in the database
Working with multiple database tables
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.
There are currently no FAQs about Android SDK: Local Data Storage.