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
CodePudding user response：
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;
}
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 {
@Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); Intent intent = new Intent(this,GenerateWorkout.class); frigDatabase(); startActivity(intent); } void frigDatabase() { SQLiteDatabase db; String dbname = GenerateWorkout.DATABASE_NAME; File dbfile = this.getDatabasePath(dbname); boolean exists = dbfile.exists(); db = SQLiteDatabase.openOrCreateDatabase(dbfile,null); if (!exists) { db.execSQL("CREATE TABLE exercises (" "id INTEGER PRIMARY KEY," "name TEXT," "description TEXT," "equipment TEXT," "difficulty TEXT," "weightGoal TEXT," "requiredEquipment TEXT," "numberOfReps INTEGER DEFAULT 10" ");" ); ContentValues cv = new ContentValues(); cv.put("name","fred"); cv.put("description","d1"); cv.put("equipment","E1"); cv.put("difficulty","hard"); cv.put("weightGoal","10kg"); cv.put("requiredEquipment","ALL"); db.insert("exercises",null,cv); } }}
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
:-
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)
- Adding a breakpoint on the first attempt to get a value from the cursor in the body of the if (note that the IF clause has been changed to always be true to allow debug to enter the body) and running in debug mode. This would allow you to see what is what. The following is an example that shows the breakpoint (red dot placed by clicking the line number) and the result of running the App in debug mode (Shift F9) :-
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.