Kenya institute of software engineering

Mobile Development II

Android Database -SQLite Database

URL: [Link] Lecturer: Mr. John Kinyanjui

Tel: +254 202529389, +254 713 810 752, +254 732 609 809 email: kenyainstituteofsoftwareeng@[Link]
 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: [Link] Lecturer: Mr. John Kinyanjui

Tel: +254 202529389, +254 713 810 752, +254 732 609 809 email: kenyainstituteofsoftwareeng@[Link]
 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: [Link] Lecturer: Mr. John Kinyanjui

Tel: +254 202529389, +254 713 810 752, +254 732 609 809 email: kenyainstituteofsoftwareeng@[Link]
 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: [Link] Lecturer: Mr. John Kinyanjui

Tel: +254 202529389, +254 713 810 752, +254 732 609 809 email: kenyainstituteofsoftwareeng@[Link]
 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: [Link] Lecturer: Mr. John Kinyanjui

Tel: +254 202529389, +254 713 810 752, +254 732 609 809 email: kenyainstituteofsoftwareeng@[Link]
 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: [Link] Lecturer: Mr. John Kinyanjui

Tel: +254 202529389, +254 713 810 752, +254 732 609 809 email: kenyainstituteofsoftwareeng@[Link]
 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: [Link] Lecturer: Mr. John Kinyanjui

Tel: +254 202529389, +254 713 810 752, +254 732 609 809 email: kenyainstituteofsoftwareeng@[Link]
 The Database API
 The SQLiteDatabase and
SQLiteOpenHelper classes, both part
of [Link], are the
two most frequently used classes in the
Database API.
 In the [Link] package, the
Cursor interface is one of the most
important types.

URL: [Link] Lecturer: Mr. John Kinyanjui

Tel: +254 202529389, +254 713 810 752, +254 732 609 809 email: kenyainstituteofsoftwareeng@[Link]
 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: [Link] Lecturer: Mr. John Kinyanjui

Tel: +254 202529389, +254 713 810 752, +254 732 609 809 email: kenyainstituteofsoftwareeng@[Link]
 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: [Link] Lecturer: Mr. John Kinyanjui

Tel: +254 202529389, +254 713 810 752, +254 732 609 809 email: kenyainstituteofsoftwareeng@[Link]
 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: [Link] Lecturer: Mr. John Kinyanjui
Tel: +254 202529389, +254 713 810 752, +254 732 609 809 email: kenyainstituteofsoftwareeng@[Link]
 Example:
public class SqliteManager extends
SQLiteOpenHelper {
public static final String DATABASE_NAME
= "[Link]";
public static final int version = 1;

public SqliteManager(Context context) {

super(context, DATABASE_NAME, null,
version);
}
@Override
URL: [Link] Lecturer: Mr. John Kinyanjui
Tel: +254 202529389, +254 713 810 752, +254 732 609 809 email: kenyainstituteofsoftwareeng@[Link]
 Example(Cont…):
public void onCreate(SQLiteDatabase
sqLiteDatabase) {
String dbQuery = "CREATE TABLE Items (id
INTEGER PRIMARY KEY
AUTOINCREMENT,name TEXT, description TEXT)";
[Link](dbQuery);
}
@Override
public void onUpgrade(SQLiteDatabase
sqLiteDatabase, int oldVersion, int newVersion)
{
}
}
URL: [Link] Lecturer: Mr. John Kinyanjui
Tel: +254 202529389, +254 713 810 752, +254 732 609 809 email: kenyainstituteofsoftwareeng@[Link]
 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: [Link] Lecturer: Mr. John Kinyanjui

Tel: +254 202529389, +254 713 810 752, +254 732 609 809 email: kenyainstituteofsoftwareeng@[Link]
 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: [Link] Lecturer: Mr. John Kinyanjui
Tel: +254 202529389, +254 713 810 752, +254 732 609 809 email: kenyainstituteofsoftwareeng@[Link]
 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: [Link] Lecturer: Mr. John Kinyanjui

