‫اﻟﺟﻣﮭورﯾــﺔ اﻟﺟزاﺋرﯾــﺔ اﻟدﯾﻣﻘراطﯾــﺔ اﻟﺷﻌﺑﯾــﺔ‬

Algerian Democratic and Popular Republic

‫وزارة اﻟﺗﻌﻠﯾم اﻟﻌﺎﻟﻲ واﻟﺑﺣث اﻟﻌﻠﻣﻲ‬
Ministry of Higher Education and Scientific Research

University of Ahmed Zabana Relizane

Faculty of Science & Technology
Computer Science Department

Course
Mobile Applications

Chapter 07

Presented by : Dr. Oussama DERNI

oussama.derni@univ-relizane.dz

20 /05/2025
 Chapter 07

Databases with SQLite

PLAN
01 DATABASES WITH SQLITE

• Introduction

• Defining a schematic

• Creating a database

• Inserting data into a database

• Reading data from a database

• Deleting and updating a database

 Introduction

• Mobile application database development is important for many reasons.

• Your database will determine how effectively your mobile application can perform its

intended functions.

• They enable a mobile application to store data, search for information and share data

with other applications.

4
 Defining a schema

• One of the fundamental principles of SQL databases is the schema: a formal statement

of how the database is organized.

• The schema is reflected in the SQL statements you use to create your database.

• You may find it useful to create a companion class, called a contract class, which

explicitly specifies the layout of your schema in a systematic, self-documenting way.

5
 Defining a schema

The contract class

• Is a container for constants that define the names of URIs, tables and columns.
• The contract class lets you use the same constants in all other classes in the same
package.
• This allows you to change a column name in one place and propagate it throughout
your code.
• A good way to organize a contract class is to place global definitions for your entire
database at the root level of the class.
• Then create an inner class for each table. Each inner class lists the columns of the
corresponding table.
6
 Defining a schema

The contract class: declaration of our LibraryManagerContract class

package com.si. gestionnaire_bibliotheque;

import android.provider.BaseColumns;

public final class LibraryManagerContract {

private LibraryManagerContract() {}

/* Inner class that defines the table contents */

public static class BookEntry implements BaseColumns {
public static final String TABLE_NAME = "book";
public static final String COLUMN_NAME_TITLE = "title";
public static final String COLUMN_NAME_DESCRIPTION = "description";
public static final String COLUMN_NAME_PUBLISHER = "publisher";
public static final String COLUMN_NAME_YEAR = "year";
}
}
7
 Create a database

The SQLiteOpenHelper class

• A helper class to manage database creation and versioning.

• You create a subclass implementing onCreate(SQLiteDatabase),

onUpgrade(SQLiteDatabase, int, int) and optionally onOpen(SQLiteDatabase), and
this class takes care of opening the database if it exists, creating it if it doesn't, and
upgrading it if necessary.

• Transactions are used to ensure that the database is always in a reasonable state.

8
 Create a database

The SQLiteOpenHelper class: creating our LibraryManagerDbHelper class

Example :

2 3

1 Right
click

9
 Create a database

The SQLiteOpenHelper class: creating our LibraryManagerDbHelper class

Example :

10
 Create a database

The SQLiteOpenHelper class: creating our LibraryManagerDbHelper class

