android database crud example layout

Android Database Without SQLite Part 3: Bulk Insert and Read All Queries

In the PREVIOUS post, we discussed how we can create multiple tables with foreign key constraints using ActiveAndroid library without writing any SQLite code. In this post, let’s take a look at what are the different queries that we can perform with our existing database. And of course, if you haven’t read the previous posts, please be sure to read them before proceeding below.

Download The Source Code Here

What do we discuss in this article?

  1. Inserting several rows efficiently [Bulk Insert]
  2. Reading all the values

The UI

Our Existing UI as per the previous post is not good tailored to handle the above scenarios. This is how our proposed new UI will look.

android database example CRUD operations

slidenerd

This will be the overall layout for our app.

android database crud example layout

slidenerd

Let’s move to the hardest section first, the bulk insert. We can easily use a ListView to support a UI that allows users to enter multiple rows, but let us take a different approach to learn something new. This approach will also solve THIS issue on stackoverflow in a standards compliant way.

1) BULK INSERT

If you remember from our previous posts, this was our existing database,

android database example schema

slidenerd

MAIN STEPS: FOR THOSE WHO JUST WANNA KNOW HOW BULK INSERT WORKS

  1. Get a list of Person objects to save such as an ArrayList<Person>.
  2. Begin a transaction by saying ActiveAndroid.beginTransaction().
  3. For each Person from the list
    1. Save the current score.
    2. Save the current Person object.
  4. Set the transaction successful.
  5. End the transaction.

To ensure the transaction either executes fully or does not execute in compliance to ACID properties of a database, we use a try..catch block.

This is the code snippet that does the actual work of performing bulk Insert. Notice the variable listPeople below is an ArrayList<Person> containing several values.

A] CREATING A DYNAMIC UI FOR THE DIALOG

android database bulk insert example

slidenerd

The UI for DialogFragment that supports bulk insert looks like this

android database bulk insert ui

slidenerd

Since the UI for the DialogFragment will be generated dyamically to allow several entries to be inserted, all the UI controls such as EditText will be created in code. Assigning an ID to each UI control to extract data from them is very cumbersome compared to assigning them a tag.

android database bulk insert ui structure

slidenerd

The Initial UI for the DialogBulkInsert would look just like this.

dialog fragment bulk insert initial layout

slidenerd

The UI would be coded as follows in XML under the res->layout directory

Now let’s get the code to work for this Dialog.

Create a class DialogBulkInsert that extends DialogFragment [from support library is more preferred]

  • Override the onAttach() and store a reference to Context, we’ll need this later.

  • Override onCreateDialog() and request your Dialog window to be shown without a title.

  • Create a layout for this dialog in XML with a ScrollView as its root [Remember, we need to keep adding new rows and hence the size of Dialog vertically will at some point exceed the size of the screen] (ALREADY DONE above).
  • Create a vertical LinearLayout inside this ScrollView which will contain all our dynamically generated children.
  • Add 2 buttons inside this LinearLayout, first one to keep adding new rows and second one to perform bulk insert.
  • Hide the 2nd button which performs Bulk Inserts so that it can be clicked only if there is atleast 1 row of data that can be inserted.
  • Use the onCreateView() to return this layout after inflating it.

B] CREATING EACH CONTROL FOR OUR DYNAMIC UI

Now, let’s see how we can create the UI controls dynamically in code. We need 5 EditText to store name, age, marks for physics, chemistry, maths and biology for a single person. A single EditText’s construction in code can be broken down as follows.

android programmatically create edittext

slidenerd

The code for creating the above EditText for storing the person’s name and age would be as follows. Notice the variable mId below which is an int with a value 0. It is an instance variable created before writing the constructors or any methods and increments by 1 for each dynamic view created. We also create TAGS for each dynamic View so that we can extract data from these tags later.

 

Now here comes the tricky part. Creating the 4 EditText in a row that take P, C, M, and B values.

android programmatically create linearlayout

slidenerd

The code for achieving the above effect is split into 5 methods, the 1st method creates the Linear Layout and calls the other 4 methods to create the respective EditText that you see inside this LinearLayout and returns the same. Creating the other 4 EditText is very similar to creating a single one we just mentioned above.

C] HANDLING SCREEN ORIENTATION CHANGES IN OUR DYNAMIC UI IN A STANDARD WAY

There are still 3 things that we need to manage

  1. Getting all the values entered by the user from all the dynamically created EditText
  2. Handling the orientation changes because of the following issue below.
  3. Bulk Inserting the data to our database.
dialog bulk insert orientation change initial

slidenerd

As you notice, we lose all our dynamically created views. So we need to maintain 2 things.

  1. The number of Views created dynamically prior to rotation so that it can be restored.
  2. The content of each View without losing data.

The first problem is handled by maintaining an instance variable called mCountRows  which is initialized with 0. When rotation takes place, onSaveInstanceState() is called, we save the value of this variable here and restore it back inside our onCreateView() using the argument Bundle savedInstanceState. The second problem is already tackled because we assigned a unique ID to each dynamically created View. Remember the instance variable mId above while generating those EditText that is incremented each time for each View.

dialog bulk insert onSaveInstanceState

