Using local database with SQLite on Linux Arm and Android

Learn how to create, read, update and delete local data with SQLite

It is already well known in mobile programming that it is necessary to build a local database which will record all data and, if necessary for your application, when the device is connected to the internet it will send this data to the server through a web service. There are several local database options but the best known and used is SQLite, which is the local and open source database used when developing native Android with Java.

In the development of embedded systems (Linux Arm) like Raspberry Pi, BeagleBone, Labrador, Toradex and NXP, the scenario is very similar, with SQLite and GDBM as the most common options. This is because the Linux community prioritizes the use of free and open source software. In addition, these cards do not have much memory and that is why it’s necessary to be very careful how they are used, as it is necessary to share them with the sensors, interface etc. If your integrated device has limited storage — usually data stored locally for the time being until the device receives a connection — then it loads the data into the cloud. Alternatively, if it has a resistance connection, the small local storage can function as a stack for the embedded device to push the data temporarily in place. Then it tries to send the data to the cloud server too; if the operation was successful it cleans the local instance, freeing up space again and restarting the process.

The use of SQLite is so common in this part of shipments that Automotive Grade Linux uses it in its projects. And with the arrival of TotalCross as part of this project of the Linux Foundation this can bring even more benefits. Using Java together with the TotalCross framework it is possible to generate applications for both Android and Linux Arm using SQLite embedded in the device, in order to generate compatibility with any platform and OS version on it.

This makes it even easier (and fluid) to automate houses, cars, or other embedded projects that are also integrated into your cell phone.

And if you’re still not completely sure about using SQLite, know that SQLite supports all relational database features. To access this database, you do not need to establish any type of connection to it, such as JDBC, ODBC and others.

To understand better let’s create a simple project in the next steps and learn how to do CRUD (Create, Read, Update and Delete) with Java and TotalCross.

summary

1. Creating a TotalCross Project with SQLite
2. Creating the Local Database
3. Reading Local Data
4. Updating Data
5. Deleting Data
6. User Interface
7. Viewing Local Data
8. Conclusion

Remember that the link to download the source code is in the topic ‘References’ at the end of this session.

Let’s use this project.

Creating a TotalCross Project with SQLite

Project Structure

As you will learn better from the chapters in the suggested architecture and design patterns, TotalCross suggests following the MVC and DAO standard when creating your applications.

So let’s start by creating three packages:

• ui: Where will all UI classes stay.
• util: This is the SQLite database creation class
• dao: This is the package that will save all Data Access Objects (DAO).

Once this is done, our project will be structured as follows:

Creating the local database

Inside the package util, we will create a class called DatabaseManager and a static object of type SQLiteUtil called sqliteUtil.

That done, inside a static block, we will initialize the sqliteUtil passing as argument Settings.appPath and the name of our database in quotation marks. It is important to always put “.db” next to the name.

Now let’s go to our Statement (the interface used to execute SQL command) to establish the connection with our database and then pass the command to create the CPF table (CPF means “Natural Persons Register”; it is the Brazilian individual taxpayer registry identification).

Prior knowledge of SQLite is required to better work with the creation of your application’s local database. We recommend this PDF to learn more.

OBS: Always import from totalcross packages.

Now we have finished the first part. Here’s how it went:

Now let’s create a static method to return our connection to the database so we can use it inside the DAO package when we manipulate data.

And ready. See the connection class with the database below:

Inserting data into the table

Inside our DAO package, we will create a CPFDAO class. Let’s call it this because it is the name of our table in the database + DAO as the default name to say that that class corresponds to an access object to the database, in this case, the CPF.

Once this is done, we will create a method for registering the CPF in the local database. For this method, just ask as an argument the CPF of type String then create a connection with the bank and execute the SQLite command passing it as text in the .executeUpdate.

Reading local data

To make the data of a table not SQLite is very simple, just execute the Select command through an executeQuery to return the data from the table and then create a While to traverse all rows of the returned table and store that data within an ArrayList.

In the code below you will be able to return all the data registered in the local database. The call and display part for the user is in the UI topic.

Updating Data

Updating the data is also very simple; just pass the SQLite command called Update with the value of the old field and the current field. If you want to learn more about the way this command works in SQLite, we recommend this article.

Deleting Data

To delete a record, simply execute the command Delete and pass through the PreparedStatement the value to be deleted, and run through the executeUpdate.

You can see the complete CPFDAO.java class in the link below:

TotalCross/sqlite-sample

User Interface

In this chapter, our focus is not on the user interface but rather to learn the basics about SQLite and apply it to TotalCross. Knowing this, let’s put the interface code that we use to build this basic example here. If you have any doubts about this most graphic part, we recommend a quick read on the Components section in our documentation.

Remember that everything related to interfaces goes inside the UI package.

Initial Screen

Where we call the method to register the CPF:

1. Notice that on line 69 we call the CPFDAO().InsertCPF (cpf) and register the CPF entered in the local database.
2. On line 70 we call the getCPF() method to read the database data and assign the result to the cpfs array.
3. On line 135 we call the deleteCPF(cpf) method to delete the CPF that was typed.

Update Screen

And there is the interface where the data can be changed, just to test the update command with SQLite.

Viewing local data

To obtain the data registered in an SQLite database it is necessary to download a program called DB Browser For SQLite.

• After downloading and installing the program, simply run the file below to open the database and then select the .db file that is inside your project.
• Click “Browse Data” to see all the records of your SQLite database!

Conclusion

So now you can start with SQLite as your local database for both Linux Arm and Android! Although we use TotalCross, the SQLite commands are the same regardless of language and framework, and TotalCross is basically Java so you can apply the content of this article to a purely Java application without major changes.

You can ask any questions you may have about SQLite, development for Linux Arm or Android on the TotalCross forum, or you can join the community via Telegram or Discord!

References

• The entire project can be accessed through our GitHub;
• SQLite Tutorial.
• Get Started with TotalCross.