Use of SQLite database on Android

Android provides built-in support for SQLite databases. We do not need any additional libraries, all that is necessary for native Android. SQLite is an easy-to-use lightweight database and is used not only on Android. Also in regular Java or JEE we can use this database. However, there are some differences in service. First of all, in regular Java / JEE we need to add SQLite libraries, and secondly, with the same database, we use JDBC or some ORM, eg Hibernate. Here the whole database is contained in one file which in addition is generated automatically 🙂

In this example, we will create a simple contact database. In the first place, I have to create a class that inherits from SQLiteOpenHelper and override the onCreate and onUpgrade methods and create a parameterized constructor. This class will be used to create the database and the necessary tables.

The constructor itself accepts only one parameter – context, which it then passes on to the constructor of the class after which it inherits. “Contacts.db” is the name of the database file that will be created. One in the constructor is the database version. We can increase this value with subsequent changes in the base structure.

When you first use the object of our SQLiteObject class, SQLiteOpenHelper will notice that the base file (here.db) is missing and will create it and then call it onCreate. In my onCreate method, using the execSQL method of the SQLiteDatabase object, I create a table in which I will store the data. Nothing extraordinary – ordinary SQL. The onUpgrade method will be called if we refer to an outdated version of the database (the database engine will know this based on the version number we specify as the constructor parameter). We can, for example, implement a call to expand or change the structure of the database.

We will now add functionality for adding, modifying, deleting and displaying data. For now, it is very poor 🙂 The next two methods I add to the class of ManagerBase. Of course, this scheme is very abducted, but we should use separate DAO classes for this type of task. Such a simplified form will make it easier to understand the very principle of operation, then everyone can do it at their own discretion. I have added a contact method that will be used to add new contacts. This method accepts 3 parameters which state data added later to the table ie name, phone name.

On line 34, I get the base holder. I’m using the getWritableDatabase method here because I will make changes to the database. In case I just want to read the data, I would call the getReadableDatabase method. Lines 36-38 are used to substitute values for individual columns. The first parameter of the put method is the name of the column to which we insert the data, the second is the value that is to be hit. I do not have to supplement everyone. By way of example, column No. I do not complement at all. This is a column of type “autoincrement”, so the values in it will be generated automatically. On line 16 I invoke the insertOrThrow method, which I pass as parameters to the name of the table into which the data is inserted, and the list of values of the ContentValues class I added earlier. This method inserts a row into the table.

In case of problems, we will throw an exception of type SQL exception, but we can catch it by including the try-catch block.

To the class of the administrator, I also added a method to all. It fetches data from the database.

On line 43 I declared the columns I want to read and their order. I do not have to read all the columns, nor do I have to draw them in the order in which they are on the table. In line 44 I pick up the base holder. This time, however, I call the getReadableDatabase method, because I will not modify anything in the database, but just read it. Basically, data retrieval is performed using the query method on line 45. I give here the name of the table from which I will read the data and as a parameter list the columns that I will retrieve. The other parameters (which are null here) are used to stead conditions WHERE, Having, grouping, sorting etc.

It’s time to make a presentation layer. So stick to the main component of the TextView class.


In this example, I will add new contacts to the table by using the 3 newly created contacts and then display them.

On the main activity onCreate method, the most important element for us is located in lines 18-28. Line 18 is the creation of a database manager object – that is, the class that is responsible for connecting to the database and operations on it. The constructor of this class requires a stencil. Here, as a counter, I indicate “this”, and thus the current activity. Using the “addContact” method included in this class, I add three new contacts. Next, I want to download them from the database and display them on the screen. I am using the All method, which returns a Cursor object. Processing such a cursor is not the most convenient, so in a moment we will deal with the conversion of everything in such a way that the method returns all objects, such as the Contact class. Step by step. For now, we have spartan conditions.

In the while loop (line 23) we see the moveToNext () method, using this method allows us to move the query result down the line as long as there are still some data to read. In lines 24-27, I assign column contents from the following rows according to the position of this column from the left – hence the getInt (0), getString (3) calls – the column number from the left according to the order we mentioned in the method. Classroom ManagementBase. Note that I also use different methods depending on the expected type of data returned from the query – I mean the getString and getInt methods. Keep this in mind, because if you try to do getInt or getLong example and you get a text string, the program throws an exception. In line 28, I simply concatenate the information and display it in the TextView component that I glued there a moment ago. In order not to get stuck in a long string of characters – before each subsequent contact I paste “\ n” means the newline.



I will show you an interesting property based on a certain example. The contents of this database will persist between subsequent runs of the program. So if I run this program again, and so I add three new contacts, I should now see duplicate records. The only thing that should distinguish it is the record identifiers, ie the content of the column that is the primary key of the “phones” table and having the “autoincrement” property. So I make a small cosmetic change in the code:

In line 28 I add only column display No other changes are not applied. The effect is as we predicted:


