android - 列表大小从 sqlite 查询返回 0

Question

我尝试通过连接三个表来查询前提列表。但是，当我添加 WHERE 时，我的列表返回空。我试图记录一切......似乎没有它，我能够获得前提列表......但这是错误的。我尝试在 DB Browser for sqlite 上运行查询，它成功运行并返回正确的列表。请帮助我。

这是我的查询代码..

public List<TXN_Premise> getTxnTableData(String tableName, String columnName) {

        ArrayList<TXN_Premise> itemList = new ArrayList<>();
        String selectQuery = "SELECT "
                + Constants.COLUMN_PREMISE_REF + ", "
                + Constants.COLUMN_PREMISE_NAME + ", "
                + Constants.COLUMN_PREMISE_ADDRESS + ", "
                + Constants.REF_PREMISE_CATEGORY_TABLE + "." + Constants.COLUMN_PREMISE_CATEGORY_ID + " AS " + Constants.COLUMN_FK_PREMISE_CATEGORY_ID +", "
                + Constants.REF_PREMISE_CATEGORY_MASTER_TABLE + "." + Constants.COLUMN_PREMISE_CATEGORY_MASTER_ID + " AS " + Constants.COLUMN_FK_PREMISE_CATEGORY_MASTER_ID + ", "
                + Constants.COLUMN_PREMISE_REG_NO + ", "
                + Constants.COLUMN_PREMISE_REG_DATE + ", "
                + Constants.REF_PREMISE_CATEGORY_TABLE + "." + Constants.COLUMN_DESCRIPTION + " AS premiseCategoryDescription, "
                + Constants.REF_PREMISE_CATEGORY_MASTER_TABLE + "." + Constants.COLUMN_DESCRIPTION + " AS premiseCategoryMasterDescription "

                + " FROM " + tableName

                + " LEFT JOIN " + Constants.REF_PREMISE_CATEGORY_MASTER_TABLE + " ON " +
                Constants.TXN_PREMISE_TABLE + "." + Constants.COLUMN_FK_PREMISE_CATEGORY_MASTER_ID + " = " + Constants.REF_PREMISE_CATEGORY_MASTER_TABLE + "." + Constants.COLUMN_CODE

                + " LEFT JOIN " + Constants.REF_PREMISE_CATEGORY_TABLE + " ON " +
                Constants.TXN_PREMISE_TABLE + "." + Constants.COLUMN_FK_PREMISE_CATEGORY_ID + " = " + Constants.REF_PREMISE_CATEGORY_TABLE + "." + Constants.COLUMN_CODE

//              + " WHERE " + Constants.REF_PREMISE_CATEGORY_TABLE + "." + Constants.COLUMN_LANG + " = 'MYS'"
//              + Constants.REF_PREMISE_CATEGORY_MASTER_TABLE + "." + Constants.COLUMN_LANG + " = 'MYS' AND "
//              + Constants.COLUMN_PREMISE_NAME + " <> '-' AND " + Constants.COLUMN_PREMISE_NAME + " <> '-TIADA-'"

                + " ORDER BY " + columnName + " ASC LIMIT 3";

        Log.d("test", "getTxnTableData: " + selectQuery);

        try {
            open();
            //make sure the database is not empty
            if (sqLiteDatabase != null) {
                //get a cursor for all state in the database
                Cursor cursor = sqLiteDatabase.rawQuery(selectQuery, null);

                Log.d("test", "getTxnTableData: cursor " + cursor.toString());

                Log.d("test", "getTxnTableData: outside");

                if (cursor.moveToFirst()) {
                    while (!cursor.isAfterLast()) {
                        TXN_Premise premiseColumn = new TXN_Premise();
//                      premiseColumn.setPremiseId(cursor.getString(0));
                        premiseColumn.setPremiseRef(cursor.getString(cursor.getColumnIndex(Constants.COLUMN_PREMISE_REF)));
                        premiseColumn.setPremiseName(cursor.getString(cursor.getColumnIndex(Constants.COLUMN_PREMISE_NAME)));
                        premiseColumn.setPremiseAddress(cursor.getString(cursor.getColumnIndex(Constants.COLUMN_PREMISE_ADDRESS)));
                        premiseColumn.setFk_premiseCategoryId(cursor.getString(cursor.getColumnIndex(Constants.COLUMN_FK_PREMISE_CATEGORY_ID)));
                        premiseColumn.setFk_premiseCategoryMasterId(cursor.getString(cursor.getColumnIndex(Constants.COLUMN_FK_PREMISE_CATEGORY_MASTER_ID)));
                        premiseColumn.setPremiseRegNo(cursor.getString(cursor.getColumnIndex(Constants.COLUMN_PREMISE_REG_NO)));
                        premiseColumn.setPremiseRegDate(cursor.getString(cursor.getColumnIndex(Constants.COLUMN_PREMISE_REG_DATE)));


                        //  todo: temp premise category description
                        premiseColumn.setPremiseCategoryName(cursor.getString(cursor.getColumnIndex("premiseCategoryDescription")));
                        premiseColumn.setPremiseCategoryMasterName(cursor.getString(cursor.getColumnIndex("premiseCategoryMasterDescription")));

                        Log.d("test", "getTxnTableData: premiseName " + cursor.getString(cursor.getColumnIndex(Constants.COLUMN_PREMISE_NAME)));

                        //add premiseColumn in the cursor
                        itemList.add(premiseColumn);
                        cursor.moveToNext();
                    }
                }
                cursor.close();
            }
            close();

        } catch (SQLException e) {
            e.printStackTrace();
        }

        Log.d("test", "getTxnTableData: itemlist " + itemList.size() );

        return itemList;
    }

