bgneer bgneer - 1 month ago 7
Android Question

List<Object> returns 0 or null

I'm having issues getting some results from this query. The function below always returns

null
or
0
. The data is being passed from the previous activity which contains a
List<String>
like so:

Intent intent = this.getIntent();
Bundle bundle = intent.getExtras();

List<Category> strings = (List<Category>)bundle.getSerializable("selectedItems");


Here, I'm passing
String
s into a function:

DatabaseHandler db = new DatabaseHandler(this);
List<Activities> activities = db.getActivities(strings);

assert activities != null;
String a = "";
for(Activities act : activities) {
a = a + act.getActivityName() + "\n";
}
textView.setText(a);


Here is my database search function:

public List<Activities> getActivities(List<Category> category) { //size = 2
List<Activities> activitiesList = new ArrayList<>(); //activitiesList: size = 0
Activities activities = new Activities(); //activities: "null"
int size = category.size(); //size = 2

sqLiteDatabase = this.getReadableDatabase();
try {
for(int i = 0; i < size; i++) { //size = 2
Cursor cursor = sqLiteDatabase.rawQuery("SELECT a.activity_name"
+ " AS ActivityName, GROUP_CONCAT(t.category_name)"
+ " AS CategoryList FROM activities AS a"
+ " LEFT JOIN activity_tags AS at"
+ " ON at.id_activity = a.activity_id"
+ " LEFT JOIN categories AS t"
+ " ON t.category_id = at.id_tag"
+ " WHERE t.category_name LIKE '%" + category.get(i) + "%'"
+ " GROUP BY a.activity_id, a.activity_name", null);
cursor.moveToFirst();

while (!cursor.isAfterLast()) {
activities.setActivityName(cursor.getString(0));
activitiesList.add(activities); //activities: "null"
cursor.moveToNext();
}
cursor.close();
}
sqLiteDatabase.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
sqLiteDatabase.close();
}
return activitiesList; //activitiesList: size = 0
}


Here are the tables in my database:

Categories
cateogry_id category_name
1 party
2 adventure

Activities
activity_id activity_name
1 Sky Diving
2 Pool Party
3 Foam Party

Activities Tag
row_id id_activity id_tag
7 3 1
8 2 1
9 1 2


What am I doing wrong? Any suggestions?

Answer

Alright, your loop is entered twice

for(int i = 0; i < size; i++) { //size = 2

But is this loop entered?

while (!cursor.isAfterLast()) {

Probably not if you get an empty list returned.

Why are you getting an empty list returned? Well, let's look at the query.

Assuming all the SQL tables and columns are properly named, something does stick out - The WHERE ... LIKE statement is using the entire list as a string value, rather than individual values.

+ " WHERE t.category_name LIKE '%" + category + "%'"

Without using category.get(i) there, you are going to get a query like this

WHERE t.category_name LIKE '%["category1", "category2"]%'

If you in fact wanted to use the list for the WHERE clause, you need to construct an IN expression.

Comments