Introduction to Android SQLite Database

In the series of Android tutorials, this part of the tutorial describes about an Android SQLite Database .

What is SQLite?


SQLite is an open source database. It supports standard relational database features like SQL syntax, prepared statement , transactions,  etc. SQLite does not have a separate server process. SQLite reads and writes directly to ordinary disk files.

It has methods to create, delete, execute SQL commands and perform other common database management tasks.

SQLite supports following datatypes:

  • TEXT – It is similar to String in Java
  • INTEGER – Similar to long in Java
  • REAL – It is similar to Double in Java.

Have you wondered why SQLite in Android?

SQLite in Android


  • Android does not require a setup procedure or administration of the database using an SQLite database.
  • It stores data to a text file on a device and supports all the relational database features.
  • SQLite database is embedded in every Android device.
  • To use the SQLite database, the SQLiteOpenHelper class is used which provides various functionality.

Useful Resources : Top 25 Android Interview Questions and Answers

SQLite Database 


1. Database Package –  Android.database.sqlite is main package that contains the classes to manage your own databases

2. Database  Creation – The openOrCreateDatabase() method is called for creating a database with your database name and mode as a parameter.This method returns an instance of SQLite database.

Syntax : 

SQLiteDatabse database = openOrCreateDatabase (“database_name”, MODE_PRIVATE, null);

3. Database Insertion – This method is defined in SQLiteDatabase class and the execSQL() method is used to insert the data into table.

Syntax : 

execSQL(String SQL, Object[] bind Args)

The execSQL() method is used not only to insert a data, but also to update or modify the existing data in database, using bind arguments.

Example : 

mydatabase.execSQL(“CREATE TABLE Tutorials(Admin_name VARCHAR, Password VARCHAR);”);

mydatabase.execSQL(“INSERT INTO Tutorials VALUES(‘ABC’, ‘123’);”);

4. Database Fetching – An object of the Cursor class is used to fetches the data from the database. A method calling of Cursor class is called rawQuery() which returns a resultset with cursor pointing to the table.The rawQuery() accepts an SQL SELECT statement as an input.

Example : 

Cursor resultset = mydatabase.rawQuery(“SELECT * FROM Employee”, null);resultset.moveToFirst();
String eid = resultset.getString(1);
String ename = resultset.getString(2);

5. Database Helper Class – Helper class For managing all the operations related to the database ,  An class of  helper has given and is called SQLiteOpenHelper. It automatically manages the creation and updation of the database.

public class DBHelper extends SQLiteOpenHelper {

public DBHelper(){

super(context,DATABASE_NAME,null,1);

}

public void onCreate(SQLiteDatabase db) {}

public void onUpgrade(SQLiteDatabase database, int oldVersion, int newVersion) {}

}

Advantages of SQLite


  • Data storing is very easy and efficient in SQLite. When you need to store an archive files SQLite can produce as smaller size archives and much amount of metadata included than Usual ZIP archives.
  • SQLite can be used as a temporary dataset to do some processing on some data within an application.
  • SQLite is a very light weight database. So, it is a very good choice to develop embedded software for devices like televisions, cameras, Mobile phones, home electronic devices, etc.
  • SQLite provides an easy and efficient way to deal with data rather than do the data processing internally inside in-memory variables.