How to populate ListView with SQLite table where int value matches column in table

Question

I am new to android development and can't get this to work. I am trying to populate a listview in my AdminActivity with the int value that I am passing from a previous list view. That int value then needs to be used to populate the ListView with the users that match the int value in the booking column of the users table.

Here is the DBHelper class:

package com.example.shashank.fffffffffffffffffffffffffff;


import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import androidx.annotation.Nullable;

import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

public class DBHelper extends SQLiteOpenHelper {
    public static final String DBNAME = "Login.db";
    public static final String FLIGHTS = "FLIGHTS";
    public static final String COLUMN_ID = "ID";
    public static final String COLUMN_DESTINATION = "DESTINATION";
    public static final String COLUMN_PRICE = "PRICE";
    public static final String COLUMN_DEPARTURE_TIME = "DEPARTURE_TIME";
    public static final String COLUMN_ARRIVAL_TIME = "ARRIVAL_TIME";
    public static final String COLUMN_DURATION = "DURATION";
    public static final String COLUMN_AVAILABLE_SEATS = "AVAILABLE_SEATS";
    public static final String USERS = "users";
    public static final String USERNAME = "username";
    public static final String PASSWORD = "password";
    public static final String EMAIL = "email";
    public static final String BALANCE = "balance";
    public static final String BOOKING = "booking";

    public DBHelper(Context context) {
        super(context, "Login.db", null, 1);
    }

    @Override
    public void onCreate(SQLiteDatabase MyDB) {

        String createTable1 = ("create Table " + USERS + "(" + USERNAME + " TEXT primary key, " + PASSWORD + " TEXT, " + EMAIL + " TEXT UNIQUE, " + BALANCE + " REAL, " + BOOKING + " INTEGER)");
        MyDB.execSQL(createTable1);

        MyDB.execSQL("CREATE TABLE " + FLIGHTS + "(" + COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + COLUMN_DESTINATION + " TEXT, " + COLUMN_PRICE + " REAL, " + COLUMN_DEPARTURE_TIME + " TEXT, " + COLUMN_ARRIVAL_TIME + " TEXT, " + COLUMN_DURATION + " TEXT, " + COLUMN_AVAILABLE_SEATS + " INTEGER)");


        ContentValues insertValues = new ContentValues();
        insertValues.put(COLUMN_DESTINATION, "Johannesburg");
        insertValues.put(COLUMN_PRICE, 1200);
        insertValues.put(COLUMN_DEPARTURE_TIME, "12:00");
        insertValues.put(COLUMN_ARRIVAL_TIME, "14:15");
        insertValues.put(COLUMN_DURATION, "2:15");
        insertValues.put(COLUMN_AVAILABLE_SEATS, 10);
        MyDB.insert(FLIGHTS, null, insertValues);

        ContentValues insertValues2 = new ContentValues();
        insertValues2.put(COLUMN_DESTINATION, "Johannesburg");
        insertValues2.put(COLUMN_PRICE, 1000);
        insertValues2.put(COLUMN_DEPARTURE_TIME, "16:00");
        insertValues2.put(COLUMN_ARRIVAL_TIME, "18:15");
        insertValues2.put(COLUMN_DURATION, "2:15");
        insertValues2.put(COLUMN_AVAILABLE_SEATS, 22);
        MyDB.insert(FLIGHTS, null, insertValues2);

        ContentValues insertValues3 = new ContentValues();
        insertValues3.put(COLUMN_DESTINATION, "Durban");
        insertValues3.put(COLUMN_PRICE, 800);
        insertValues3.put(COLUMN_DEPARTURE_TIME, "12:00");
        insertValues3.put(COLUMN_ARRIVAL_TIME, "14:00");
        insertValues3.put(COLUMN_DURATION, "2:00");
        insertValues3.put(COLUMN_AVAILABLE_SEATS, 2);
        MyDB.insert(FLIGHTS, null, insertValues3);

        ContentValues insertValues4 = new ContentValues();
        insertValues4.put(COLUMN_DESTINATION, "Port Elizabeth");
        insertValues4.put(COLUMN_PRICE, 700);
        insertValues4.put(COLUMN_DEPARTURE_TIME, "08:00");
        insertValues4.put(COLUMN_ARRIVAL_TIME, "09:10");
        insertValues4.put(COLUMN_DURATION, "1:10");
        insertValues4.put(COLUMN_AVAILABLE_SEATS, 0);
        MyDB.insert(FLIGHTS, null, insertValues4);

        ContentValues insertValues5 = new ContentValues();
        insertValues5.put(COLUMN_DESTINATION, "Port Elizabeth");
        insertValues5.put(COLUMN_PRICE, 700);
        insertValues5.put(COLUMN_DEPARTURE_TIME, "12:00");
        insertValues5.put(COLUMN_ARRIVAL_TIME, "13:10");
        insertValues5.put(COLUMN_DURATION, "1:10");
        insertValues5.put(COLUMN_AVAILABLE_SEATS, 22);
        MyDB.insert(FLIGHTS, null, insertValues5);

        ContentValues insertValues6 = new ContentValues();
        insertValues6.put(COLUMN_DESTINATION, "Durban");
        insertValues6.put(COLUMN_PRICE, 900);
        insertValues6.put(COLUMN_DEPARTURE_TIME, "14:00");
        insertValues6.put(COLUMN_ARRIVAL_TIME, "16:00");
        insertValues6.put(COLUMN_DURATION, "2:00");
        insertValues6.put(COLUMN_AVAILABLE_SEATS, 11);
        MyDB.insert(FLIGHTS, null, insertValues6);

        

    }


