Deleting data from database tables
Video: Deleting data from database tablesAt this point, my tour finder application uses two tables, one for the primary tour data, and one for a mytours table that tracks tours that the user has selected and I can look at a filtered view showing just those tours. I'd like to add functionality to the application that lets the user remove an item from that custom list. I've added some code to the application so that when the user goes into an item from the mytours list, they now see a menu choice, REMOVE FROM MY TOURS .
- 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
Deleting data from database tables
At this point, my tour finder application uses two tables, one for the primary tour data, and one for a mytours table that tracks tours that the user has selected and I can look at a filtered view showing just those tours. I'd like to add functionality to the application that lets the user remove an item from that custom list. I've added some code to the application so that when the user goes into an item from the mytours list, they now see a menu choice, REMOVE FROM MY TOURS .
I'll show you how I did that. First, I'll go the XML file, tour_detail.xml that you can find in the resource area's menu folder. I've added a second item to the menu. Its ID is menu_delete, and this string menu_delete equates to the string you see on the screen, Remove from My Tours. Then I added logic to TourDetailActivity.java. In the CreateOptionsMenu method shown here, I now have code that filters which menu item is selected.
If we came from the My Tours view, then we are going to show the delete item. And if we didn't come from My Tours, then we'll show the Add Item. And here is how we know whether we came from My Tours. In the MainActivity class, I've added a boolean field called isMyTours, and each time I'm presenting data I'm setting that value to true or false. In the on create method, when I first present the data, I'm setting it to false. And when a user selects an item from the list, for all but the filter from MyTours I'm also setting it to false, but here I'm setting it to true.
And finally, I'm passing that value into the new activity by adding it as an extra right here in the onListItemClick method. So the entire chain of logic is, when the user clicks on a filter, I determine whether they're watching my tours or not. Then when they open the detail activity, I pass that value in and the detail activity evaluates it and decides what menu choices to present. So that's just the management of the user interface. Now we come to the important point for this course, how to actually remove data from the database.
As with all code that deals directly with the database, I recommend that you put this into the DataSource class. I'll go to my ToursDataSource.java file and open it to full screen, and I'll go down to the bottom of the class. I'm going to add a new method that I will call removeFromMyTours, and I'll place it right here after the addToMyTours method. I'll make it a public boolean method, and again, it will be called removeFromMyTours, and just like addToMyTours, it will receive a single argument data type as the tour class. Next, I'll create a selection string.
This will be a simple value starting with the name of the primary key column, then an equals operator, and then the ID of the tour object that was passed in as an argument. I'll create a String named where and I'll start it with the name of the column, which I'll get from a constant of my OpenHelper class, ToursDBOpenHelper.column_ID. Then I'll append an equals operator and I'll append to that the ID of the current tour, which I'll get from tour.getId.
So now I have a string that I can use to filter a delete operation. Make sure you've typed this part correctly, because if you get it a little bit wrong you can actually remove all the data from the table. So next, I'll call the delete method of the database object. This is similar to the insert method. It's going to create an SQL statement for me that deletes the row from the table filtered on the ID that I pass in. The code will look like this. First, I'll declare a variable named result, data typed as an integer.
Then I'll get that value from database.delete. Notice that the delete method returns an int, not a long value like the insert method. I'll pass in the name of the table that I want to delete a row from, again, I'll use a constant, ToursDBOpenHelper.TABLE_MYTOURS. Next, I'll pass in the where clause, and that's the string that I just created. And finally for the last argument, I'll pass in a value of null.
I don't need to pass in any arguments because I've included everything I need in the where clause. Finally, I'll do a return and I'll evaluate the result. When you call the delete method, the result would be a numeric value indicating how many rows were affected. The same thing is true of the update method of the database object. So if I get back a value of 1, that means that I successfully deleted one row from the database table. So I'll pass back this expression, result == 1, and if it's any other value I'll pass back false.
So that's all the work I need to do in the DataSource class. Now I'll go to the DetailActivity class where I'll make a call to this method. I've added code to the onOptionsItemSelected method to handle the new menu delete menu choice. I'll use this code to delete the selected tour from the database table. If datasource.removeFromMyTours then I'll pass in the current tour object and if I get back a value of true, that means the data was successfully removed from the table.
I'm going to set a result from this activity that can be read from the launching activity. I'll call the setResult method and pass in a value of -1. This value can be anything I want. It's simply a flag that will be read by the sending activity to determine what happened. My logic says I removed an item from the database table, so it's a -1. I have one less item. Then I'll call a method called finish. When you launch an activity from another activity, calling the finish method removes the current activity and goes back to the previous one.
So my logic is if I successfully removed the row from the database table, then tell the sending activity that it happened and close the current activity. I'll save those changes and I have one more code change to make before I test the application. I'll go to the MainActivity class. Notice in this version of the application I launched the DetailActivity class with a method called startActivitytForResult, and then I passed in a value known as a request code.
I'm using a constant, TOUR_DETAIL_ACTIVITY. It doesn't matter what the value of this constant is, I just need to use it to find out when I come back to this activity where I came from. So now I'll add one more method to this class. When I've started this second activity and then I come back, that will trigger a method called onActivityResult. It's a method that's a part of the super class, so to create it all I need to do is type the beginning of the method name, press Ctrl+Space and then choose a method from the list.
OnActivityResult receives three arguments, requestCode, resultCode, and Intent. I'll use conditional logic to find out whether I came from the DetailActivity and whether something was removed from the database table. So I'll use an if clause for that. I'll type if, I'll press Ctrl+Space, and choose an if statement. And here is my condition. If request code matches the constant TOUR_DETAIL_ACTIVITY and the resultCode has a value of -1--remember that's the value I set in the detail activity--then that means that something has changed in the MyTours list, and I'll refresh the list.
Within the conditional block, I'll make sure that my DataSource is open. I'll call datasource.open. I'll refresh the list by saying tours = datasource.findMyTours. And as I have in the past, I'll call my refreshDisplay method. So now when I remove an item from the list, from the DetailActivity and then return to the screen, I'll detect that that has happened, and I'll re-query the data and show the refreshed list.
And finally, I'll still be on the MyTours list so I'll say isMyTours = true, and that's all of the code. Now I'm ready to test. I'll run the application in the emulator. When the application comes to the screen, I'll click on MyTours. That shows me the items that I've previously added to the MyTours table. I'll click into an item, and because I came from that view, I see the REMOVE FROM MY TOURS menu choice, I click it, it's removed from the table, and I immediately come back to the MainActivity where my data is refreshed.
I'll click into another item, I'll remove it, I come back and the data is refreshed. So now you have a complete working example of an application that uses multiple tables in a database and can insert and delete data as needed. Remember at all times that the user is always capable of removing this sort of private data from their application. Local data that you put on the device is never permanent. The only way to make it permanent is to save it up to a server.
Working with server-hosted data is outside the scope of this course, but now that you have a good sense of the various options that are available for local data storage with preferences, internal and external files, and SQLite hosted data, you should be able to manage the data that's on your device in all of these different forms.
There are currently no FAQs about Android SDK: Local Data Storage.