Sqlite query to keep the last 5 searches on android

Question

I have a SQLite database in my android app.

Each time I open the search activity of my app, I want to display a list of my search history but limited to the latest 5 searched items.

DBHelper searchDBHelper = DBHelper.getInstance(getApplicationContext()); //extends SQLiteOpenHelper
//lookup the historical data from the DB first and pass it to the adapter
try {
    searchDBHelper.deletePlaceSearchItems();
    hSearchDao = searchDBHelper.getHSearchDao();
    searchArrayList = new LinkedList<>(hSearchDao.queryForAll());
    historicalSearchAdapter = new HistoricalSearchAdapter(searchArrayList);

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

my SearchDBHelper.java has the following delete method in it which is called above:

public void deletePlaceSearchItems() {
    SQLiteDatabase db = getWritableDatabase();
        String[] args ={"5"};
        db.rawQuery("DELETE FROM placesearch " +
                "WHERE id < (" +
                " SELECT MIN(id)" +
                " FROM (SELECT id" +
                " FROM placesearch" +
                " ORDER BY date DESC" +
                " LIMIT ?))", args);
}

The sqlite query code was obtained from here: Keep only N last records in SQLite database, sorted by date

I keep on running the app but the code does not seem to delete anything at all. Also not sure how I can debug this as no error message was printed to my logs. This is what my sqlite db looks like inside - it has 6 items:

This is my ormlite_config.txt file:

#################################
# --table-start--
dataClass=Model.PlaceSearch
tableName=placesearch
# --table-fields-start--
# --field-start--
fieldName=id
columnName=id
generatedId=true
# --field-end--
# --field-start--
fieldName=placeSearch
columnName=places_search
# --field-end--
# --field-start--
fieldName=time
columnName=time
# --field-end--
# --table-fields-end--
# --table-end--
#################################

What is wrong with my code?

score 2 · Accepted Answer · edited Nov 24 '15 at 17:39

2

In your database, I'm not seeing any column with the name date; you have a field named time.

You need to change your code like:

db.execSQL("DELETE FROM placesearch " +
                "WHERE id < (" +
                " SELECT MIN(id)" +
                " FROM (SELECT id" +
                " FROM placesearch" +
                " ORDER BY time DESC" +
                " LIMIT ?))", args);

edited Nov 24 '15 at 17:39

Simon

19,658
27
149
217

answered Nov 23 '15 at 20:08

Midhun MP

103,496
31
153
200

OK agreed and made the adjustment - well spotted, but its still not working. I have posted my ormlite_config code as well. – Simon Nov 23 '15 at 20:28
@Simon: The query is working fine for me!!! Are you checking the same database file were these changes happening ? – Midhun MP Nov 24 '15 at 07:49
OK . Your code is actually correct but the method call should change to db.execSQL(). rawQuery does not delete anything unless you call moveToFirst() according to this thread which is highly confusing: http://stackoverflow.com/questions/6337764/android-delete-query – Simon Nov 24 '15 at 17:39
@Simon: Thanks for the comment. It is a valuable information. Thank you :) Happy coding !!! – Midhun MP Nov 24 '15 at 17:43

Sqlite query to keep the last 5 searches on android

1 Answers1