Tel: +254 202529389, +254 713 810 752, +254 732 609 809 email: kenyainstituteofsoftwareeng@[Link]
 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: [Link] Lecturer: Mr. John Kinyanjui

Tel: +254 202529389, +254 713 810 752, +254 732 609 809 email: kenyainstituteofsoftwareeng@[Link]
 The SQLiteDatabase Class
 public long insert (String table,
String
nullColumnHack,ContentValues
values)
 Here, table is the name of the table and
values is an
[Link] that
contains pairs of field names/values to
be inserted to the table. This method
returns the row identifier for the new
row.
URL: [Link] Lecturer: Mr. John Kinyanjui
Tel: +254 202529389, +254 713 810 752, +254 732 609 809 email: kenyainstituteofsoftwareeng@[Link]
 Example
SQLiteDatabase db =
getWritableDatabase();
ContentValues contentValues = new
ContentValues();
[Link]("name", “Sugar”);

URL: [Link] Lecturer: Mr. John Kinyanjui

Tel: +254 202529389, +254 713 810 752, +254 732 609 809 email: kenyainstituteofsoftwareeng@[Link]
 Example
// name - column
[Link]("description", “Short
description”);
// description is column in items
table, [Link] has value for
description
[Link]("Items", null,
contentValues);//Items is table name
[Link]();

URL: [Link] Lecturer: Mr. John Kinyanjui

Tel: +254 202529389, +254 713 810 752, +254 732 609 809 email: kenyainstituteofsoftwareeng@[Link]
 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 ([Link] table,
[Link] whereClause,
[Link][] whereArgs)
 public int update ([Link] table,
[Link] values,
[Link] whereClause,
[Link][] whereArgs)
URL: [Link] Lecturer: Mr. John Kinyanjui
Tel: +254 202529389, +254 713 810 752, +254 732 609 809 email: kenyainstituteofsoftwareeng@[Link]
 Update Example
[Link](new
[Link]() { @Override
public
void onClick(View view) { String name =
[Link]().toString(); String
age =

URL: [Link] Lecturer: Mr. John Kinyanjui

Tel: +254 202529389, +254 713 810 752, +254 732 609 809 email: kenyainstituteofsoftwareeng@[Link]
 Update Example
[Link]().toString();
[Link](_id,
name, age);
[Link](getApplicationContext(),
"Updated successfully!",
Toast.LENGTH_SHORT).show(); } });

URL: [Link] Lecturer: Mr. John Kinyanjui

Tel: +254 202529389, +254 713 810 752, +254 732 609 809 email: kenyainstituteofsoftwareeng@[Link]
 Delete Example
delete(_id);
[Link](getApplicationContext(),
"Deleted successfully!",
Toast.LENGTH_SHORT).show();

URL: [Link] Lecturer: Mr. John Kinyanjui

Tel: +254 202529389, +254 713 810 752, +254 732 609 809 email: kenyainstituteofsoftwareeng@[Link]
 execSQL
 To execute a SQL statement, use the
execSQL method.
 public void execSQL
([Link] sql)

URL: [Link] Lecturer: Mr. John Kinyanjui

Tel: +254 202529389, +254 713 810 752, +254 732 609 809 email: kenyainstituteofsoftwareeng@[Link]
 Retrieve example
DBManager dbManager = new
DBManager(getActivity());
[Link]();
Cursor cursor = [Link]();
[Link]();

URL: [Link] Lecturer: Mr. John Kinyanjui

Tel: +254 202529389, +254 713 810 752, +254 732 609 809 email: kenyainstituteofsoftwareeng@[Link]
 Retrieve example
final TextView studentName =
(TextView)
getActivity().findViewById([Link]
OfStudent);
[Link]([Link]
(0));

URL: [Link] Lecturer: Mr. John Kinyanjui

Tel: +254 202529389, +254 713 810 752, +254 732 609 809 email: kenyainstituteofsoftwareeng@[Link]
 END

URL: [Link] Lecturer: Mr. John Kinyanjui

Tel: +254 202529389, +254 713 810 752, +254 732 609 809 email: kenyainstituteofsoftwareeng@[Link]