SQL database for beginners — Part 1

Most android apps need to save data, even if only to save information about the app state during onPause()so the user’s progress is not lost. Most apps need to save the user settings and some apps manage large amount of information in files and database. This series is made to give a quick idea about the basics of SQL Database. So lets get started!

There are 3 classes necessary to completely define a database.

1. Contract Class : It is used to explicitly define the schema of your database.
2. DbHelper Class : This class is used to create and upgrade the tables.
3. ContentProvider Class : It helps an application manage access to data stored by itself. It encapsulates the data and provide mechanisms for defining data security.

Basic Terminologies

1. Content URI : A content URI is a Uniform Resource Identifier that identifies data in a provider. These include symbolic name of the entire provider(its authority) and a name that points to a table or file (a path). The optional id part points to an individual row in a table. Every data access method in Provider has a content URI as argument which allows you to determine the table, row or file to access.
2. Authority : A provider usually has a single authority, which serves as its Android-internal name. You can define your own provider authority as extension of name of package containing the provider. For example, if your package name is com.example.<appname>, you should give your provider the authority com.example.<appname>.provider.
3. URIMatcher : To help choose which action to take for incoming content URI, provider API includes class URIMatcher which maps the URI patterns to integer values. You can use the integer values in a switch statement that chooses the desired action for content URI.

Define a Schema and Contract

Create a contract class that acts as a container for constants that define names for URIs, tables and columns.The definitions that are global to your whole database are put in the root level of the class. Then inner class is created for each table that defines the table name and its columns. Every inner class should implement BaseColumns so that it can inherit a primary key field called _ID that some Android classes and cursor adapters will expect.

For example, this snippet defines the DataContract class that I created for a sample project :

Create Database using SQL Helper

Once the contract class is defined, you should implement methods that create and maintain database tables. Your Helper Class will extend the SQLiteOpenHelper class that will @Override onCreate() and onUpgrade() methods. In the onCreate() method the query for creating the table will be executed.

For example, here is the snippet that defines the DataHelper class :

That’s all for the first Part of the post. In second part we’ll see how to implement your own ContentProvider.

This completes the setting up the schema of the database . Part 2 continues to setup the ContentProvider for the database.

If you found this post helpful, show some love ❤ and share! Until next time keep practicing and happy coding!