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
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:-
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):-
As can be seen rather than test using an asset etc, the database is in the standard location, it being created and populated in the initial (MainActivity) which is :-
public class MainActivity extends AppCompatActivity {
}
other changes made were to select the one and only row (and to use column names based upon the Exercise class (note that
getColumnIndexis case dependent)).you may also notice the use of the DatabaseUtils dumpCursor method with this then the resultant output used to test was
:-
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:-
Then the output is would be more recognisable:-
Additional
As you say that the dumpCursor does extract the expected data. Then the most likely issue is that at least one of the
??columnIndexindex values is looking for a column that is not in the cursor and is hence -1 and thus will not satisfy theifstatement (if (idColumnIndex != -1 && nameColumnIndex != ....) (check the dumpCursor output is tells you the actual column names)As can be ascertained weightGaolIndex is -1 and thus that the column name, as per the line
int weightGoalColumnIndex = cursor.getColumnIndex("Goal");(noteGoalpurposefully used to produce the -1(. Using debug to expand cursor and then to expand mColumnNameMap shows:-i.e. the column name in the Cursor is weigthGoal NOT Goal
You should also note that the
getColumnIndexmethod is case sensitive so the case of the name being searched MUST match the case of the column in the cursor.