SQLLite

SQLite

Introduction
The SQLite database is an open-source database engine that Android uses to store small
amounts of structured data, such as your list of contacts or SMS (Short Message Service)
information. It is a built-in database engine and can create and execute all database
operations. To use the SQLite database engine, you first need to create the database
and the database tables and then use it to write and read data. Once you are done
working with your database operations, you need to close it to avoid any unpredictable
behavior. The lifecycle of the Android SQLite database activities can be summarized like
this:
1. Create a database.
2. Open the database.
3. Read/write and update the data.
4. Close the database.

2
 SQLite
SQLiteOpenHelper Class
Android has a package called [Link] which contains all the interfaces
and classes that are needed to create and manage a database. Android uses the
SQLiteOpenHelper class to create and open databases. The class has multiple
constructors and public methods to manage the database. One of the widely used
SQLiteOpenHelper constructors is:
SQLiteOpenHelper (Context context, String databaseName,
[Link] factory, int version)
where the input parameters are:
• Context is the activity that opens the database.
• String databaseName is the file that will contain the data.
• CursorFactory is an object to create cursor objects and normally is null.
• int version is the version of your database.

3
 SQLite
• To use a database in your app, you must create a subclass of the SQLiteOpenHelper
class for your application. In the code snippet shown in the following listing,
MySQLiteHelper is a subclass of SQLiteOpenHelper.
import [Link];
import [Link];
import [Link];

public class MySQLiteHelper extends SQLiteOpenHelper {. . .}

4
 SQLite
• SQLiteDatabase Class
Once you create your database, you need to be able to manage it.
That is, you need to be able to run SQL queries on your database to create
and delete tables, insert data into tables, remove and update table entries,
etc.
The SQLiteDatabase class has methods that enable you to run such SQL
queries.
For our database demo app, we will be using the SQLiteDatabase methods
to run queries on the database.

5
 SQLite
• Overriding Methods of the SQLiteOpenHelper Class
There are several essential methods from the SQLiteOpenHelper class that
you must override to implement whatever tasks and actions you want your
app to do.
The methods, or functions, are the onCreate(), onUpgrade(), and
onOpen(optional) methods and the class constructor methods.
Below we describe the roles of each method in the development of SQLite
databases for your apps.

6
 SQLite
• The Class Constructor Method
In the subclass you write to create and open a database, you must call a superclass
constructor and pass certain information to it.
The SQLiteOpenHelper has three constructors. The signatures of these constructors are
shown below:
SQLiteOpenHelper(Context context, String name, [Link] factory,
int version);
SQLiteOpenHelper(Context context, String name, [Link] factory,
int version, DatabaseErrorHandler errorHandler);
SQLiteOpenHelper(Context context, String name,

int version, [Link] openParams);

7
 SQLite
• In our Android database demo app, we used the first constructor using the following
line of code: super (context, DATABASE_NAME, null, DATABASE_VERSION);
The context parameter is DatabaseMainActivity, the database name is
[Link], CursorFactory is null, and DATABASE_VERSION is an int value, for
example, 1 or 2, depending on the version of the database. The code snippet shown in
bellow listing shows a call to the superclass constructor for our database demo project.
Note that the database file extension is db.
public class MySQLiteHelper extends SQLiteOpenHelper {
private static final String DATABASE_NAME = "[Link]";
private static final int DATABASE_VERSION = 1;
MySQLiteHelper (Context context) {
super (context, DATABASE_NAME, null, DATABASE_VERSION);
}...}

8
 SQLite
• The onCreate() Method
If the database does not exist, i.e., the onCreate() method of the MySQLiteHelper class
has not been called, the onCreate() method is called immediately. The onCreate() method
signature would be like this:
@Override
public void onCreate(SQLiteDatabase database) { // database is the database object.
}
To elaborate more, in the subclass you create, you call the superclass constructor which in
turn calls the onCreate() method to create a database. The call to the superclass
constructor should happen only once, the first time you create the database. You do not
want to create a new database every time you run the app. Instead, you should use an
already created database.

9
 SQLite