In order for the program to be useful in any way and to make the code clear, you will need to make a few changes:

  • Get rid of this nasty “TextView” at the very top of the list.
  • Add the ability to delete contacts
  • Add the ability to update contacts
  • Create a “Contact” class and use objects of this class rather than a list of String elements.
  • Provide methods that allow you to retrieve your contacts according to the criteria (eg ID, phone number, or name).
  • Add some pane to allow you to add, update and delete contacts easily.
  • Separate the code responsible for reading, writing, updating and deleting contacts to a separate class.

Resolution # 1 only comes down to clearing the text displayed in the TextView component before completing it in the loop. We see it in line 22:

By the way, we see that our 3 contacts have been added again;) With that, we have to do something. In plus that we have different identification numbers.


So I’m going to add a simple method for deleting unnecessary records.

Of course, we base it in the write mode, as we see in line 50. In line 51 I define the list of arguments for the where clause. This must be a list of String arguments. I think it would be best to explain this by example. Let’s look at line 52. The first condition is the name of the table from which we want to delete rows. The second is the condition that is to be followed by the where clause. Same here we do not exchange here. Instead of the numerical or textual values that would appear in the conditions, we place the question mark. If we had more than one parameter, this text string would look like this: “department_id =? and manager_id =? and salary>? “. The system would expect to receive three values that would be sent to our query characters. The list of these values is given in the form of the String collection defined in line 51 through the third parameter of the delete method. In my case, the list consists of one whole element which, in addition, is less elegant, although a popular cast on the String type. So in summary:

Argument # 1 is the name of the table we are deleting

Argument 2 is the condition for the where clause

Argument 3 is a list of values that are to be found in the parameters of the where clauses defined as argument 2 of the delete method 🙂

Now let’s test our new method.


A little bit of our main activity code. Lines 19 through 21 were responsible for adding the lines I just commented on, but 2 lines 25-27 added a deletion of all contacts with identifiers from 4 to 9. The contact display remained as it was at the beginning. Current state :


There would be a method for modifying entries in the database.

I’ve moved the method in the “Batch Manager” class because the newly added “updateContact” method has some common elements with the previously written “deleteContact” and “addContact” methods. In line 49 I have defined what parameters the method accepts. The first is the row identifier (unique number from the column number) according to which I will select the line to update. The rest are values to update. I assumed that I would not delimit the updates of individual columns, made separate methods or complicated conditional blocks to update only those columns actually changed. After the least resistance line. I assume that I simply read a row, change one or more fields, and pass this method to all columns, regardless of whether or not something has changed in them.

Just as it was when adding a new line, here in lines 51-54 I give a list of values that are going to hit each column. These are values that change – those that I give through the parameters of the method. As before, the put method takes as arguments the name of the column in which we update the value, and the value for that column. As with the delete methodContact here and here in line 55 the values that will be given as arguments to the where clause. In line 56, it is an update method call which takes as parameters the following: the name of the updated table, the updated column values, the where clause, the value, or the values that are to go to the where clause in place of the query characters.

Now I’m doing the main activity so that before calling you update the method and change the phone number of Grandfather Frost:

Of course, programmers purport that the fingerprints of all the included values and those unchanged, that these data should be retrieved from the database, returned by some object method, and then changed after the value of the selected field passed as an object to the update method in the base. And to be able to achieve this, we need to first convert it to a more object-oriented form, and create a method to return the selected line by the id :). Let’s start by defining a class that will represent a single line. I tried to define the fields in the class to match the column names in the table.


There is such a good programming practice that POJO classes are not public and private, but access to them by so-called getters and setters. This gives us more control over the content of these fields. In Eclipse there is even a special function to automatically generate such methods. Choose from the menu available under the right mouse button:

When the window appears, we select all options:

and click on the “OK” button at the bottom.

Right now our class should look something like this:

I will now create a method to retrieve from the database and pass the pointer as a new Contact object. I add to the Management classBase method to giveContact. On line 67 I am creating an empty contact class object for a while, which will be returned after the method is completed. On line 68, as in any previous case, I hook up the catch to the base. In line 69 I define columns that I will read. We see that the same passage appears again, so it will have to be separated in the future. Line 70 is the same as before the substitution of the value that goes under the “?” In the where clause. Line 71 is the principal execution of the query. The arguments for grouping, condition having, sorting, and limiting the number of rows returned (in that order) leave null, I will not do any of these. If I get at least one line returned from the query (which I check on line 72), I go to reading the data from the line fields and completing the object. On line 73 I go to the first (and in this case the only) line.

Lines 74-77 are already familiar with previous data reads, but this time I do not assign them to variables, and using setters completes the contact class object. In line 79 already completed (hopefully – we could look for a contact after the number that does not exist in the table) object class contact return method. The whole method:


I will now move the remaining methods from the class of the Board Manager, so that the whole is as object-oriented 🙂