package com.si.gestionnaire_bibliotheque;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class LibraryManagerDbHelper extends SQLiteOpenHelper {

// If you modify the BDD schema, you must increment the version
public static final int DATABASE_VERSION = 1;
public static final String DATABASE_NAME = "library.db"; // DB filename
// SQL queries for creating and deleting the Book table
private static final String SQL_CREATE_ENTRIES =
"CREATE TABLE " + LibraryManagerContract.BookEntry.TABLE_NAME + " (" +
LibraryManagerContract.BookEntry._ID + " INTEGER PRIMARY KEY," +
LibraryManagerContract.BookEntry.COLUMN_NAME_TITLE + " TEXT," +
LibraryManagerContract.BookEntry.COLUMN_NAME_DESCRIPTION + " TEXT," +
LibraryManagerContract.BookEntry.COLUMN_NAME_PUBLISHER + " TEXT," +
LibraryManagerContract.BookEntry.COLUMN_NAME_YEAR + " INTEGER)";

private static final String SQL_DELETE_ENTRIES =

"DROP TABLE IF EXISTS " + LibraryManagerContract.BookEntry.TABLE_NAME;
11
 Create a database

The SQLiteOpenHelper class: creating our LibraryManagerDbHelper class

public LibraryManagerDbHelper(Context context) {

super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
// Called when the database is first created. This is where table creation and initial
table filling take place.
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(SQL_CREATE_ENTRIES);
}
// Called when the database is to be upgraded. The implementation must use this method to
do whatever it needs to do to upgrade to the new schema version.

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL(SQL_DELETE_ENTRIES);
onCreate(db);
}
public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
onUpgrade(db, oldVersion, newVersion);
}
} 12
 Create a database

Create an instance of the LibraryManagerDbHelper class

• To access your database, instantiate your class, which inherits the SQLiteOpenHelper
class.
• In our example, this is the LibraryManagerDbHelper class.
• the LibraryManagerDbHelper class will handle all types of transactions on the SQLite
database.

LibraryManagerDbHelper dbHelper = new LibraryManagerDbHelper(this);

• We use the dbHelper object for all transactions

13
 Insert data

db.insert()

ajouterBtn.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
// Retrieves data repository in write mode

SQLiteDatabase db = dbHelper.getWritableDatabase();
// Creates a value map, where the column names are the keys
ContentValues values = new ContentValues();
values.put(LibraryManagerContract.BookEntry.COLUMN_NAME_TITLE,
titreLivreEditText.getText().toString());
values.put(LibraryManagerContract.BookEntry.COLUMN_NAME_DESCRIPTION,
descriptionLivreEditText.getText().toString());
values.put(LibraryManagerContract.BookEntry.COLUMN_NAME_PUBLISHER,
editeurEditText.getText().toString());
values.put(LibraryManagerContract.BookEntry.COLUMN_NAME_YEAR,
anneeEditText.getText().toString());
14
 Insert data

db.insert()

More

// Inserts the new row, returning the primary key value of the new row
long newRowId = db.insert(LibraryManagerContract.BookEntry.TABLE_NAME,
null, values);
}
});

• The first argument to insert() is the table name.

• The second argument tells the framework what to do if ContentValues is empty. If you specify a
column name, the framework inserts a row and sets the value of that column to null. If you
specify null, the framework doesn't insert a row.
• The third argument specifies a value map of type ContentValues 15
 Read data

db.query()

afficherBtn.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
// Retrieves data repository in read mode
SQLiteDatabase db = dbHelper.getReadableDatabase();
// Filter results WHERE “year” >= 2000
String selection = LibraryManagerContract.BookEntry.COLUMN_NAME_YEAR + " >= ?";
String[] selectionArgs = { "2000" };

// Cursor: interface provides access to the set of returned results

Cursor cursor = db.query(
LibraryManagerContract.BookEntry.TABLE_NAME, // Target Table
null, // The array of columns to be returned, type String[]
selection, // WHERE clause columns, String type
selectionArgs, // WHERE clause values, String[] type
null, // does not group lines (groupBy), type String
null, // does not filter by groups of lines (having), type String
null // Sort order (orderBy), type String
);
16
 Read data

db.query()

More Column name

// Test if there are no results
// Move the cursor to the next line.
if (cursor.moveToNext() == false) return;

// Store retrieved data in local variables

Long id = cursor.getLong(cursor.getColumnIndexOrThrow(BaseColumns._ID));
String titre =
cursor.getString(cursor.getColumnIndexOrThrow(LibraryManagerContract.BookEntry.COLUMN_NAME_
TITLE));
String description =
cursor.getString(cursor.getColumnIndexOrThrow(LibraryManagerContract.BookEntry.COLUMN_NAME_
DESCRIPTION));
String editeur =
cursor.getString(cursor.getColumnIndexOrThrow(LibraryManagerContract.BookEntry.COLUMN_NAME_
PUBLISHER));
Long annee =
cursor.getLong(cursor.getColumnIndexOrThrow(LibraryManagerContract.BookEntry.COLUMN_NAME_YE
AR)); 17
 Read data