• Create Table in Database
The onCreate() method can be used to execute SQL table creation
statements. The code snippet shown in the follwoing Listing is an example of
a table creation statement where the SQL statement is created as a string
and saved in a static final string variable named DATABASE_CREATE.
Listing An example of creating a table statement.
private static final String DATABASE_CREATE = "create table " +
TABLE_Of_My_ITEMS + " ( " + Item_ID
+ " integer primary key autoincrement, " + ITEM_NAME + " text not null);";

10
 SQLite
When executed, the statement above creates a table called
tableOfMyItems that has two fields, or columns. The columns are _id and an
itemName. The _id type is an integer, and the ITEM_NAME type is text or
string. The _id is the primary key for the table and is an auto-increment, i.e.,
when a record is inserted into the table, a unique number is generated
automatically.
The create table statement would be executed only once, when you create a
database for the first time. After writing your create table statement, you
need to execute it to create tables. The execSQL() method is a function that
executes a string SQL statement; see the code snippet below:
@Override
public void onCreate(SQLiteDatabase database) {
[Link](DATABASE CREATE);
}

11
 SQLite
• The execSQL() method executes a single SQL statement that is not a
select or any other SQL statement that returns data.
So far, we have described four steps to create a database in Android using
the SQLite database, and they are:
1. Subclassing the SQLiteOpenHelper class.
2. Composing the SQL statement for the database/table creation.
3. Calling the superclass constructor.
4. Calling the execSQL() method with the database create statement. This
call is made inside the onCreate() method.
The code snippet in following Listing summarizes the four steps above

12
 SQLite
• [Link].
package [Link];
import [Link];
import [Link];
import [Link];
import [Link];
// 1. Subclassing SQLiteOpenHelper class
public class MySQLiteHelper extends SQLiteOpenHelper {
public static final String TABLE_Of_My_ITEMS = "tableOfMyItems";
public static final String Item_ID = "_id";
public static final String ITEM_NAME = "itemName";
private static final String DATABASE_NAME ="[Link]";
private static final int DATABASE_VERSION = 2;

13
 SQLite
• // 2. Table/Database creation statement
private static final String DATABASE_CREATE = "create table " + TABLE_Of_My_ITEMS + "("
+ Item_ID
+ " integer primary key autoincrement, " + ITEM_NAME + " text not null);";
// 3. super constructor call
MySQLiteHelper (Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION); }
@Override
public void onCreate(SQLiteDatabase database) { // 4. Executing SQL command

[Link](DATABASE_CREATE); }

14
 SQLite
• onUpgrade Method
The SQLiteOpenHelper class calls the onUpgrade() method when the
database needs to be upgraded. You update your database if, for example,
you change a type of a field in a table, add/remove fields in a table, or
add/delete tables.
If you change the database version number inside your code and restart your
app, the onUpgrade() method is called. The method signature for
onUpgrade() is as follows:
public abstract void onUpgrade (SQLiteDatabase db, int oldVersion, int
newVersion);

15
 SQLite
• Once the onUpgrade() method is invoked, you have a chance to upgrade your data.
For example, you can add new columns to an existing table, create a new table, drop
a table, or change the table schema. You can also delete all your data using the
execSQL() method the with DROP statement as follows:
[Link](“DROP TABLE IF EXISTS TABLENAME”);
In our database demo project, when onUpgrade() is called, we will delete all the data in
the table and call the onCreate() method with the table name to recreate the table;
see the code snippet shown in the following Listing.
An example of an onUpgrade() method implementation.
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
[Link]("DROP TABLE IF EXISTS TABLE_Of_My_ITEMS" );
onCreate(db); // or [Link](“CREATE TABLE . . .”); }

16
 SQLite
• onDowngrade Method
The onDowngrade() method is called when you want to downgrade the
database. If you decide to use an older version of the database, the
onDowngrade() method can be used. The onDowngrade() method
implementation may include a create table statement or/and drop a table
statement.
Unlike the onCreate() and onUpgrade() methods, the onDowngrade()
method is not an abstract method; therefore, you do not have to override it,
i.e., its implementation is optional. You do not normally use the
onDowngrade() method, but if you need it, it exists and is one of the
SQLiteOpenHelper methods

17
 SQLite
