什么SQLite DB查询获取具有特定字符串ID的最后一行记录

Question

如何获得最后一行以检查该用户或退房。这是我的数据库助手类查询：

public List<UserEntry> getAllUserLastEntryType(String s) {
    List<UserEntry> codeList = new ArrayList<UserEntry>();
    // Select All Query
    String selectQuery = "SELECT  * FROM " + TABLE_USER_ENTRY + " WHERE " + KEY_USER_ID_ENTRY + "=" + s;
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor cursor = db.rawQuery(selectQuery, null);
    System.out.println("cursor:" + cursor.isLast());
    // looping through all rows and adding to list
    if (cursor.moveToFirst()) {
        do {
            UserEntry user = new UserEntry();
            user.setIdCode(cursor.getString(1));
            user.setUserCheckType((cursor.getString(4)));
            // Adding contact to list
            codeList.add(user);
        } while (cursor.moveToLast());
    }
    // return contact list
    return codeList;
}

这是活动类，我在那里获取记录：

String userId = PreferencesUtils.getString(UserActivity.this, PreferencesUtils.PREF_KEY_USER_ID_ENTRY, "");
    Log.e("userId", userId);
    List<UserEntry> userEntries = databaseHandler.getAllUserLastEntryType(userId);
    for (UserEntry userEntry : userEntries) {
        String id = userEntry.getIdCode();
        Log.e("checkId", id);
        String type = userEntry.getUserCheckType();
        Log.e("checkType", type);
    }

，但没有返回。请告知我。

Answer 1

我更改了您的功能中的两个语法，请检查您的功能

public List<UserEntry> getAllUserLastEntryType(String s) {
        List<UserEntry> codeList = new ArrayList<UserEntry>();
        // Select All Query
        String selectQuery = "SELECT * FROM " + TABLE_USER_ENTRY + " WHERE " + KEY_USER_ID_ENTRY + " = '" +s+"'";
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
        System.out.println("cursor:" + cursor.isLast());
        // looping through all rows and adding to list
        if (cursor.moveToFirst()) {
            do {
                UserEntry user = new UserEntry();
                user.setIdCode(cursor.getString(1));
                user.setUserCheckType((cursor.getString(4)));
                // Adding contact to list
                codeList.add(user);
            } while (cursor.moveToLast());
        }
        // return contact list
        return codeList;
    }

我使用了getReadableDatabase并在选定查询中进行了更正，您还可以找出codeList是否偶数，意味着用户已签出或检查其奇数，用户被检查。它完全取决于您的编程逻辑。还要检查cursor.getString是否正确参数。

Answer 2

您发行的问题可能是由于 s 不在报价中，即查询的SQL是

String selectQuery = "SELECT * FROM " + TABLE_USER_ENTRY + " WHERE " + KEY_USER_ID_ENTRY + "=" + s;

您可能会发现String selectQuery = "SELECT * FROM " + TABLE_USER_ENTRY + " WHERE " + KEY_USER_ID_ENTRY + "='" + s "'";

会起作用。

假设值正确，例如，让我们说ID存储为字符串001和" 001"。作为对众心者的传递。没有引号sqlite被告知将值视为一个数字，它是 1 not 001 and 1不是存储的。

但是，尽管使用SQLiteDatabase query方法可能会稍长一点，通常被视为可取的查询方式。

使用查询的等效物可能是： -

    String whereclause = KEY_USER_ID_ENTRY + "=?";
    String[] whereargs = {s};
    Cursor cursor = db.query(
        TABLE_USER_ENTRY,     // Table (can include JOINS with ON here)
        null,                 // String array of columns to include, null equivalent to *
        whereclause,          // WHERE (less WHERE) with optional ?'s as placeholders
        whereargs,            // arguments to replace placeholders
        null,                 // GROUP BY (less GROUP BY) clause, null no clause
        null,                 // HAVING (less HAVING), null no clause
        null                  // ORDER BY (LESS ORDER BY), null no clause
    );                        // optional LIMIT

因此， String whereclause = KEY_USER_ID_ENTRY + "=?";等于 WHERE KEY_USER_ID_ENTRY =something，其中从 whereargs 字符串阵列中获得的东西，因此在这种情况下，它是 s ，当时将其传递给该方法时。因此，如果用弗雷德（Fred）打电话，则查询将使用相当于WHERE KEY_USER_ID_ENTRY ='FRED'的等效。

---

插图

要根据您的特定问题说明差异，请考虑您在您的sqliteopenhelper子cclclass中的以下方法： -