I started with the change method addContact. Instead of accepting values of individual fields as separate arguments of a method, I simply pass one object of the Contact class that is added to the database. One minor difference is seen in lines 36-39. Instead of using separate variables representing values for individual fields, I simply extract the necessary data from the object using the getter classes defined in the class.


The method returns a Cursor object whose processing is not the most convenient. I will also redirect this method to return the list (class LinkedList) of the objects.

After a couple of fixes the method gave all works just as we wished. The data retrieval method is the same, I added only in lines 67-73 processing the cursor and loading the data to the previously prepared (in line 63) of the contact list.

Processing such a list is much more convenient later. Here’s the use of the new version of the method given in the main activity.


By the way, we have a new method here: d Log class. It is invoked in the following way:

Log.d ( “tag”, “text”); and the effect looks like this:

This is especially true in the case of System.out.println, which may be somewhat lacking in Java developers;)

This is a small but important element. In these examples we will not use it, but it may be necessary to group, apply the clause, sort or limit the number of rows in the query. You must specify the parameters in the query method. It will be best to show the example. If I would like to receive, for example, the result of such a query:


This query method call would look like this:

SQLiteDatabase db = getReadableDatabase ();

String [] column = {“avg (salary) as medium”, “department_id”};

Cursor cursor = db.query (“phones”, columns, null, null, ” department_id” , null ,

Department_id desc” );

Parameters in order:

  1. The name of the table we are reading
  2. Columns that we read
  3. Where conditions
  4. Values to where conditions
  5. Column/columns I grouped with
  6. Condition to clause having
  7. Sort method

Here everyone can decide whether or not to use this character. In my opinion, it’s average comfort and not intuitive. Besides what if I would like to apply joins between tables, subqueries etc? Instead of such a somewhat abstract form of querying using query, we can use the rawQuery method. To our database administrator I added a method to extract contacts with the name that I pass as a parameter.


In the case of rawQuery, we do not use any additional parameters for grouping or sorting. All this kind of things we define in the SQL query itself without any surprises. As you can see in lines 98-100, the parameter I just joined was a glitch, the WHERE clause and the sorting is in the SQL itself. I strongly prefer this form, as more friendly 🙂 In lines 101-105 I also inserted a commented alternative form of the same call. In this case, instead of typing the query, I used question marks and substitute the values that these query characters would hit while executing from the list given as the second parameter of the rawQuery method (here, the list is just one String element). Just download the data from this cursor exactly as before.

Checking directory contents and file properties

We will start by checking the contents of the directory. The results will be displayed on the ListView component.

In the first place, I add such a component to my activity:

For this component, you will need to add a layout for individual items in the list (this is necessary for later stages), for now, I am creating an empty XML file in the layout directory.

Its content is as follows:

Nothing special. The file simply describes how a single item in a list looks like. Here it will be just an inscription, but we can use TableLayout here as well, and lay out more complex structures (eg image and text).

It’s time for the code:

The most important elements are in lines 23-28. On line 23 of the list of String elements I assign a list of files from the specified directory. The File class is a plain jav File, not an Android version. In Java, each directory is also a file, so this may be a bit strange record. The directory that I download is “.”, Ie the root directory of the system. As well, this may be any other directory, as a parameter for the File class constructor. On line 24, I create an ArrayList object. I will need to add an adapter (that is, an element that complements the contents of a ListView component) to a list of just that type. On line 26, I’m doing a “conversion” of the string that I’ve used so far into an ArrayList object. Line 27 is the initialization of the previously mentioned adapter. We have three parameters, the first is the context,



I changed the directory whose content I display on “/ sdcard”, ie the contents of the SD card. The result looks like this:


We will now display the properties of the files and directories.

I’ve made some changes to the code. On line 27, I changed a call to the File class constructor to use a String variable named directory instead of the direct path. I did this because I’m still using the path to the file a bit and I do not want to duplicate the same code. From lines 29 to 37, iterates through elements of string lists containing filenames and directories. Depending on the properties of the file / directory to its name, I am storing different information. The remainder of the code remains unchanged. Line 31 is labeled R if the file / directory can be read. Line 32 is the addition to the letter W if we have the ability to write to the file. Lines 33-35 add a date to the file / directory name. I’m using a java.util.Date object here because the return value of the lastModified () method is int.

In the XML file list_group.xml describing the appearance of a single element of the list also made a small change (pure cosmetics). I reduced font size from 15 to 11 because more information would cause the line to wrap, so we have all the information about the file / directory in one line 🙂


Checking the free space on the SD card

If you want to check the free space on the SD card or in the directory, we can use the StatFs class. Unfortunately, this does not work for the root directory.

First, I stick a TextView element on the screen. This will display the amount of space on the card.


Now you need to add a bit of code that will handle it:

In essence, what’s most important is in lines 16 and 17. In 16 using the StatFs constructor, I indicate the directory for which the free space will be checked. On line 17, I use the getBlockSize and getBlockCount methods to check the amount of space in the byte card. As it is more readable in megabytes, I divide the value twice by 1024 and round it in line 18. Effect of the program: