从 SQLite Column 中获取所有数字字符串并获取总和计算 [英] Get all numeric strings from SQLite Column and get total sum calculation

问题描述

我是 Android 和 SQLite 的新手.我在 SQLite 中有一个只有数字的列AMOUNT"，我可以在 ListView 中显示它.但是我无法以任何我理解的方式将它们全部添加并显示在 TextView 中.

I am new in Android and in SQLite. I have a column "AMOUNT" of just numbers in SQLite which I am able to show in a ListView. But I can't get any way that I understand to add them all and show in a TextView.

这里是数据库助手

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseHelper extends SQLiteOpenHelper {

    private static final String DATABASE_NAME = "people.db";
    private static final String TABLE_NAME = "people_table";
    private static final String COL1 = "ID";
    private static final String COL2 = "DATE";
    private static final String COL3 = "DESCRIPTION";
    private static final String COL4 = "AMOUNT";


    DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, 1);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String createTable = "CREATE TABLE " + TABLE_NAME + " (ID INTEGER PRIMARY KEY AUTOINCREMENT, " +
                " DATE TEXT, DESCRIPTION TEXT, AMOUNT TEXT)";
        db.execSQL(createTable);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
        onCreate(db);
    }

    public boolean addData(String inc_date, String inc_description, String inc_amount){
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put(COL2, inc_date);
        contentValues.put(COL3, inc_description);
        contentValues.put(COL4, inc_amount);

        long result  = db.insert(TABLE_NAME, null, contentValues);

        return result != -1;
    }

    public Cursor showData(){
        SQLiteDatabase db = this.getWritableDatabase();
        return db.rawQuery("SELECT * FROM " + TABLE_NAME, null);
    }

    public boolean updateData(String id, String name, String email, String tvShow){
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put(COL1,id);
        contentValues.put(COL2,name);
        contentValues.put(COL3,email);
        contentValues.put(COL4,tvShow);
        db.update(TABLE_NAME, contentValues, "ID = ?", new String[] {id});
        return true;
    }

    public Integer deleteData(String id){
        SQLiteDatabase db = this.getWritableDatabase();
        return db.delete(TABLE_NAME, "ID = ?", new String[] {id});
    }

}

这是活动

import android.database.Cursor;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.widget.ArrayAdapter;
import android.widget.ListAdapter;
import android.widget.ListView;
import android.widget.TextView;

import java.util.ArrayList;

public class IncPag extends AppCompatActivity {

    DatabaseHelper peopleDB;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.inc_pag);
        all_inc_dat();
        amo_sum();
    }

    private void all_inc_dat(){
        TextView inc_data=findViewById(R.id.tex_inc);
        ListView dat_col=findViewById(R.id.dat_col);
        ListView des_col=findViewById(R.id.des_col);
        ListView amo_col=findViewById(R.id.amo_col);
        peopleDB=new DatabaseHelper(this);

        Cursor data = peopleDB.showData();

        if (data.getCount() == 0) {
            inc_data.setText(R.string.no_data_found);
            return;
        }
        ArrayList<String> dat_lis = new ArrayList<>();
        ArrayList<String> des_lis = new ArrayList<>();
        ArrayList<String> amo_lis = new ArrayList<>();

        while (data.moveToNext()) {
            dat_lis.add(data.getString(data.getColumnIndex( "DATE")));
            des_lis.add(data.getString(data.getColumnIndex( "DESCRIPTION")));
            amo_lis.add(data.getString(data.getColumnIndex( "AMOUNT")));

        }

        ListAdapter dat_ada = new ArrayAdapter<>(this, android.R.layout.simple_list_item_1, dat_lis);
        dat_col.setAdapter(dat_ada);
        ListAdapter des_ada = new ArrayAdapter<>(this, android.R.layout.simple_list_item_1, des_lis);
        des_col.setAdapter(des_ada);
        ListAdapter amo_ada = new ArrayAdapter<>(this, android.R.layout.simple_list_item_1, amo_lis);
        amo_col.setAdapter(amo_ada);
    }

    private void amo_sum(){
        TextView tv_sum=findViewById(R.id.tv_sum);
        tv_sum.setText(amount total?);
    }
}

如果在数据库助手中更容易计算，那么向我展示一个 SQLite 解决方案.或者如果进入listview后更容易进行计算

If its easier to calculate within the Database helper then show me a SQLite solution. or if its easier to do calculation after getting on listview

推荐答案

Solution Part 1

您可以将以下方法添加到数据库助手中

Solution Part 1

You could add the following method to the database helper

public long getAmountSum(){
    long rv = 0;
    String[] columns = new String[]{"sum(" + COL4 + ")"};
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor csr = db.query(TABLE_NAME,columns,null,null,null,null,null);
    if (csr.moveToFirst()) {
        rv = csr.getLong(0);
    }
    csr.close();
    return rv;
}

• 请注意，如果 AMOUNT 列中的值不是有效整数，则该行的值为 0.

  • Note if a value in the AMOUNT column is not a valid integer then it will be given a value of 0 for that row.

    这等同于运行查询 SELECT sum(AMOUNT) FROM people_table; 但使用 SQLiteDatabase query 便捷方法而不是使用 rawQuery 方法.

    This equates to running the query SELECT sum(AMOUNT) FROM people_table; but uses the SQLiteDatabase query convenience method as opposed to using the rawQuery method.

    • 与使用 rawQuery 方法相反，在可以使用便利方法的情况下使用它们被认为是更好的做法.
    • 在每次使用时使用单一来源的表和列名称而不是硬编码列名称也被认为是更好的做法(因此使用 COL4 而不是 "AMOUNT").
    • 虽然使用硬编码的列偏移量(例如上面的 0)，但上面是一个例外，其中实际的列名称(即 sum(AMOUNT))，因为它是从底层生成的 SQL 派生出来的，编码它可能比编码列偏移量(第一个/唯一的列，所以它总是为 0)更成问题.

    如果表中没有行，则返回 0.

    If there are no rows in the table then 0 will be returned.

    上述内容可以与修改后的 amo_sum 方法结合使用，如下所示:-

    The above could be used in conjunction with an amended amo_sum method as per :-

    private void amo_sum(){
        TextView tv_sum=findViewById(R.id.tv_sum);
        tv_sum.setText(String.valueOf(peopleDB.getAmountSum));
    }
    

    • 请注意，以上是原则性代码，尚未经过测试，因此可能包含一些错误.

    这篇关于从 SQLite Column 中获取所有数字字符串并获取总和计算的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！