Android SQLite Database

In the previous tutorial, you have learned about Android Application Resources. In this tutorial, you will learn about SQLite database in Android.

SQL

SQL stands for Structured Query Language.  SQL is the language designed to manage relational databases. You can use SQL to Select, Insert, Update and Delete operations in the database.

SQLite

SQLite is a software library for Android, which is used to connect your Android application with database. SQLite implements transactional SQL database. SQLite is a serverless library which is self-contained and the most important this is that it is serverless. It requires very minimal support with the external environment. Using SQLite, you can directly read and write the data from the database on disk. You don’t need to install anything to use SQLite database before using it. The SQLite database is efficient enough to roll back if something goes wrong during the transaction.

SQLite Requirements

There are no specific requirements or prerequisites for Android devices to use Android applications with the database. Also, there are no specific requirements for Android Studio to build and compile Applications with the database.

SQLite Supported Data Types

In SQLite, you have following datatypes.

  1. Text (As String in Java)
  2. Integer (As long in Java)
  3. Real (As double in Java)

All the other data types must be converted into the data types mentioned above types. SQLite does not validate the data types you specify i.e. If you put Integer in Text column SQLite will not validate the data type.

SQLite and Android

Android provides full support for SQLite. Any database you create will be accessible by name in any class of the application, but you can’t access the database outside the application. In Android project only thing you need to import is SQLite, this will be the only API you must import to use SQLite.

Package for SQLite

Directory Path of SQLite in Android Device

/data/data//databases

How to Use Android SQLite Database

You can use SQLite in any Android application by defining the schema for the database and subclass of SQLiteOpenHelper. 

SQLite Database Schema

The database schema is the formal declaration of structure for the database. SQLite database schema tells that how the database is organized.

SQLiteOpenHelper

You must create SQLite database under the subclass of SQLiteOpenHelper class. All the logic behind CRUD operations in SQLite databases is defined in SQLiteOpenHelper. Use SQLiteOpenHelper for the creation and the version management of SQLite database. Read the complete documentation of SQLiteOpenHandler.

Define SQLite Database Schema

SQLite database schema is reflected from the SQL statements which you will use to create SQLite database. You can specify the structure of your database in a supporting database contract file. Database contract file explicitly specifies the layout of SQLite database. A database contract contains database name with the names of the database tables and its columns. The concept of using database helper is to separate the schema of SQLite database from your logical Android application code.

Advantages of Using Separate Database Contract File

  • The database contract class allows you to use same constants throughout the project within the same package. You can change the name of any column and helps to maintain the changed name all over the code within the same package.
  • You can keep all the global definitions of the database which remain same, in the contract class. Place an inner class which contains the column definitions of the table.

Contract Class

By implementing the base columns for the table, your inner class can also inherit a primary key field called _ID that some Android cursor adapter classes expect. _ID is not always required, but it helps your Android application to run harmoniously.

Create SQLite Database

Once you have defined structure of the database, you can now implement methods to create and maintain the database you created. There are a useful set of APIs available in SQLiteOpenHelper.

To use SQLiteOpenHelper (database helper), create a subclass of SQLiteOpenHelper and override onCreate(), onUpgrade(), onOpen(), and onDowngrade() callback methods.

Using SQLiteOpenHelper to Create Database and Tables

onCreate()

Add Tables by Executing SQL Using onCreate()

In overridden public void onCreate(SQLiteDatabase DB) method execute the query to create a table.

 

onUpgrade()

The onUpgrade() method is used when you want to update your database. You must indicate the Android System that database has been upgraded by changing the Android version.

Alter Table

Now execute the alter table query in onCreate() method.

 

Access Database in Other Classes

To access the previously created database, you must instantiate the subclass of SQLiteOpenHelper, which you created before.

Get Data from Database

To get data from the database you just need two methods, i.e. getWritableDatabase() or getReadableDatabase() methods of your subclass instance (dbHelper).

getWritableDatabase()

getReadableDatabase()

 

Insert Data in the Database

When you want to insert data in the database you can use insert method with three parameters, table of type String, nullColumnHack of type String and values of type ContentValues.

Parameters

table is the name of the table to insert rows into.

nullColumnHack is not the necessary field to insert values, you can put null, SQL does not allow to insert an empty row, you must at least place one column name. If you provide empty values, with no column names, then no row will be inserted in the database. If the values are not set to null, the nullColumnHack parameter provides the name of the nullable column name to explicitly insert a NULL into in the case where your values are empty.

Values contain the initial column values for the row. The keys should be the column names, and the values are the column values.

Return Values

The return value is the row ID of the newly inserted row. It also returns -1 if any error occurs.

Syntax

 

Adding a Record in SQLite Database

Database Query

A database query is a method to query the given table, returning a Cursor over the result set. A cursor object is positioned before the first entry.

Syntax

Get Record from SQLite

Use Adapter to Set Records in ListView

All activities related to the views from the database must be performed after closing the cursor and before closing the database connection.

Read more about Adapters and How to set data in a View using ArrayAdapter.

OnItemClickListener() for ListView Item Click

Start a new Activity on Item Click

ManageUser Method

Get Single Records from SQLite Database

Delete Data from SQLite Database

Use delete() method to delete rows from SQLite database with three parameters i.e. table as String, whereClause as String and whereArgs as String array.

Parameters

table to delete data from.

whereClause is optional when deleting the data. passing null values will delete the entire data from all the rows along with the rows.

whereArgs You may include ?s in the where clause, which will be replaced by the values from whereArgs. The values will be bound as the Strings.

Return Value

The delete() method returns the number of rows affected if the where clause passes. Otherwise, it returns 0.

Delete Record Method

 

Update Record in SQLite Database

The update() method is used to update record in SQLite database. This method contains four parameters i.e. table of type String, values of type ContentValues, whereClause of type String and whereArgs of type String array.

Parameters

table is the name of the table to update data.

values are the map from column names to new column values. null is a valid value that is be translated to NULL at the time of updating.

whereClause is the optional WHERE clause to apply when updating. Passing null will update all the rows in the table.You can also include ?s in the where clause, which will be replaced by the values from whereArgs. The values will be bound as Strings.

Return Values

The update() method returns the number of rows affected.

Update Record Method

 

Summary

Define a Schema by using a Contract class also create an inner class (subclass of BaseColumns) for each table.

Create SQLite database using the subclass of SQLiteOpenHelper class.

Implment onCreate() and/or onUpgrade() methods to CREATE, UPDATE, DELETE, SELECT, etc. operations on SQLite database by using an instance of SQLiteDatabase class.

Get SQLiteDatabase object using getWritableDatabase() or getReadableDatabase() methods to perform CRUD operations.

Learn more about SQLite database.

Hope you like this tutorial, Stay tuned for more upcoming tutorials. Stay Blessed!
Don’t forget to give your feedback through comments.

Leave a Reply

Your email address will not be published. Required fields are marked *