Kenya institute of software engineering
Mobile Development II
Android Database -SQLite Database
URL: www.KenyaInstituteofsoftwareengineering.com Lecturer: Mr. John Kinyanjui
Tel: +254 202529389, +254 713 810 752, +254 732 609 809 email:
[email protected] Introduction
Android has its own technology for
working with databases and it has
nothing to do with Java Database
Connectivity (JDBC), the technology Java
developers use for accessing data in a
relational database.
In addition, Android ships with SQLite, an
open source database.
URL: www.KenyaInstituteofsoftwareengineering.com Lecturer: Mr. John Kinyanjui
Tel: +254 202529389, +254 713 810 752, +254 732 609 809 email:
[email protected] Database schema
Database Schemas define the
characteristics of the data stored in a
database table. For example, the table
schema for a customer database table
might define that the customer name is
a string of no more than 20 characters in
length, and that the customer phone
number is a numerical data field of a
certain format.
URL: www.KenyaInstituteofsoftwareengineering.com Lecturer: Mr. John Kinyanjui
Tel: +254 202529389, +254 713 810 752, +254 732 609 809 email:
[email protected] Database schema
Schemas are also used to define the
structure of entire databases and the
relationship between the various tables
contained in each database.
URL: www.KenyaInstituteofsoftwareengineering.com Lecturer: Mr. John Kinyanjui
Tel: +254 202529389, +254 713 810 752, +254 732 609 809 email:
[email protected] SQLite
SQLite is an embedded, relational
database management system
(RDBMS).
SQLite is referred to as embedded
because it is provided in the form of a
library that is linked into applications.
URL: www.KenyaInstituteofsoftwareengineering.com Lecturer: Mr. John Kinyanjui
Tel: +254 202529389, +254 713 810 752, +254 732 609 809 email:
[email protected] SQLite
As such, there is no standalone database
server running in the background.
All database operations are handled
internally within the application through
calls to functions contained in the SQLite
library.
URL: www.KenyaInstituteofsoftwareengineering.com Lecturer: Mr. John Kinyanjui
Tel: +254 202529389, +254 713 810 752, +254 732 609 809 email:
[email protected] SQLite
SQLite is written in the C programming
language and as such, the Android SDK
provides a Java based “wrapper” around
the underlying database interface. This
essentially consists of a set of classes
that may be utilized within the Java or
Kotlin code of an application to create
and manage SQLite based databases.
URL: www.KenyaInstituteofsoftwareengineering.com Lecturer: Mr. John Kinyanjui
Tel: +254 202529389, +254 713 810 752, +254 732 609 809 email:
[email protected] The Database API
The SQLiteDatabase and
SQLiteOpenHelper classes, both part
of android.database.sqlite, are the
two most frequently used classes in the
Database API.
In the android.database package, the
Cursor interface is one of the most
important types.
URL: www.KenyaInstituteofsoftwareengineering.com Lecturer: Mr. John Kinyanjui
Tel: +254 202529389, +254 713 810 752, +254 732 609 809 email:
[email protected] The SQLiteOpenHelper Class
To use a database in your Android
application, extend SQLiteOpenHelper
to help with database and table creation
as well as connecting to the database.
In a subclass of SQLiteOpenHelper,
you need to do the following.
URL: www.KenyaInstituteofsoftwareengineering.com Lecturer: Mr. John Kinyanjui
Tel: +254 202529389, +254 713 810 752, +254 732 609 809 email:
[email protected] The SQLiteOpenHelper Class
Provide a constructor that calls its
super, passing, among others, the
Context and the database name.
Override the onCreate and
onUpgrade methods.
URL: www.KenyaInstituteofsoftwareengineering.com Lecturer: Mr. John Kinyanjui
Tel: +254 202529389, +254 713 810 752, +254 732 609 809 email:
[email protected] onCreate() and onUpgrade()
onCreate(), called when there is no database
and your app needs one, which passes you a
SQLiteDatabase object, pointing at a newly-
created database, that you use to populate
with tables and initial data, as appropriate.
onUpgrade(), called when the schema version
you are seeking does not match the schema
version of the database, which passes you a
SQLiteDatabase object and the old and new
version numbers, so you can figure out how
best to convert the database from the old
schema to the new one.
URL: www.KenyaInstituteofsoftwareengineering.com Lecturer: Mr. John Kinyanjui
Tel: +254 202529389, +254 713 810 752, +254 732 609 809 email:
[email protected] Example:
public class SqliteManager extends
SQLiteOpenHelper {
public static final String DATABASE_NAME
= "Test.db";
public static final int version = 1;
public SqliteManager(Context context) {
super(context, DATABASE_NAME, null,
version);
}
@Override
URL: www.KenyaInstituteofsoftwareengineering.com Lecturer: Mr. John Kinyanjui
Tel: +254 202529389, +254 713 810 752, +254 732 609 809 email:
[email protected] Example(Cont…):
public void onCreate(SQLiteDatabase
sqLiteDatabase) {
String dbQuery = "CREATE TABLE Items (id
INTEGER PRIMARY KEY
AUTOINCREMENT,name TEXT, description TEXT)";
sqLiteDatabase.execSQL(dbQuery);
}
@Override
public void onUpgrade(SQLiteDatabase
sqLiteDatabase, int oldVersion, int newVersion)
{
}
}
URL: www.KenyaInstituteofsoftwareengineering.com Lecturer: Mr. John Kinyanjui
Tel: +254 202529389, +254 713 810 752, +254 732 609 809 email:
[email protected] Datatypes
Currently at version 3, SQLite offers a
minimum number of data types: Integer,
Real, Text, Blob, and Numeric.
One interesting feature of SQLite is that
an integer primary key is automatically
auto-incremented when a row is inserted
without passing a value for the field.
URL: www.KenyaInstituteofsoftwareengineering.com Lecturer: Mr. John Kinyanjui
Tel: +254 202529389, +254 713 810 752, +254 732 609 809 email:
[email protected] Connection to the database
SQLiteOpenHelper automatically
manages connections to the underlying
database.
To retrieve the database instance, call
one of these methods, both of which
return an instance of SQLiteDatabase.
public SQLiteDatabase
getReadableDatabase()
public SQLiteDatabase
getWritableDatabase()
URL: www.KenyaInstituteofsoftwareengineering.com Lecturer: Mr. John Kinyanjui
Tel: +254 202529389, +254 713 810 752, +254 732 609 809 email:
[email protected] Connection to the database
The first time one of these methods is
called a database will be created if none
exists. The difference between
getReadableDatabase and
getWritableDatabase is the former
can be used for read-only whereas the
latter can be used to read from and write
to the database.
URL: www.KenyaInstituteofsoftwareengineering.com Lecturer: Mr. John Kinyanjui
Tel: +254 202529389, +254 713 810 752, +254 732 609 809 email:
[email protected] The SQLiteDatabase Class
Once you get a SQLiteDatabase from a
SQLiteOpenHelper’s
getReadableDatabase or
getWritableDatabase method, you
can manipulate the data in the database
by calling the SQLiteDatabase’s insert
or execSQL method.
For example, to add a record, call the
insert method whose signature is as
follows.
URL: www.KenyaInstituteofsoftwareengineering.com Lecturer: Mr. John Kinyanjui
Tel: +254 202529389, +254 713 810 752, +254 732 609 809 email:
[email protected] The SQLiteDatabase Class
public long insert (String table,
String
nullColumnHack,ContentValues
values)
Here, table is the name of the table and
values is an
android.content.ContentValues that
contains pairs of field names/values to
be inserted to the table. This method
returns the row identifier for the new
row.
URL: www.KenyaInstituteofsoftwareengineering.com Lecturer: Mr. John Kinyanjui
Tel: +254 202529389, +254 713 810 752, +254 732 609 809 email:
[email protected] Example
SQLiteDatabase db =
getWritableDatabase();
ContentValues contentValues = new
ContentValues();
contentValues.put("name", “Sugar”);
URL: www.KenyaInstituteofsoftwareengineering.com Lecturer: Mr. John Kinyanjui
Tel: +254 202529389, +254 713 810 752, +254 732 609 809 email:
[email protected] Example
// name - column
contentValues.put("description", “Short
description”);
// description is column in items
table, item.description has value for
description
db.insert("Items", null,
contentValues);//Items is table name
db.close();
URL: www.KenyaInstituteofsoftwareengineering.com Lecturer: Mr. John Kinyanjui
Tel: +254 202529389, +254 713 810 752, +254 732 609 809 email:
[email protected] Update and delete
To update or delete a record, use the
update or delete method, respectively.
The signatures of these methods are as
follows.
public int delete (java.lang.String table,
java.lang.String whereClause,
java.lang.String[] whereArgs)
public int update (java.lang.String table,
android.content.ContentValues values,
java.lang.String whereClause,
java.lang.String[] whereArgs)
URL: www.KenyaInstituteofsoftwareengineering.com Lecturer: Mr. John Kinyanjui
Tel: +254 202529389, +254 713 810 752, +254 732 609 809 email:
[email protected] Update Example
btnupdate.setOnClickListener(new
View.OnClickListener() { @Override
public
void onClick(View view) { String name =
nameText.getText().toString(); String
age =
URL: www.KenyaInstituteofsoftwareengineering.com Lecturer: Mr. John Kinyanjui
Tel: +254 202529389, +254 713 810 752, +254 732 609 809 email:
[email protected] Update Example
ageText.getText().toString();
dbManager.update(_id,
name, age);
Toast.makeText(getApplicationContext(),
"Updated successfully!",
Toast.LENGTH_SHORT).show(); } });
URL: www.KenyaInstituteofsoftwareengineering.com Lecturer: Mr. John Kinyanjui
Tel: +254 202529389, +254 713 810 752, +254 732 609 809 email:
[email protected] Delete Example
delete(_id);
Toast.makeText(getApplicationContext(),
"Deleted successfully!",
Toast.LENGTH_SHORT).show();
URL: www.KenyaInstituteofsoftwareengineering.com Lecturer: Mr. John Kinyanjui
Tel: +254 202529389, +254 713 810 752, +254 732 609 809 email:
[email protected] execSQL
To execute a SQL statement, use the
execSQL method.
public void execSQL
(java.lang.String sql)
URL: www.KenyaInstituteofsoftwareengineering.com Lecturer: Mr. John Kinyanjui
Tel: +254 202529389, +254 713 810 752, +254 732 609 809 email:
[email protected] Retrieve example
DBManager dbManager = new
DBManager(getActivity());
dbManager.open();
Cursor cursor = dbManager.fetch();
cursor.moveToFirst();
URL: www.KenyaInstituteofsoftwareengineering.com Lecturer: Mr. John Kinyanjui
Tel: +254 202529389, +254 713 810 752, +254 732 609 809 email:
[email protected] Retrieve example
final TextView studentName =
(TextView)
getActivity().findViewById(R.id.name
OfStudent);
studentName.setText(cursor.getString
(0));
URL: www.KenyaInstituteofsoftwareengineering.com Lecturer: Mr. John Kinyanjui
Tel: +254 202529389, +254 713 810 752, +254 732 609 809 email:
[email protected] END
URL: www.KenyaInstituteofsoftwareengineering.com Lecturer: Mr. John Kinyanjui
Tel: +254 202529389, +254 713 810 752, +254 732 609 809 email:
[email protected]
