Everything you need to know about Android SQLite Database – [Complete Free Course]

Database:
Database is a collection of structured information that is stored so that it can be easily accessed, managed and manipulated.

Android provides two packages which support databases:
1. android.db – It is used to server interaction
2. android.db.sqlite; – It is used to support sqlite local database
SQLite:
-It is an open source database.
-It exhibits all the features of the relational database.
-The most important feature is that it is stored locally on your device (mobile, tablet etc) in a local text file and needs no server.
-Android system has by-default implemented the SQLite which means no extra library required.
-SQLite only supports following datatypes:

INT – similar to java long
TEXT – similar to java string
REAL – similar to java double
BLOB – binary large object to store the file contents or image bit datastream etc

We need to convert all the values to these data types and then store in the database.

There are two SQLite classes:
——————————
1. SQLiteOpenHelper class:
To create and upgrade the database, we need to create a subclass of the SQLiteDatabaseHelper class. To create the database, we use our constructor to call
the super constructor of the SQLiteDatabaseHelper parent class by passing arguments like super(context, database_name, factory(null), version(1)).

There are two methods of SQLiteDatabaseClass
i.e. onCreate() is called where we nhaeed to create the tables when database is being created, onUpgrade() is called
where we need to upgrade our database.

for example:
onCreate(Context context){
db.execSQL(“CREATE TABLE table_name (col1 INT PRIMARY KEY AUTOINCREAMENT, col2 TEXT, col3 REAL”);
}
onUpgrade(){
db.execSQL(“DROP TABLE IF EXISTS table_name”);
onCreate(this);
}
2. SQLiteDatabase class:
This class is used to create, delete, execute SQL commands and perform various database management tasks.

It has two methods:
getReadableDatabase() – It returns SQLiteDatabase class object which is only readable (means we can only use select sql statements)
getWriteableDatabase() – It returns SQLiteDatabase class object which supports all the data manipulation statements except select.

============
============
Codes:
============
============

Creating table:
———————
previously done
Dropping table:
———————
previously done
Inserting a record:
———————
SQLiteDatabase db = getWriteableDatabase();
ContentValues values = new ContentValues();
values.put(“col1″, “value”);
values.put(“col2″, “value”);
values.put(“col3″, “value”);
db.insert(“table_name”, null, values);
Updating a record:
———————
SQLiteDatabase db = getWriteableDatabase();
ContentValues values = new ContentValues();
values.put(“col1″, “value”);
values.put(“col2″, “value”);
values.put(“col3″, “value”);
db.update(“table_name”, values, “col1=?”, new String[] {“a”});
Deleting a record:
———————
SQLiteDatabase db = getWriteableDatabase();
db.delete(“table_name”, “col1=?”, new String[] {“a”});
Selecting records:
——————–
Android provides two methods to select/retrieve records:
1. rawQuery;
2. query;
1. rawQuery:
It return cursor object, but only takes two parameters as;
rawQuery(sqlquery, selectionArgs[]);

2. query;
It return cursor object, but only takes following parameters as;
query(table_name, columns[], where condition, selectionArgs[], group by, having, order by);

SQLiteDatabase db = getReadableDatabase();
Cursor cur = db.rawQuery(“SELECT * FROM table_name where col1=?”, new String[] {“a”});
if(cur != null)
cur.moveToFirst();
cur.getString(0);
Q: What is Cursor and its methods?
Cursor is the interface which provides random read-write access to the data collection returned from the selection query.
The following are the methods available in the Cursor interface which iterates through the Cursor, setting the Cursor pointer to desired location.

moveToFirst();
moveToLast();
moveToNext();
moveToPrevios();
moveToPosition(position);

The other methods are:
cur.getCount();
cur.isAfterLast();

NBJokhio

I am Muhammad Naveed Baig Jokhio, the CEO and Founder of Crispylogs, I am doing Graduation from MUET in Computer Systems Engineering. I am a professional Article Writer, Web enthusiast, WordPress lover, a passionate Graphics Designer, Technology geek, Programmer and a Researcher.