Android SQLite Database

What is SQLite?

SQLite is an open source database. It supports standard relational database features like SQL syntax, transactions, prepared statement, etc. It has methods to create, delete, execute SQL commands and perform other common database management tasks.

SQLite supports following datatypes:

DatatypeDescription
TEXTIt is similar to String in Java.
INTEGERIt is similar to Long in Java.
REALIt is similar to Double in Java.

SQLite in Android

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

SQLite Database Architecture

PackageThe android.database.sqlite is the main package in Android SQLite which contains the classes to manage your own databases.
CreationThe 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 mydatabase = openOrCreateDatabase (“database_name”, MODE_PRIVATE, null);
InsertionThe execSQL() method is used to insert the data into table. This method is defined in SQLiteDatabse class.

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 TutorialRide(Admin_name VARCHAR, Password VARCHAR);”);

mydatabase.execSQL(“INSERT INTO TutorialRide VALUES('ABC', '123');”);
FetchingAn object of the Cursor class is used to fetch the data from the database. Calling a method of Cursor class is called rawQuery which returns a resultset with the 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);
SQLiteOpenHelper ClassThis class is used for creating a database and version management.

In this class, there are two methods which need to override for creating and updating the database, they are onCreate() and onUpgrade().

If the database is accessed but not yet created then onCreate() method is called by the framework.

If the database version is increased in your application code then onUpgrade() method is called. It allows to modify the exisitng database.

The SQLiteOpenHelper class provides the getReadableDatabase() and getWriteableDatabase() methods to get access to an SQLiteDatabase object.