Android SQLite Cursor returning only one entry when trying to read all entries

Question

I am trying to read all entries from a SQLite database table. The problem is that the cursor returns with only one entry every time. I know that the table has around 30 entries already because when I try to save the entries again, I get this error for each entry:

android.database.sqlite.SQLiteConstraintException: UNIQUE constraint failed: list.id (code 1555)                                                                    
#################################################################
Error Code : 1555 (SQLITE_CONSTRAINT_PRIMARYKEY)
Caused By : Abort due to constraint violation.
(UNIQUE constraint failed: list.id (code 1555))
#################################################################

I have seen a couple of questions here about similar problems, but none of the solutions works for me. Here is what I have tried so far:

public class Table
{
    private static final String TABLE_NAME = "list";

    // Column names
    private static final String ID = "id";
    private static final String INDEX = "index";
    private static final String TITLE = "title";


    private static final String CREATE_STATEMENT = "CREATE TABLE IF NOT EXISTS " + TABLE_NAME +
            "(" + ID + " INTEGER PRIMARY KEY, " + TITLE + " TEXT, " + 
              INDEX + " INTEGER)";

    public static void create(SQLiteDatabase db)
    {
        db.execSQL(CREATE_STATEMENT);
    }

    public static void drop(SQLiteDatabase db)
    {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
    }

    public static void saveList(SQLiteDatabase db, ArrayList<MyObject> list)
    {
        for (MyObject obj : list)
        {
            long id = insert(db, obj);
        }
    }

    private static long insert(SQLiteDatabase db, MyObject obj)
    {
        final ContentValues values = new ContentValues();
        values.put(ID, obj.getId());
        values.put(INDEX, obj.getIndex());
        values.put(TITLE, obj.getTitle());
        return db.insert(TABLE_NAME, null, values);
    }

    public static ArrayList<MyObject> loadList(SQLiteDatabase db)
    {
        final ArrayList<MyObject> list = new ArrayList<>();
        Cursor cursor = db.rawQuery("SELECT * FROM " + TABLE_NAME, null);
        Log.d(TABLE_NAME, "Cursor size: " + cursor.getCount());
        if (cursor.moveToFirst())
        {
            do
            {
                final long id = cursor.getLong(cursor.getColumnIndexOrThrow(ID));
                final String title = cursor.getString(cursor.getColumnIndexOrThrow(TITLE));
                final int index = cursor.getInt(cursor.getColumnIndexOrThrow(INDEX));
                list.add(new MyObject(id, index, title);
            }
            while (cursor.moveToNext());
        }
        cursor.close();
        return list;
    }
}

I have also tried using cursor = db.query(TABLE_NAME, null, null, ..., null) but got the same results.

This is how I call the above functions from my SQLiteOpenHelper class:

public void saveList(ArrayList<MyObj> list)
{
    final SQLiteDatabase db = getWritableDatabase();
    Table.drop(db);
    Table.create(db);
    Table.saveList(db, list);
}

public ArrayList<MyObject> loadList()
{
    return Table.loadList(getReadableDatabase());
}

I have another table in the same database that has very similar code but works just fine. I have been trying to fix that for a while now, but I can't see what may be going on. Any help would be much appreciated

Answer 1

you have issue with your

obj.getId()

private static long insert(SQLiteDatabase db, MyObject obj)
{
    final ContentValues values = new ContentValues();
    values.put(ID, obj.getId()); // check whether you are updating this with different value or not
    values.put(INDEX, obj.getIndex());
    values.put(TITLE, obj.getTitle());
    return db.insert(TABLE_NAME, null, values);
}