dimanche 28 juin 2015

How do I query my database with WHERE clause and use ORDER BY DESC for the last 5 most recent records

The current query I have gives me all the Treatment objects of a certain Room, using roomId. I need to return only the last 5. Can I use the db.query method? Or will I have to write a rawQuery SQL statement?

Should I just add ORDER BY DESC and snip my list array?

Which would be better?

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;
}

These are my fields - "added" is the field I need to sort by: its TEXT NOT NULL and filled with this:

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();

Aucun commentaire:

Enregistrer un commentaire