Get SQLite data (basic cursor)

A SQLite helper was created for Starbuzz in the previous section.
Currently, data is still obtained from the Java Drink class. At this time, the application needs to be modified to obtain data from the SQLite database.
All codes for this article are stored in
https://github.com/MADMAX110/Starbuzz

1. Modify DrinkActivity to use Starbuzz database

Basic steps:
1. Get a reference to the Starbuzz database
2. Create a cursor to read data from the database
3. Navigate to beverage records
4. Display detailed information about drinks in DrinkActiviy

1. Get database reference

First you need to get a reference to the Starbuzz database using the SQLite helper created in the previous chapter. To do this we first need to get a reference to the SQLite helper.

 SQLiteOpenHelper starbuzzDatabaseHelper = new StarbuzzDatabaseHelper(this);

Then call the SQLite helper’s getReadableDatabase() or getWriteableDatabase() to get a reference to the database. The former is read-only and the latter can be updated. Both of these will return a SQLiteDatabase object, which can be used to access the database.

 SQLiteDatabase db = starbuzzDatabaseHelper.getReadableDatabase();
    SQLiteDatabase db = starbuzzDatabaseHelper.getWritableDatabase();

If Android fails to get a reference to the database, a SQLiteExeception will be thrown. This would occur, for example, if getWriteableDatabase was called to read and write to the database, but the database could not be written to because the disk was full.
If you get an exception, you can use a Toast (a pop-up message) to tell the user that the message is not available.
Once you have a reference to the database, you can use the cursor to retrieve data from the database.

2. Use a cursor to obtain data from the database

Create cursor

Cursor cursor = db.query(...);

The simplest database query is to return all records from a table in a database. The first parameter is the table name, and the second parameter indicates that you want to return the values in these columns. If you want to return all records in a table, Set these parameters to null.

Cursor cursor = db.query("DRINK", new String[]{<!-- -->"_id", "NAME", "DESCRIPTION"}, null, null, null, null, null);

By default, the data in the table is displayed in the order of _id, because of the order in which the data is entered. If you want to order by NAME in ascending order, you can use the following code.

 Cursor cursor = db.query("DRINK", new String[]{<!-- -->"_id", "NAME", "DESCRIPTION"}, null, null, null, null, null, " NAME ASC");

The ASC keyword indicates that you want to sort this column in ascending order. By default, the queue is sorted in ascending order, so you can omit ASC. If you want to sort in descending order, use DESC.
You can also sort on multiple columns, such as FAVORITE in descending order and then NAME in ascending order.

Cursor cursor = db.query("DRINK", new String[]{<!-- -->"_id", "NAME", "DESCRIPTION"}, null, null, null, null, null, " FAVORITE DESC, NAME");

Set filter conditions for the data and return specific records: for example, return the record of the drink named Lattle in the DRINK list:

Cursor cursor = db.query("DRINK", new String[]{<!-- -->"_id", "NAME", "DESCRIPTION"}, "NAME = ?", new String[] { <!-- -->"Lattle"}, null, null, null);

Or the record with _id 1 in the DRINK list:

 Cursor cursor = db.query("DRINK", new String[]{<!-- -->"_id", "NAME", "DESCRIPTION"}, "_id = ?", new String[] { <!-- -->Integer.toString(1)}, null, null, null);

3. Navigate to the cursor record

To get the value of a specific record from a cursor, first navigate to the record.
Cursors mainly have four methods, which are moveToFirst, moveToLast, moveToPrevious, and moveToNext.
To return the first record of the cursor, use the moveToFirst method. If a record is found, this method will return a true value. If the cursor does not return any records, it returns false.

if (cursor.moveToFirst()){<!-- -->
}

In the same way, moveToLast returns the last record of the cursor, moveToPrevious is the previous record, and moveToNext is the next record.

4. Get the cursor value

Get the string from column 0 and the numeric value from column 2.

String name = cursor.getString(0);
int imageRes = cursor.getInt(2);

Finally, close the cursor and database.

cursor.close();
db.close();

Complete DrinkActivity code

package com.hfad.starbuzz;

import androidx.appcompat.app.AppCompatActivity;

import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Bundle;
import android.widget.ImageView;
import android.widget.TextView;
import android.widget.Toast;

public class DrinkActivity extends AppCompatActivity {<!-- -->

    public static final String EXTRA_DRINKID = "drinkId";

    @Override
    protected void onCreate(Bundle savedInstanceState) {<!-- -->
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_drink);

        int drinkId = (Integer)getIntent().getExtras().get(EXTRA_DRINKID);

