Using SQLite in Android to get RowID

Question

I am creating an app to record assignments for different classes. Each class has it's own unique ID so the assignments listed for each class don't overlap into other classes. Here is a method I made to find the rowid for a certain class.

public int getIdFromClassName(String className){
    String query = "SELECT rowid FROM " + CLASSES_TABLE_NAME + " WHERE " + CLASSES_COLUMN_NAME + " = '" + className + "'";
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor res = db.rawQuery(query, null);
    return res.getColumnIndex("id");
}

However, this always returns a value of -1.

Any thoughts on what to change to return the proper rowid value?

EDIT:

@Override
public void onCreate(SQLiteDatabase db) {
    // TODO Auto-generated method stub
    db.execSQL(
            "create table " + CLASSES_TABLE_NAME + " " +
                    "(id integer primary key, " + CLASSES_COLUMN_NAME + " text)"
    );
    db.execSQL(
            "create table " + ASSIGNMENTS_TABLE_NAME + " " +
                    "(id integer primary key, " + ASSIGNMENTS_COLUMN_NAME + " text, " + ASSIGNMENTS_COLUMN_TOTAL_POINTS
                    + " INTEGER, " + ASSIGNMENTS_COLUMN_CLASS_ID + " INTEGER)");

}

have you tried this? return res.getString( res.getColumnIndex("id") ); — kulvinder
– kulvinder, Commented Jan 15, 2016 at 18:45
That would return a String, but I am looking for the id in the form of an int — Kinoscorpia
– Kinoscorpia, Commented Jan 15, 2016 at 18:48
it depends...data type you are using for id...i think you can also use getInt ..if its data type is integer — kulvinder
– kulvinder, Commented Jan 15, 2016 at 18:50
if still not resolved..you can check res.moveToFirst() method — kulvinder
– kulvinder, Commented Jan 15, 2016 at 18:55
if you dont know what columns your Cursor holds call DatabaseUtils.dumpCursor — pskink
– pskink, Commented Jan 15, 2016 at 19:03

Febin Mathew · Accepted Answer · 2018-06-19 07:29:02Z

2

Try this query below to create a new column name rowID:

Cursor cursor= db.rawQuery("SELECT *,"+CLASSES_TABLE_NAME +".rowid AS rowID"+" FROM "+CLASSES_TABLE_NAME , null);

And after this query you can fetch the real rowid from the rowID column :

while (cursor.moveToNext()) {
        long chatRow=cursor.getLong(
                cursor.getColumnIndexOrThrow("rowID"));
}

answered Jun 19, 2018 at 7:29

Febin Mathew

1,0411 gold badge12 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

CL. · Accepted Answer · 2016-01-15 20:00:05Z

The column returned by your query is called rowid, so you will not find a column called id.

Ensure that you use the same column name in the query and in the call to getColumnIndex.

And the index of this column is always zero; you also need to read the actual value from the column:

int colIndex = res.getColumnIndexOrThrow("rowid"); // = 0
if (res.moveToFirst()) {
    return res.getInt(colIndex);
} else {
    // not found
}

However, Android has an helper function that makes it much easier to read a single value:

public int getIdFromClassName(String className){
    String query = "SELECT rowid" +
                   " FROM " + CLASSES_TABLE_NAME +
                   " WHERE " + CLASSES_COLUMN_NAME + " = ?;";
    SQLiteDatabase db = this.getReadableDatabase();
    return DatabaseUtils.longForQuery(db, query, new String[]{ className });
}

Nick H · Accepted Answer · 2016-01-15 18:51:47Z

0

res.getColumnIndex("id") is going to get you the column index of the column with the name "id". and since you are getting -1, it cant find it.. are you sure your id column is not "_id"?

To get this to work you should do something like this..

res.getLong(res.getColumnIndex("_id"));

Cursor.getColumnIndex()

Cursor.getLong()

(I would recommend you use getLong() rather than getInt() because SQLite database ID's can get larger than int's).

answered Jan 15, 2016 at 18:51

Nick H

9,0029 gold badges44 silver badges66 bronze badges

4 Comments

Kinoscorpia Over a year ago

That returns the exeption: android.database.CursorIndexOutOfBoundsException: Index -1 requested, with a size of 1

Nick H Over a year ago

@Kinoscorpia What is the name of your ID column? It will return -1 if it cant find "id" or "_id". EDIT: Judging by the SQL, the name of your column is "rowid" correct? so try res.getLong(res.getColumnIndex("rowid"));

Kinoscorpia Over a year ago

Added the onCreate method

Nick H Over a year ago

You need to check your SQL statement.. it should be "SELECT id FROM " + CLASSES_TABLE_NAME + " WHERE " + CLASSES_COLUMN_NAME + " = '" + className + "'";

kulvinder · Accepted Answer · 2016-01-15 19:43:31Z

0

You can try this:

public int getIdFromClassName(String className){
String query = "SELECT id FROM " + CLASSES_TABLE_NAME + " WHERE " + CLASSES_COLUMN_NAME + " = '" + className + "'";
SQLiteDatabase db = this.getReadableDatabase();
Cursor res = db.rawQuery(query, null);
int id=-1;
If(res!=null&&res.moveToFirst())
id=res.getInt(res.getColumnIndex("id"));
return id;
}

edited Jan 15, 2016 at 19:43

answered Jan 15, 2016 at 19:19

kulvinder

5395 silver badges17 bronze badges

Comments

marc_s · Accepted Answer · 2016-02-14 15:56:43Z

public int getIdFromClassName(String className) {
        String query = "SELECT rowid FROM " + CLASSES_TABLE_NAME + " WHERE "
                + CLASSES_COLUMN_NAME + " = '" + className + "'";
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor res = db.rawQuery(query, null);
        if (res != null) {
            if (res.moveToFirst()) {
                do {
                    return Integer.parseInt(res.getString(res.getColumnIndex("id"))); // if your column name is rowid then replace id with rowid
                } while (res.moveToNext());
            }
        } else {
            Toast.makeText(context, "cursor is null", Toast.LENGTH_LONG).show();
        }
    }

Check your Cursor if its null check your Log that your table is created successfully and if its not null make sure your table has column id in it.

Collectives™ on Stack Overflow

Using SQLite in Android to get RowID

5 Answers 5

Comments

Comments

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related