• To invoke the onDowngrade() method, lower the version number of your
database. For example, if the current version of your database is 3, change
it to 2, and restart your app; you will see that the onDowngrade() function
is called.
Restarting your app results in calling the MySQLiteHelper class constructor,
and when the database version value is lower than the previously provided
value, the onDowngrade() method is called.
Since onDowngrade() is an optional method, you do not need to use the
@Override keyword when implementing it.
An example of how the onDowngrade() method can be implemented is
shown in the following Listing.

18
 SQLite
• An example of an onDowngrade method implementation.
public void onDowngrade (SQLiteDatabase db, int oldVersion, int
newVersion){
Log.w(MySQLiteHelper .[Link](),
"Downgrading database from version " + newVersion + " to " + oldVersion);
[Link]("DROP TABLE IF EXISTS TABLE_Of_My_ITEMS" );
onCreate(database);
}
Here, the onCreate() method handles the request for downgrading the
database.

19
 SQLite
• onOpen() Method
The onOpen() method is called when the database has been opened. It is an
optional method that you do not have to override. If you decided to override
the onOpen() method, you should include the database status check using
the isReadOnly() method in the implementation body of your method.
The isReadOnly() method returns a true or false value indicating whether the
database mode is read-only or read/write, respectively. You should allow
database updates only if the database status is read and write.
By default, when the onCreate() and onUpgrade()/onDowngrade() methods
are called, the onOpen() method gets called last. It can also get called
regardless of the onCreate()/onUpgrade() methods, i.e., you can call it
separately when needed.

20
 SQLite
• Read and Read/Write Access
Once you create your database, you need to open it to use it. You have
the option to open your database in a read-only mode using the
getReadableDatabase() method or in a read/write mode. In our demo
app, we open the database in a read/write mode as shown below:
dbHelper = new MySQLiteHelper (context);
public void open () throws SQLException {
database = [Link](); }
To open a database in read mode only, it can be done as follows:
public void open () throws SQLException {
database= [Link]() ; }

21
 SQLite
The return type of the getWritableDatabase() method is a
SQLiteDatabase object.
If the database does not exist when either the
getWritableDatabase() method or the getReadableDatabase()
method is called, the onCreate() method is called to create and
return the database.

22
 SQLite
• The execSQL Method from SQLiteDatabase Class
Android uses the execSQL() method from the SQLiteDatabase class to
execute a single SQL statement. The statement should not return any data.
For example, the execSQL() method cannot be used with the select
statement.
The SQLiteDatabase class has more than one version of the execSQL()
method. The one we used in our demo app has this signature: public void
execSQL (String sql);.
The type for the input parameter for the execSQL() method is a string.
Hence, the SQL statement you write for your app needs to be formed as a
string and passed to execSQL() method to run. We have used the execSQL()
method throughout our demo app.

23
 SQLite
Below is an example of how we used the execSQL() method. The input parameter is a
create table statement.
final String contact_table = "create table contacts" + " ( " +
"firstName" + " text primary key , " + "lastName" + " text , " +
"email" + " text , " + " phoneNumber" + " number " + ");";
[Link](contact_table);
In the code above, the SQL statement creates a table called contacts which
has four fields: first name, last name, email, and phone number. The type of
the first three fields is text, i.e., string, and the phone number type is an
integer.

24
 SQLite
• Content Values and Cursor Objects
In this part of the chapter, we will study how to interact with the data in the database.
You will learn how to insert, remove, update, and query data in the database tables.
Android has two classes to enable such interactions or transactions. The classes are
ContentValues and Cursor; both are described below.

25
 SQLite
• Content Values and Insert Method
The insert() method from the SQLiteDatabase class is an easy way to insert a
row into the database tables. The signature for the insert method is as
follows.
public long insert (String table, String nullColumnHack, ContentValues
values);
The first parameter of the method is the table name into which you want to
insert the row.
The second parameter is an optional string, which may be null, and the last
parameter is an object of type ContentValues.
The ContentValues class is a class that you can use to store a set of values
using key/value pairs
26
 SQLite
