listsqliteandroid-cursordatabase-cursor

Unable to print out generated list with SQL Database statements [Android Studio]

I have coded some code that connects to a databse in the internal storage of the device, runs a SQL statement to filter out excercises, then iterate through the results and create a list that can be used later in the program. However, the list returns nothing when checked with Log statements. I have checked whether the SQL statements work with log statements before the iteration and it returns correct row values.

package com.example.fit_world;

import androidx.annotation.NonNull;
import androidx.appcompat.app.AppCompatActivity;

import android.content.res.AssetManager;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.util.Log;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

import com.example.fit_world.Exercise;


public class GenerateWorkout extends AppCompatActivity {
    private static final String DATABASE_NAME = "Exercises.db";
    private SQLiteDatabase database;

    private void copyDatabaseToInternalStorage() {
        InputStream inputStream = null;
        OutputStream outputStream = null;
        try {
            // Open the database file in the assets folder
            AssetManager assetManager = getAssets();
            inputStream = assetManager.open(DATABASE_NAME);
            if (inputStream == null) {
                throw new IOException("Failed to open file: " + DATABASE_NAME);
            }

            // Generate a unique file name for the database in the internal storage
            File outputFile = new File(getFilesDir(), "Exercises_" + System.currentTimeMillis() + ".db");

            // Check if the file already exists in the internal storage
            if (outputFile.exists()) {
                // Delete the file if it already exists
                outputFile.delete();
            }

            outputStream = new FileOutputStream(outputFile);

            // Copy the database file from the assets folder to the internal storage
            byte[] buffer = new byte[1024];
            int length;
            while ((length = inputStream.read(buffer)) > 0) {
                outputStream.write(buffer, 0, length);
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            // Close the streams
            try {
                if (inputStream != null) {
                    inputStream.close();
                }
                if (outputStream != null) {
                    outputStream.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_generate_workout);
        String equipmentType = "None";
        String difficultyType = "Easy";
        String weightGoalType = "Weight Loss";

        List<Exercise> exercises = getExercisesFromDatabase(equipmentType, difficultyType, weightGoalType);
        for (Exercise exercise : exercises) {
            Log.d("LOL", exercise.toString());
        }
    }

    private List<Exercise> getExercisesFromDatabase(String equipmentType, String difficultyType, String weightGoalType) {
        List<Exercise> exercises = new ArrayList<>();
        // Check if the database file exists in the internal storage
        File databaseFile = new File(getFilesDir(), DATABASE_NAME);
        if (!databaseFile.exists()) {
            // If the database file does not exist, copy it from the assets folder
            copyDatabaseToInternalStorage();
        }


            // Open the database file in the internal storage
            SQLiteDatabase database = null;
            Cursor cursor = null;
            try {
                // Open the database file in the internal storage
                database = openOrCreateDatabase("Exercises.db", MODE_PRIVATE, null);

                // Execute the query to retrieve the list of exercises
                String query = "SELECT * FROM Exercises WHERE Equipment = ? AND Difficulty = ? AND Goal = ?";
                String[] selectionArgs = new String[]{equipmentType, difficultyType, weightGoalType};
                cursor = database.rawQuery(query, selectionArgs);
                int rowCount = cursor.getCount();
                System.out.print(rowCount);
                Log.d("TAG", "Row count: " + rowCount);
                Log.d("MMM", "Row count: " + database.rawQuery("SELECT * FROM exercises", null).getCount());



                // Iterate through the result set and create a list of exercises
                while (cursor.moveToNext()) {
                    int idColumnIndex = cursor.getColumnIndex("id");
                    int nameColumnIndex = cursor.getColumnIndex("name");
                    int descriptionColumnIndex = cursor.getColumnIndex("description");
                    int equipmentColumnIndex = cursor.getColumnIndex("equipment");
                    int difficultyColumnIndex = cursor.getColumnIndex("difficulty");
                    int weightGoalColumnIndex = cursor.getColumnIndex("weight_goal");
                    int requiredEquipmentColumnIndex = cursor.getColumnIndex("required_equipment");
                    int numberOfRepsColumnIndex = cursor.getColumnIndex("number_of_reps");
                    if (idColumnIndex != -1 && nameColumnIndex != -1 && descriptionColumnIndex != -1 && equipmentColumnIndex != -1 &&
                            difficultyColumnIndex != -1 && weightGoalColumnIndex != -1 && requiredEquipmentColumnIndex != -1 &&
                            numberOfRepsColumnIndex != -1) {
                        int id = cursor.getInt(idColumnIndex);
                        String name = cursor.getString(nameColumnIndex);
                        String description = cursor.getString(descriptionColumnIndex);
                        String equipment = cursor.getString(equipmentColumnIndex);
                        String difficulty = cursor.getString(difficultyColumnIndex);
                        String weightGoal = cursor.getString(weightGoalColumnIndex);
                        String requiredEquipment = cursor.getString(requiredEquipmentColumnIndex);
                        int numberOfReps = cursor.getInt(numberOfRepsColumnIndex);
                        Exercise exercise = new Exercise(id, name, description, equipment, difficulty, weightGoal, requiredEquipment, numberOfReps);
                        exercises.add(exercise);


                        Log.d("TAG", "Exercise name: " + name);
                        Log.d("TAG", "Exercise description: " + description);
                        Log.d("TAG", "Exercise equipment: " + equipment);
                        Log.d("TAG", "Number of exercises retrieved: " + exercises.size());

                    }
                }
            } catch (SQLException e) {
                e.printStackTrace();
                Log.e("TAG", "Error accessing database: " + e.getMessage());
            } finally {
            // Close the cursor and database
            if (cursor != null) {
                cursor.close();
            }
            if (database != null) {
                database.close();
            }
        }
        return exercises;

        }
    }

I tried to use log statements in order to see the list of excercises that is printed however nothing gets printed to the logcat, the SQL statements work as I have used a log statement to see the values printed and that works fine

Solution

  • I suspect that when valid data exists that something does get printed out, but not what is expected and therefore perhaps missed.

    More specifically what is printed depends upon the toString method of the Exercise class as is used by:-

        for (Exercise exercise : exercises) {
        Log.d("LOL", exercise.toString());
    }

    It may be something along the lines of D/LOL: a.a.so75035530javasqliteemptylist.Exercise@58c3a3e (this from an adaption of the code you have supplied)

    Otherwise the core code appears to be fine. The following was the adaptated code used to test with some data (a single row) in the database that is opened (just one row):-

    private List<Exercise> getExercisesFromDatabase(String equipmentType, String difficultyType, String weightGoalType) {
    List<Exercise> exercises = new ArrayList<>();
    // Check if the database file exists in the internal storage
    /*
    File databaseFile = new File(getFilesDir(), DATABASE_NAME);
    if (!databaseFile.exists()) {
        // If the database file does not exist, copy it from the assets folder
        copyDatabaseToInternalStorage();
    }

     */


    // Open the database file in the internal storage
    SQLiteDatabase database = null;
    Cursor cursor = null;
    try {
        // Open the database file in the internal storage
        //database = openOrCreateDatabase("Exercises.db", MODE_PRIVATE, null);
        File dbfile = this.getDatabasePath(DATABASE_NAME);
        database = SQLiteDatabase.openOrCreateDatabase(dbfile,null);

        // Execute the query to retrieve the list of exercises
        String query = "SELECT * FROM Exercises /*WHERE Equipment = ? AND Difficulty = ? AND weightGoal = ?*/";
        String[] selectionArgs = new String[]{equipmentType, difficultyType, weightGoalType};
        cursor = database.rawQuery(query, /*selectionArgs*/ new String[]{});
        DatabaseUtils.dumpCursor(cursor);
        int rowCount = cursor.getCount();
        System.out.print(rowCount);
        Log.d("TAG", "Row count: " + rowCount);
        Log.d("MMM", "Row count: " + database.rawQuery("SELECT * FROM exercises", null).getCount());

        // Iterate through the result set and create a list of exercises
        while (cursor.moveToNext()) {
            int idColumnIndex = cursor.getColumnIndex("id");
            int nameColumnIndex = cursor.getColumnIndex("name");
            int descriptionColumnIndex = cursor.getColumnIndex("description");
            int equipmentColumnIndex = cursor.getColumnIndex("equipment");
            int difficultyColumnIndex = cursor.getColumnIndex("difficulty");
            int weightGoalColumnIndex = cursor.getColumnIndex("weightGoal");
            int requiredEquipmentColumnIndex = cursor.getColumnIndex("requiredEquipment");
            int numberOfRepsColumnIndex = cursor.getColumnIndex("numberOfReps");
            if (1 == 1 /*idColumnIndex != -1 && nameColumnIndex != -1 && descriptionColumnIndex != -1 && equipmentColumnIndex != -1 &&
                    difficultyColumnIndex != -1 && weightGoalColumnIndex != -1 && requiredEquipmentColumnIndex != -1 &&
                    numberOfRepsColumnIndex != -1*/) {
                int id = cursor.getInt(idColumnIndex);
                String name = cursor.getString(nameColumnIndex);
                String description = cursor.getString(descriptionColumnIndex);
                String equipment = cursor.getString(equipmentColumnIndex);
                String difficulty = cursor.getString(difficultyColumnIndex);
                String weightGoal = cursor.getString(weightGoalColumnIndex);
                String requiredEquipment = cursor.getString(requiredEquipmentColumnIndex);
                int numberOfReps = cursor.getInt(numberOfRepsColumnIndex);
                Exercise exercise = new Exercise(id, name, description, equipment, difficulty, weightGoal, requiredEquipment, numberOfReps);
                exercises.add(exercise);


                Log.d("TAG", "Exercise name: " + name);
                Log.d("TAG", "Exercise description: " + description);
                Log.d("TAG", "Exercise equipment: " + equipment);
                Log.d("TAG", "Number of exercises retrieved: " + exercises.size());

            }
        }
    } catch (SQLException e) {
        e.printStackTrace();
        Log.e("TAG", "Error accessing database: " + e.getMessage());
    } finally {
        // Close the cursor and database
        if (cursor != null) {
            cursor.close();
        }
        if (database != null) {
            database.close();
        }
    }
    return exercises;

}

    :-

    2023-01-07 09:34:55.792 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@67e09f
2023-01-07 09:34:55.792 I/System.out: 0 {
2023-01-07 09:34:55.793 I/System.out:    id=1
2023-01-07 09:34:55.793 I/System.out:    name=fred
2023-01-07 09:34:55.793 I/System.out:    description=d1
2023-01-07 09:34:55.793 I/System.out:    equipment=E1
2023-01-07 09:34:55.793 I/System.out:    difficulty=hard
2023-01-07 09:34:55.793 I/System.out:    weightGoal=10kg
2023-01-07 09:34:55.793 I/System.out:    requiredEquipment=ALL
2023-01-07 09:34:55.793 I/System.out:    numberOfReps=10
2023-01-07 09:34:55.793 I/System.out: }
2023-01-07 09:34:55.793 I/System.out: <<<<<
2023-01-07 09:34:55.793 D/TAG: Row count: 1
2023-01-07 09:34:55.794 D/MMM: Row count: 1
2023-01-07 09:34:55.795 D/TAG: Exercise name: fred
2023-01-07 09:34:55.795 D/TAG: Exercise description: d1
2023-01-07 09:34:55.795 D/TAG: Exercise equipment: E1
2023-01-07 09:34:55.795 D/TAG: Number of exercises retrieved: 1

2023-01-07 09:34:55.802 D/LOL: a.a.so75035530javasqliteemptylist.Exercise@ca545ec

    As you can see the output/results are consistent with the expectation BUT the very last line is what is obtained from the loop through the returned List and thus probably not what was expected.

    However if the following were used:-

        List<Exercise> exercises = getExercisesFromDatabase(equipmentType, difficultyType, weightGoalType);
    for (Exercise exercise : exercises) {
        Log.d("LOL", exercise.toString());
        Log.d("LOL_","Name  is " + exercise.name + " Description is " + exercise.description + " etc."); /*<<<<<<<<<< ADDED >>>>>>>>>>*/ 
    }

    Then the output is would be more recognisable:-

    2023-01-07 09:51:07.442 D/LOL: a.a.so75035530javasqliteemptylist.Exercise@ca545ec
2023-01-07 09:51:07.442 D/LOL_: Name  is fred Description is d1 etc.

    Additional

    As you say that the dumpCursor does extract the expected data. Then the most likely issue is that at least one of the ??columnIndex index values is looking for a column that is not in the cursor and is hence -1 and thus will not satisfy the if statement (if (idColumnIndex != -1 && nameColumnIndex != ....) (check the dumpCursor output is tells you the actual column names)

    enter image description here