android - 如何使用 WHERE 子句查询我的数据库并使用 ORDER BY DESC 获取最近 5 条记录

Question

我当前的查询使用 roomId 为我提供了某个房间的所有治疗对象。我只需要返回最后 5 个。我可以使用 db.query 方法吗？还是我必须编写一条 rawQuery SQL 语句？

我应该只添加 ORDER BY DESC 并剪切我的列表数组吗？

哪个更好？

public List<Treatment> getLastFiveTreatmentsOfRoom(long roomId) {
    // var to store last 5 treatments
    List<Treatment> listTreatments = new ArrayList<Treatment>();
    Cursor cursor = mDatabase.query(DatabaseOpenHelper.TABLE_TREATMENT, mAllColumns, DatabaseOpenHelper.KEY_TREATMENT_ROOM_ID
    + " =?", new String[] { String.valueOf(roomId) }, null, null, null);
    if (cursor!= null) {
        cursor.moveToFirst();
    }
    while (!cursor.isAfterLast()) {
        Treatment treatment = cursorToTreatment(cursor);
        listTreatments.add(treatment);
        cursor.moveToNext();
    }
    // make sure to close cursor
    cursor.close();
    return listTreatments;
}

这些是我的字段 - “添加”是我需要排序的字段:它的 TEXT NOT NULL

public static final String TABLE_TREATMENT = "treatment";
public static final String KEY_TREATMENT_ROWID = "_id";
public static final String KEY_TREATMENT_ISOLATION = KEY_ISO;
public static final String KEY_TREATMENT_TREATEDBY = "treated_by";
public static final String KEY_TREATMENT_ADDED = "added";
public static final String KEY_TREATMENT_HOSPITAL_ID = "hospitalId";
public static final String KEY_TREATMENT_ROOM_ID = "roomId";

这是填充添加字段的代码

Calendar calendar = Calendar.getInstance();
java.util.Date now = calendar.getTime();
java.sql.Timestamp currentTimestamp = new java.sql.Timestamp(now.getTime());
String addedTime = currentTimestamp.toString();

Answer 1

这是查询方法签名:

query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit)

前最后一个参数是sql ORDER BY，选择参数是sql WHERE。

所以您需要更改以下行:

Cursor cursor = mDatabase.query(DatabaseOpenHelper.TABLE_TREATMENT, mAllColumns, DatabaseOpenHelper.KEY_TREATMENT_ROOM_ID
    + " =?", new String[] { String.valueOf(roomId) }, null, "YOUR_COLUMN DESC", null);