Android SQLiteDatabase at item from top

Question

I would like all new items to be added at the top, ie, if ROW-ID 1,2,3 are taken and I add a new item, it should be added at position 1, and rest should be shifted. But right now when I add an item, it gets added at ID 4 (ie at the back).

Method

Temp_Cover_StoryBaseList_DatabaseHandler db = new Temp_Cover_StoryBaseList_DatabaseHandler(context, currentUsername);
db.addStory(pointer, filePath);
db.close();

_

    // Creating Tables
    @Override
    public void onCreate(SQLiteDatabase db) 
    {
        String CREATE_STORIES_TABLE 
                = "CREATE TABLE " + TABLE_TEMP_BASESTORY_LIST 
                + "(" 
                + KEY_ID            + " INTEGER PRIMARY KEY,"
                + KEY_POINTER       + " STRING,"
                + KEY_URI           + " STRING"
                + ")";

        db.execSQL(CREATE_STORIES_TABLE);
    }

_

    // Adding new Story
    public void addStory(String pointer, String filePath) 
    {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(KEY_POINTER,  pointer  );
        values.put(KEY_URI,      filePath );

        // Inserting Row
        long id = db.insert(TABLE_TEMP_BASESTORY_LIST, null, values);
        Log.d(TAG, "TempCover ID:" + Long.toString(id));

        db.close(); 
    }

_

public List<StoryData> getAllStories()
{
    List<StoryData> storyDataList = new ArrayList<StoryData>();

    // Select All Query
    String selectQuery = "SELECT  * FROM " + TABLE_STORIES_LIST;

    SQLiteDatabase db = this.getWritableDatabase();
    Cursor cursor = db.rawQuery(selectQuery, null);

    // looping through all rows and adding to list
    if(cursor !=null)
    {
        if (cursor.moveToFirst()) 
        {
            do 
            {
                ArrayList<Integer> coverList = new ArrayList<Integer>();
                coverList.add( cursor.getInt(4) );
                coverList.add( cursor.getInt(5) );

                StoryData sData = new StoryData
                        (
                        cursor.getInt(0),
                        cursor.getString(1),
                        cursor.getString(2),
                        cursor.getInt(3),
                        coverList,
                        Long.parseLong(cursor.getString(6)),
                        Boolean.parseBoolean(cursor.getString(7)),
                        cursor.getString(8)
                        );

                // Adding contact to list
                storyDataList.add(sData);

            } while (cursor.moveToNext());
        }
    }


    return storyDataList;
}

You cannot ! instead Why don't you retrieve data in descending order ? — Shree Krishna, Apr 10 '16 at 15:52
I've added the code for the method getAllStories() ; should I just use Collections.reverse(storyDataList); before returning the list; or is there another way? //@ShreeKrishna — Zen, Apr 10 '16 at 16:10
SQL tables are not ordered; a query without ORDER BY can return the rows in any order. So what do you mean with "top"? — CL., Apr 10 '16 at 16:13
@summers No, I don't prefer that. You can sort by It's id in descending order in custom way. — Shree Krishna, Apr 10 '16 at 16:15
Whats the method to sort it using ID? Could you share code? @ShreeKrishna — Zen, Apr 10 '16 at 16:16
Do you have id property in `StoryData` class to store the value of id field from database ? — Shree Krishna, Apr 10 '16 at 16:18
@summers I've added it as answer, feel free to ask if any confusion. — Shree Krishna, Apr 10 '16 at 16:26

score 0 · Answer 1 · answered Apr 10 '16 at 16:01

It appears that you are trying to change the autoincrement id of the SQL row. This is set automatically when you create a new row. You cannot, or at least should not, set the autoincrement id or depend on the physical order of rows in the table.

It is better practice to create a separate column that contains the desired order of rows as an integer. Then, when you query the table, you can sort the rows based on the integer in that column.

You could add the column as seen below:

@Override
    public void onCreate(SQLiteDatabase db) 
    {
        String CREATE_STORIES_TABLE 
                = "CREATE TABLE " + TABLE_TEMP_BASESTORY_LIST 
                + "(" 
                + KEY_ID            + " INTEGER PRIMARY KEY,"
                + KEY_POINTER       + " STRING,"
                + KEY_URI           + " STRING,"
                + KEY_ORDER         + " INTEGER"
                + ")";

        db.execSQL(CREATE_STORIES_TABLE);
    }

Now add the order when you insert the item. Just increment the value for order each time you insert a row:

ContentValues values = new ContentValues();
values.put(KEY_POINTER,  pointer  );
values.put(KEY_URI,      filePath );
values.put(KEY_ORDER,    order );

// Inserting Row
long id = db.insert(TABLE_TEMP_BASESTORY_LIST, null, values);

Nop. I just want to add new values at the top rather than the bottom — Zen, Apr 10 '16 at 16:11
Why you insist on add as first row? You can always display the rows in any order. Let it be any Id, you can display it on top or bottom — Arun Shankar, Apr 10 '16 at 16:14
Okay. But how do I use the KEY_ORDER property to reorder the ArrayList? — Zen, Apr 10 '16 at 16:23
You can use the Collections class: Collections.sort(arrayList, comparator); — Benstrom, Apr 10 '16 at 16:29

score 0 · Accepted Answer · answered Apr 10 '16 at 16:25

0

To say simply, You cannot do so,

Directly using Collections.reverse(storyDataList); may not work so I suggest getting all the data and sorting it with the ID, Like here using Comparable and creating custom class for sorting like

public class SortDataCompare implements Comparator<StoryData> {
    @Override
    public int compare(StoryData s1, StoryData s2) {
        return s1.getId().compareTo(s2.getId());
    }
}

And use The class like

Collections.sort(storyDataList, new SortDataCompare());

Then your storyDataList Will be sorted.

answered Apr 10 '16 at 16:25

Shree Krishna

8,474
6
40
68

Just a note. Using this return gives a "Cannot invoke compareTo(int) on the primitive type int" : http://stackoverflow.com/a/14178549/2534694 – Zen Apr 16 '16 at 09:32
@summers Thanks, I appreciate your note. – Shree Krishna Apr 17 '16 at 04:09

Android SQLiteDatabase at item from top

2 Answers2