public List<UserEntry> getAllUserLastEntryType(String s) {
    List<UserEntry> codeList = new ArrayList<UserEntry>();
    List<UserEntry> codeListAlternate1 = new ArrayList<UserEntry>();
    List<UserEntry> codeListAlternate2 = new ArrayList<UserEntry>();

    // Existing where s is not enclosed in quotes
    String selectQuery = "SELECT  * FROM " + TABLE_USER_ENTRY + " WHERE " + KEY_USER_ID_ENTRY + "=" + s;
    Cursor csr;
    /*
    while (csr.moveToNext()) {
        UserEntry user = new UserEntry();
        user.setIDCode(csr.getString(csr.getColumnIndex(KEY_USER_ID_ENTRY)));
        user.setUserCheck(csr.getString(csr.getColumnIndex(USER_TYPE)));
        codeList.add(user);
    }
    */
    addUsersToList(codeList,csr = db.rawQuery(selectQuery,null),"METHOD1",s);
    Log.d("METHDO1", "Raw Query returned " + csr.getCount() + " Rows using SQL=" + selectQuery);
    String alt1Query = "SELECT  * FROM " + TABLE_USER_ENTRY + " WHERE " + KEY_USER_ID_ENTRY + "='" + s + "'";
    addUsersToList(codeListAlternate1,csr = db.rawQuery(alt1Query,null),"METHOD2",s);
    Log.d("METHDO2", "Raw Query returned " + csr.getCount() + " Rows using SQL=" + alt1Query);

    String whereclause = KEY_USER_ID_ENTRY + "=?";
    String[] whereargs = {s};
    addUsersToList(codeListAlternate2,
            db.query(TABLE_USER_ENTRY,null,whereclause,whereargs,null,null,null),
            "METHOD3",s);
    csr.close();
    return codeListAlternate2;
}
private void addUsersToList(List<UserEntry> ulist, Cursor csr, String logtag, String s) {
    csr.moveToPosition(-1); // just in case ensure cursor is before first
    Log.d(logtag,"Rows in Cursor = "+ csr.getCount() + " Based upon Search criteria ===>" + s + "<===");
    while (csr.moveToNext()) {
        UserEntry u = new UserEntry();
        u.setIDCode(csr.getString(csr.getColumnIndex(KEY_USER_ID_ENTRY)));
        u.setUserCheck(csr.getString(csr.getColumnIndex(USER_TYPE)));
        ulist.add(u);
        Log.d(logtag,"Add User IDCode=" + u.getIDCode() + " Check=" + u.getUserCheck());
    }
}

使用以下活动（ SO46195289DBHelper是上方的助手）： -

    SO46195289DBHelper dbhlpr = new SO46195289DBHelper(this);
    dbhlpr.insertUser("001","FRED FLINSTONE","[e-mail]","TYPE1"); //insert test data
    dbhlpr.insertUser("002","BERT REYNOLDS","[e-mail]","TYEP2"); // insert test data
    dbhlpr.insertUser("003","TOM HANKS","[e-mail]","TYPE3"); // inert test data
    List<UserEntry> list1 = dbhlpr.getAllUserLastEntryType("001");
    dbhlpr.getAllUserLastEntryType("003"); // test all three methods

然后输出就像： -

09-14 14:02:03.817 3562-3562/? D/METHOD1: Rows in Cursor = 0 Based upon Search criteria ===>001<===
09-14 14:02:03.817 3562-3562/? D/METHDO1: Raw Query returned 0 Rows using SQL=SELECT  * FROM users WHERE keyuseridentry=001
09-14 14:02:03.818 3562-3562/? D/METHOD2: Rows in Cursor = 1 Based upon Search criteria ===>001<===
09-14 14:02:03.818 3562-3562/? D/METHOD2: Add User IDCode=001 Check=TYPE1
09-14 14:02:03.818 3562-3562/? D/METHDO2: Raw Query returned 1 Rows using SQL=SELECT  * FROM users WHERE keyuseridentry='001'
09-14 14:02:03.818 3562-3562/? D/METHOD3: Rows in Cursor = 1 Based upon Search criteria ===>001<===
09-14 14:02:03.818 3562-3562/? D/METHOD3: Add User IDCode=001 Check=TYPE1
09-14 14:02:03.822 3562-3562/? D/METHOD1: Rows in Cursor = 0 Based upon Search criteria ===>003<===
09-14 14:02:03.822 3562-3562/? D/METHDO1: Raw Query returned 0 Rows using SQL=SELECT  * FROM users WHERE keyuseridentry=003
09-14 14:02:03.822 3562-3562/? D/METHOD2: Rows in Cursor = 1 Based upon Search criteria ===>003<===
09-14 14:02:03.822 3562-3562/? D/METHOD2: Add User IDCode=003 Check=TYPE3
09-14 14:02:03.823 3562-3562/? D/METHDO2: Raw Query returned 1 Rows using SQL=SELECT  * FROM users WHERE keyuseridentry='003'
09-14 14:02:03.824 3562-3562/? D/METHOD3: Rows in Cursor = 1 Based upon Search criteria ===>003<===
09-14 14:02:03.825 3562-3562/? D/METHOD3: Add User IDCode=003 Check=TYPE3

---

偏移（问题的潜在替代原因）

在 addUsersToList 方法中，您可能会发现我使用getColumnIndex(column name as String)方法而不是硬编码的OffSFET，以基于列名来获取偏移。这可以显着降低将列转换为偏移的错误的可能性。

例如，可能是您的 KEY_USER_ID_ENTRY 是表中的第一列，如果是的，则偏移量是0，而不是1。这也可能导致找不到行。