Android SQLite Database

Android SQLite is a very lightweight database which comes with Android OS. Android SQLite combines a clean SQL interface with a very small memory footprint and decent speed. For Android, SQLite is “baked into” the Android runtime, so every Android application can create its own SQLite databases.

Android SQLite native API is not JDBC, as JDBC might be too much overhead for a memory-limited smartphone. Once a database is created successfully its located in data/data//databases/ accessible from Android Device Monitor.

SQLite is a typical relational database, containing tables (which consists of rows and columns), indexes etc. We can create our own tables to hold the data accordingly. This structure is referred to as a schema.

SQLiteOpenHelper class:

The android.database.sqlite.SQLiteOpenHelper class is used for database creation and version management. For performing any database operation, you have to provide the implementation of onCreate() and onUpgrade() methods of SQLiteOpenHelper class. Constructors of SQLiteOpenHelper class

There are two constructors of SQLiteOpenHelper class.

Constructors:

  • SQLiteOpenHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version)
              Description: creates an object for creating, opening and managing the database.
  • SQLiteOpenHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version, Database ErrorHandler errorHandler)
                 Description: creates an object for creating, opening and managing the database. It specifies the error handler.

Methods of SQLiteOpenHelper class:

  • public abstract void onCreate(SQLiteDatabase )
        Description: called only once when the database is created for the first time.
  • public abstract void onUpgrade(SQLiteDatabase , int oldVersion, int newVersion)
        Description: called when database needs to be upgraded
  • public synchronized void close ()
         Description: closes the database object.
  • public void onDowngrade(SQLiteDatabase, int oldVersion, int newVersion)
         Description: called when the database needs to be downgraded

SQLiteDatabase class

It contains methods to be performed on SQLite database such as create, update, delete, select etc.

Methods of SQLiteDatabase class

There are many methods in SQLiteDatabase class. Some of them are as follows:

  • void execSQL(String SQL)
        Description: executes the SQL query, not a select query.
  • long insert(String table, String nullColumnHack, ContentValues values)
        Description: inserts a record on the database. The table specifies the table name, nullColumnHack doesn’t allow completely null values. If the second argument is null, the android will store null values if the values are empty. The third argument specifies the values to be stored
  • int update(String table, ContentValues values, String whereClause, String[] whereArgs)
        Description: updates a row.
  • Cursor query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy)
       Description: returns a cursor over the resultset.

Database – Creation

In order to create a database you just need to call this method openOrCreateDatabase with your database name and mode as a parameter. It returns an instance of SQLite database which you have to receive in your own object. Its syntax is given below

private final String CREATE_TABLE_NOTES = "CREATE TABLE IF NOT EXISTS  " + TABLE_NOTES + "(" + NOTE_RECEIVED + " text unique not null );";

Database – Insertion:

Insert a new record in the android SQLite database. Its syntax is given below

                          public void insert(String name, String desc) {
                          ContentValues contentValue = new ContentValues();
                          contentValue.put(DatabaseHelper.SUBJECT, name);
                          contentValue.put(DatabaseHelper.DESC, desc);
                          database.insert(DatabaseHelper.TABLE_NAME, null, contentValue);
                               }

Update a Single Record:

Update a single record in the android SQLite database. Its syntax is given below

                          public int update(long _id, String name, String desc) {
                          ContentValues contentValues = new ContentValues();
                          contentValues.put(DatabaseHelper.SUBJECT, name);
                          contentValues.put(DatabaseHelper.DESC, desc);
                          int i = database.update(DatabaseHelper.TABLE_NAME, contentValues, DatabaseHelper._ID + " = " + _id, null);
                          return i;
                                }

Delete a Single Record:

                           public void delete(long _id) {
                           database.delete(DatabaseHelper.TABLE_NAME, DatabaseHelper._ID + "=" + _id, null);
                              }

Android SQLite Cursor

A Cursor represents the entire result set of the query. Once the query is fetched a call to cursor.moveToFirst() is made. Calling moveToFirst() does two things:

It allows us to test whether the query returned an empty set (by testing the return value) It moves the cursor to the first result (when the set is not empty)

The following code is used to fetch all records:

                           public Cursor fetch() {
                           String[] columns = new String[] { DatabaseHelper._ID, DatabaseHelper.SUBJECT, DatabaseHelper.DESC };
                           Cursor cursor = database.query(DatabaseHelper.TABLE_NAME, columns, null, null, null, null, null);
                           if (cursor != null) {
                           cursor.moveToFirst();
                             }
                           return cursor;
                                }

Author: Vineela Devi Chalumuri – Android Developer
Source: Turtorialspoint