SQLite is a self-contained i.e., having all the
modules and dependencies with in it & does not depend much on external
libraries. This is one of the important reasons why most of the embedded
devices use SQLite as temporary storage. Well-known examples are Google,
Mozilla Firefox, Apple, Adobe and Android devices. It has zero-configuration
with out any separate process for installation or configuration.
Android has 2 main packages, which lets us to use
SQLite in the application. android.database
and android.database.sqllite has all
the necessary api's for create/read/update and delete actions.
Also SQLiteOpenHelper class provides public
methods like onCreate, onUpgrade, onOpen
methods. onCreate is called whenever
the database is created for first time, onOpen
when the DB has been opened and onUpgrade when the databased needs to be
upgraded.
A database handler class which implements the
SQLiteOpenHelper should call the constructor to create helper object to create,
open or manage database. Constructor takes database name & database
version. See below.
public SQLiteOpenHelper (Context context, String name,
SQLiteDatabase.CursorFactory factory, int version)
As a first step to implement SQLite, the database
handler class should extend SQLiteOpenHelper. In the constructor method call
the super() method by passing the context, database name and version.
public
DatabaseHandler(Context context) {
super(context, DATABASE_NAME,
null, DATABASE_VERSION);
}
Database handler class should also implement
the onCreate & onUpgrade methods. onCreate method is called by framework
when the DB is created for the first time and onUpgrade method is used whenever
DB tables need to be updated.
ex:
public void onCreate(SQLiteDatabase db) {
// Category table create query
String
CREATE_CATEGORIES_TABLE = "CREATE TABLE "
+ TABLE_LABELS + "("
+ KEY_ID + "
INTEGER PRIMARY KEY," + KEY_NAME
+ " TEXT)";
db.execSQL(CREATE_CATEGORIES_TABLE);
}
// Upgrading database
public void onUpgrade(SQLiteDatabase db, int oldVersion, int
newVersion) {
// Drop older table if existed
db.execSQL("DROP TABLE IF EXISTS " + TABLE_LABELS);
// Create tables again
onCreate(db);
}
For any read/write operation, SQLiteOpenHelper class provides
getReadableDatabase() & getWritableDatabase() respectively. These methods
returns reference to SQLiteDatabase object which can be used to create, read,
update and delete from tables. For ex, below example shows Create/Open
database.
SQLiteDatabase db = this.getReadableDatabase();
// Create/open a database
for read/write operation.
SQLiteDatabase db = this.getWritableDatabase();
The db object returned from the getReadableDatabase
& getWritableDatabase provides create(), insert(), delete(), update()
methods.
Ex:
insert takes table name, optional null column and content values that need to
be inserted into the table.
//
Inserting Row
db.insert(TABLE_LABELS, null,
values);
We can write query in 2 ways, First is raw query like "select *
from ", "insert into" etc. and second one is structured query.
For this SQLiteDatabase
provides 2 methods query() & rawQuery().
query() methods provides structured interface which takes table
name, array of columns that needs to be fetched, filter column (for WHERE
clause), groupBy, orderBy, having and limit. Example below.
db.query(TABLE_NAME, String[]
columnNames, String[] selection, group by, having, order by);
rawQuery() takes the raw sql just like any sql database. ex
db.rawQuery("select
* from TABLE_NAME", null);
Both query() & rawQuery() returns something called Cursor object.
Cursor is not actual result set; rather it is an object that is positioned
before the first row of the result set. The handler class should iterate
through the cursor object to get through all rows.
ex:
Cursor cursor =
db.rawQuery(selectQuery, null);
// Looping through all rows and adding to list
if (cursor.moveToFirst()) {
do {
cursor.getString(1);
} while (cursor.moveToNext());
}
Cursor.get*(), methods are the way to access columns of each rows.
All the above methods insert(), read(), delete() etc. should implement
close() method to close db & cursor operations.
//
closing connection
cursor.close();
db.close();
Reference materials:
sqlite.org
Dr Barbara Hecker
Dr Barbara Hecker
