Cohort+Problem+5+-+SQLite+Database

=Introduction=

Previously, you've stored data and/or accessed data in your app using
 * SharedPreferences
 * JSON file format

This example will show you how to use SQLite as an internal database within your app.

=Objective= = = We'll make an app that aims to help you record your expenses.



=On Relational Databases=

A very quick summary on database design:

First Normal Form. Every row in a table must have the same number of columns. Put in another way, each column should only have one value.

Second Normal Form. Every row has a unique key, and everything in a row is related to the key. Facts that don't relate to the key belong in different tables.

//TO DO complete this

**Part 1 - Decide your database schema and create the contract**

The database will have one table with three columns
 * ID
 * Remarks
 * Amount

You will need to refer to these column names throughout your programming, hence it is helpful to create a database contract class that specifies the tables and the column names.

I'll call it **SpendingContract.java.** We have one inner class called **SpendingEntry** that represents that single table in the database. SpendingEntry should implement **BaseColumns**, so that we get a default ID column called "_ID".

code format="java" public class SpendingContract {

public static final class SpendingEntry implements BaseColumns {

public static final String TABLE_NAME = "SpendingRecord"; public static final String COL_AMOUNT = "Amount"; public static final String COL_REMARKS = "Remarks";

} } code

=Part 2 - Create a database helper class=

Relevant SQLite Commands
Next, we'll create a helper class. It has two methods that :
 * create the table
 * creates a new table when the schema changes

SQLite command to create a table
The SQLite command that creates the table is code format="sql" CREATE TABLE SpendingRecord ( _ID INTEGER PRIMARY KEY AUTOINCREMENT, Amount TEXT NOT NULL, Remarks TEXT NOT NULL ); code

What are the data types of each column? What is the meaning of primary key? What is the meaning of NOT NULL?

Command to deleting a Table
When the schema changes (e.g. you add new columns to your table), we'll have to delete the table and then create the table again. The SQLite command to delete the table is: code format="sql" DROP TABLE IF EXISTS SpendingRecord code

Skeleton & Constructor
Our class, SpendingDbHelper.java needs to extend the abstract class SQLiteOpenHelper. At this stage, check the definition of SQLiteOpenHelper and answer the following questions without scrolling down further: (To see the definition, put your cursor on the class name and press Alt+Space).
 * what inputs does the constructor take? (this helps you to write the constructor of your subclass)
 * what methods are abstract? (this helps you to know what methods to override)

Here's the skeleton:

code format="java" public class SpendingDbHelper extends SQLiteOpenHelper {

private final Context context; private static final int DATABASE_VERSION = 1; private SQLiteDatabase sqLiteDatabase;

SpendingDbHelper(Context context){ super(context, SpendingContract.SpendingEntry.TABLE_NAME, null, DATABASE_VERSION ); this.context = context; }

@Override public void onCreate(SQLiteDatabase sqLiteDatabase) {

//TODO 2.1 Build the SQLite command string to create the table final String SQL_CREATE_TABLE = "";

//Execute the SQL command sqLiteDatabase.execSQL(SQL_CREATE_TABLE);

}

@Override public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {

//TO DO 2.2 Build the SQLite command string to delete the table //TO DO 2.3 execute this SQLite command //TO DO 2.4 Create the table

} code

Writing the onCreate method
The onCreate method has two tasks:
 * build the string that contains the SQLite command. Remember to use the information in the database contract.
 * execute this command by calling the execSQL method of the sqLiteDatabase.

Writing the onUpgrade method
This is invoked when your class variable DATABASE_VERSION increases.

The onUpgrade method has three tasks:
 * build the string that contains the SQLite command. Remember to use the information in the database contract.
 * execute this command by calling the execSQL method of the sqLiteDatabase.
 * call onCreate.

=Part 3 - Create your UI and access the database=

Layout
Using RelativeLayout, use the following XML Layout.

Aligning widgets in RelativeLayout is best done in the Design View, but we may not have enough classroom time for that.

code format="xml" 











 code

MainActivity.java
The outline of the code for MainActivity is as follows.

code format="java" public class MainActivity extends AppCompatActivity {

private SpendingDbHelper spendingDbHelper; private SQLiteDatabase spendingDb;

@Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main);

//TO DO 3.1 Create a new instance of SpendingDbHelper //TO DO 3.2 Get an instance of the database that can be written to

}

public void onClickAddToDb(View view){

//TO DO 3.3 Get instances of the edit text widgets and extract their contents //TO DO 3.4 Store the contents into a ContentValues Object //TO DO 3.5 Insert the ContentValues object into the database //TO DO 3.6 (Optional) Display a Toast Message

}

public void onClickGetEntireDb(View view){

//TO DO 3.7 Call the query or rawQuery method of the spendingDb object and //         store the result in a Cursor object

//TO DO 3.8 Extract the data from the Cursor object and //         display it on the textView widget

}