Answer 1

不可能重现问题并因此确定问题，因为 WHERE 子句依赖于基础数据。

因此，您需要遵循简单的问题确定。

正如您所指出的，它无需 WHERE 子句即可工作

• 1) 删除 WHERE 子句并运行。

  • 1a) 如果您现在检索行，然后逐个条件逐步构建 WHERE 子句，这将突出显示该问题。

  • 1b) 如果删除 WHERE 子句后没有任何行的问题仍然存在，则逐步构建从 String selectQuery = "SELECT * FROM " + tableName; 开始的整个 SQL ，检查结果是否符合预期。

我建议更改

    Log.d("test", "getTxnTableData: cursor " + cursor.toString());

至

    Log.d("test", "getTxnTableData: cursor " + cursor.getCount());

这将显示光标中的行数。

此外，您还可以利用 the utillities here

<小时/>

编辑

仔细观察一下，WHERE 子句似乎存在一些问题:-

为简化起见，无论在何处，都将做出以下决议:-

• Constants.REF_PREMISE_CATEGORY_TABLE似乎它将被替换为 rpc

• Constants.REF_PREMISE_CATEGORY_MASTER_TABLE似乎它将被替换为 mrpc

• Constants.COLUMN_LANG出现时，它将被替换为 lang

• Constants.COLUMN_PREMISE_NAME出现时，它将被替换为 name

所以:-

" WHERE " + Constants.REF_PREMISE_CATEGORY_TABLE + "." + Constants.COLUMN_LANG + " = 'MYS'"
              + Constants.REF_PREMISE_CATEGORY_MASTER_TABLE + "." + Constants.COLUMN_LANG + " = 'MYS' AND "
              + Constants.COLUMN_PREMISE_NAME + " <> '-' AND " + Constants.COLUMN_PREMISE_NAME + " <> '-TIADA-'"

变为( ????(n) 用于指示问题，其中 n 标识特定问题，注意它不是 SQL 的一部分):-

" WHERE rpc.lang = 'MYS' <强> ????(1) mrpc.lang = 'MYS' AND name <> '-' AND name <> '-TIADA-' <强> ????(2)

• 第 1 期 ????(1) 两次检查之间没有条件，例如也许应该是WHERE rpc.lang = 'MYS' <强> AND mrpc.lang = 'MYS' .......

  • 这可能会产生类似以下的错误
  • .....
[ near "?????????": syntax error ]
Exception Name: NS_ERROR_FAILURE
Exception Message: Component returned failure code: 0x80004005 (NS_ERROR_FAILURE)

• 第 2 期 ????(2) 这对条件将永远满足名称只能是-或-TIADA- 切勿两者兼而有之。也许您的意思是OR 在这种情况下，我会将它们括在括号中。

所以代码可能是:-

" WHERE " + 
    Constants.REF_PREMISE_CATEGORY_TABLE + "." + 
        Constants.COLUMN_LANG + 
        " = 'MYS'" + 
" AND " + 
    Constants.REF_PREMISE_CATEGORY_MASTER_TABLE + "." +
        Constants.COLUMN_LANG + " = 'MYS' " + 
" AND (" +
            Constants.COLUMN_PREMISE_NAME + " <> '-' " + 
            "  OR " + 
            Constants.COLUMN_PREMISE_NAME + " <> '-TIADA-'" + 
")"