How to add previous data to new data from saved ListView?

Question

I am making a budge calculator using SQL in Android Studio. Users put in date of purchase, name of item and value of item. I used SQL to save each "user input" into a new ListView.

I want my code to access the previous cursor to get the value, add it to the newly-typed data and calculate the cumulative total:

[10.01.22]	3.75	Coffee
[10.02.22]	5.00	Pencil
[10.03.22]	10.00	Ring

So it shows total money spent (but it only saves the ListView and does not save the total value when the app is closed).

    String queryString = "SELECT * FROM " + CATEGORY_VALUE;

    SQLiteDatabase db = this.getReadableDatabase();

    // cursor is the result from the SQlite
    Cursor cursor = db.rawQuery(queryString, null);
    Cursor cursor2 = db.rawQuery(queryString, null);
    Cursor cursor3 = db.rawQuery(queryString, null);

    String categoryValue;
    String categoryName;
    String categoryInfo;
    String categoryDate;
    String categoryTotal;

    if (cursor.moveToFirst()) {
        //if ther eare results loop through the results and creat the new customer results for each row
        // insert it into the return list and be able to make this function succeed
        do {

            categoryValue = cursor.getString(1);
            categoryName = cursor.getString(2);
            categoryInfo = cursor.getString(3);
            categoryDate = cursor.getString(4);
            //cursor3 = cursor;                        (I was trying to convert it to float,
            //cursor3.moveToPrevious();                 sum the previous numbers and put it
            //lastCategory = cursor3.getString(5);       back to string)
            //lastCate_f = Float.valueOf(lastCategory);   
            categoryTotal = cursor.getString(5);
            //thisCate_f = Float.valueOf(categoryTotal);  
            //totalCate_f = lastCate_f + thisCate_f;

            categoryTotal = Float.toString(totalCate_f);

            ValueModel newValue = new ValueModel(categoryDate, categoryValue, categoryInfo, categoryName, categoryTotal);
            returnList.add(newValue);
        } while(cursor.moveToNext());

    } else {
        //do not add anything to the list.
    }

    // close both things
    cursor.close();
    db.close();

    return returnList;
}

CATEGORY_VALUE is the name of my table.

When I run the app and type random examples in it actually sums up the total! However, when I close and re-open the app the ListView is still there but the total starts from scratch.

[10.01.22] 3.75 Coffee 3.75
[10.02.22] 5.00 Pencil 8.75
[I turned off the app and re-launch it]
[10.03.22] 10.00 Ring 10.00

Answer 1

There is likely no need to do this the way that you are trying, rather you could do this all in a single insert e.g.:-

INSERT INTO category_value (categorydate,categoryvalue,categoryname,categoryinfo,categorytotal)
    VALUES(
        '10.01.22', /* The date */
        '3.75', /* the value */
        'Coffee', /* The name*/
        'hot drink', /* The info */
        CAST('3.75' /* SAME VALUE AS the value !!!IMPORTANT!!!*/ AS REAL) +
        COALESCE( /* just in case null (e.g. when inserting the very first row */
            (
                SELECT CAST(categorytotal AS REAL) 
                FROM category_value 
                /* RE ORDER BY */
                /* NOTE assumes 1 row per date */
                    /* could use ORDER BY rowid DESC as an alternative IF ALWAYS INSERTING a later date*/
                    /* Really dates should be in a recognised format see https://www.sqlite.org/lang_datefunc.html */ 
                ORDER BY substr(categorydate,7,2)||substr(categorydate,1,2)||substr(categorydate,4,4) DESC
                LIMIT 1
            )
        ,0)
    )
;

As a demo, consider the following which is used to insert all 4 four rows in progression (i.e. run the same query but with different values):-

/* Preapre the demo environment (tables) */
DROP TABLE IF EXISTS category_value;
CREATE TABLE IF NOT EXISTS category_value (id INTEGER PRIMARY KEY, categoryvalue TEXT, categoryname TEXT, categoryinfo TEXT, categorydate TEXT, categorytotal TEXT DEFAULT 0);


