Android SQLite Update Column

Question

User Case :

1. Select item by id, if its already present in the basket.
2. Check the numbers of this item.
3. If item numbers == 1 then delete this item from basket table and return 0.
4. If its greater than 1 then decrement it by one and update the table and return number-- .

Its not updating my "numbers" column, and silently passes through the execution and I am helpless nor can I post any stack trace here, but I am posting my code fragment, responsible for this job.

public int removeFromBasketasAnonymous(Long _id){

    Log.d("app : ", " _id = " + _id);
    int numbers = 0;
    try {
        database = openDatabaseInReadMode();
        Cursor cursor = database.rawQuery("select * from basket where basket._id=" + _id + ";", null);
        if (cursor != null) {
            cursor.moveToFirst();
            Log.d(APP, "GetCount = " + cursor.getCount());
            if (cursor.getCount() == 1) {
                //this item already present in basket
                numbers = cursor.getInt(1);
                Log.d(APP, " numbers  = " + numbers);
                Log.d(APP, "db id = " + cursor.getString(0));
                String[] columns = cursor.getColumnNames();
                for(String str : columns){
                    Log.d("APP ", " columns = "+str);
                }
                Log.d(APP, " id = " + _id);
                if (numbers == 1) {
                    //remove this row entry
                    cursor.close();

                    database.close();
                    database = openDatabaseInReadWriteMode();
                    database.beginTransaction();
                    String strSQL = "DELETE from basket where basket._id=" + _id;
                    try{
                        database.execSQL(strSQL);
                    }catch(Exception e){
                        e.printStackTrace();
                    }finally{
                        database.endTransaction();
                        database.close();
                    }
                    numbers--;

                } else {
                    //decrement this number by one
                    Log.d(APP, " number " + numbers);
                    numbers--;
                    Log.d(APP, " dcremented numbers = " + numbers);
                    cursor.close();
                    database.close();
                    database = openDatabaseInReadWriteMode();
                    database.beginTransaction();
                    try{
                        ContentValues data = new ContentValues();
                        data.put("numbers", numbers);
                        database.update("basket", data, "_id = " + _id, null);
                    }catch (Exception e){
                        e.printStackTrace();
                    }finally {
                        database.endTransaction();
                        database.close();
                    }
                }
            }

        }
    }finally{
        if(database != null){
            database.close();
        }
    }
    return numbers;
}


public SQLiteDatabase openDatabaseInReadMode() {
    File dbFile = context.getDatabasePath(DB_NAME);
    if (!isDataBaseExist()) {
        try {
            copyDatabase(dbFile);
        } catch (IOException e) {
            throw new RuntimeException("Error creating source database", e);
        }
    }
    /*Log.d("DB available", "path = " + dbFile.exists() + " path" + dbFile.getPath());*/
    /*Log.d("actual path ", "exists = " + isDataBaseExist());*/
    return SQLiteDatabase.openDatabase(dbFile.getPath(), null, SQLiteDatabase.OPEN_READONLY);
}


public SQLiteDatabase openDatabaseInReadWriteMode() {
    File dbFile = context.getDatabasePath(DB_NAME);
    if (!isDataBaseExist()) {
        try {
            copyDatabase(dbFile);
        } catch (IOException e) {
            throw new RuntimeException("Error creating source database", e);
        }
    }
    /*Log.d("DB available", "path = " + dbFile.exists() + " path" + dbFile.getPath());*/
    /*Log.d("actual path ", "exists = " + isDataBaseExist());*/
    return SQLiteDatabase.openDatabase(dbFile.getPath(), null, SQLiteDatabase.OPEN_READWRITE);
}

Regards, Shashank

Answer 1

How you can use database transaction in Android

1. If you want to start the transaction there is a method beginTransaction()
2. If you want to commit the transaction there is a method setTransactionSuccessful() which will commit the values in the database
3. If you had start the transaction you need to close the transaction so there is a method endTransaction() which will end your database transaction

Now there are two main points

1. If you want to set transaction successful you need to write
   setTransactionSuccessful() and then endTransaction() after beginTransaction()

2. If you want to rollback your transaction then you need to endTransaction() without committing the transaction by setTransactionSuccessful().