SQLite 

database

MOBILE
APPLICATION
DEVELOPMENT MAD MOODLE 1
 INDEX

▪ Introduction to SQLite database
▪ creating and opening a database
▪ creating tables, inserting retrieving and deleting data

MOBILE
APPLICATION
DEVELOPMENT MAD MOODLE 2
 Introduction to SQLite
❏ SQLite is an open-source relational database
management System i.e. used to perform database operations on
android devices such as storing, manipulating or retrieving persistent
data from the database.
❏ It is embedded in android by default. 
So, there is no need to perform any 
database setup or administration
 task.

MOBILE
APPLICATION
MAD MOODLE 3
DEVELOPMENT
 Introduction to SQLite

MOBILE
APPLICATION
MAD MOODLE 4
DEVELOPMENT
 Features of SQLite

 It is open source: free for use and no license is required.

 Zero-Configuration: The configuration process is very easy, no setup or
administration needed.
 SQLite is serverless: SQLite doesn't require a different server process or
system to operate
 It is very small and light weight-  less than 400KB fully configured or less than
250KB with optional features omitted.
 Variable-length records: The length of the columns is variable and is not fixed
 The transactions follow ACID properties i.e. atomicity, consistency, isolation,
and durability even after system crashes and power failures.
 Complete database can be stored in a single cross-platform disk file.
MOBILE
 SQLite is
APPLICATION written in ANSI-C and provides simple and easy-to-use API.
DEVELOPMENT MAD MOODLE 5
 Architecture of Sqlite

MOBILE
APPLICATION
DEVELOPMENT MAD MOODLE 6
 Architecture of Sqlite
This interface will read the SQL queries what ever it is generated by the user
and it will pass this SQL Commands to the SQL Command Processor

The SQL Command Processor will read the SQL Commands from Interface and it
will forward that SQL Query Strings to the Tokenizer of SQL Compiler

This Virtual Machine will read the code what ever the code generated by the code
MOBILE generator of the SQL Compiler
APPLICATION
DEVELOPMENT MAD MOODLE 7
 Architecture of Sqlite
When a string containing SQL statements is to be executed, the
interface passes that string to the tokenizer. The job of the
tokenizer is to break the original string up into tokens and pass
those tokens one by one to the parser. The tokenizer is hand-
coded in C in the file tokenize.c.

The parser is the piece that assigns meaning to tokens based on

their context, it does not leak memory when syntax errors are
encountered, parser assembles tokens into complete SQL
statements

the code generator to produce virtual machine code that will do the work that the SQL
statements request. Ex: vacuum.c and where.c in which where.c handles code generation
MOBILE for WHERE clauses on SELECT, UPDATE and DELETE statements
APPLICATION
DEVELOPMENT MAD MOODLE 8
 Architecture of Sqlite
An SQLite database is maintained on disk using a B-tree
implementation found in the btree.c source file. A separate B-tree
is used for each table and index in the database. All B-trees are
stored in the same disk file. Details of the file format are recorded
in a large comment at the beginning of btree.c.

The B-tree module requests information from the disk in fixed-

size chunks. The default chunk size is 1024 bytes but can vary
between 512 and 65536 bytes. The page cache is responsible for
reading, writing, and caching these chunks. The page cache also
provides the rollback and atomic commit abstraction and takes
care of locking of the database file.

In order to provide portability between POSIX (Portable Operating System Interface (for
Unix))and Win32 operating systems, SQLite uses an abstraction layer to interface with the
operating system. The interface to the OS abstraction layer is defined in os.h.
MOBILE
APPLICATION
DEVELOPMENT MAD MOODLE 9
 Creating and opening a database

To create or open database we need to import the following package.

“android.database.sqlite”.  

It contains the following Classes and interfaces.

§ SQLite Database 
§ Exposes methods to manage a SQLite database. 
§ SQLite Database has methods to create, delete, execute SQL commands, and
perform other common database management tasks.
§ SQLiteOpenHelper 
§  A helper class to manage database creation and version management.

MOBILE
APPLICATION
DEVELOPMENT MAD MOODLE 10
 Creating and opening a database
Creating a Database using  SQLiteDatabase class:
• Contains a method  creating or opening database i.e.
openOrCreateDatabase(..)
Syntax:
SQLiteDatabase db=openOrCreateDatabase(String name, int mode, factory);
//name is the name of database name .
//mode  is permissions of a table accessing
//facrory is the obj of CursorFacrory