• If you have multiple fields such as first name, last name, email, and phone
number for which you need to insert a row into a table, you first create a
ContentValues object and then use the insert() method from the
SQLiteDatabase class to add the newly created object into a table row.
You create ContentValues objects by calling the constructor of the class.
You can use the put(String ColumnName, String value) method of the
ContentValues class to add data into ContentValues objects.
Once you have your ContentValues object ready, use the insert() method
from the SQLiteDatabase class to add the ContentValues object into a table
row.

27
 SQLite
• The code snippet below shows the two steps above, i.e., how the
ContentValues object is used to insert data into the database using the
put() and the insert() methods:
ContentValues cValues = new ContentValues();
[Link]("FristName", "Abdul-Rahman");
[Link]("LastName", "Mawlood-Yunis"); [Link]("email",
"amawloodyunis@[Link]" );
[Link]("TableName","NullPlaceHolder,",cValues) ;

28
 SQLite
• Note that the second parameter to the insert() method, i.e., the string
nullColumnHack of the method, is the column name. If you forgot to provide the
column name, the “NullPlaceHolder” will be inserted into the row for the
missing column name.
In our demo app, the createItem() method is called with an item object as a
parameter to add an item object to the database. The key for the ContentValues
objects is MySQLiteHelper.ITEM_NAME, and the values are fields of the incoming
parameter. See the code snippet below.
public Item createItem(Item item) {
ContentValues values = new ContentValues() ;
[Link](MySQLiteHelper .ITEM_NAME, [Link]());
long insertId = [Link]
(MySQLiteHelper .TABLE_Of_My_ITEMS, null,values); . . .
}
29
 SQLite
• Cursor
Android has a class called cursor. Objects of the cursor class can hold rows
returned from a query. Cursor objects can contain a single row or an entire
table. You can think of the cursor class as an iterator class in Java or a
reference to the result set returned from a query that you can iterate
through.
The cursor class has several useful functions. These include methods to
move where the cursor is pointing to, for example, moveToNext,
moveToFirst, moveToLast, etc. The following Table lists some of these
methods and their descriptions.
The code snippets showing in the following Listings show how it is used
inside the getAllItem() and createItem() methods.

30
 SQLite
Table 1. Useful methods of the Cursor class and their description

Returns the number of rows a query returned, i.e., the number of rowsin
getCount()
the cursor
moveToFirst() Moves the cursor to the first row
moveToLast() Moves the cursor to the last row
moveToNext() Moves the cursor to the next row
moveToPosition(int
Moves the cursor to a specified position
position)

close() Closes the cursor object and releases all its resources

31
 SQLite
