SQLiteを用いたAndroid開発でORDER BYが効かない

発生している問題・エラーメッセージ

AndroidJavaで簡単なタスク管理システムを作っています。
ソート機能を付けようと思ってSQLiteに入ってるデータを降順にして画面に反映させようとしましたが、
cursorインスタンスから取り出してnumberListに入れたときに降順になっていませんでした。
実際の画面はこんな感じです。オプションメニューをタップして列を選んだら降順になるようにしたいです。

該当のソースコード

Java
1package com.example.taskmanagement;
2
3import androidx.appcompat.app.AppCompatActivity;
4
5import android.content.Intent;
6import android.database.Cursor;
7import android.database.sqlite.SQLiteDatabase;
8import android.os.Bundle;
9import android.view.Menu;
10import android.view.MenuInflater;
11import android.view.MenuItem;
12import android.widget.ArrayAdapter;
13import android.widget.ListView;
14
15import java.util.ArrayList;
16import java.util.Collections;
17import java.util.List;
18
19public class TaskList extends AppCompatActivity {
20
21    private UserDatabaseHelper helper;
22    private int userId;
23    private boolean tap = false;
24
25    @Override
26    protected void onCreate(Bundle savedInstanceState) {
27        super.onCreate(savedInstanceState);
28        setContentView(R.layout.activity_task_list);
29
30        setList(ColumnTask.number, SortType.ASC);
31    }
32
33    public void setList(ColumnTask column, SortType sortType) {
34        ListView lvNumber = findViewById(R.id.lvNumber);
35        ListView lvTask = findViewById(R.id.lvTask);
36        ListView lvUrgency = findViewById(R.id.lvUrgency);
37        ListView lvImportance = findViewById(R.id.lvImportance);
38
39        List<Integer> numberList = new ArrayList<>();
40        List<String> taskList = new ArrayList<>();
41        List<Integer> urgencyList = new ArrayList<>();
42        List<Integer> importanceList = new ArrayList<>();
43
44        Intent intentFromRegisterTask = getIntent();
45        userId = intentFromRegisterTask.getIntExtra("userId", 0);
46        helper = new UserDatabaseHelper(TaskList.this);
47        SQLiteDatabase db = helper.getWritableDatabase();
48        String selectSQL = "SELECT * FROM task WHERE userId = ? ORDER BY ? " + sortType;
49        String[] params = {String.valueOf(userId), String.valueOf(column)};
50        Cursor cursor = db.rawQuery(selectSQL, params);
51        while (cursor.moveToNext()) {
52            int numberIdx = cursor.getColumnIndex("number");
53            numberList.add(cursor.getInt(numberIdx));
54            int taskIdx = cursor.getColumnIndex("task");
55            taskList.add(cursor.getString(taskIdx));
56            int urgencyIdx = cursor.getColumnIndex("urgency");
57            urgencyList.add(cursor.getInt(urgencyIdx));
58            int importanceIdx = cursor.getColumnIndex("importance");
59            importanceList.add(cursor.getInt(importanceIdx));
60        }
61        ArrayAdapter<Integer> numberAdapter = new ArrayAdapter<>(TaskList.this, android.R.layout.simple_list_item_1, numberList);
62        lvNumber.setAdapter(numberAdapter);
63        ArrayAdapter<String> taskAdapter = new ArrayAdapter<>(TaskList.this, android.R.layout.simple_list_item_1, taskList);
64        lvTask.setAdapter(taskAdapter);
65        ArrayAdapter<Integer> urgencyAdapter = new ArrayAdapter<>(TaskList.this, android.R.layout.simple_list_item_1, urgencyList);
66        lvUrgency.setAdapter(urgencyAdapter);
67        ArrayAdapter<Integer> importanceAdapter = new ArrayAdapter<>(TaskList.this, android.R.layout.simple_list_item_1, importanceList);
68        lvImportance.setAdapter(importanceAdapter);
69    }
70
71    @Override
72    public boolean onCreateOptionsMenu(Menu menu) {
73        MenuInflater inflater = getMenuInflater();
74        inflater.inflate(R.menu.menu_sort, menu);
75        return true;
76    }
77
78    @Override
79    public boolean onOptionsItemSelected(MenuItem item) {
80        boolean returnValue = true;
81        switch (item.getItemId()) {
82            case R.id.menuSortNumber:
83                if (tap == false) {
84                    setList(ColumnTask.number, SortType.DESC);
85                    tap = true;
86                } else {
87                    setList(ColumnTask.number, SortType.ASC);
88                    tap = false;
89                }
90                break;
91            case R.id.menuSortTask:
92                if (tap == false) {
93                    setList(ColumnTask.task, SortType.DESC);
94                    tap = true;
95                } else {
96                    setList(ColumnTask.task, SortType.ASC);
97                    tap = false;
98                }
99                break;
100            case R.id.menuSortUrgency:
101                if (tap == false) {
102                    setList(ColumnTask.urgency, SortType.DESC);
103                    tap = true;
104                } else {
105                    setList(ColumnTask.urgency, SortType.ASC);
106                    tap = false;
107                }
108                break;
109            case R.id.menuSortImportance:
110                if (tap == false) {
111                    setList(ColumnTask.importance, SortType.DESC);
112                    tap = true;
113                } else {
114                    setList(ColumnTask.importance, SortType.ASC);
115                    tap = false;
116                }
117                break;
118            default:
119                returnValue = super.onOptionsItemSelected(item);
120                break;
121        }
122        return returnValue;
123    }
124
125    @Override
126    public void onDestroy() {
127        super.onDestroy();
128        helper.close();
129    }
130}

