SQLite database tutorial under

Android
Prepared by: Joseph Azar

Objectives
• In this tutorial, you will save the data to the SQLite database.
the help of a simple example and perform some basic operations (CRUD) of SQLite.
oC: Create
or: Read
oU: Update
oD: Delete
• You will learn to use the MVC (Model-View-Controller) framework. You will separate
your project in packages of models, views, and controllers in order to better structure the
project
• You will discover the LayoutInflater and its use.
• You will discover the ScrollView element
• You will create a view (e.g., TextView) programmatically using Java.
• You will learn to use AlertDialog

Note: This project was prepared before the migration to AndroidX.

Introduction
SQLite is a lightweight database that is already integrated into the Android framework. It is a
open source database that only takes 250 KB of memory at runtime.
The SQLite database supports a limited number of data types. These types
data are:

• Text
• Integer
• Real
• BLOB

Thus, all other data types must be converted into the data types mentioned.
before inserting them into the database. SQLite supports features

1
standard relational database such as SQL syntax, transactions and the
prepared syntax.

In this example, we will use the following operations from SQLite:

Create a new database

Update - Update the already created database
Insert- Inserts a new entry into the database
Retrieve one or more entries from the database
Update - Update the database entries
Delete - Delete entries from the database

Create a new project

2
Create a database
Then create a new class named SQLiteHelper and extend it with the class
SQLiteOpenHelper is a helper class that manages the creation and
database version management of SQLite in Android. Thus, in the class
SQLiteHelper, we will implement the methods onCreate(), onUpgrade() and onOpen()
of the SQLiteOpenHelper class to create the table for our application.

We are going to create a student table with three columns: ID of integer type, Name of type
text and group of type text.

3
4
In the code above, we created a database table to store the
student information. Here, we retrieve the SQLiteDatabase instance and create a
new database table using the onCreate() method of our class
assistance. Now, we can create methods to perform various operations
related to the database.

Create two packages, respectively Managers and Views. Move the MainActivity class to
the package Viewset the class SQLiteHelper to the package Managers

5
Create a student model
Create a new package 'Models'. Under this package, create a new class 'Student'.
La classeStudentsera utilisée pour instancier des objetsstudentque nous utiliserons pour
insert and extract data from the database.

6
Insert information into the database

Under the package 'Managers', create a new class StudentHandler.

Extend the SQLiteHelper class and create the constructor

To insert data into the database table, we need access to

writing to our database. In the newly created "StudentHandler" class, we
we need to create a new instance of our class SQLiteHelper to get the instance of
database.

Next, we need to create a method that will perform the insertion operation. So,
start by getting write access to our database using:

SQLiteDatabase db = [Link]();

7
Ensuite, créez un objetContentValueset ajoutez les informations à l’objet à insérer.
ContentValues is essentially a class used to store a set of values.
that can be handled by ContentResolver.

Finally, using the insert() method of the database instance, add the values to the
desired table. We need to pass the name of the database table and the object
ContentValues as arguments for the insert() method. Also close access to the
database once the insertion is completed.

public intaddStudent (Student student) {

SQLiteDatabase db = [Link]();
ContentValues values = newContentValues();
[Link](dbHelper.KEY_NAME, [Link]());
[Link](dbHelper.KEY_GROUP, [Link]());
longinsertId = [Link](dbHelper.TABLE_STUDENTS, null, values);
[Link]();
return(int)insertId;
}

Retrieve information from the database

We can retrieve all the information stored in our table or retrieve a
set of information based on a selection criterion.
To retrieve a dataset from the table, we need readable access.
"readable" of our database instance.

SQLiteDatabase db = [Link]();

8
Then we will use the method query() to implement the condition and retrieve the
table data. It returns an objectCursor through which we can iterate the entire set
results. Here is the method example to retrieve the data based on a
condition:

publicStudent getStudent(intid) {
SQLiteDatabase db =[Link]();
Cursor cursor = [Link](dbHelper.TABLE_STUDENTS, newString[] { dbHelper.KEY_ID,
dbHelper.KEY_NAME, dbHelper.KEY_GROUP}, dbHelper.KEY_ID+ "=?",
newString[] { [Link](id) }null, null, null, null);
if(cursor != null)
[Link]();
Student student =newStudent([Link]([Link](0)),
[Link](1), [Link](2);
returnstudent
}

Pour récupérer les informations complètes présentes dans la table de base de données, nous
we need write access to our database instance. This looks more like
an SQL query where we select all the data from the table. To perform this
operation, we will use the rawQuery() method of the database instance. It
will return a Cursor object that we will use to extract the entire set of results. Here is
the example code for retrieving the data:

9
publicList<Student> getAllStudents() {
SQLiteDatabase db = [Link]();
List<Student> studentList = newArrayList<Student>();
String selectQuery = SELECT * FROM + dbHelper.TABLE_STUDENTS;
Cursor cursor = [Link](selectQuery,null);
if([Link]()) {
do{
Student student =newStudent([Link]([Link](0)),
[Link](1),[Link](2);
[Link](student);
} while([Link]());
}
returnstudentList;
}

10
Delete information from the database
To delete entries from the database table, we need to define a criterion for
Selection to identify the entries to delete. We will therefore use the delete() method.
from the database that requires three arguments:

• Database name
• Selection clause ('Where')
• Selection argument

Combined, these arguments will create a delete request that will work exactly
in the same way as an SQL query. Here is the code example to delete the data
from the table:
public voiddeleteStudent(Student student) {
SQLiteDatabase db = [Link]();
[Link](dbHelper.TABLE_STUDENTS, dbHelper.KEY_ID + " = ?",
newString[] { [Link]([Link]()) });
[Link]();
}

Update the information

We can update the information in our database table using
the update() method of our database instance. To update the table, we
we must add the values in the object ContentValues and set a selection criterion for
Identify the entries to modify. Here is the example of code:

11
We have now finished with the package managers and models in which we
We have created the database operations. We will now create the views.

Créer l'interface graphique

Place a 'Create Student' button

• Place a 'Create Student' button in your res/layout/activity_main.xml

• Delete the 'Hello World!' TextView
• set the button text attribute to 'Create a student'
• Define the value of the button id as "@+id/buttonCreateStudent"

The code should look like this:

12
We are going to define the OnClickListener for the 'Create a student' button.

We can identify the button by its identifier 'buttonCreateStudent'.

The following code will be placed in the onCreate() method, under setContentView
([Link].activity_main); code from your [Link] file.

Right-click on the 'Views' package, create a new class

"OnClickListenerCreateStudent" and insert the following code:

13
Prepare the input form for a student.
Right-click on res/layout/è Click on 'New'è Click on "File"> Name it
student_input_form.xml

Place the following code in student_input_form.xml:

<RelativeLayout xmlns:android="[Link]
xmlns:tools="[Link]
android:layout_width="match_parent"
android:layout_height="match_parent">

<EditText
android:id="@+id/editTextStudentname"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_alignParentLeft="true"
android:layout_alignParentTop="true"
android:hint="Nom de l'etudiant"
android:singleLine="true">

<requestFocus/>
</EditText>

<EditText
android:id="@+id/editTextStudentGroup"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_alignParentLeft="true"
android:layout_below="@+id/editTextStudentname"
android:hint="Groupe de l'etudiant"
android:singleLine="true"/>

</RelativeLayout>

14
Show the 'create a student' form to the user
Return and open your class '[Link]'.
Add the following code to your class (under the onClick(View view) function) to open the
Create a student.

Context context = [Link]().getContext();

LayoutInflater inflater = (LayoutInflater)

[Link](Context.LAYOUT_INFLATER_SERVICE);
finalView formElementsView = [Link]([Link] input form, null,
false);

finalEditText editTextStudentname = (EditText)

[Link]([Link]);
finalEditText editTextStudentGroup = (EditText)
[Link]([Link]);

[Link](context)
.setView(formElementsView)
.setTitle(Create a student)
.setPositiveButton(Add,
[Link]() {
public voidonClick(DialogInterface dialog,intid) {

[Link]();
}

}).show();

15
Note:

The LayoutInflater class is used to instantiate the XML layout file into objects.
View correspondents.

In other words, it takes an XML file as input and builds the objects view from it.
this one.

It is never used directly - use getLayoutInflater() or getSystemService(String)

to retrieve a standard LayoutInflater instance that is already connected to the current context
and properly configured for the device on which you are running.

[Link]() provides a way to convert a file res/layout/*.xml defining

a view as a usable objectView in the source code of your application.

Inflater means reading the XML file that describes a layout (or GUI element) and creating the
real objects that correspond to it, thus making the object visible in an Android application.

If you run the application after adding the code above. After pressing the
button 'Create a student', you should get the result below:

16
Record the user's input
Open the class OnClickListenerCreateStudent. In the onClick() method of AlertDialog, we
We will record the student's information.
Using the already created StudentHandler class in the Managers package, we will
save the entered information

[Link](context)
.setView(formElementsView)
.setTitle(Create a student)
.setPositiveButton(Add,
[Link]() {
public voidonClick(DialogInterface dialog,intid) {
Student student=newStudent();
String studentName =[Link]().toString();
String studentGroup =
[Link]().toString();

[Link](studentName);
[Link](studentGroup);

intcreatedStudent= new
StudentHandler(context).addStudent(student);

[Link](context,The student has been well registered.,

Toast.SHORT_LENGTH).show();

[Link]();
}

).show();

17
Count the records in the Android database
SQLite
In your layout/activity_main.xml, place a TextView under your 'Create a' button.
student

18
Now, we want to create the method count in the class Managers/StudentHandler.
Ouvrez la classe StudentHandler et ajoutez la méthode ci-dessous:

public intcount() {
SQLiteDatabase db = [Link]();
String sql = SELECT * FROM + dbHelper.TABLE_STUDENTS;
intrecordCount = [Link](sql,null).getCount();
[Link]();
returnrecordCount;
}

19
Open the MainActivity class, create the countRecords() method, and call it in onCreate()

We also want to display the number of students in the database after having
registered a new student. Open the classOnClickListenerCreateStudent and add the
method countRecords after creating a new student to update the TextView value.

20
[Link](context)
.setView(formElementsView)
.setTitle(Create a student)
.setPositiveButton(Add,
[Link]() {
public voidonClick(DialogInterface dialog,intid) {
Student student=newStudent();
String studentName =[Link]().toString();
String studentGroup =
[Link]().toString();

[Link](studentName);
[Link](studentGroup);

intcreatedStudent= new
StudentHandler(context).addStudent(student);

[Link](context,The student has been properly registered.,

Toast.SHORT_LENGTH).show();
countRecords(context,view);
[Link]();
}

}).show();

public voidcountRecords(Context context,View view) {

intstudentCount = newStudentHandler(context).count();
TextView textViewRecordCount = (TextView)
[Link]().findViewById([Link]);
[Link](studentCount + records found.);
}

21
Run your application and try to create a new student. When the application is
created for the first time, it displays the text '0 records found'. After creating
a new student, it will display '1 record found'

22
Read records from the SQLite database
Open the file res/layout/activity_main.xml and place a ScrollView with a LinearLayout.
the interior under the TextView 'textViewRecordCount'.

23
In your [Link], create the method readRecords(). This will display the
database records on the user interface.

public voidreadRecords() {

LinearLayout linearLayoutRecords = (LinearLayout)

findViewById([Link]);
[Link]();

List<Student> students =newStudentHandler([Link]();

if([Link]() > 0) {

for(Student obj : students) {

intid = [Link]();
String studentName = [Link]();
String studentgroup = [Link]();

String textViewContents = [Link](id) +"-" + studentName + " - " +

student group
TextView textViewStudentItem= newTextView(this);
[Link](0,10,0,10);
[Link](textViewContents);
[Link]([Link](id));
[Link](textViewStudentItem);
}

else{

TextView locationItem =newTextView(this);

[Link](8,8,8,8);
[Link](No records at the moment.);
[Link](locationItem);
}

call the method readRecords() in Create().

24
We will now create the method readRecords() in the class in the same way.
OnClickListenerCreateStudent when we create a new student to update the
content of ScrollView.

Open OnClickListenerCreateStudent and add the method readRecords as below:

25
public voidreadRecords(Context context,View view) {

LinearLayout linearLayoutRecords = (LinearLayout)

[Link]().findViewById([Link]);
[Link]();

List<Student> students = newStudentHandler(context).getAllStudents();

if([Link]() > 0) {

for(Student obj : students) {

intid = [Link]();
String studentName = [Link]();
String studentgroup = [Link]();

String textViewContents = [Link](id) +"-" + studentName + " - " +

student group
TextView textViewStudentItem=newTextView(context);
[Link](0,10,0,10);
[Link](textViewContents);
[Link]([Link](id));
[Link](textViewStudentItem);
}

else{

TextView locationItem =newTextView(context);

[Link](8,8,8,8);
[Link](No recording at the moment.);
[Link](locationItem);
}

Call the function readRecords(context, view) in the onClick method.

26
[Link](context)
.setView(formElementsView)
.setTitle(Create a student)
.setPositiveButton(Add,
[Link]() {
public voidonClick(DialogInterface dialog,intid) {
Student student=newStudent();
String studentName =[Link]().toString();
String studentGroup =
[Link]().toString();

[Link](studentName);
[Link](studentGroup);

intcreatedStudent= new
StudentHandler(context).addStudent(student);

[Link](context,The student has been well registered.,

Toast.SHORT_LENGTH).show();
countRecords(context,view);
readRecords(context,view);
[Link]();
}

}).show();

27
28
Now, launch the application and try to create new students. The list of
students must appear as shown in the image below:

Updating a record in the SQLite database

Create a new class [Link] under the package View. We
we will use a long click to give the user an update option.

29
Define OnLongClickListener for each of the records to display. Access your
[Link]è readRecords (),dans la boucle ‘for’, placez le code suivant sous
[Link]([Link](id));

Do the same thing in the class OnClickListenerCreateStudent.

To test the above code, launch the application and long press on a student.
TextView. The dialogue below should appear:

30
In the onClick() method of the AlertDialog, in the onLongClick(view) method of the class
OnLongClickListenerStudentRecord, insert the following code. “Modify” has an index of 0.

@Override
public booleanonLongClick(finalView view) {
context = [Link]();
id= [Link]().toString();

finalCharSequence[] items = {Modifier, Delete};

[Link](context).setTitle(Student File)
.setItems(items,[Link]() {
public voidonClick(DialogInterface dialog,intitem) {
if(item == 0) {
editRecord([Link](id),view);
}
[Link]();
}
}).show();

return false;
}

We want to create the method editRecord in which we allow the user to

modify a student's information and perform an update operation.

31
public voideditRecord(final intstudentId,finalView view) {
LayoutInflater inflater = (LayoutInflater)
[Link](Context.LAYOUT_INFLATER_SERVICE);
finalView formElementsView = [Link]([Link] input form, null,
false);

Student student = newStudentHandler(context).getStudent(studentId);

finalEditText editTextStudentname = (EditText)

[Link]([Link]);
finalEditText editTextStudentGroup = (EditText)
[Link]([Link]);

[Link]([Link]());
[Link]([Link]());

[Link](context)
.setView(formElementsView)
.setTitle(Update the information)
.setPositiveButton(Save,
[Link]() {
public voidonClick(DialogInterface dialog,intid) {
Student objectStudent = newStudent();
[Link](studentId);

[Link]([Link]().toString());

[Link]([Link]().toString());

intupdatedStudent=new
StudentHandler(context).updateStudent(objectStudent);

[Link](context, The student's file has been put to

day., Toast.SHORT_LENGTH).show();
countRecords(context,view);
readRecords(context,view);
[Link]();
}

}).show();

Finally, add the methods countRecordset readRecords (the same as those created in the)
classeOnClickListenerCreateStudent) à la classeOnLongClickListenerStudentRecordet appelez-
in the method editRecord in order to update the graphical interface after an operation
of update.

32
public voidcountRecords(Context context,View view) {
intstudentCount = newStudentHandler(context).count();
TextView textViewRecordCount = (TextView)
[Link]().findViewById([Link]);
[Link](studentCount + records found.);
}

public voidreadRecords(Context context, View view) {

LinearLayout linearLayoutRecords = (LinearLayout)

[Link]().findViewById([Link]);
[Link]();

List<Student> students = newStudentHandler(context).getAllStudents();

if([Link]() > 0) {

for(Student obj : students) {

intid = [Link]();
String studentName = [Link]();
String studentgroup = [Link]();

String textViewContents = [Link](id) +"-" + studentName + " - " +

student group
TextView textViewStudentItem= newTextView(context);
[Link](0,10,0,10);
[Link](textViewContents);
[Link]([Link](id));
[Link](new
OnLongClickListenerStudentRecord());
[Link](textViewStudentItem);
}

else{

TextView locationItem = newTextView(context);

[Link](8,8,8,8);
[Link](No records at the moment.);
[Link](locationItem);
}

33
34
Now, test your application and make sure that the update operation works
correctly.

35
Delete a record in Android SQLite
Access your [Link]è onLongClick ()è in
the method AlertDialog onClick(). Put the following code after the first if statement:

@Override
public booleanonLongClick(finalView view) {
context= [Link]();
id= [Link]().toString();

finalCharSequence[] items = { Modifier, Delete};

[Link](context).setTitle(Student File)
.setItems(items, [Link]() {
public voidonClick(DialogInterface dialog,intitem) {
if(item == 0) {
editRecord([Link](id),view);
}else if(item == 1) {
Student student = newStudent();
[Link]([Link](id));
newStudentHandler([Link](student);
[Link](context, Record successfully deleted,
Toast.SHORT_LENGTH).show();
countRecords(context,view);
readRecords(context,view);
}
[Link]();
}
}).show();

return false;
}

36
Launch the application and perform the deletion operation.

37