INSERT INTO category_value (categorydate,categoryvalue,categoryname,categoryinfo,categorytotal)
    VALUES(
        '10.01.22', /* The date */
        '3.75', /* the value */
        'Coffee', /* The name*/
        'hot drink', /* The info */
        CAST('3.75' /* SAME VALUE AS the value !!!IMPORTANT!!!*/ AS REAL) +
        COALESCE( /* just in case null (e.g. when inserting the very first row */
            (
                SELECT CAST(categorytotal AS REAL) 
                FROM category_value 
                /* RE ORDER BY */
                /* NOTE assumes 1 row per date */
                    /* could use ORDER BY rowid DESC as an alternative IF ALWAYS INSERTING a later date*/
                    /* Really dates should be in a recognised format see https://www.sqlite.org/lang_datefunc.html */ 
                ORDER BY substr(categorydate,7,2)||substr(categorydate,1,2)||substr(categorydate,4,4) DESC
                LIMIT 1
            )
        ,0)
    )
;

INSERT INTO category_value (categorydate,categoryvalue,categoryname,categoryinfo,categorytotal)
    VALUES(
        '10.02.22',
        '5.00',
        'Pencil',
        'Writing and Drawing implement',
        CAST('5.00' AS REAL) +
        COALESCE(
            (
                SELECT CAST(categorytotal AS REAL) 
                FROM category_value 
                ORDER BY substr(categorydate,7,2)||substr(categorydate,1,2)||substr(categorydate,4,4) DESC
                LIMIT 1
            )
        ,0)
    )
;

INSERT INTO category_value (categorydate,categoryvalue,categoryname,categoryinfo,categorytotal)
    VALUES(
        '10.03.22',
        '10.00',
        'Ring',
        'flat round thing',
        CAST('10.00' AS REAL) +
        COALESCE(
            (
                SELECT CAST(categorytotal AS REAL) 
                FROM category_value 
                ORDER BY substr(categorydate,7,2)||substr(categorydate,1,2)||substr(categorydate,4,4) DESC
                LIMIT 1
            )
        ,0)
    )
;

INSERT INTO category_value (categorydate,categoryvalue,categoryname,categoryinfo,categorytotal)
    VALUES(
        '10.04.22',
        '20.00',
        'new thing',
        'that would be telling',
        CAST('20.00' AS REAL) +
        COALESCE(
            (
                SELECT CAST(categorytotal AS REAL) 
                FROM category_value 
                ORDER BY substr(categorydate,7,2)||substr(categorydate,1,2)||substr(categorydate,4,4) DESC
                LIMIT 1
            )
        ,0)
    )
;
SELECT * FROM category_value;
/* Cleanup demo environment */
DROP TABLE IF EXISTS category_value;

The result output (from the SELECT) being:-

• i.e. there would be no need for virtually all of your code just the insert (based upon the INSERT SQL above but catering for passing values instead of the hard coded values in the demo) and then building the new list after the insert.

Applied on Android

connverting the above into an insert method (in the database helper and assuming that db is an SQLiteDatabase member variable that has been instantiated) the:-

public void insert(float value, String name, String info, String date) {
    String sql="INSERT OR IGNORE INTO " + CATEGORY_VALUE +
            "(" +
            COLUMN_VALUE +
            "," +COLUMN_NAME +
            "," + COLUMN_INFO +
            "," + COLUMN_DATE +
            "," + COLUMN_TOTAL +
            ") " +
            "VALUES(" +
            "? /* The Value */," +
            "? /* the name */," +
            "? /* The info */," +
            "? /* The date */," +
            "CAST(? /* SAME VALUE AS the value !!!IMPORTANT!!!*/ AS REAL) +" +
            "COALESCE( /* just in case null (e.g. when inserting the very first row */" +
            "(" +
            "SELECT CAST(" + COLUMN_TOTAL + " AS REAL) " +
            "FROM " + CATEGORY_VALUE + " " +
            "ORDER BY substr(" + COLUMN_DATE + ",7,2)||substr(" + COLUMN_DATE + ",1,2)||substr(" + COLUMN_DATE + ",4,4) DESC " +
            "LIMIT 1" +
            ")" +
            ",0)" +
            ")" +
            ";";
    db.execSQL(sql,new Object[]{value,name,info,date,value});
}