試したこと

SQLiteのDBブラウザで実際にSQL実行したところ以下のSQL文は効いて降順になっていました。
→SELECT * FROM task WHERE userId = 1 ORDER BY number DESC;
whereやlimit等を試しましたが、order byのみが効いていない状態です。
原因等心当たりありましたら教えていただきたいです。
（まだ作成途中のため色々と疑問に思う書き方はあると思いますが、何卒ご容赦ください…）

行動規範の内容に同意します

回答3件

並び替えだけなら、毎回 SQL しなくても List の中身を並び替える方法もあります。

TaskListActivity .java

java
1public class TaskListActivity extends AppCompatActivity {
2  enum SortType {
3    ASC, DESC;
4
5    SortType next() {
6      return values()[(ordinal() + 1) % values().length];
7    }
8  }
9
10  enum ColumnTask {
11    number, task, urgency, importance
12  }
13
14  private UserDatabaseHelper helper;
15  private int userId;
16  private SortType sortType = SortType.ASC;
17  private TaskListAdapter adapter;
18
19  @Override
20  protected void onCreate(Bundle savedInstanceState) {
21    super.onCreate(savedInstanceState);
22    setContentView(R.layout.activity_task_list);
23
24    userId = getIntent().getIntExtra("userId", 0);
25    helper = new UserDatabaseHelper(this);
26
27    setList(ColumnTask.number);
28  }
29
30  public void setList(ColumnTask orderby) {
31    if(adapter == null) {
32      adapter = createAdapter();
33      ((ListView)findViewById(R.id.taskList)).setAdapter(adapter);
34    }
35    adapter.sort(orderby.toString(), sortType.toString());
36  }
37
38  private TaskListAdapter createAdapter() {
39    TaskListAdapter adapter = new TaskListAdapter();
40/*
41    SQLiteDatabase db = helper.getReadableDatabase();
42    String selectSQL = "SELECT number, task, urgency, importance FROM task WHERE userId = ?";
43    try(Cursor cursor = db.rawQuery(selectSQL, new String[]{"" + userId});) {
44      int numberIdx = cursor.getColumnIndex("number");
45      int taskIdx = cursor.getColumnIndex("task");
46      int urgencyIdx = cursor.getColumnIndex("urgency");
47      int importanceIdx = cursor.getColumnIndex("importance");
48
49      while(cursor.moveToNext()) {
50        int number = cursor.getInt(numberIdx);
51        String content = cursor.getString(taskIdx);
52        int urgency = cursor.getInt(urgencyIdx);
53        int importance = cursor.getInt(importanceIdx);
54
55        adapter.add(new Task(number, content, urgency, importance));
56      }
57    }
58*/
59    //テストデータ
60    adapter.add(new Task(2, "洗濯", 1, 3));
61    adapter.add(new Task(1, "掃除", 3, 2));
62    adapter.add(new Task(3, "炊飯", 2, 1));
63
64    return adapter;
65  }
66
67  @Override
68  public boolean onCreateOptionsMenu(Menu menu) {
69    getMenuInflater().inflate(R.menu.menu_sort, menu);
70    return true;
71  }
72
73  private static final Map<Integer,ColumnTask> sortMenuResIdMap = new HashMap<>();
74  static {
75    sortMenuResIdMap.put(R.id.menuSortNumber, ColumnTask.number);
76    sortMenuResIdMap.put(R.id.menuSortTask, ColumnTask.task);
77    sortMenuResIdMap.put(R.id.menuSortUrgency, ColumnTask.urgency);
78    sortMenuResIdMap.put(R.id.menuSortImportance, ColumnTask.importance);
79  }
80
81  @Override
82  public boolean onOptionsItemSelected(MenuItem item) {
83    ColumnTask column = sortMenuResIdMap.get(item.getItemId());
84    if(column != null) {
85      sortType = sortType.next();
86      setList(column);
87      return true;
88    }
89    return super.onOptionsItemSelected(item);
90  }
91
92  @Override
93  public void onDestroy() {
94    super.onDestroy();
95    helper.close();
96  }
97}
98
99class TaskListAdapter extends BaseAdapter {
100  private final List<Task> taskList = new ArrayList<>();
101
102  void add(Task task) {
103    taskList.add(task);
104    notifyDataSetChanged();
105  }
106
107  void sort(String orderBy, String sortType) {
108    taskList.sort((Task o1, Task o2) -> {
109      if(sortType.equalsIgnoreCase("DESC")) { Task t = o1; o1 = o2; o2 = t; }
110      if(orderBy.equalsIgnoreCase("number")) return o1.number - o2.number;
111      if(orderBy.equalsIgnoreCase("task")) return o1.content.compareTo(o2.content);
112      if(orderBy.equalsIgnoreCase("urgency")) return o1.urgency - o2.urgency;
113      if(orderBy.equalsIgnoreCase("importance")) return o1.importance - o2.importance;
114      return 0;
115    });
116    notifyDataSetChanged();
117  }
118
119  @Override
120  public int getCount() {
121    return taskList.size();
122  }
123
124  @Override
125  public Object getItem(int position) {
126    return taskList.get(position);
127  }
128
129  @Override
130  public long getItemId(int position) {
131    return 0;
132  }
133
134  @Override
135  public View getView(int position, View convertView, ViewGroup parent) {
136    if(convertView == null) {
137      convertView = LayoutInflater.from(parent.getContext()).inflate(R.layout.task_list_row, null);
138      convertView.setTag(new ViewHolder(convertView));
139    }
140    ViewHolder vh = (ViewHolder)convertView.getTag();
141
142    Task task = taskList.get(position);
143    vh.number.setText("" + task.number);
144    vh.content.setText(task.content);
145    vh.urgency.setText("" + task.urgency);
146    vh.importance.setText("" + task.importance);
147
148    return convertView;
149  }
150
151  private static class ViewHolder {
152    final TextView number, content, urgency, importance;
153    ViewHolder(View view) {
154      number = view.findViewById(R.id.lvNumber);
155      content = view.findViewById(R.id.lvTask);
156      urgency = view.findViewById(R.id.lvUrgency);
157      importance = view.findViewById(R.id.lvImportance);
158    }
159  }
160}
161
162class Task {
163  final int number;
164  final String content;
165  final int urgency;
166  final int importance;
167
168  Task(int number, String content, int urgency, int importance) {
169    this.number = number;
170    this.content = content;
171    this.urgency = urgency;
172    this.importance = importance;
173  }
174}