        SQLiteOpenHelper starbuzzDatabaseHelper = new StarbuzzDatabaseHelper(this);
        try {<!-- -->
            SQLiteDatabase db = starbuzzDatabaseHelper.getReadableDatabase();
            Cursor cursor = db.query("DRINK",
                    new String[]{<!-- -->"NAME", "DESCRIPTION", "IMAGE_RESOURCE_ID"},
                    "_id = ?",
                    new String[] {<!-- -->Integer.toString(drinkId)},
                    null, null, null);
            if (cursor.moveToFirst()) {<!-- -->
                String nameText = cursor.getString(0);
                String descriptionText = cursor.getString(1);
                int photoId = cursor.getInt(2);

                TextView name = (TextView) findViewById(R.id.name);
                name.setText(nameText);

                TextView description = (TextView) findViewById(R.id.description);
                description.setText(descriptionText);

                ImageView photo = (ImageView) findViewById(R.id.photo);
                photo.setImageResource(photoId);
                photo.setContentDescription(nameText);
            }
            cursor.close();
            db.close();
        }catch (SQLException e){<!-- -->
            Toast toast = Toast.makeText(this,
                    "Database unavailable",
                    Toast.LENGTH_SHORT);
            toast.show();
        }
    }
}

2. Modify DrinkCategoryActivity to use Starbuzz database

The steps here are different from the previous ones because here we are displaying a list view that uses beverage data as its data source. We need to convert the data source of this data to the Starbuzz database,
1. Create a cursor to read beverage data from the database.
2. Replace the list view’s array adapter with a cursor adapter.
The first step is the same as before, what needs to be done here is to replace the array data in the list view.

Simple Cursor Adapter

principle:
1. The list view requests data from the adapter
2. The adapter requests data from the database from the cursor.
3. The adapter returns data to the list view
Using a simple cursor adapter is very similar to using an array adapter: you initialize the adapter and then associate it with the list view.

 SimpleCursorAdapter listAdapter = new SimpleCursorAdapter(this , //current activity
            android.R.layout.simple_list_item_1,//Display a value corresponding to each row in the list view
            cursor,//This is the cursor
            new String[]{<!-- -->"NAME"},//Which columns of the cursor are used?
            new int[]{<!-- -->android.R.id.text1},//Want to display these data in those views
            0);//Used to determine the behavior of the cursor, usually 0, which is the default value, you can also register a content observer
    listDrinks.setAdapter(listAdapter);

Modified DrinkCategoryActivity code:

package com.hfad.starbuzz;

import androidx.appcompat.app.AppCompatActivity;

import android.content.Intent;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Bundle;
import android.view.View;
import android.widget.AdapterView;
import android.widget.ArrayAdapter;
import android.widget.ListView;
import android.widget.SimpleCursorAdapter;
import android.widget.Toast;

public class DrinkCategoryActivity extends AppCompatActivity {<!-- -->

    private SQLiteDatabase db;
    private Cursor cursor;
    
    @Override
    protected void onCreate(Bundle savedInstanceState) {<!-- -->
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_drink_category);
        ListView listDrinks = (ListView) findViewById(R.id.list_drinks);
        SQLiteOpenHelper starbuzzDatabaseHelper = new StarbuzzDatabaseHelper(this);
        try {<!-- -->
            db = starbuzzDatabaseHelper.getReadableDatabase();
            cursor = db.query("DRINK",
                    new String[]{<!-- -->"_id", "NAME"},
                    null, null, null, null, null);
            SimpleCursorAdapter listAdapter = new SimpleCursorAdapter(this, //current activity
                    android.R.layout.simple_list_item_1,//Display a value corresponding to each row in the list view
                    cursor,//This is the cursor
                    new String[]{<!-- -->"NAME"},//Which columns of the cursor are used?
                    new int[]{<!-- -->android.R.id.text1},//Want to display these data in those views
                    0);//Used to determine the behavior of the cursor, usually 0, which is the default value, you can also register a content observer
            listDrinks.setAdapter(listAdapter);
        }catch(SQLException e){<!-- -->
            Toast toast = Toast.makeText(this, "Database unavailable", Toast.LENGTH_SHORT);
            toast.show();
        }

        AdapterView.OnItemClickListener itemClickListener = new AdapterView.OnItemClickListener() {<!-- -->
            @Override
            public void onItemClick(AdapterView<?> parent, View view, int position, long id) {<!-- -->
                if (position == 0) {<!-- -->
                    Intent intent = new Intent(DrinkCategoryActivity.this, DrinkCategoryActivity.class);
                    startActivity(intent);
                }
                Intent intent = new Intent(DrinkCategoryActivity.this, DrinkActivity.class);
                //Add the ID of the clicked list item to the intent. The first parameter indicates the use of this constant name to represent the additional information name in the intent.
                //This way you can know that DrinkCategoryActivity and DrinkActivity are using the same string
                //Increase this constant when creating a DrinkActivity activity.
                intent.putExtra(DrinkActivity.EXTRA_DRINKID, (int)id);
                startActivity(intent);
            }
        };

       listDrinks.setOnItemClickListener(itemClickListener);
    }

    @Override
    protected void onDestroy() {<!-- -->
        super.onDestroy();
        cursor.close();
        db.close();
    }
}