Working with databases

• Android SQLite
• 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 run me, so every Android applica on
can create its own SQLite databases.
• Android SQLite na ve 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 rela onal 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.
 Database - Crea on
• Database - Package
• The main package is android.database.sqlite that
contains the classes to manage your own databases
• 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
• SQLiteDatabase mydatabase = openOrCreateDatabase("
your database name",MODE_PRIVATE,null);
• we can create table or insert data into table
using execSQL method defined in
SQLiteDatabase class.
• Its syntax is given below
• mydatabase.execSQL("CREATE TABLE IF NOT
EXISTS TutorialsPoint(Username VARCHAR,
Password VARCHAR);");
• mydatabase.execSQL("INSERT INTO
TutorialsPoint VALUES('admin','admin');");
 Database - Fetching
• We can retrieve anything from database using an object
of the Cursor class.
• We will call a method of this class called rawQuery and
it will return a resultset with the cursor poin ng to the
table.
• We can move the cursor forward and retrieve the data.
• Cursor resultSet = mydatbase.rawQuery("Select * from
TutorialsPoint",null);
• resultSet.moveToFirst();
• String username = resultSet.getString(0);
• String password = resultSet.getString(1);
• There are other func ons available in the Cursor
class that allows us to effec vely retrieve the
data. That includes
• getColumnCount()
• This method return the total number of columns
of the table.
• getColumnIndex(String columnName)
• This method returns the index number of a
column by specifying the name of the column
• getColumnName(int columnIndex)
• This method returns the name of the column by
specifying the index of the column
• getColumnNames()
• This method returns the array of all the column
names of the table.
• getCount()
• This method returns the total number of rows in
the cursor
• getPosi on()
• This method returns the current posi on of the
cursor in the table
• isClosed()
• This method returns true if the cursor is closed
and return false otherwise
Android SQLite SQLiteOpenHelper
• Android has features available to handle changing
database schemas, which mostly depend on using
the SQLiteOpenHelper class.
• SQLiteOpenHelper is designed to get rid of two very
common problems.
• When the applica on runs the first me – At this
point, we do not yet have a database. So we will have
to create the tables, indexes, starter data, and so on.
• When the applica on is upgraded to a newer schema
– Our database will s ll be on the old schema from
the older edi on of the app. We will have op on to
alter the database schema to match the needs of the
rest of the app.
• SQLiteOpenHelper wraps up these logic to create
and upgrade a database as per our specifica ons.
For that we’ll need to create a custom subclass
of SQLiteOpenHelper implemen ng at least the
following three methods.
• Constructor : This takes the Context (e.g., an
Ac vity), the name of the database, an op onal
cursor factory, and an integer represen ng the
version of the database schema you are using
(typically star ng from 1 and increment later).
• public DatabaseHelper(Context context)
• {
• super(context, DB_NAME, null, DB_VERSION);
• }
• onCreate(SQLiteDatabase db) : It’s called when
there is no database and the app needs one. It
passes us a SQLiteDatabase object, poin ng to a
newly-created database, that we can populate with
tables and ini al data.
• onUpgrade(SQLiteDatabase db, int oldVersion, int
newVersion) : It’s called when the schema version
we need does not match the schema version of the
database, It passes us a SQLiteDatabase object and
the old and new version numbers. Hence we can
figure out the best way to convert the database
from the old schema to the new one.
 Opening and Closing Android SQLite Database Connec on
• Before performing any database opera ons like
insert, update, delete records in a table, first open
the database connec on by
calling getWritableDatabase() method as shown
below:
• public DBManager open() throws SQLExcep on {
dbHelper = new DatabaseHelper(context); database
= dbHelper.getWritableDatabase(); return this; }
• The dbHelper is an instance of the subclass
of SQLiteOpenHelper.
• To close a database connec on the following method
is invoked.
• public void close() { dbHelper.close(); }
 Inser ng new Record into Android
SQLite database table
• The following code snippet shows how to insert a new
record in the android SQLite database.
• 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);
• }
• Content Values creates an empty set of values using the
given ini al size.
 Upda ng Record in Android SQLite
database table
• The following snippet shows how to update a single
record.
• 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;
• }
Android SQLite – Dele ng a Record
• We just need to pass the id of the record to
be deleted as shown below.
• public void delete(long _id)
• { database.delete(DatabaseHelper.
TABLE_NAME, DatabaseHelper._ID + "=" + _id,
null);
• }
 Android SQLite Cursor
• A Cursor represents the en re 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 tes ng 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;
}