You could then have, to demonstrate the insert method use:-

public class MainActivity extends AppCompatActivity {

    DBHelper dbHelper;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        dbHelper = DBHelper.getInstance(this);
        dbHelper.insert(3.5F,"Coffee","Hot Drink","10.01.22");
        dbHelper.insert(5.00F,"Pencil","Writing and Drawing Implement","10.02.22");
        dbHelper.insert(10.00f,"Ring","Flat, circular object","10.03.22");
        dbHelper.insert(20.00F,"New Thing","That would be telling","10.04.22");
    }
}

The result (via AppInspection) being a database that has:-

The full DBHelper class used being:-

class DBHelper extends SQLiteOpenHelper {

    public static final String DATABASE_NAME = "the_database.db";
    private static final int DATABASE_VERSION = 1;
    public static final String CATEGORY_VALUE = "cv";
    public static final String COLUMN_ID = BaseColumns._ID;
    public static final String COLUMN_VALUE = "categoryValue";
    public static final String COLUMN_NAME = "categoryName";
    public static final String COLUMN_INFO = "categoryInfo";
    public static final String COLUMN_DATE = "categoryDate";
    public static final String COLUMN_TOTAL = "categoryTotal";
    private static final String CV_CRT_SQL = "CREATE TABLE IF NOT EXISTS " + CATEGORY_VALUE + "(" +
            COLUMN_ID + " INTEGER PRIMARY KEY" +
            "," + COLUMN_VALUE + " REAL" +
            "," + COLUMN_NAME + " TEXT" +
            "," + COLUMN_INFO + " TEXT" +
            "," + COLUMN_DATE + " TEXT" +
            "," + COLUMN_TOTAL + " REAL" +
            ");";

    private SQLiteDatabase db;

    private DBHelper(Context context) {
        super(context,DATABASE_NAME,null,DATABASE_VERSION);
        db = this.getWritableDatabase(); /* readable database is 90%+ writeable anyway */
    }
    private volatile static DBHelper instance;
    public static DBHelper getInstance(Context context) {
        if (instance==null) {
            instance = new DBHelper(context);
        }
        return instance;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CV_CRT_SQL);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int i, int i1) {

    }

    public void insert(float value, String name, String info, String date) {
        String sql="INSERT OR IGNORE INTO " + CATEGORY_VALUE +
                "(" +
                COLUMN_VALUE +
                "," +COLUMN_NAME +
                "," + COLUMN_INFO +
                "," + COLUMN_DATE +
                "," + COLUMN_TOTAL +
                ") " +
                "VALUES(" +
                "? /* The Value */," +
                "? /* the name */," +
                "? /* The info */," +
                "? /* The date */," +
                "CAST(? /* SAME VALUE AS the value !!!IMPORTANT!!!*/ AS REAL) +" +
                "COALESCE( /* just in case null (e.g. when inserting the very first row */" +
                "(" +
                "SELECT CAST(" + COLUMN_TOTAL + " AS REAL) " +
                "FROM " + CATEGORY_VALUE + " " +
                "ORDER BY substr(" + COLUMN_DATE + ",7,2)||substr(" + COLUMN_DATE + ",1,2)||substr(" + COLUMN_DATE + ",4,4) DESC " +
                "LIMIT 1" +
                ")" +
                ",0)" +
                ")" +
                ";";
        db.execSQL(sql,new Object[]{value,name,info,date,value});
    }
}