Ready to watch this entire course?
Become a member and get unlimited access to the entire skills library of over 4,900 courses, including more Developer and personalized recommendations.Start Your Free Trial Now
- View Offline
- 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
Skill Level Intermediate
So far in my sample application, I'm always showing all tours in my data set, but I'd like to be able to filter the data. So I've added a few features to this version of the project named Filter and Sort. I'll run the application in the emulator and show you the User Interface changes. Just as before, I'm displaying all the data in the List activity, but now I've added three items to the Action Bar. They're labeled All, Cheap, and Fancy. I'll show you where I've set those up.
I'll go the menu which is named activity_main.xml in the menu subfolder under resources. This is where the menu items are defined. There's one for All tours, one for Cheap tours, and one for Fancy tours. And because their showAsAction attribute is set to always, they're showing up in the action bar. I've kept the strings very short so that they'll fit even on a very small device. But in your testing, you should take a look at your target devices and determine whether you can use the action bar in this way.
I've also added code to the MainActivity.java class to listen for the event that occurs when menu items are selected. That code is down here. It's called onOptionsItemSelected, and right now I have a switch statement with two cases. Once for Cheap tours, and one for Fancy tours. I'll add one for All tours. I'll place the cursor within the switch statement, then add a case with the ID R.id.menu_all, and I'll add a break statement. And now I'm ready to handle all three cases, when the user wants to see all tours, and when they want to filter for Cheap and Fancy tours.
I'll save those changes and we'll come back to this in a moment. The first thing you need to do to filter the data from an SQLite database is to add methods to your data source class that know how to do the filtering. So, I'll go to my ToursDataSource class where I already have a method called findAll. If you want to filter and sort on a single table, you can use exactly the same syntax as I'm using here that is calling the query method, and then you'll modify two of the arguments that you pass in.
The third argument, which currently is null, can be set to a string which filters the data. It's the part of the SQL where clause, after the keyword where. And then if you want to order the data or sort it, you use the last argument, and you pass in the name of the column or columns on which we want to sort in ascending or descending keywords, if you want. If you don't remember which of the arguments needs to use in the future, remember, you can move the cursor over the method and you'll see the method signature, and this tells me that the first argument is the table or table name, the second is the array of column names, and the third argument is the selection-- that's what I'll be using--and the last is the orderBy clause.
I'm going to create a new version of this findAll method, and it's going to receive two arguments, a selection value and an orderBy value. Before I make a copy of this method, I'm going to take some of its code that's going to be used elsewhere and separate or extract it to its own separate method. I'll take my declaration of the tours object, and I'll move it down to just above the if condition. Then I'll select all the code, starting with the declaration of the list and including the entire conditional clause.
Then I'll right-click on the selected code, and I'll re-factor and say I want to extract a method. I'll name the method cursorToList. The Method signature preview at the bottom tells me that this method will receive a cursor object and return a list of tours. I'll click OK and that shortens the findAll method. So, now it's calling cursor to list to process the data and get the list back, and there's the result.
I now have a reasonable method cursorToList that I can call from many versions of my find method. I'll make a new version of my find method. I'll select what remains of the findAll method, and I'll duplicate it. Remember that you can duplicate code by holding down the Command and Option keys on Mac or the Ctrl and Alt key on Windows and pressing the down cursor arrow. I'll change the name and signature of my new method. I'll name this new method findFiltered, and I'll pass in two strings.
The first will be selection and the second will be orderBy. Next, I'll change the call to the query method and take these arguments and pass them into the appropriate arguments of query. I'll pass selection into the third argument and orderBy into the final argument. And now I have a method that I can call from anywhere in the Application, and it will let me filter and determine the order of the data that will be returned.
I'll save those changes. Now I'll come back to my main activity class where I have my on OptionsItemSelected method. This method will be called whenever the user selects an item from the menu, either from the main menu or from the action bar. I'll start with the menu item with an ID of menu_cheap. I'm going to be updating the tours object. Before I do this, let's take a look at where the tours object is declared and make sure that it's persisting for the entire activity.
I'll go back up to the top of the code, and I'll see that in this version of the Application, the Tours object is declared outside of any methods, and that's exactly what you want. I also want to re-factor the code that's updating the display so that it's easy to call from anywhere. Right now in my onCreate method, I have this bit of code that's using an array adapter and updating the list display. I'm going to move this into its own separate method.
I'll cut it to the clipboard, then I'll scroll down and find a method named refreshDisplay that I left from a previous exercise, and I'll paste the code in there. Then I'll come back up to where that code was before and I'll call the method. And now it's easy to update the display from anywhere in the activity code. Now I'm really ready. I'll go the menu_cheap menu item. I'll update the Tours list using this code, Tours = datasource.findFiltered.
I'm going to filter on the price column. So, I'll pass in a literal string, "price <= 300". You're working in SQL syntax here, so whatever string you need to pass in must match what the database engine expects. Then I'll set the order by clause, and once again, this is what goes after the keyword orderBy. It'll be a string again, and I'll pass in a value of price_ASC for ascending.
That refreshes the Tours list, and now it's up to me to update the display. So, I'll call my new refreshDisplay method and that should show only the cheap tours. Now I'll do the same thing for fancy tours. I'll select these two lines of codes and copy them, and I'll paste them into this menu choice, menu_fancy. For this menu item selection, I'll say that I only want to see Tours that are at least $1,000. So, I'll change my selection value to >= 1000, and I'd like to see the most expensive Tours first this time.
So I'll change from price_ASC for the orderBy to price_DESC, and again, I'm using standard SQL syntax. Finally, I'll go back to my menu_all command. I'll paste that code in again, and for this one, instead of calling findFiletered, I'll once again call findAll. So, these are my three conditions. When the application first opens, it'll display all the tours, but then the user can switch back and forth between seeing Cheap or Expensive Tours and can go back to the full list anytime they want. I'll save my changes and I'll run the code.
As promised, when the application first opens, it shows all the data in its native order. I'll click Cheap, and now I can only see the most inexpensive Tours. I'll click on Fancy, and now I see the most expensive tours, and they're ordered from expensive to less expensive. And I can go back to seeing all tours anytime I want by clicking on the appropriate menu choice. So, that's how you can add filtering and sorting to your application. As always, put all of your database manipulation code into your datasource class, then call the public methods of the datasource from anywhere else in your application.