res/layout/activity_task_list.xml

xml
1<?xml version="1.0" encoding="utf-8"?>
2<androidx.constraintlayout.widget.ConstraintLayout
3    xmlns:android="http://schemas.android.com/apk/res/android"
4    xmlns:app="http://schemas.android.com/apk/res-auto"
5    xmlns:tools="http://schemas.android.com/tools"
6    android:layout_width="match_parent"
7    android:layout_height="match_parent"
8    tools:context=".TaskListActivity">
9
10  <TextView
11      android:id="@+id/lvNumber"
12      android:layout_width="30dp"
13      android:layout_height="wrap_content"
14      android:text="項番"
15      android:layout_marginStart="10dp"
16      app:layout_constraintLeft_toLeftOf="parent"
17      app:layout_constraintRight_toLeftOf="@id/lvTask"
18      app:layout_constraintTop_toTopOf="parent" />
19  <TextView
20      android:id="@+id/lvTask"
21      android:layout_width="0dp"
22      android:layout_height="wrap_content"
23      android:text="タスク"
24      android:textAlignment="center"
25      app:layout_constraintLeft_toRightOf="@id/lvNumber"
26      app:layout_constraintRight_toLeftOf="@id/lvUrgency"
27      app:layout_constraintTop_toTopOf="parent" />
28  <TextView
29      android:id="@+id/lvUrgency"
30      android:layout_width="50dp"
31      android:layout_height="wrap_content"
32      android:text="緊急度"
33      app:layout_constraintLeft_toRightOf="@id/lvTask"
34      app:layout_constraintRight_toLeftOf="@id/lvImportance"
35      app:layout_constraintTop_toTopOf="parent" />
36  <TextView
37      android:id="@+id/lvImportance"
38      android:layout_width="50dp"
39      android:layout_height="wrap_content"
40      android:text="重要度"
41      android:layout_marginEnd="10dp"
42      app:layout_constraintLeft_toRightOf="@id/lvUrgency"
43      app:layout_constraintRight_toRightOf="parent"
44      app:layout_constraintTop_toTopOf="parent" />
45
46  <ListView
47      android:id="@+id/taskList"
48      android:layout_width="0dp"
49      android:layout_height="0dp"
50      app:layout_constraintBottom_toBottomOf="parent"
51      app:layout_constraintLeft_toLeftOf="parent"
52      app:layout_constraintRight_toRightOf="parent"
53      app:layout_constraintTop_toBottomOf="@id/lvNumber" />
54
55</androidx.constraintlayout.widget.ConstraintLayout>

