java - 如何避免在微调器中从 sqlite 获取双数据？

Question

我正在尝试从 sqlite 获取数据以将它们设置到微调器中。用户创建旅行日期行并将其设置到数据库中 (COLUMN_DATE_DEPART)。在第一个旋转器中，我想获取 COLUMN_DATE_DEPART 中现有年份的列表。在第二个中，我想获取在第一个旋转器中选择的一年中的月份列表。

问题如下:在第一个旋转器中，我得到了预期的年份。在第二个旋转器中，我得到了月份，但是是双倍的，这样如果用户在 1 月 1 日和 31 日创建了两次旅行，我将获得 1 月两次而不是一次。

我的问题是:我错过了什么？

查找附加的我的代码。

Java fragment Activity

List<String> an = helper.ListeAnneeTrajet(et_iduser);
                final ArrayAdapter<String> adapterA= new ArrayAdapter<>(this.getActivity(), R.layout.spinner_item, an);
                adapterA.setDropDownViewResource(android.R.layout.simple_spinner_dropdown_item);
                annee.setAdapter(adapterA);
                annee.setOnItemSelectedListener(new AdapterView.OnItemSelectedListener() {

                    @Override
                    public void onItemSelected(AdapterView<?> parentView, View selectedItemView, int position, long id) {

                        List<String> mo = helper.ListeMoisTrajet(et_iduser, annee.getSelectedItem().toString().trim());
                        ArrayAdapter<String> adapterB = new ArrayAdapter<>(getActivity(), R.layout.spinner_item, mo);
                        adapterA.setDropDownViewResource(android.R.layout.simple_spinner_dropdown_item);
                        mois.setAdapter(adapterB);
                        mois.setOnItemSelectedListener(new AdapterView.OnItemSelectedListener() {

                            @Override
                            public void onItemSelected(AdapterView<?> parentView, View selectedItemView, int position, long id) {
                                // your code here
                            }
                            @Override
                            public void onNothingSelected(AdapterView<?> parentView) {
                                // your code here
                            }
                        });
                    }
                    @Override
                    public void onNothingSelected(AdapterView<?> parentView) {
                        // your code here
                    }
                });

数据库助手

public List<String> ListeAnneeTrajet(String et_iduser) {
        List<String> an = new ArrayList<>();
        String selectQuery = "SELECT DISTINCT * FROM " + TABLE_NAME3 + " WHERE (" + COLUMN_ID_CHAUFFEUR + " = '" + et_iduser + "' AND " + COLUMN_DATE_DEPART + " >= DATE('NOW')) GROUP BY SUBSTR(" + COLUMN_DATE_DEPART + ",1,4) ORDER BY " + COLUMN_DATE_DEPART + " ASC";
        db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);

        if (cursor.moveToFirst()) {
            do {
                an.add(cursor.getString(cursor.getColumnIndex(COLUMN_DATE_DEPART)).substring(0,4));
            }
            while (cursor.moveToNext());
        }
        cursor.close();
        db.close();
        return an;
    }

    public List<String> ListeMoisTrajet(String et_iduser, String et_annee) {
        List<String> mo = new ArrayList<>();
        String selectQuery = "SELECT DISTINCT * FROM " + TABLE_NAME3 + " WHERE (" + COLUMN_ID_CHAUFFEUR + " = '" + et_iduser + "' AND " + COLUMN_DATE_DEPART + " >= DATE('NOW') AND SUBSTR(" + COLUMN_DATE_DEPART + ",1,4) = '" + et_annee + "') GROUP BY SUBSTR(" + COLUMN_DATE_DEPART + ",6,7) ORDER BY " + COLUMN_DATE_DEPART + " ASC";
        db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);

        if (cursor.moveToFirst()) {
            do {
                mo.add(cursor.getString(cursor.getColumnIndex(COLUMN_DATE_DEPART)).substring(5,7));
            } while (cursor.moveToNext());
        }
        cursor.close();
        db.close();
        return mo;
    }

Answer 1

@Mike T，你应该在巴黎香榭丽舍大街上有一尊金色雕像，但是，因为到目前为止我无法为你 build 它，请接受我永恒的感激之情。实际上我按照你所说的改变了我的查询:

public List<String> ListeMoisTrajet(String et_iduser, String et_annee) {
        List<String> mo = new ArrayList<>();
        String selectQuery = "SELECT DISTINCT " + COLUMN_DATE_DEPART + " FROM " + TABLE_NAME3 + " WHERE (" + COLUMN_ID_CHAUFFEUR + " = '" + et_iduser + "' AND " + COLUMN_DATE_DEPART + " >= DATE('NOW') AND SUBSTR(" + COLUMN_DATE_DEPART + ",1,4) = '" + et_annee + "') GROUP BY SUBSTR(" + COLUMN_DATE_DEPART + ",6,2) ORDER BY " + COLUMN_DATE_DEPART + " ASC";

我不敢相信我花了几个星期的时间来解决这个问题......:-)