Reputation: 10468
How do I get the row ID from a Cursor?
Upvotes: 17
Views: 24788
Reputation: 57103
As long as the TABLE is not defined using WITHOUT ROWID
you can get the ROWID (or should that be rowid see below for case) if you specify it as a column to be retrieved.
For example for a table with 3 defined columns (Names, Colour and Age) with no conventional _id column. The following rawQuery works and returns the ROWID :-
Cursor csr = db.rawQuery("SELECT Names, Colour, Age, ROWID FROM " + TABLE_NAME,null);
Note! that the column name in the cursor is lowercase as per :-
Note! ROWID in the SELECT SQL is case independent (e.g. RoWiD works).
Using
Cursor csr = db.rawQuery("SELECT * FROM " + TABLE_NAME,null);
WILL NOT return the ROWID (likewise for null for the columns when using the query
method).
Using query
(as opposed to rawQuery
) works in the same way, that is you need to specifiy ROWID (note alternatives to ROWID below) as a column to be retrieved e.g. :-
Cursor csr = db.query(TABLE_NAME,new String[]{
"OiD",
"Names",
"Colour",
"Age"
},null,null,null,null,null);
Instead of ROWID
, you can also use _ROWID_
or OID
(case independent) as the column name in the query noting that the column name in the resultant cursor is rowid i.e. it is lowercase.
Upvotes: 0
Reputation: 5336
return sqlite_db.query(table, new String[] { "rowid", "*" }, where, args, null, null, null);
In my case I have "rowid" in DataManager.FIELD_ID and this is SQLite identity column (each table in sqlite has this special kind of column), so I don't need any of my own custom unique id column in tables.
Upvotes: 2
Reputation: 11539
Cursor cursor = mySQLiteHelper.getReadableDatabase().query(TABLE_NAME, new String[] { "ROWID", "*" }, where, null, null, null, null);
then
long rowId = cursor.getLong(0);
Upvotes: 0
Reputation: 6592
I don't think the Cursor exposes this directly.
SQLiteDatabase.insert()
returns the row id of the newly inserted row. Or in Android the convention is that there is a column named "_id"
that contains the primary autoincrement key of the table. So cursor.getLong(cursor.getColumnIndex("_id"))
would retrieve this.
Upvotes: 14
Reputation: 119
Concerning the last sentence of Nic Strong's answer,following command didn't work for me. cursor.getColumnIndex("_id")
was still -1
cursor.getLong(cursor.getColumnIndex("_id"))
Maybe there's some other issue in my configuration that's causing the problem?
Personally I've taken to maintaining my own custom unique id column in each table I create; A pain, but it gets around this issue.
Upvotes: 3
Reputation: 41
I had this same problem where the column index for the primary key was reported as -1 (meaning it isn't there). The problem was that I forgot to include the _ID column in the initial SELECT clause that created the cursor. Once I made sure it was included, the column was accessible just like any of the others.
Upvotes: 4