• Listing Using the Cursor object to return all the items in the table.
public List<Item> getAllItem() {
List<Item> items = new ArrayList<>();
Cursor cursor = [Link] (
MySQLiteHelper .TABLE_Of_My_ITEMS, allItems, null, null, null, null, null);
[Link]();
while (![Link]()) {
Item item = cursorToItem(cursor);
Log.d(TAG, "get item = " + cursorToItem(cursor).toString());
[Link](item);
[Link]();
[Link]();
return items;
}

32
 SQLite
• Listing Creating an Item object and inserting it into a table.
public Item createItem(Item item) {
ContentValues values = new ContentValues();
[Link](MySQLiteHelper .ITEM_NAME, [Link]());
long insertId = [Link](
MySQLiteHelper .TABLE_Of_My_ITEMS, null,values);
Cursor cursor = [Link](
MySQLiteHelper .TABLE_Of_My_ITEMS, allItems, MySQLiteHelper .Item_ID +
" = " + insertId, null, null, null, null);
[Link]();
Item newItem = cursorToItem(cursor); [Link]();
return newItem;
}
33
 SQLite
There are multiple interesting things that one can observe about
the two methods above.
Both examples show that running queries on the database returns
cursor objects, and the cursor objects hold the item object
inserted into the table.
The createItem() method demonstrates how an id that is returned
from the [Link] () method can be reused as part of the
query to retrieve an item with the specified id.
In both methods, the ContentValues object is used to insert an
item object into the database table. We will analyze the database
demo code in more detail in the next part of this chapter.

34
 SQLite
• Query Data
The query() method is a convenient function for creating the built-
in SQL statements. Android provided three different versions of the
query methods. The return type for all three query methods is a
cursor object which can be processed by the app. The syntax and
components of one of the query methods used in our demo app
are as follows:
query ( boolean distinct, String tableName, String[] columns, String
selection, String[] selectionArgs,
String groupBy, String having, String orderBy, String limit)
35
 SQLite
• The meaning of each parameter is as follows:
• distinct is a boolean variable and when set to true, it will return a unique result.
• The string tableName is the table name for the FROM part of SELECT clause.
• Columns [] is a list of strings, i.e., column names to be used for selection. For
example, the new String [] = [“A”,”B”,”C”] is the same as saying SELECT (A, B, C)
columns.
• Selection is a string for the WHERE clause without using the WHERE keyword,
for example, “ firstName like ? AND lastName not ? “. The ? is a placeholder
which will be replaced by the selectionArgs [] array.
• selectionArgs[] is an array of strings to replace the “?” in the selection. The order
of replacement is in a left to right order.
• The names of the other parameters explain their purposes.

36
 SQLite
• Below are two test cases using the query clause. The database is called
tableOfMyItems, and the columns are id and ITEM_NAME.
Example 1
database = [Link]();
[Link](true, "tableOfMyItems", null,null, null,null, null, null, null);
The query statement above is equivalent to SELECT * from tableOfMyItems.
Example 2
Cursor c = [Link]("tableOfMyItems", new String[]{"*"}, "_id" + "=?" + " And " + "
ITEM_NAME",
new String[]{"8", "Gift Cards"}, null, null, null, null);
The query in example 2 is equivalent to:
SELECT * FROM tableOfMyItems WHERE _id=8 AND itemName = Gift Cards ;

37
 SQLite
• rawQuery
If you are comfortable with writing the SQL statements yourself, the rawQuery()
construct or method lets you do it. You embed your query inside the rawQuery()
method to run in your app. In the following Listing, some examples from our demo
app on how to write and use a rawQuery() method in the app are shown.
Listing Examples from our demo app on how to write and use a raw query.
public void test4() {
database = [Link]();
String q1 = "select itemName from tableOfMyItems where _id=8";
String q2 ="select _id from tableOfMyItems where itemName='Gift Cards’”;
String q3 ="select _id from tableOfMyItems where itemName like '%Gift%'";

38
 SQLite
// 1.
[Link](q1, null); [Link](q2, null); [Link](q3, null);
//2. Get a specific item
Cursor c = [Link](
"select * from tableOfMyItems where _id = ? ", new String[] { " 25 " });
//3. get all table names from the database Cursor tables = [Link](
"select name from sqlite_master where type= ? ", new String [] { " table "}) ;
//4. get count
Cursor cursor = [Link]("select count(*) " + "from tableOfMyItems", null);
//5. to format the output
Cursor cursor2 = [Link]([Link]( "select count(*)
from %s", "tableOfMyItems"), null);
}
To see the result of the above queries, you need to run the demo app prepared for this chapter. See the
source code of the demo app for more examples of rawQuery().

39
 SQLite
• More Methods of the SQLiteDatabase Class
The SQLiteDatabase class is one of the main classes for interacting with
database tables when using the SQLiteDatabase with Android.
This class has methods for all types of operations that can be performed on
the database. So far, we have used the create(), insert(), query(), and
rawQuery() methods, but there are more.
Below we describe three more methods of this class that are very relevant to
database management.

40
 SQLite
• Replace Method
The replace() method from the SQLiteDatabase is used to replace a
row in a table. It will insert a new row if a row does not already
exist. The signature of the method is as follows:
public long replace (String table,
String nullColumnHack, ContentValues initialValues);

41
 SQLite
• Update Method
The SQLiteDatabase has a method for updating rows in tables, the
update() method. The signature of the method is as follows:
public int update (String table,
ContentValues values, String whereClause, String[] whereArgs);

42
 SQLite
• Delete Method
The delete() method is used to delete rows in a table. The
signature of the method is as follows:
public int delete (String table, String whereClause, String[]
whereArgs);
The methods replace(), update(), and delete(), which are used in
our demo app, will be described in the next part of this chapter.

43