public void onClickDeleteFromDb(View view){

try{ //TO DO 3.9 Get an instance of the editText Widget //         and extract the contents //TO DO 3.10 Delete the entry

}catch(Exception ex){ //TO DO 3.11 Display a toast if an exception occurs }

} } code

TO DO 3.1 and 3.2
We recall that **Context** is a superclass of **AppCompatActivity**. Use this fact to complete TO DO 3.1.

Next, our **SpendingDbHelper** is a subclass of **SQLiteOpenHelper**. What methods can you call to get your database? Complete TO DO 3.2.

Introduction
At this stage, our database is empty, so we need to give it data.

TO DO 3.3 - Get instances of the editText widgets and get their contents
By now you should be able to complete this yourself.

TO DO 3.4, 3.5, 3.6 - Update the database.
The command to insert data into a database is as follows. Check its method signature and you'll realize that it requires an instance of the ContentValues class. code format="java" spendingDb.insert; code

So we'll do just that. Create an instance of the ContentValues class and proceed as follows.

code format="java" //variables which are completed in TO DO 3.3 String amount = ; String remarks = ;

ContentValues cv = new ContentValues; cv.put(SpendingContract.SpendingEntry.COL_AMOUNT, amount ); cv.put(SpendingContract.SpendingEntry.COL_REMARKS, remarks); spendingDb.insert(SpendingContract.SpendingEntry.TABLE_NAME, null ,cv );

code

You should be able to make the Toast to tell the user that the entry has been updated successfully.

Querying the entire database
= =

SQLite Command To Query The Entire Database
To get the entire table mytable:

code format="sql" SELECT * FROM mytable code

To get the name column from mytable where the gender column contains 'female'

code format="sql" SELECT name FROM mytable WHERE gender = 'female' code

The SQLiteDatabase class offers a few ways to execute these queries.

TO DO 3.7 (option 1) - using the rawQuery method
If we want to query the entire table using the rawQuery method, we would do this. The result is stored in a Cursor object. This object stores the table that the query returns and points to the start of the table.

code format="java" Cursor cursor = spendingDb.rawQuery("SELECT * FROM mytable", null); code With this example, implement it for this particular context.

TO 3.7 (option 2) - using the query method
To use the query method, one would do this. code format="java" Cursor cursor = spendingDb.query("mytable", null, null, null, null, null, null) code

Cursor object - a few things to consider
The Cursor class is an abstract class. So what is the actual type of the cursor variable?

How many rows are there in the database? Print the result to the Logcat.

What methods allow you to move between the rows of the database?

How do you locate the columns?

TO DO 3.8 - Extract the result and display
The pseudocode for this part is as follows
 * get the column indices of the columns that you want to display
 * for each row in the cursor,
 * get the entry for the columns
 * add the information to the output string

code format="java" //Get the reference to your textView widget to display the results in       TextView textView = ... ;

String outstring = "";

//Get the column index. The first one is done for you. Complete the rest. int indexRemarks = cursor.getColumnIndex(SpendingContract.SpendingEntry.COL_REMARKS);

while(cursor.moveToNext){

//Get the contents of the particular row in that column. The first one is done for you. String myRemarks = cursor.getString(indexRemarks);

//format the output string and add it to outstring

}

textView.setText(outstring); code

SQLite Command
The SQLite command for deleting a row is

code format="sql" DELETE FROM my_table_name WHERE where_clause code

TO DO 3.9, 3.10, 3.11 - Delete a row with an id.
You will be able to do TO DO 3.9 & TO DO 3.11 yourself.

TO DO 3.10 (Option 1) - Build the SQL query in a string and call the execSQL method.
code format="java" String sqlQuery = ... ; spendingDb.execSQL(sqlQuery); code

TO 3.10 (Option 2) - Use the delete method
Read the method signature and code accordingly.

=Going Further=

This is for your own exploration. If we have time, we'll discuss some of this in class.

Extract parts of the database
Allow your app's user to enter a search string and return the relevant parts of the database.

E.g. the user can enter "Gom" and all rows with remarks containing "Gom" e.g. "GomGom", "Gombak" will be returned.

Use Regular Expressions
Use regular expressions to help the user enter an appropriate form for the amount column e.g. "300" "2.10" "5.1" are acceptable but "300x" "abcd" is not.

Display the Results in a Recycler View
Make use of what you have learnt in Cohort Problem 4 to display the results of the database queries in a RecyclerView widget.

Have a second table
A second table would contain user-defined categories e.g. "Food", "Concerts" etc. In the first table, the user can now specify the category, in addition to the remarks.

The category column would store the ID of the corresponding row in the category table. This is known as a foreign key.

=Afternote=

Fixing Typos in Create Table 15 Nov 2017
Some students had typos in the SQLite command to create a table. That caused the database not to work properly. After fixing those typos, you must increase the database version, so that the onUpgrade method can run and create the new table for you.