active android select example

Android Database Without SQLite Part 4: Select, Delete and Update Queries

In the previous post, we talked about how we can perform bulk insert and display all operations on our database which uses the ActiveAndroid library. In this post, we’ll talk about how we can perform select, delete and update queries on our database using the existing database schema, tables and UI.

Download Source Code Right Here

What do we discuss in this article?

  1. Select Query using ActiveAndroid library
  2. Delete Query using ActiveAndroid library
  3. Update Query using ActiveAndroid library.

A] SELECT QUERY

We’ll execute the Select query first because, we’ll use the same select query to perform Delete saving our work. There are 3 cases that we need to handle while dealing with Select Query.

1)Select Only from the Person Table

This case arises when we are querying rows with respect to Name or Age. It is the simplest of all cases which executes under the following steps

  • Find if the user has entered name or age or both
  • Query the person table for the above details
  • Get the results and show them inside a ListView right below
active android select query

slidenerd

2) Select Only from the Score Table

This case arises when we are querying with respect to score. It is a 2 stage select process.

  1. Find the list of rows from Score table that match the given score.
  2. Find the list of rows from Person Table whose score id [the foreign key] matches with the id of the rows from the above Score Table.
active android select example

slidenerd

3) Select from both the Person Table and the Score Table

This is the most complex of all select cases in our app. It is again a 2 stage process.

  1. Find the list of rows from the Score table that match the given scores in the input.
  2. Find the list of rows from the Person table that match the criteria specified in the input.
  3. Find those rows in the Person Table whose foreign key [score ID] matches with the rows from the Score table in step 1
active android select example

slidenerd

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

THE UI

We’ll have a separate Activity called the SelectDeleteActivity.java. It will have the EditText necessary to gather inputs and Buttons to perform Select and Delete. It’ll also have a ListView to display the results right below the buttons in case of Select. The layout for this Activity will be defined under res->layout->activity_select_delete.xml  and will contain code as follows.

Our class SelectDeleteActivity.java will look like this initially.

 

Let’s write the onCreate() method which will simply initialize all the Views and register the listeners if any.

Let’s write some methods to extract details from the EditText. Wherever we have to convert the EditText values to an integer, we have handled a NumberFormatException just to ensure the app doesn’t crash when it detects and invalid number such as “abc”.

We need to perform separate queries on the Person table and Score table since the documentation of ActiveAndroid doesn’t specify [at the time of writing this post] about how to select rows from multiple tables with columns from multiple tables for performing a JOIN type of query.

We”make an inner class called Query within the same file that contains 4 variables, 2 variables that contain the query itself for Score and Person table and 2 variables that contain the Query arguments for both tables.

The idea behind the class is something like this.

active android select example

slidenerd

  • The user can leave any EditText box empty,
  • If no value is found, no select query will be issued, if a value is found for name or age, a query to Person table will be issued with the appropriate arguments.
  • If a value is found for any one of the scores, an appropriate query will be issued to the Score Table from which, its ID will be used to select the corresponding Person [multiple if many rows are found] from the Person table.
  • If a value is supplied by the user for both the fields that are linked to the Person table and the Score table, a query will be issued to find the right set of Person objects from the Person table that satisfy the criteria specified in both tables
  • For example, if you want to select a Person whose name=”XYZ”, and score in Chemistry=25, it requires searching both tables and finding the common Person object that satisfies both conditions.

With this idea in mind, let’s write our buildQuery() method that will check which EditText contains values and which ones don’t and build the appropriate query with arguments for querying both tables. The first thing we do below is to extract values from all our EditText fields.

In the next step, we make the variables below, queryForPerson will keep adding the columns to be selected when values are found for the EditText corresponding to the Person table, queryArgumentsForPerson holds those values, queryForScore will keep adding columns to be selected when values are found for the EditText corresponding to the Score table, queryArgumentsForScore holds these values.

If we have valid values for Name or Age or both, we append those column names and values as follows to our StringBuilder

If we have valid values for scores in PCMB for any combination of the fields, we add those values to our StringBuilder as follows

The only thing that we need to do at this point is to remove the EXTRA AND that we kept appending at the end of each added column above. If we have entered values for name and age, the queryForPerson would look like this “personName=? AND personAge=? AND”. We need to remove the last AND.