db.query()

More

// Display data in the graphical interface

idEditText.setText(id.toString());
titreLivreEditText.setText(titre);
descriptionLivreEditText.setText(description);
editeurEditText.setText(editeur);
anneeEditText.setText(annee.toString());

});

18
 Read data

db.rawQuery() : create a Book class

public class Book {

Long id;
String title;
String description;
String publisher;
Long year;
public Book(Long id, String title, String description, String publisher, Long year ) {
this.id = id;
this.title = title;
this.description = description;
this.publisher = publisher;
this.year = year;
}
}
19
 Read data

db.rawQuery()

afficherToutBtn.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
List<Book> bookList = new ArrayList<>();
// Gets the data repository in write mode

SQLiteDatabase db = dbHelper.getReadableDatabase();
String sql = "SELECT book._id, book.title, book.description, book.publisher, book.year" + "\n" +
"FROM book" + "\n" +
"ORDER BY book.year desc";
Cursor cursor = db.rawQuery(sql, null);
while(cursor.moveToNext()) {
bookList.add(new Book(
cursor.getLong(cursor.getColumnIndexOrThrow("_id")),
cursor.getString(cursor.getColumnIndexOrThrow("title")),
cursor.getString(cursor.getColumnIndexOrThrow("description")),
cursor.getString(cursor.getColumnIndexOrThrow("publisher")),
cursor.getLong(cursor.getColumnIndexOrThrow("year"))
));
} 20
cursor.close();
 Read data

db.rawQuery()

More

ListView bookListView = findViewById(R.id.bookListView);

List<String> mesLivres = new ArrayList<>();
// Creating a String list from bookList
for(Book book : bookList) {
mesLivres.add(book.id + " : " + book.title + ", " + book.year + ", " + book.publisher);
}
// ArrayAdapter acts as a link between the UI component and the data source. It transforms data from data sources into
View elements that can be displayed in the UI component.
ArrayAdapter<String> livresAdapter =
new ArrayAdapter<String>(v.getContext(), android.R.layout.simple_list_item_1, mesLivres);

bookListView.setAdapter(livresAdapter);

}
});

Layout of ListView items List of items 21

 Update data

db.update()

mettreJourBtn.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
SQLiteDatabase db = dbHelper.getWritableDatabase();

// Creates a value map, where the column names are the keys
ContentValues values = new ContentValues();
values.put(LibraryManagerContract.BookEntry.COLUMN_NAME_TITLE,
titreLivreEditText.getText().toString());
values.put(LibraryManagerContract.BookEntry.COLUMN_NAME_DESCRIPTION,
descriptionLivreEditText.getText().toString());
values.put(LibraryManagerContract.BookEntry.COLUMN_NAME_PUBLISHER,
editeurEditText.getText().toString());
values.put(LibraryManagerContract.BookEntry.COLUMN_NAME_YEAR,
anneeEditText.getText().toString());

22
 Update data

db.update()

More

// Which line to update, according to the ID

String selection = BaseColumns._ID + " = ?";
// Specifies arguments in placeholder order.
String[] selectionArgs = { idEditText.getText().toString() };

int count = db.update(

LibraryManagerContract.BookEntry.TABLE_NAME,
values,
selection,
selectionArgs);
}

});

23
 Delete data

db.delete()

supprimerBtn.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
SQLiteDatabase db = dbHelper.getWritableDatabase();
// Defines the 'where' part of the query.
String selection = BaseColumns._ID + " = ?";
// Specifies arguments in placeholder order.
String[] selectionArgs = { idEditText.getText().toString() };
// Issues an SQL statement.
int deletedRows =
db.delete(LibraryManagerContract.BookEntry.TABLE_NAME,
selection,
selectionArgs);
}
});

24
 Example

@+id/mettreJourBtn
@+id/ajouterBtn
@+id/supprimerBtn
@+id/afficherBtn
@+id/afficherToutBtn

@+id/bookListView

25
Example

26