    @Override
    public void onUpgrade(SQLiteDatabase MyDB, int i, int i1) {
        MyDB.execSQL("drop Table if exists " + USERS);
        MyDB.execSQL("drop Table if exists " + FLIGHTS);

        onCreate(MyDB);
    }

    public Boolean insertData(String username, String password, String email, Double balance){
        SQLiteDatabase MyDB = this.getWritableDatabase();
        ContentValues contentValues= new ContentValues();
        contentValues.put(USERNAME, username);
        contentValues.put(PASSWORD, password);
        contentValues.put(EMAIL, email);
        contentValues.put(BALANCE, balance);
        long result = MyDB.insert(USERS, null, contentValues);
        if(result==-1) return false;
        else
            return true;
    }

    public Boolean checkusername(String username) {
        SQLiteDatabase MyDB = this.getWritableDatabase();
        Cursor cursor = MyDB.rawQuery("Select * from " + USERS + " where " + USERNAME + " = ?", new String[]{username});
        if (cursor.getCount() > 0)
            return true;
        else
            return false;
    }

    public Boolean checkusernamepassword(String username, String password){
        SQLiteDatabase MyDB = this.getWritableDatabase();
        Cursor cursor = MyDB.rawQuery("Select * from " + USERS + " where " + USERNAME + " = ? and " + PASSWORD + " = ?", new String[] {username,password});
        if(cursor.getCount()>0)
            return true;
        else
            return false;
    }


    public List getEveryone(){

        List returnList = new ArrayList<>();

        String queryString = "SELECT * FROM " + FLIGHTS;

        SQLiteDatabase db = this.getReadableDatabase();

        Cursor cursor = db.rawQuery(queryString, null);

        if(cursor.moveToFirst()){
            do {
                int id = cursor.getInt(0);
                String destination = cursor.getString(1);
                double price = cursor.getDouble(2);
                String departure = cursor.getString(3);
                String arrival = cursor.getString(4);
                String duration = cursor.getString(5);
                int space = cursor.getInt(6);

                FlightsModel newFlight = new FlightsModel(id, destination, price, departure, arrival, duration, space);
                returnList.add(newFlight);


            }while (cursor.moveToNext());
        }
        else{

        }
        cursor.close();
        db.close();
        return returnList;
    }