Example:
SQLiteDatabase database = openOrCreateDatabase(“students.db",

MOBILE Context.MODE_PRIVATE, null);

APPLICATION
DEVELOPMENT MAD MOODLE 11
 SQLiteOpenHelper class
• SQLiteOpenHelper is an abstract class i used for database
creation and version management

• For performing any database operation, you have to provide the implementation
of onCreate() and onUpgrade() methods of SQLiteOpenHelper class.

MOBILE
APPLICATION
DEVELOPMENT MAD MOODLE 12
  Implementation of SQLiteOpenHelper

MOBILE
APPLICATION
DEVELOPMENT MAD MOODLE 13
  SQLiteOpenHelper- DB Creation
Import android.database.sqlite.SQLiteOpenHelper;
public class DatabaseHelper extends SQLiteOpenHelper {
private static final String DBNAME = “mydb”;
private static final int DBVERSION = 1;

public DatabaseHelper(Context context){

super(context,DBNAME,null,DBVERSION);
}

@Override
public void onCreate(SQLiteDatabase db) {
String stmt=“CREATE TABLE STUDENT(ID INTERGER PRIMARYKEY, FNAME TEXT, LNAME TEXT,
MOB TEXT)”;
db.execSQL(stmt);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
 }

         }
MOBILE
APPLICATION
DEVELOPMENT MAD MOODLE 14
  SQLiteOpenHelper- Adding/inserting data
• To create a new row, construct a ContentValues object and use its put() methods to add name/value pairs representing each column
name and its associated value.
• Insert the new row by passing the Content Values into the insert method called on the target database — along with the table name —
as shown
//Code to add data
void insertData(int id, String fn, String ln, String mb) 
{
/*To access a database using the SQLite Open Helper, call getWritableDatabase() */
SQLiteDatabase db = this.getWritableDatabase();

ContentValues values = new ContentValues();  
values.put(ID,id ); 
values.put(FNAME, fn); 
values.put(LNAME, ln); 
values.put(MOB, mb);

// Inserting Row  

long res=db.insert(“STUDENT”, null, values);  

db.close(); // Closing database connection
}
MOBILE
APPLICATION
DEVELOPMENT MAD MOODLE 15
  SQLiteOpenHelper- updating data
• Updating rows is also done with Content Values. Create a new ContentValues object, using the put() methods to assign new
values to each column you want to update.

//Code to add data

void updateData(int id, String fn, String ln, String mb) 
{
/*To access a database using the SQLite Open Helper, call getWritableDatabase */

SQLiteDatabase db = this.getWritableDatabase();

ContentValues values = new ContentValues();  
values.put(FNAME, fn); 
values.put(LNAME, ln); 

// Inserting Row  

int res=db.update(“STUDENT”, Values, “FNAME=?", new String[]{fn});  

db.close(); // Closing database connection
}

MOBILE
APPLICATION
DEVELOPMENT MAD MOODLE 16
  SQLiteOpenHelper- deleting data
• To delete a row, simply call the delete method on a database, specifying the table name and
a where clause that returns the rows you want to delete

//Code to add data

void deleteData(String fn) 
{

SQLiteDatabase db = this.getWritableDatabase();

long result = db.delete(“STUDENTS”, “FNAME=?", new String[]{fn});

db.close(); // Closing database connection
}

MOBILE
APPLICATION
DEVELOPMENT MAD MOODLE 17
  SQLiteOpenHelper- Querying the database
• 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 pointing to the table. 

//Code to add data

void dispData() 
{
SQLiteDatabase db = this.getWritableDatabase();
String selectQuery = "SELECT  * FROM  STUDENTS"; 
Cursor cursor = DB.rawQuery (selectQuery, null);
if (cursor.moveToFirst()) {
   do {   //read data from each column of the row.
int rno= cursor.getInt(0);  // reads the values of first column
 String fn=cursor.getString(1)); // reads the values of second column
 String ln= cursor.getString(2));  
 }while (cursor.moveToNext());  
   } 
db.close(); // Closing database connection
}

MOBILE
APPLICATION
DEVELOPMENT MAD MOODLE 18
 THANK YOU

MOBILE
APPLICATION
DEVELOPMENT MAD MOODLE 19