How to find row number in database using primary key (Android SQLite Eclipse Java)

Question

I have a selection of sqlite entries displayed in a ListView, and I have their primary keys in a parallel array. When a user selects an item, the item is sent to an Editor activity (so the user can edit that entry). The way I programmed it, the Editor activity class retrieves the entry from the database using its row number (meaning position), using the Cursor's moveToPosition function.

How can I use an entry's primary key to find out its row number?

EDIT :
I have found a solution, wherein I first retrieve a selection of entries from the database, and then go back and retrieve the primary keys of those entries. I store the entries in one array, and the primary keys in another array.

Hopefully you'll be able to give clearer responses after seeing the code. I'm looking for a more elegant solution, although this seems to work.

Here is the relevant code.

CalendarMenu.java

@Override
public void onCreate(Bundle savedInstance){
  incomeManager = new SQLManagerIncome(this);
incomeManager.open();

  //This method (see below) gets all of the SQLite entries between two dates
    incomeArray = incomeManager.getDatedEntriesString(
            currentDay.getTimeInMillis(), 
              nextDay.getTimeInMillis());

  //This method (see below) gets the primary keys of all the entries retrieved 
  //in the previous method

    incomeArrayKeys = incomeManager.getDatedKeys(
           currentDay.getTimeInMillis(),
            nextDay.getTimeInMillis());
    incomeManager.close();

    // Sending the SQL info to the ListViews
    incomeAdapter = new ArrayAdapter<String>(this,
            android.R.layout.simple_list_item_1, incomeArray);
    dayList.setAdapter(incomeAdapter);
}


    @Override
public void onItemClick(AdapterView<?> listView, View view, int pos, long id) {
    Bundle basket = new Bundle();
    // we need to send the position of an entry inside the entire database to the
    // Editor class. So we'll use the primary key of an entry to retrieve its position.

    switch (listView.getId()) {
    case R.id.dayList:
        int entryPos = 0;
        //incomeArrayKeys holds all of the primary keys. It is a parallel
        //array to the array that is displayed in the listview 
        //(using a standard ArrayAdapter)
            incomeManager.open();
            entryPos = incomeManager.findPosByKey(incomeArrayKeys[pos]);
            incomeManager.close();

        basket.putLong("index", entryPos);
                    Class dayClass;
            try {
                dayClass = Class.forName("com.shulim.maaser.EditIncome");
                Intent dayIntent = new Intent(CalendarMenu.this, dayClass);
                dayIntent.putExtras(basket);
                startActivityForResult(dayIntent, 0);
            } catch (ClassNotFoundException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        break;
}

SQLManagerIncome.java

public String[] getDatedEntriesString(Long firstDay, Long lastDay){
    String[] columns = new String[] { KEY_ROWID, KEY_DESCRIPTION,
            KEY_AMOUNT, KEY_DATE, KEY_OWED };
    String selection = KEY_DATE + " >=" + firstDay + " AND " + KEY_DATE
            + "<" + lastDay;
    Cursor c = ourDatabase.query(INCOME_TABLE, columns, selection, null, null, null, null);
    int iDescription = c.getColumnIndex(KEY_DESCRIPTION);
    int iAmount = c.getColumnIndex(KEY_AMOUNT);
    ArrayList<String> keys = new ArrayList<String>();

    for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) {
        Long dateMillis = c.getLong(3);
        final Calendar cal = Calendar.getInstance();
        cal.setTimeInMillis(dateMillis);
        int mYear = cal.get(Calendar.YEAR);
        int mMonth = cal.get(Calendar.MONTH)+1;
        int mDay = cal.get(Calendar.DAY_OF_MONTH);
        String date = mMonth + "-" + mDay + "-" + mYear;
        String result = "Desc: " + c.getString(iDescription) 
                + ". Amount: $"
                + c.getString(iAmount) + ". Date: "
                + date + "\n";
        keys.add(result);
    }
    String[] stringDateKeys = new String[keys.size()];
    stringDateKeys = keys.toArray(stringDateKeys);
    c.close();
    return stringDateKeys;
}

public Integer[] getDatedKeys(Long firstDay, Long lastDay) {
    String[] columns = new String[] { KEY_ROWID, KEY_DESCRIPTION,
             KEY_AMOUNT, KEY_DATE };
    String selection = KEY_DATE + " >=" + firstDay + " AND " + KEY_DATE
            + "<" + lastDay;
    Cursor c = ourDatabase.query(INCOME_TABLE, columns, selection, null,
            null, null, null);
    int iRow = c.getColumnIndex(KEY_ROWID);
    ArrayList<Integer> keys = new ArrayList<Integer>();

    for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) {
        int result = c.getInt(iRow);
        keys.add(result);
    }

        Integer[] intKeys = new Integer[keys.size()];
        intKeys = keys.toArray(intKeys);
        c.close();
        return intKeys;
}

public int findPosByKey(Integer integer) {
    String[] columns = new String[] { KEY_ROWID, KEY_DESCRIPTION,
            KEY_AMOUNT, KEY_DATE };
    Cursor c = ourDatabase.query(INCOME_TABLE, columns, null, null,
            null, null, null);
    int iRow = c.getColumnIndex(KEY_ROWID);
    int position = 0;
    for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) {
        if (c.getInt(iRow) == integer) {
            break;
        }
        position++;
    }
    c.close();
    return position;
}

Please post some relevant code of what you have tried. But guessing that your ListView is connected to a cursorAdapter, you can pull the row id from your cursorAdapter in onItemClickListener. — Sam, Apr 26 '12 at 02:59

score 2 · Accepted Answer · answered Apr 26 '12 at 02:57

The way I programmed it, the Editor activity class retrieves the entry from the database using its row number (meaning position), using the Cursor's moveToPosition function.

You don't need to use the cursor's moveToPosition function. Instead, there's a much better solution: use the primary key that you already have available. The primary key allows a very simple and efficient SQL query that will return only the exact row you want. I don't know your column names, but an example SQL query to load this row would be

SELECT foo, bar, baz
FROM FooBar
WHERE pk = userSelectedPrimaryKey;

where userSelectedPrimaryKey is inserted into the statement via a query builder.

Of course this is just a rough sketch, but the point is that this query will return exactly one row, which is the only row with the primary key value that the user selected. This is simpler, and improves performance because there is no need to iterate through unnecessary rows.

Thanks for the answer. I don't know why I bothered getting the position in the database first if i could just find the entry using the primary key itself? Beginner's way of thinking :) — Shulim, Apr 29 '12 at 13:47
I'm a new developer & I also face such kind of problem. So can you please tell me how I store this foo,bar & baz. Thanks — Atiar Talukdar, Jan 13 '16 at 08:42

score 0 · Answer 2 · answered Apr 26 '12 at 05:51

You have mentioned that I have their primary keys in a parallel array.You have an option to retrieve the Primary Key of the item selected from this array by calling int pk= array[position] and passing this value to EditorActivity. In EditorActivity you can easily retrieve the row you want to edit by passing this primary key in where condition of your SQL query like what Adam Mihalcin has mentioned in his answer.

score 0 · Answer 3 · answered Apr 29 '12 at 08:22

I have found a solution, wherein I first retrieve a selection of entries from the database, and then go back and retrieve the primary keys of those entries. I store the entries in one array, and the primary keys in another array. See the code I that I posted in the question.

I'm looking for a more elegant solution, although this seems to work for now. I'm hoping for something more optimized.

How to find row number in database using primary key (Android SQLite Eclipse Java)

3 Answers3