    @SuppressLint("Range") // suppress Bug/issue with getColumnIndex
    public FlightsModel getFlightById(int id) {
        FlightsModel rv;
        SQLiteDatabase db = this.getWritableDatabase();
        // Uses the query convenience method rather than raw query
        Cursor csr = db.query(FLIGHTS,null,COLUMN_ID+"=?",new String[]{String.valueOf(id)},null,null,null);
        if (csr.moveToFirst()) {
            rv = new FlightsModel(
                    csr.getInt(csr.getColumnIndex(COLUMN_ID)),
                    csr.getString(csr.getColumnIndex(COLUMN_DESTINATION)),
                    csr.getDouble(csr.getColumnIndex(COLUMN_PRICE)),
                    csr.getString(csr.getColumnIndex(COLUMN_DEPARTURE_TIME)),
                    csr.getString(csr.getColumnIndex(COLUMN_ARRIVAL_TIME)),
                    csr.getString(csr.getColumnIndex(COLUMN_DURATION)),
                    csr.getInt(csr.getColumnIndex(COLUMN_AVAILABLE_SEATS))
            );
        }  else {
            rv = new FlightsModel();
        }
        csr.close();
        // No need to close the database (inefficient to keep opening and closing db)
        return rv;
    }



    @SuppressLint("Range")
    public UsersModel getPasswordByName(String name){

        UsersModel rv;
        SQLiteDatabase db = this.getWritableDatabase();

        Cursor cr = db.query(USERS, null, USERNAME+"=?", new String[]{name}, null, null, null);

        if (cr.moveToFirst()) {
            rv = new UsersModel(
                    cr.getString(cr.getColumnIndex(USERNAME)),
                    cr.getString(cr.getColumnIndex(PASSWORD)),
                    cr.getString(cr.getColumnIndex(EMAIL)),
                    cr.getDouble(cr.getColumnIndex(BALANCE)),
                    cr.getInt(cr.getColumnIndex(BOOKING))

            );
        }  else rv = new UsersModel();

        cr.close();
        return rv;
    }

    public int setBookingByUserName(int bookingAmount, String userName) {
        ContentValues cv = new ContentValues();
        cv.put(BOOKING,bookingAmount);
        return this.getWritableDatabase().update(USERS,cv,USERNAME+"=?",new String[]{userName});
    }

    public double makingPayment(double balance, String userName){
        ContentValues cv = new ContentValues();
        cv.put(BALANCE,balance);
        return this.getWritableDatabase().update(USERS,cv,USERNAME+"=?",new String[]{userName});
    }


    public int setAvailableSeatsAfterPayment(int seats, int flightID){
        ContentValues cv = new ContentValues();
        cv.put(COLUMN_AVAILABLE_SEATS,seats);
        return this.getWritableDatabase().update(FLIGHTS,cv,COLUMN_ID+"=?",new String[]{String.valueOf(flightID)});
    }


    public int cancelBooking(int bookingAmount, String userName){
        ContentValues cv = new ContentValues();
        cv.put(BOOKING,bookingAmount);
        return this.getWritableDatabase().update(USERS,cv,USERNAME+"=?",new String[]{String.valueOf(userName)});
    }


    @SuppressLint("Range")
    public UsersModel getUsersModelByName(String userName) {
        UsersModel rv = new UsersModel();
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor csr = db.query(USERS,null,USERNAME+"=?", new String[]{userName},null,null,null);
        if (csr.moveToFirst()) {
            rv = new UsersModel(
                    csr.getString(csr.getColumnIndex(USERNAME)),
                    csr.getString(csr.getColumnIndex(PASSWORD)),
                    csr.getString(csr.getColumnIndex(EMAIL)),
                    csr.getDouble(csr.getColumnIndex(BALANCE)),
                    csr.getInt(csr.getColumnIndex(BOOKING))
            );
        }
        csr.close();
        return rv;
    }

