How get a specific row where value is updated in android

I am using SQLite database. There is continuous update coming in to table, each time 1 or 2 row values get updated.

For example: Now there are 20 rows in TABLE1 some of the rows values gets updated..

Can we know which rows got updated in TABLE1 ?

I have solution but the performance matters to me. Cursor registers data-observer with a select query that selects all rows, when there is an update I retrieve entire 20 rows with updated values, do my coding. So if there are 100 rows I need to loop all which is a tedious task for low end devices.

Kindly assist with a solution, that how to retrieve the updated row in a table in Android using a cursor.

If it is possible for you to alter the table then add a new column 'modified_time' of type TIMESTAMP. Update this column whenever a row is updated.

Now when you want to know which rows got updated, run this SQL query:

select _id from table2 where modified_time = (select max(modified_time) from table2)

The result of this query gives the id of all rows which got updated latest.

Edit: As you have mentioned in your comment "... server pushes the data to table with changed values.i need take the update values and update to other table", the best option for you is to create a Trigger in the DB which will update the second table based on the updates on first table. Here is the approach.

Let's say server pushes the data to 'table1' and you want to monitor the rows updated. Create another table2 (or use existing) which will automatically get updated.

Now, create a Trigger as follows:

CREATE TRIGGER trigger1 AFTER UPDATE ON table1
 BEGIN
  update table2 SET modified_time = datetime('now') WHERE row_id = old._id;
 END;

Of course your table2 will differ but the idea is that you can insert or update rows in table2 automatically using trigger. You may refer these SO posts for more details:

Does sqlite3 support a trigger to automatically update an 'updated_on' datetime field?

Using SQLite Trigger to update "LastModified" field

Sample Application: I have created a sample application to demonstrate the use of Trigger in an Android application based on the scenario you have described. You can download the Eclipse project here - trigger_example_eclipse_project.zip

In brief, the sample application has a product Table which receives updates on product price. So we create another updates Table which will automatically get updated based on the updates on product Table through a Trigger.

There is an UI to simulate updates on product Table by an EditText field and a Button. And to display the recent updates we have a ListView wich uses a Cursor to fetch data from updates Table.

Here some relevant code snippet from DbHelper.java

public class DbHelper extends SQLiteOpenHelper {

    public static final String TABLE1 = "product";
    public static final String TABLE2 = "updates";

    public static final String COL_ID = "_id";
    public static final String COL_NAME = "name";
    public static final String COL_PRICE = "price";
    public static final String COL_MODIFIED_TIME = "modified_time";

    public static final String KINDLE = "Kindle";

    private static final String SQL_CREATE_TABLE1 = 
            "CREATE TABLE product (_id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, price INTEGER);";

    private static final String SQL_CREATE_TABLE2 = 
            "CREATE TABLE updates (_id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, price INTEGER, modified_time TIMESTAMP);";

    private static final String SQL_CREATE_TRIGGER = 
            "CREATE TRIGGER price_update AFTER UPDATE OF price ON product"+
            "   BEGIN" +
            "       INSERT INTO updates(name,price,modified_time) VALUES(old.name,new.price,datetime('NOW'));"+
            "   END;";

    public DbHelper(Context context) {
        super(context, "sampledb", null, 1);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(SQL_CREATE_TABLE1);
        db.execSQL(SQL_CREATE_TABLE2);
        db.execSQL(SQL_CREATE_TRIGGER);

        ContentValues cv = new ContentValues();
        cv.put(COL_NAME, KINDLE);
        cv.put(COL_PRICE, 99);
        db.insert(TABLE1, null, cv);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int arg1, int arg2) {
    }

}