res/layout/task_list_row.xml

xml
1<?xml version="1.0" encoding="utf-8"?>
2<androidx.constraintlayout.widget.ConstraintLayout
3    xmlns:android="http://schemas.android.com/apk/res/android"
4    xmlns:app="http://schemas.android.com/apk/res-auto"
5    android:layout_width="match_parent"
6    android:layout_height="wrap_content"
7    android:paddingBottom="10dp"
8    android:paddingTop="10dp">
9
10  <TextView
11      android:id="@+id/lvNumber"
12      android:layout_width="30dp"
13      android:layout_height="wrap_content"
14      android:text="項番"
15      android:layout_marginStart="10dp"
16      android:layout_marginEnd="10dp"
17      app:layout_constraintLeft_toLeftOf="parent"
18      app:layout_constraintRight_toLeftOf="@id/lvTask"
19      app:layout_constraintTop_toTopOf="parent" />
20  <TextView
21      android:id="@+id/lvTask"
22      android:layout_width="0dp"
23      android:layout_height="wrap_content"
24      android:text="タスク"
25      app:layout_constraintLeft_toRightOf="@id/lvNumber"
26      app:layout_constraintRight_toLeftOf="@id/lvUrgency"
27      app:layout_constraintTop_toTopOf="parent" />
28  <TextView
29      android:id="@+id/lvUrgency"
30      android:layout_width="50dp"
31      android:layout_height="wrap_content"
32      android:text="緊急度"
33      app:layout_constraintLeft_toRightOf="@id/lvTask"
34      app:layout_constraintRight_toLeftOf="@id/lvImportance"
35      app:layout_constraintTop_toTopOf="parent" />
36  <TextView
37      android:id="@+id/lvImportance"
38      android:layout_width="50dp"
39      android:layout_height="wrap_content"
40      android:text="重要度"
41      android:layout_marginEnd="10dp"
42      app:layout_constraintLeft_toRightOf="@id/lvUrgency"
43      app:layout_constraintRight_toRightOf="parent"
44      app:layout_constraintTop_toTopOf="parent" />
45
46</androidx.constraintlayout.widget.ConstraintLayout>