    public boolean makeBooking(String userName, int bookingId) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues cv = new ContentValues();
        cv.put(BOOKING,bookingId);
        return db.update(USERS,cv,USERNAME + "=?", new String[]{userName}) > 0;
    }


    public String makeBookReceiptFile(String userName, Context context) {
        String rcpts_directory = "receipts";
        String rv = "";
        SQLiteDatabase db = this.getWritableDatabase();
        UsersModel currentUser = getUsersModelByName(userName);
        FlightsModel currentFlightsModel = new FlightsModel();
        if (currentUser.booking > 0) {
            currentFlightsModel = getFlightById(currentUser.booking);
            if (currentFlightsModel.getId() < 1) {
                rv = "INVALID - unable to extract booking for id " + currentUser.booking;
            }
        } else {
            rv = "INVALID - unable to extract user who's name is " + userName;
        }
        if (rv.length() > 0) return rv;
        String rcpt_filename =
                currentUser.getName() + "-" +
                        currentFlightsModel.getDestination() + "-" +
                        currentFlightsModel.getDeparture_time() + "-" +
                        currentFlightsModel.getArrival_time()
                ;
        File rcpt = new File(context.getFilesDir().getPath() + File.separatorChar + rcpts_directory + File.separatorChar + rcpt_filename);
        rcpt.getParentFile().mkdirs();
        try {
            FileWriter fw = new FileWriter(rcpt);
            fw.write("Invoice for flight:" + "
" +
                    "
" + "-----------------------------------------------" +
                    "
" + "User: " + userName +
                    "
" + "Destination: " + currentFlightsModel.getDestination()+
                    "
" + "Departure time at: " + currentFlightsModel.getDeparture_time() +
                    "
" + "Arrival time at: " + currentFlightsModel.getArrival_time() +
                    "
" + "Duration of flight: " + currentFlightsModel.getDuration() +
                    "
" + "Amount paid: " + "R" + currentFlightsModel.getPrice()
            );
            fw.flush();
            fw.close();
            rv = rcpt.getAbsolutePath();
        } catch (IOException e) {
            e.printStackTrace();
            rv = "IOERROR - " + e.getMessage();
        }
        return rv;
    }


}

Here is the usersmodel class:

package com.example.shashank.fffffffffffffffffffffffffff;

public class UsersModel {

    private String name, password, email;

    private double balance;

    int booking;

    public UsersModel(String name, String password, String email, double balance, int booking) {
        this.name = name;
        this.password = password;
        this.email = email;
        this.balance = balance;
        this.booking = booking;
    }

    public UsersModel() {

    }

    public int getBooking() {
        return booking;
    }

    public void setBooking(int booking) {
        this.booking = booking;
    }

    public double getBalance() {
        return balance;
    }

    public void setBalance(double balance) {
        this.balance = balance;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    @Override
    public String toString() {
        return "UsersModel{" +
                "name='" + name + '\'' +
                ", password='" + password + '\'' +
                '}';
    }
}

Here is the AdminActivity:

package com.example.shashank.fffffffffffffffffffffffffff;

import androidx.appcompat.app.AppCompatActivity;

import android.content.Intent;
import android.os.Bundle;
import android.widget.ListView;
import android.widget.Toast;

public class AdminActivity extends AppCompatActivity {

    ListView userList;


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

        userList = findViewById(R.id.userList);

        int intValue;
        Intent mIntent = getIntent();
        intValue = mIntent.getIntExtra("intVariableName", 0);
        intValue = intValue + 1;

        Toast.makeText(AdminActivity.this, Integer.toString(intValue), Toast.LENGTH_SHORT).show();


    }
}

I want to use the intValue and populate the list with all the users where the intValue matches the int value in their booking column. Any help will be appreciated thank you

user_4744514 · Answer

First Add a method to DBHelper to get the list of users as a Cursor by the int (BOOKING) such as :-

public Cursor getUsersByBookingNumber(int bookingNumber) {
    SQLiteDatabase MyDB = this.getWritableDatabase();
    /* return cursor with ALL columns AND a column called _ID which has the rowid */
    return MyDB.query(USERS, new String[]{"*","rowid AS " + BaseColumns._ID},BOOKING+"=?",new String[]{String.valueOf(bookingNumber)},null,null,null);
}

Note that Cursor Adapters MUST have a column name _id (BaseColumns._ID resolves to _id) so this is achieved by getting the rowid column AS _id.

A ListView requires an adapter, Cursor Adapters are designed for use with Cursors so SimpleCursorAdapter is used.

