SQLite is an embedded database especially designed for local data storage and simplicity. In this video, learn how SQLite stores data and how it is used on the Android platform.
- [Narrator] One of the ways to persist data in android is to use SQLite. Let's talk briefly about what SQLite is, and how you can use it to store your application's data. First, it's very small in size. The entire library is less than a couple hundred kilobytes. This makes it ideal as a storage solution for mobile devices. Second, SQLite is an embedded database, so there is no separate server process somewhere. It works by reading and writing to files on disk.
Unlike other more familiar database solutions, such as MySQL, Oracle, of Postgres, SQLite is especially designed for local data storage and simplicity. You interact with your database via normal SQL commands, such as create, insert, or delete. However, some of the more advanced commands around data manipulation in querying are not available. You can view the full listing of omitted features at sqlite.org/omitted.
By default, Android ships with the ability to add an SQL database to your application, without having to add any additional dependencies. The APIs that you'll need in order to use a database on Android, are available in the android.database.sqlite package. You will initially need to interact with the SQLiteOpenHelper and SQLite database classes, in order to create your database and its related tables. SQLite has the following storage classes available for saving your data: null, for null values; integer, for values like one, two or three; real, for floating point values, like 2.7; text, for holding your strings; and then finally, blob, for keeping values just as you input them.
This is typically used for things like images. Now, this is slightly different from how we normally store data in Java. We're used to using primitive types, like boolean, int, or long, in their box variance. Therefore, when you design your SQLite tables, and associated columns, you will have to map the Java types to the appropriate SQLite type. For example, if we have a recipe class, with the following fields, a long for 'id', string for 'name' and boolean for 'isYummy', then the long would map to an integer, the string to a text, and the boolean to an integer as well.
Now, this might surprise you. But since SQLite does not have a boolean type, it stores booleans as integers. A zero for false, and one for true. SQLite also has support for foreign keys. It allows a parent-child relationship to exist between entities. For example, you can have an author table, which serves as the parent for a book table, which would be the child. The relationship is defined by means of the 'References Clause'.
This is attached to the child table. We're going to be using this when we define our database schema in our sample application. Finally, it's important to know that the constraints are not enforced by default. So, we will have to enable them explicitly if we want to enjoy the benefits. Now why should you use SQLite? First, when you need a small application footprint. The library is very small, and it's already available on your Android device.
Next, when you need to integrate with other Android APIs, such as content providers, content resolvers, and more. Finally, if you feel comfortable with SQL, and you need to have more complex interactions over the basic current operations. For more advanced SQLite features, you can write raw SQL queries, or even create your own database triggers. Yes, SQLite is a great way to persist your data in Android.
Let's now look at getting set up.
To begin, Annyce Davis reviews using the de facto data persistence solution available on Android: SQLite. She takes you through basic SQLite concepts—such as how to create a database wrapper and insert data into a table—as well as a few more advanced topics. Next, she covers working with Cupboard, an open-source project dedicated to simplifying your SQLite interactions. To wrap up, she dives into working with Realm, an object database solution designed with mobile devices in mind. Throughout the course, Annyce discusses establishing relationships between database tables, creating and running queries, as well as performing data migrations.
- Overview of SQLite
- Defining a database table's schema
- Exploring SQLite create table syntax
- Executing create table statements
- Inserting data with a foreign key relation
- Inspecting the database using a terminal
- Updating and deleting records
- Working with Cupboard
- Working with Realm