Finally we return a new Query object which we constructed earlier in this post. Remember? the inner class Query?

And that completes our whole process of building a dynamic query. The complete code for this class will follow at the bottom of this post. For now, let’s focus on how to perform the select operation now that we have built a query. Observe something carefully in the above steps, if a StringBuilder is empty, it means the user has not entered any values in the EditText related to that particular table. In other words, if queryForPerson is empty, it means user has not entered any values for Name or Age to perform the select operation. We use this as basis in our image below.

active android select types

slidenerd

Now, let’s make our ArrayList<Person> select(Query query) which accepts a Query object and returns a list of Person objects that satisfy the query. Let’s deal with the hardest case first.

 Case 1: Both Tables

  • Find all the rows from Person table which satisfy the person criteria.
  • Find all the rows from Score table which satisfy the score criteria.
  • For each person from the list of Person objects, compare it with every Score object from the list of Score objects.
  • if the foreign key [Score object contained inside the Person object] matches with any Score object from the list, it means our current Person object satisfies the criteria for Person and Score specified by the user.
  • If the current Person object does not match with any of the Score objects from the list, it means the current Person object does not satisfy Score criteria specified by the user and hence should be removed from the final list.
  • Notice, how we have used an Iterator<Person> to iterate through the ArrayList containing Person objects. The reason is to avoid a ConcurrentModificationException which may be thrown if you attempt to remove elements from an ArrayList while iterating through it.

 Case 2: Only Person Table

This is the simplest of all the cases. If no arguments have been specified for the Score object’s table search, it means we are searching only the Person Table.

 Case 3: Only Score Table

The code is very similar to Case 1 discussed above.

  • Find all the Person objects, notice there is no criteria for choosing Person rows here.
  • Find all the Score objects which satisfy the criteria.
  • For each Person from the List of Person objects, check the ID of the foreign key [ID of the score object belonging to the Person object with the ID of each Score object generated from the list above].
  • If an ID match is found, it means the current Person object satisfies the Score criteria specified by the user.
  • If no match is found, remove the current Person object from the list of all Person objects.
  • At the end of this complete iteration, we get a list of Person objects that satisfy the score criteria specified by the user.
The complete code for this class is shown below.

 B] DELETE QUERY

All the work needed to construct a Query for Deletion is already taken care of, thanks to our buildQuery() method.

  • To perform a Delete operation, simply call select() to get the right set of rows that match the user criteria for deletion.
  • Since more than one rows may be deleted, begin a transaction
  • Iterate through the list of Person objects that are to be deleted.
  • Perform the delete.
  • Set the transaction successful once all objects are deleted
  • Use a finally statement to end the transaction if something goes wrong above.
 

active android delete example

slidenerd

C] UPDATE QUERY

Let’s look at the UI flow to understand how Update works in our app. This doesn’t support BULK UPDATES currently. To do bulk updates, enable your ListView to be selected in muti-choice mode and for all selected choices, construct a Dialog with dynamic Views to show and update values very similar to our Bulk Insert dialog.

activeandroid update example

slidenerd

We”ll need a reference to the controls inside the Dialog to show old values and gather new ones and a LayoutInflater object to initialize the View for our custom Dialog.

 

When the ListView item is clicked, these are the following steps we must perform

  1. Get the currently selected Person object from the Adapter.
  2. Show a Dialog with View elements containing values from this Adapter.
  3. Let the user change one or more values.
  4. Save the new values.

Notice how we have used setTag() method of Button to save a Person object inside the Button‘s tag so that we can update and save this Person object when the user clicks on the Button and a reference to the current Dialog object so that it may be closed when the Button is clicked.

Both the IDs are defined in strings.xml as follows.

When the Save Button is clicked, the Update operation takes place as follows,

  • Get values from all the EditText fields.
  • Retrieve the Person object which was set as a tag from onItemClick() method.
  • Update the values of this Person object with the new values
  • Call save() to update this Person object.
  • Get a reference to the currently opened Dialog which was again set from the onItemClick() method and close this dialog.
Here’s the full code for the ShowAllActivity.java which we promised in the previous post.

 CONCLUSION

Its been massive 4 parts to discuss how to make SQLite free databases in Android using this library ActiveAndroid. However, the world of Android is still far from over. Stay tuned with slidenerd and as always “Let Intelligence Be Your Only Keyword”.