An adapter requires a layout that will be used to display each item in the list (an item can consist of multiple views). In this case the stock layout simple_list_item2 will be used (you would probably want to create your own layout). You setup the adapter and then tell the ListView what adapter to use.

All of the above is done in the respective activity. To demonstrate MainActivity has been used, it is :-

public class MainActivity extends AppCompatActivity {

    DBHelper dbHelper;
    Cursor userListViewCursor; /* The Cursor to be used for the ListView */
    SimpleCursorAdapter sca; /* The adapter for the ListView */
    ListView userListView; /* The ListView */
    int currentBookingNumber; /* the int */

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        userListView = this.findViewById(R.id.activity_admin);
        /* Prepare Database */
        dbHelper = new DBHelper(this);
        /* Add some test data */
        dbHelper.insertData("Fred","password","fred@email",0.00);
        dbHelper.insertData("Mary","password","mary@email",0.00);
        dbHelper.insertData("Jane","password","jane@email",0.00);
        dbHelper.insertData("Tom","password","tom@email",0.00);
        dbHelper.insertData("Sue","password","sue@email",0.00);
        dbHelper.insertData("Bob","password","bob@email",0.00);

        dbHelper.makeBooking("Fred",1);
        dbHelper.makeBooking("Mary",2);
        dbHelper.makeBooking("Sue",3);
        dbHelper.makeBooking("Jane",1);
        dbHelper.makeBooking("Tom",2);
        dbHelper.makeBooking("Bob",1);

        currentBookingNumber = 1; /*(mimic getting the booking number)*/
        /* setup the ListView as the adapter will be null */
        setupOrRefreshLIstView(currentBookingNumber);

    }

    private void setupOrRefreshLIstView(int bookingNumber) {
        userListViewCursor = dbHelper.getUsersByBookingNumber(bookingNumber);
        if (sca == null) {
            sca = new SimpleCursorAdapter(
                    this,
                    /* The layout for each item in the ListView */
                    android.R.layout.simple_list_item_2,
                    userListViewCursor,
                    /* Columns in the Cursors */
                    new String[]{DBHelper.USERNAME,DBHelper.EMAIL},
                    /* id of the View in the ListView's item layout  that corresponds with the column in the Cursor*/
                    /* NOTE 1st column corresponds to first id and so on so :- */
                    /* USERNAME column value is placed into text1 View, EMAIl column into text2 */
                    /* simple list item has 2 TextViews with ids text1 and text2 */
                    new int[]{android.R.id.text1,android.R.id.text2},
                    0
            );
            /* Tie the adapter sca to the ListView userListView */
            userListView.setAdapter(sca);
            /* set listeners here if required e.g. */
            userListView.setOnItemClickListener(new AdapterView.OnItemClickListener() {
                @SuppressLint("Range")
                @Override
                public void onItemClick(AdapterView adapterView, View view, int i, long l /* will be the value of _id column */) {
                    Toast.makeText(view.getContext(),
                            "You clicked on the row with _id " + l
                                    + " the user is " + userListViewCursor.getString(userListViewCursor.getColumnIndex(DBHelper.USERNAME))
                                    + " the email is " + userListViewCursor.getString(userListViewCursor.getColumnIndex(DBHelper.EMAIL))
                                    /* even though balance etc is not shown as the values are in the cursor they can be retrieved */
                                    + " the balance is " + userListViewCursor.getDouble(userListViewCursor.getColumnIndex(DBHelper.BALANCE))
                            ,
                            Toast.LENGTH_SHORT).show();
                }
            });
        } else {
            sca.swapCursor(userListViewCursor);
        }
    }

    /* if the activity is resumed then refresh the ListView just in case the data has changed */
    @Override
    protected void onResume() {
        super.onResume();
        setupOrRefreshLIstView(currentBookingNumber);
    }

    @Override
    protected void onDestroy() {
        super.onDestroy();
        userListViewCursor.close(); /*should always close a cursor when done with it */

    }
}

Note that some testing data is generated and loaded to demonstrate.
An onItemClickListener has been added, this will toast the details of the clicked item.

Running the above results in :-

How to populate ListView with SQLite table where int value matches column in table

1 Answers