CODEWITHSUNDEEP
androidsqlite
Andrew Quebe
Andrew Quebe

Reputation: 2293

Determine Position in Android SQLite Database

I want to be able to find the position of a string in a column. I have an app where the user adds to a list which forms a card and when they swipe the card, it deletes. I'm new to SQLite and I'm having a bad time trying to delete the items I want.

Here's what I have so far:

c2.moveToFirst();
String contentLabel = c2.getString(c2.getColumnIndex("Content"));
db.delete("Lists", "Content = '" + contentLabel + "'", null);

Now the problem with this is that when I swipe the card away, say, on the third card, the first card gets removed and the card that was swiped away moves to the top of the list.

Upvotes: 1

Views: 1840

Answers (4)

0x00h
0x00h

Reputation: 790

The most accurate way to delete the correct item from the Sqlite database is by specifying the unique ID of the item to be deleted.

Did you create your database with an _id column? If not you may be able to use Sqlite's default ROWID column instead - never tried it, but I believe that android automatically maps this to _id anyway. You must add the ID number to your loader's projection, so that you have this value in your cursor when you fill your card views with data.

Assuming that your list items - or cards - are using a custom layout, you should have an implementation of CursorAdapter which fills the cards with data by either recycling an existing view, or creating a new view for each list item that is displayed.

When you populate each list item with data, in the cursor adapter, you should also call listItemView.setTag(cursor.getString(cursor.getColumnIndex('_id'))); on the card view. This will store the the associated Sqlite row id number in the card view. Which I believe is a Long.

In your item dismissed handler, you can then call listItemViewToDismiss.getTag(); to learn the ID number that you want to delete from your database. Note that we've stored this as a String, but View.getTag() will return an Object, so this will need to be cast back to string, like so:

String storedRowId = (String) listItemViewToDismiss.getTag();

Once you have the database row ID easily reachable, the rest is simple:

db.delete(URI, "_id=?", new String[] { (String) cardViewToDismiss.getTag() });

This will delete only the rows which have the unique id specified in the list item's tag - if you're using SQLite's AUTOINCREMENT on your _id column - which I hope you are - then this should only ever delete one row, or zero rows if it has already been deleted by another process.

If your content provider can handle URIs to individual items, then I think you can also insert the full URI of the current item (with an appended ID) into the view's tag and then simply call 

db.delete( (String) viewToDismiss.getTag() );

... and let the content provider delete the correct item.

Some references

Cursor Adapter's getView method: [http://developer.android.com/reference/android/widget/Adapter.html] (See getView() on that page)

Setting tags: http://developer.android.com/reference/android/view/View.html#setTag(java.lang.Object)

disclaimer

It's been a while since I've done this, and I wrote that code from memory, so I hope someone will correct me if this is no longer the best practice for Android development, or if I've made a stupid error.

Upvotes: 2

giannisf
giannisf

Reputation: 2599

I thing you should use ormLite or greenDao and focus on your app and not fighting with sql.

Upvotes: 0

user3551614
user3551614

Reputation: 5

Maybe this might help?

String cardLabel;    

card.setOnSwipeListener(new Card.OnSwipeListener() {
        @Override
        public void onSwipe(Card card) {
            lable = "[code to get the swiped card's text]"
        }
    });
    db.delete("Lists", "Content = '" + cardLabel + "'",    null);

Basically just add some type of listener to get the text of the card as it is swiped and delete where the text equals that found by the listener. The issue with deleting by the text could be that the user might have two cards with the same text, maybe accidentally added it twice, but when they try to remove the duplicate, this would delete both.

Querying this way with the user defined text might also open you up to sql injection. I'm not sure how or if Android has any mechanisms to handle that, but it's worth thinking about. I agree with the others saying the proper way would be to search by ID. If you wanted to do an ID automatically, you could add something like this to the CREATE TABLE SQL statement in your DB helper.

   Lists._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +

Hope this was useful.

Upvotes: 0

Manuel Allenspach
Manuel Allenspach

Reputation: 12725

As your code is now, you always delete the first item. Move your cursor to the element you want to delete, with the method cursor.moveToPosition(int position).

Change your code to this:

// position = position of the "swiped card" (e.g. for the third card position is 3)
c2.moveToPosition(position - 1); 
String contentLabel = c2.getString(c2.getColumnIndex("Content"));
db.delete("Lists", "Content = '" + contentLabel + "'", null);

moveToPosition(int position) returns false if it fails to move to the position (e.g. there is no such position), so you may want to add some code to check this:

if (!c2.moveToPosition(position - 1)) {
    //failed to move!
}
String contentLabel = c2.getString(c2.getColumnIndex("Content"));
db.delete("Lists", "Content = '" + contentLabel + "'", null);

Upvotes: 0

Related Questions