slidenerd

  • Create an instance variable called mCountRows=0 at the top inside your class.
  •  This variable is incremented every time the user hits the ‘New Student’ button.
  •  At any given instant, this variable contains the number of rows of Person data that has been created, [1 row in our case= 6 EditText here]
  • When the Dialog is rotated, onSaveInstanceState() is called prior to destruction of all Views. Inside this method save the value of this variable.
  •  Restore the value of this variable inside onCreateView only if this is subsequent time the Dialog is being created,
If the Dialog is created for the first time, the parameter, Bundle savedInstanceState is null otherwise it has a value.

Paste this snippet inside your onCreateView().

Notice the call to the method restoreUI(). Inside this method, we simply find the number of rows that were restored and use a for loop to add the EditText containing the person’s name, the person’s age and the LinearLayout containing the scores. Notice how we have once again changed the visibility of the Add button depending on the fact that the number of rows restored must be more than 0 to show the Button.s

 D] EXTRACTING VALUES FROM EACH DYNAMICALLY ADDED VIEW

Below is a simple guide that shows how our layout is structured and how we can extract values from each View inside our layout.

android extract value from programmatically created view

slidenerd

  1. Create 6 ArrayList<String> objects that will collect information for name, age, score in physics, chemistry, mathematics and biology.
  2. Loop through the number of children our root LinearLayout from XML contains.
  3. Store the current View inside a temporary variable.
  4. If the current View is an instance of EditText, check its TAG.
  5. If it has the tag matching name,
    1. Typecast the current View into an EditText
    2. Get the name from the EditText
    3. Store the name inside the ArrayList<String> responsible for collecting names.
  6. If it has the tag matching age.
    1. Repeat the same steps 5.1, 5.2, and 5.3 for collecting age.
  7. If the current View however is an instance of our dynamically created LinearLayout horizontal that has PCMB EditText inside it,
    1. Create a temporary LinearLayout object representing the above one and store the current View inside it.
    2. Find the number of children inside this horizontal LinearLayout. We know very well, its 4 representing the 4 EditText inside but let’s try to keep our code flexible.
    3. Loop through the inner children of the horizontal LinearLayout.
    4. If the current inner child is an instance of EditText, compare the tags to check if Physics, Chemistry, Maths, Biology are found
    5. Add the data from each EditText inside to their respective ArrayList<String>.
  8. Terminate both the loops.
  9. At this point, all the data from all the EditText is contained within 6 arrays. Let’s further process the arrays to add the data to our database.
  10. The code snippet below shows all the actions described above contained inside a single method extractValues().

 E] BULK INSERTING THE VALUES

  1. From the 6 different ArrayList<String> we have so far, generate a single ArrayList<Person> where each Person object requires name, age and a score object whereas a score object requires the 4 scores.
  2. At this point, do exactly what we discussed right at the beginning, begin the transaction, add the values, set the transaction successful and commit the values.
  3. The code for the same looks as shown below and is contained within the insertValues() method.
Inside your onClick(), don’t forget to call extractValues(), and insertValues() and dismiss() the dialog once both are done. The snippet would look like this.
Let’s add 5 records to our database with the following values

android bulk insert test data

slidenerd

Phew! That was quite a long one. Now, here’ s everything we discussed about Bulk Insert so far. The last screen in the below image is from the ‘display all’ section which we’ll construct soon.

android database bulk insert ui

slidenerd

FULL CODE FOR DIALOG BULK INSERT

 2) SHOW ALL: DISPLAYING ALL THE DATA INSIDE A LISTVIEW

This is how our ShowAll button will work. This Activity is also responsible for performing an update operation on the database when the user clicks on any item inside the ListView. The update part however, will be discussed in the next post.

android show all working

slidenerd

MAIN STEPS: FOR THOSE WHO JUST WANNA KNOW HOW SHOW ALL WORKS

  1. Create an object of Select from ActiveAndroid library.
  2. Specify you want all rows by calling all().
  3. Specify the table from where you want all data by specifying Person.class inside your from().
  4. Call execute() to get an ArrayList<Person> object containing all rows.
  5. The code snippet below shows you exactly how this query looks like.
The ListView we use to display data has a layout in XML for displaying each item. The layout contained in the file custom_list_item.xml looks as shown below

android dialog show all custom list item

slidenerd

The code for our custom item defined inside custom_list_item.xml is given below

The layout XML file activity_show_all.xml  for our Activity that displays the ListView with all information is shown below. It has a ListView and a TextView to display a message when the ListView is empty. We will link this TextView also known as empty View to the ListView in code.

The only thing that needs to be done now is to create our Adapter, and let it fill data inside our custom List Item.

  1. Create a class that extends BaseAdapter. Keep it in a separate file called VivzListAdapter.java
  2. Add all unimplemented methods.
  3. Provide getter and setter methods to get and set data to our Adapter.
  4. Override getCount() to return number of data elements inside our Adapter or 0 if the data is null.
  5. Override getItem() to return the data item for a specified position inside our Adapter or null if data is not initialized yet.
  6. Override the getView() method to use VIEW RECYCLING to fill data inside our custom List Item.
  7. The code for our Adapter and ViewHolder looks as shown below.
At this point, we won’t include the full code for the DisplayAllActivity.java since it has the update feature inbuilt as well. In the next article, let’s discuss how we can perform select, update and delete queries and finally complete this mini-app.

 CONCLUSION

You saw how to perform an efficient Bulk Insert with a dynamic UI and how to View the results inserted so far. In the next post, its time to talk about select, update and delete queries. In the meantime, stay tuned and remember “Let Intelligence Be Your Only Keyword.”.