New Feature: Playlist Center! Pick a topic and let our playlists guide the way.

Start learning with our library of video tutorials taught by experts. Get started

Android SDK: Local Data Storage
Illustration by Don Barnett

Working with multiple database tables


From:

Android SDK: Local Data Storage

with David Gassner

Video: 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.
Expand all | Collapse all
  1. 6m 34s
    1. Welcome
      50s
    2. What you should know before starting this course
      2m 11s
    3. Using the exercise files
      3m 33s
  2. 21m 6s
    1. Exploring local data storage options
      5m 58s
    2. Configuring Eclipse and the Android Developer Tools
      5m 29s
    3. Creating an Android virtual device
      3m 23s
    4. Creating a new Android project
      6m 16s
  3. 31m 30s
    1. Using preferences in Android apps
      5m 28s
    2. Defining preferences with Java
      9m 31s
    3. Defining shared preferences with an activity
      10m 50s
    4. Listening for changes to shared preferences
      5m 41s
  4. 51m 13s
    1. Creating and reading files in internal storage
      10m 18s
    2. Creating and reading JSON data files
      9m 48s
    3. Working with files in external storage
      11m 22s
    4. Parsing a read-only XML file with XmlPullParser
      11m 46s
    5. Parsing a read-only XML file with JDOM
      7m 59s
  5. 1h 8m
    1. Creating a new SQLite database
      2m 52s
    2. Defining a database with SQLiteOpenHelper
      12m 12s
    3. Managing the database with a DataSource class
      9m 38s
    4. Inserting data into a database table
      10m 29s
    5. Retrieving and displaying data
      11m 44s
    6. Importing data from XML to SQLite
      5m 15s
    7. Filtering and sorting data
      9m 27s
    8. Accessing a database from the command line
      6m 46s
  6. 42m 0s
    1. Improving the data display
      9m 29s
    2. Passing user-selected data to a detail activity
      11m 36s
    3. Working with multiple database tables
      10m 28s
    4. Deleting data from database tables
      10m 27s
  7. 49s
    1. Where to go from here
      49s

Watch this entire course now—plus get access to every course in the library. Each course includes high-quality videos taught by expert instructors.

Become a member
please wait ...
Android SDK: Local Data Storage
3h 41m Intermediate Jan 25, 2013

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.

Topics include:
  • 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
Subjects:
Developer Mobile Apps Programming Languages
Software:
Android Java
Author:
David Gassner

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.

 
Share a link to this course

What are exercise files?

Exercise files are the same files the author uses in the course. Save time by downloading the author's files instead of setting up your own files, and learn by following along with the instructor.

Can I take this course without the exercise files?

Yes! If you decide you would like the exercise files later, you can upgrade to a premium account any time.

Become a member Download sample files See plans and pricing

Please wait... please wait ...
Upgrade to get access to exercise files.

Exercise files video

How to use exercise files.

Learn by watching, listening, and doing, Exercise files are the same files the author uses in the course, so you can download them and follow along Premium memberships include access to all exercise files in the library.
Upgrade now


Exercise files

Exercise files video

How to use exercise files.

For additional information on downloading and using exercise files, watch our instructional video or read the instructions in the FAQ.

This course includes free exercise files, so you can practice while you watch the course. To access all the exercise files in our library, become a Premium Member.

join now Upgrade now

Are you sure you want to mark all the videos in this course as unwatched?

This will not affect your course history, your reports, or your certificates of completion for this course.


Mark all as unwatched Cancel

Congratulations

You have completed Android SDK: Local Data Storage.

Return to your organization's learning portal to continue training, or close this page.


OK
Become a member to add this course to a playlist

Join today and get unlimited access to the entire library of video courses—and create as many playlists as you like.

Get started

Already a member?

Become a member to like this course.

Join today and get unlimited access to the entire library of video courses.

Get started

Already a member?

Exercise files

Learn by watching, listening, and doing! Exercise files are the same files the author uses in the course, so you can download them and follow along. Exercise files are available with all Premium memberships. Learn more

Get started

Already a Premium member?

Exercise files video

How to use exercise files.

Ask a question

Thanks for contacting us.
You’ll hear from our Customer Service team within 24 hours.

Please enter the text shown below:

The classic layout automatically defaults to the latest Flash Player.

To choose a different player, hold the cursor over your name at the top right of any lynda.com page and choose Site preferencesfrom the dropdown menu.

Continue to classic layout Stay on new layout
Exercise files

Access exercise files from a button right under the course name.

Mark videos as unwatched

Remove icons showing you already watched videos if you want to start over.

Control your viewing experience

Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.

Interactive transcripts

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.

Are you sure you want to delete this note?

No

Thanks for signing up.

We’ll send you a confirmation email shortly.


Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

Keep up with news, tips, and latest courses with emails from lynda.com.

Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

   
submit Lightbox submit clicked
Terms and conditions of use

We've updated our terms and conditions (now called terms of service).Go
Review and accept our updated terms of service.