How to get total amount value from sqlite database

0

I am trying to create an application for the billing system. The values are inserted into the database and retrieved successfully. Also, I need the total amount from the database.

database helper class

public Cursor gettotal()
    {
        SQLiteDatabase database = this.getReadableDatabase();
        Cursor cursor =database.rawQuery("select * from '"+PRODUCTS+"' ;", null);
        Log.d(TAG, "gettotal: "+cursor.getCount());
        return cursor;

    }

total calculation

private void getTotal() {
int m=0;
        Cursor cursor = db.gettotal();
        if(cursor.getCount()==0)
        {
            Toast.makeText(getApplicationContext(),"No message is available", Toast.LENGTH_LONG).show();
        }
        else
        {
            for(int k = 0; k<=cursor.getCount();k++) {
                m += (int) Integer.parseInt(String.valueOf(cursor.getColumnIndex("amount")));
            }
            i=m;
            total_amount.setText(m);
        }
}

my log cat

D/MainActivity: getTotal: -1
E/uetooth_embade: Invalid ID 0x00000008.
D/AndroidRuntime: Shutting down VM
E/AndroidRuntime: FATAL EXCEPTION: main
    Process: com.example.bluetooth_embaded, PID: 14063
    android.content.res.Resources$NotFoundException: String resource ID #0x8
        at android.content.res.Resources.getText(Resources.java:360)
        at android.content.res.MiuiResources.getText(MiuiResources.java:97)
        at android.widget.TextView.setText(TextView.java:5837)
        at com.example.bluetooth_embaded.MainActivity.getTotal(MainActivity.java:100)
        at com.example.bluetooth_embaded.MainActivity.access$200(MainActivity.java:33)
        at com.example.bluetooth_embaded.MainActivity$5.onClick(MainActivity.java:181)
        at android.view.View.performClick(View.java:6616)
        at android.view.View.performClickInternal(View.java:6593)
        at android.view.View.access$3100(View.java:785)
        at android.view.View$PerformClick.run(View.java:25929)
        at android.os.Handler.handleCallback(Handler.java:873)
        at android.os.Handler.dispatchMessage(Handler.java:99)
        at android.os.Looper.loop(Looper.java:201)
        at android.app.ActivityThread.main(ActivityThread.java:6806)
        at java.lang.reflect.Method.invoke(Native Method)
        at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:547)
        at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:873)
I/Process: Sending signal. PID: 14063 SIG: 9
java
android
sqlite
android-sqlite
asked on Stack Overflow Aug 20, 2019 by Thangapandi • edited Aug 20, 2019 by forpas

2 Answers

2

Remove the single quotes around the table's name:

Cursor cursor = database.rawQuery("select * from " + PRODUCTS +";", null);

Single quotes are needed to wrap string literals.
Also replace your for loop (which has wrong upper limit) with a while loop:

private void getTotal() {
    int m=0;
    Cursor cursor = db.gettotal();
    if (cursor.getCount() == 0) {
        Toast.makeText(getApplicationContext(),"No message is available", Toast.LENGTH_LONG).show();
    } else {
        while (cursor.moveToNext()) {
            m += cursor.getInt(cursor.getColumnIndex("amount"));
        }
        total_amount.setText("" + m);
    }
}

I replaced:

String.valueOf(cursor.getColumnIndex("amount"))

which returns the index position of the column with:

cursor.getInt(cursor.getColumnIndex("amount"))

which returns the value of the column.
Also I replaced:

total_amount.setText(m);

which is the root of your problem, because m is an integer and considered as a resource and not text, with:

total_amount.setText("" + m);

Also, if all you want is the sum of the column amount you could execute this query:

Cursor cursor =database.rawQuery("select sum(amount) as total from " + PRODUCTS + ";", null);

so you only need to get the one and only row and column returned by this query and you don't need any loop to calculate the total.

answered on Stack Overflow Aug 20, 2019 by forpas • edited Aug 20, 2019 by forpas
2

Also, the error you are getting is because you are assigning an int value to the TextView instead of a string. The setText(int) overload of TextView attempts to find a string resource whose ID is equivalent to the int value you passed into setText(int) inside your APK bundle. Using the method you outlined, you need to first convert the int value to a String using String.parseInt().

As an unsolicited improvement, you can update your code to be more efficient and concise at the same time. You can perform the total amount calculation on the database side with the simple query:

SELECT SUM (amount) FROM PRODUCTS;

This is way more efficient because the database doesn't need to perform a lookup of all columns and all rows, return them to you, and then requiring you to manually iterate to calculate the total. Instead, the database will keep a running total while iterating over the columns during the execution of the initial query. Another bonus is you can perform complex filtering on the returned dataset using the full power of SQL in the form of WHERE and filter clauses.

Furthermore, I didn't see in your code, maybe you left it out but you never close your Cursor or SQLiteDatabase object. Always remember to close these objects once done with them to release resources.

answered on Stack Overflow Aug 20, 2019 by cincy_anddeveloper • edited Aug 20, 2019 by cincy_anddeveloper

User contributions licensed under CC BY-SA 3.0